Article Type: Reference Audience: Developers, IT Module: Edge

Microsoft SQL, often referred to as Microsoft SQL Server, is a relational database management system (RDBMS) developed by Microsoft. It is one of the leading database management systems in the world and is commonly used for managing and storing data in a wide range of applications and environments. Microsoft SQL Server uses a dialect of SQL (Structured Query Language) to interact with the database. The Edge Gateway can connect to a SQL Server database to read and write data as part of a Data Flow.

Connector type: Edge data connector — configured on the Edge Gateway to connect to a database (no physical device driver required).

Key Features of Microsoft SQL Server

1. Relational Database: Microsoft SQL is a relational database management system, which means it stores and manages data in structured tables with rows and columns. Tables can have relationships with each other, allowing for complex data structures.
2. SQL Support: Microsoft SQL Server supports the SQL language for querying, inserting, updating, and deleting data in a relational database.
3. Data Types: SQL Server supports a wide variety of data types, including numeric, character, date and time, binary, and custom-defined types.
4. Complex Queries: SQL Server supports complex queries, aggregation functions, and features like JOINs, subqueries, and the ability to group and aggregate data.
5. Stored Procedures and Functions: SQL Server allows the creation of stored procedures and functions, which are sets of SQL statements that can be executed by applications and triggers to perform specific tasks.
6. Transaction Control: Support for transaction control statements such as COMMIT, ROLLBACK, and SAVEPOINT, enabling the management of data integrity and consistency.
7. Concurrency Control: SQL Server handles multiple concurrent users accessing the database, ensuring data consistency and preventing conflicts between transactions.
8. Security: Robust security features, including user authentication, authorization, and data encryption. Users and roles can be defined with specific access permissions to data and database objects.
9. Performance Optimization: Query optimization features to improve query efficiency by selecting the best execution plan. Indexes and statistics are used to enhance query performance.
10. High Availability and Scalability: Features like clustering, replication, and Always On Availability Groups ensure high availability and scalability for mission-critical applications.
11. Business Intelligence: Integrated business intelligence (BI) features such as reporting, data analysis, and data mining, making it suitable for data warehousing and BI solutions.
12. Data Warehousing: Commonly used in data warehousing scenarios to store and analyze large volumes of data for reporting and decision-making.

Microsoft SQL Server is used in a wide range of applications, including web applications, enterprise software, business intelligence solutions, and more. It is known for its robustness, scalability, and extensive feature set, making it a popular choice for organizations with complex data management needs, especially those in the Microsoft technology ecosystem.

Configuration

• Server: The host name of the SQL server.
• Port: The port the SQL server is running on. Typically 1433.
• Database: The database to connect to.
• User: The user to connect to the server as.
• Password: The password for the connecting user.
• Encrypt: Whether the connection to the server is encrypted. Enabled by default.
• Trust Server Certificate: If invalid or self-signed certs should be trusted.

Functions

Query

Executes SQL against a database.

• Query: The SQL to run against the database. The string can be parameterized with @param.
• Parameters: The parameter values to be merged into the SQL to execute. This is an array of objects:
  • name: The name of the parameter.
  • dataType: The SQL data type of the parameter.
  • value (optional): The value to use for this parameter.
  • output (optional): If this is an output parameter or not.
  • length (optional): Used for char-like data types.
  • scale (optional): Used for some data types such as Decimal and Time types.
  • precision (optional): Used for some data types such as Decimal and Numeric.
• Procedure: If we are executing a stored procedure.

See Also

• MySQL
• IBM DB2 SQL
• Oracle SQL
• Edge Gateway Flows