What is SQL Server?

SQL Server is a relational database management system (RDBMS) developed by Microsoft, with capabilities to support transaction processing, business intelligence, and analytics applications. Originating from a collaboration between Microsoft, Sybase, and Ashton-Tate Corp. in the late 1980s, SQL Server has since expanded its features to become a comprehensive data management and analytics platform. It is designed to manage and query databases, primarily using Transact-SQL (T-SQL), a proprietary extension of SQL added by Microsoft.

The core component of SQL Server is the SQL Server Database Engine, which controls data storage, processing, and security. This engine is comprised of a relational engine that processes commands and queries, and a storage engine that manages database files, tables, and indexes. SQL Server's capabilities are not limited to traditional database services; it also includes a suite of business intelligence and analytics tools, like R Services, Machine Learning Services, and SQL Server Analysis Services, providing a robust environment for data professionals.

SQL Server is available in multiple editions tailored to different needs and scales of use. The Developer edition is designed for development and testing without a fee, while the Express edition is suited for small databases and is also free. For more feature-rich options, the Enterprise edition offers the full range of SQL Server's capabilities, and the Standard edition provides a partial feature set with some limits on processor cores and memory sizes. Additionally, Web and Business Intelligence editions are available for specific application scenarios. With its versatile deployment options, SQL Server can be installed on Windows or Linux systems, run within a Linux container, deployed on an Azure Virtual Machine, and integrated with various Azure services.

SQL Server Plugins for Excel

Master Data Services Add-in for Excel

The Master Data Services Add-in for Excel is a plugin designed exclusively for Windows environments. It facilitates the interaction with Master Data Services (MDS) by allowing users to load filtered lists of data from MDS directly into Excel. This integration enables users to manipulate and work with the data within the familiar Excel interface.

Data Publishing to MDS

After modifying data in Excel, the Master Data Services Add-in for Excel provides the capability to publish the data back to MDS. This step ensures that changes made in Excel are reflected in the central MDS repository, maintaining data consistency and integrity.

Prerequisites and Security Settings

To utilize the Master Data Services Add-in for Excel, ActiveX controls must be enabled. Additionally, Office Automation Security settings need to be configured to either Level 1, with macros enabled, or Level 2, where the application's macro security level is used.

Excel Integration with SQL Server

Microsoft Excel offers the ability to connect to an SQL Server database through the \"From SQL Server Database\" option found under the \"Get Data\" menu. For maintaining a stable and efficient connection between Excel and SQL Server, third-party software integration platforms such as Connect Bridge may be employed.

Enhanced Functions with Data Quality Services

When using the Master Data Services Add-in for Excel, Data Quality Services (DQS) can be leveraged to match data, which helps to prevent the occurrence of duplicate data entries in MDS. This ensures higher quality and consistency of the data managed within the Excel ecosystem.

Common Use Cases

• S

  
  Load filtered lists of data from Master Data Services into Excel
• S

  
  Publish data changes from Excel back to Master Data Services
• S

  
  Use the plugin to match data with Data Quality Services to prevent duplicates
• S

  
  Create and manage entities and attributes in Master Data Services through Excel
• S

  
  View and update data in Excel with security permissions enforced

Frequently Asked Questions

How do I enable the SQL Server plugin for Excel?

To enable the plugin, ActiveX controls must be enabled in Excel, and Office Automation Security must be set to enable macros.

What can I do with the SQL Server plugin for Excel?

With the plugin, you can load filtered lists of data from Master Data Services into Excel, work with the data as usual, create connections, load data, save shortcut queries, publish data back to MDS, and use Data Quality Services to match data and prevent duplicates.

What are the security requirements for using the SQL Server plugin in Excel?

The security level determines data access within the plugin, and a SQL login is required to validate connections for security.

Can I use the SQL Server plugin for Excel with any version of SQL Server?

The plugin is available for multiple SQL Server versions including 2016 SP2, 2017, 2019, and 2022.

Are there any limitations to be aware of when using the SQL Server plugin for Excel?

There are size limitations when interacting with the workbook in the MDS Excel add-in, and administrators need to be aware of the capabilities such as creating new entities and setting properties for loading and publishing data.