Microsoft SQL Server

Microsoft SQL Server is a relational database management system, or RDBMS, that supports a wide variety of transaction processing, business intelligence and analytics applications in corporate IT environments. It's one of the three market-leading database technologies, along with Oracle Database and IBM's DB2.

SQL Server is tied to Transact-SQL (T-SQL), an implementation of SQL from Microsoft that adds a set of proprietary programming extensions to the standard language.

Microsoft offers SQL Server in four primary editions that provide different levels of the bundled services. Two are free of charge a Developer edition for use in database development and testing, and an Express edition that can be used to run small databases with up to 10 GB.

For larger applications, Microsoft offers:

  • Enterprise edition delivers comprehensive high-end datacenter capabilities performance, virtualization, and business intelligence
  • Standard edition delivers basic data management and business intelligence database for departments and small organizations

Microsoft SQL Server are also supported in Azure SQL Database, a cloud database service built on the SQL Server Database Engine. Alternatively, users can run SQL Server directly on Azure, via a technology called SQL Server on Azure Virtual Machines. The VM offering is optimized for migrating or extending on-premises SQL Server applications to the cloud, while Azure SQL Database is designed for use in new cloud-based applications.

Microsoft Access and SQL Database Architectures

Sometimes, Microsoft Access applications are so successful that they exceed the capabilities of Access, and for this reason, you may have to migrate all or part of the MS Access application to SQL Server.

To upsize MS Access to SQL Server, we use ActiveX Data Objects (ADO) and Stored Procedures most of the time.

ADO with Microsoft Access and SQL Server

ADO gives developers a powerful, logical object model for programmatically accessing, editing, and updating data from a wide variety of data sources through OLE DB system interfaces. The most common usage of ADO is to query a table or tables in a relational database, retrieve and display the results in an application, and perhaps let users make and save changes to the data.

Stored Procedures

Stored procedures are a batch of SQL statements that can be executed. A benefit of stored procedures is that you can centralize data access logic into a single place. Stored procedures also have a security benefit in that you can grant execute rights to a stored procedure but the user will not need to have read/write permissions on the underlying tables.