Post image

Max Server Memory and SQL Server 2016 Service Pack 1

Everything changed for SQL Server Standard Edition on 16 November 2016, and how memory limits work. On that day, a slew of Enterprise Edition features made their way into editions across the board, including Express Edition and LocalDB. The memory limit of 128GB RAM applies only to the buffer pool (the 8KB data pages that[…]

Hidden

Temporal Tables and Hidden Period Columns

In my November 2015 post, An Introduction to Temporal Tables in SQL Server 2016 using a DeLorean, I wrote: The HIDDEN property is optional and will hide these columns from a standard SELECT statement for backward compatibility with our application and queries. You cannot apply the HIDDEN property to an existing column. It turns out[…]

Post image

Wait For Service Pack 1

Conventional wisdom tells us that when Microsoft releases a new version of any server product, we should wait until Service Pack 1 before deploying it to production. This hasn’t been true for a while now, since Microsoft recommended that Cumulative Updates for SQL Server carry the same confidence: SQL Server CUs are certified to the same levels[…]

Post image

How to make your T-SQL thirty percent faster

Last week, I mentioned a feature in SQL Server, and now in Azure SQL Database, called Memory-Optimized Table-Valued Parameters, which makes use of In-Memory OLTP structures to work around a dependency on tempdb, and thereby drastically improve performance. Short version: We can see more than double speed improvement by switching from temp tables to Memory-Optimized TVPs,[…]

Post image

The Data Migration Assistant

I’ve written quite a lot about Azure SQL Database recently, but that doesn’t mean I’ve forgotten about the on-premises version of SQL Server. What could be better than Microsoft announcing a new tool for upgrading to SQL Server 2016? The Data Migration Assistant (DMA) for SQL Server was announced on 26 August 2016. Data Migration Assistant[…]

Post image

Should I move my on-premises database to Azure SQL Database?

This week is a shorter post than last week, but it answers a very important question: Should I move my on-premises database to Azure SQL Database? The short answer is: It depends. Firstly, why do you want to move your database to the cloud? Is it a business requirement? A better way to ask this[…]

Post image

Basic Availability Groups in SQL Server 2016

One of the new features in SQL Server 2016 Standard Edition, which may appeal to users of the deprecated Database Mirroring feature, is Basic Availability Groups, a variation of Availability Groups found in the Enterprise Edition. At a high level, Availability Groups are an amalgamation of Database Mirroring and Windows Failover Clustering. The Windows Servers[…]

Modifying Temporal Tables – A Primer

This is part three of the Temporal Tables series. You can read parts one and two here. Last week I demonstrated how temporal tables in SQL Server 2016 work. If you have implemented a history table—populating it with triggers or stored procedures—it works the same way. This week, we are going to look at how[…]