Andy Baron November 2006 Applies to: Microsoft SQL Server 2005 Summary: One way to create applications that use Microsoft Office Access for creating user interfaces and that use Microsoft SQL Server for data storage is to link Office Access tables to SQL Server tables.This is the type of application created by using the SQL Server Migration Assistant for Office Access.We tested and identified the following configuration in SQL Server 2016 gave us the best results from performance, scale and supportability perspective.
In SQL Server 2016/2017, we can overcome this and enable workload to scale further with memory by converting temp tables, table variables for some of the high executed stored procedures and high touch tables to in-memory.The right candidates (tables or stored procedure) for in-memory tables can be discovered easily using the Transaction Performance Analysis Overview report in SSMS.In most scenarios, customers choose to add new hardware as opposed to upgrading their SQL Server since the general perception is that cost of upgrade is high.While before you go with this anecdotal assumption, it is important to assess and estimate the cost of upgrade.Later, you can test to change the DBCompat level to the latest level and use query store to identify the queries which have regressed.
Let me back the above theory and claim with some data as we recently put this approach to practice with one of our customer running Tier 1 mission critical workload on SQL Server 2008 R2.
SQL Server Upgrade Advisor gave multiple reasons to not upgrade while the team has done great job with DMA to help identify blockers for upgrade and also provide guidance on how to overcome them.
In addition, DMA assesses the databases against multiple compatibility levels in single run to provide a recommendation on which DBCompat would be a right fit for your database application.
We ran some application performance and stress tests adding more workload (22-25K Batch Requests/sec) first on SQL 2008/Windows 2008 R2 and later upgraded to SQL 2016/Windows Server 2016 to perform A/B testing keeping the hardware and application unchanged.
Following are the results of the A/B testing of application for the same workload running against SQL 2016 v/s SQL 2008.
This is where a tool like Data Migration Assistant (DMA) can be very valuable.