What SQL Server 2017 should have added

Looking at the current features being added to SQL Server 2017 there doesn’t appear to be anything to get overly excited about.
 
There’s nothing that makes you think ‘wow we need to upgrade NOW!’
 
From my perspective the following should be easy no brainers to get DBAs motivated to upgrade:
 
1. The ability to undo transactions from the transaction log / log backups. Geez, this has been around for years by 3rd parties. Surely it’s time to include it, just like native compressed backups (best thing since sliced bread).
 
2. Writable snapshots. We’ve got read-only snapshots so we are half way there. Just think of the space savings and ability to quickly use cloning for test/dev environments.
 
3. The ability to easily fail-over/fail-back log shipped databases. Man, how long has the good old reliable log shipping been around for and that dreaded pain of a fail-over test.
 
4. The ability to attach a database to an older version. Surely there could be a simple flag that says, hey I may need to go back to version x so don’t give me funky xyz features. Exporting/importing data is just painful.
 
5. Wow, wouldn’t it be great if we had a fail-over solution that didn’t drop the client connection.
 
Re-reading the above, possibly Microsoft just needs to focus on making existing features great, before pursuing new features and leaving DBAs again feeling that they could have been better i.e. JSON.

SQL Server 2017 CTP 2.1 released

The release announcement is here.
 
Interestingly,
“In CTP 2.1, we moved Reporting Services installation from the SQL Server installer to a separate installer. This is a packaging change, not a product change; access to SQL Server Reporting Services is still included with your SQL Server license. The new installation process keeps our packages lean and enables customers to deploy and update Reporting Services with zero impact on your SQL Server deployments and databases.”
 
As mentioned previously, installs look to be getting a lot more complicated. I can see why Microsoft would do this for easier internal development, but for an end-user experience, this is likely not ideal.
 
On a side note ~11 months ago SQL Server 2016 CTP 2.1 was released :)

Pre-patching SQL Server

Before you apply a SQL Server patch, it is usually advised to perform the below:
1. Ensure that there are no user/application connections to the SQL Server instance.
2. Ensure there are no SQL agents jobs, that may kick off just before you start the patching.
3. If this is a Virtual Machine (VM), take a snapshot otherwise, ensure you at least have a backup of the system databases.
4. Restart the server. (This one step surprisingly usually resolves 99% of patch application issues and it’s not mentioned by Microsoft). Just stopping/starting the services or reviewing/resolving the locked files warning is typically less effective than a server restart.
 
Of course if you are using Database Mirroring, AlwaysOn, Clustering … there are other considerations, and typically these involve patching the Passive instance to minimize the outage window. Once again, restarting the passive server prior to patching usually ensures the smoothest patching experience.
 
Once patching is completed, it is also recommended to again restart the server. This final restart can give you the confidence that future restart issues are likely not related to this patch.
 
On none HA instances, these server restarts will likely increase the outage window, but it could prove to be the difference between a successful or failed patching attempt.

SQL Server Facets

You don’t often hear about references to Facets in SSMS, however this is a great feature which allows you to review and even change the SQL Server configuration.
images1
I often use it to quickly access the good old SQL Server 2005 Surface Area Configuration settings.
image2
Take a look at the various Facets available and you may be pleasantly surprised.
image3

Upgrading SQL Server on Linux

Surprisingly this appears to be a simple process.
 
Below shows an upgrade from CTP 1.4 to CTP 2.0 on Ubuntu.
image1
1. Run: sudo apt-get update
2. Run: sudo apt-get install mssql-server
image2
and that’s it.
image3
As the Linux offering progresses we’ll hopefully better understand any caveats regarding upgrades.
 
Further info on Linux installs, upgrades and uninstalls can be found here.

SQL Server 2017 sys.dm_db_xtp_checkpoint_internals

This new DMV is not currently documented, but below shows it in action:

--create test database
CREATE DATABASE d
GO
-- allow in-memory on database
ALTER DATABASE d ADD FILEGROUP [fg] CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE d ADD FILE (NAME = f, FILENAME = N'C:\temp\') TO FILEGROUP [fg];
USE d;
--create test table
CREATE TABLE t (c1 int INDEX i1 NONCLUSTERED, c2 varchar(10)) 
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY); 
--view sys.dm_db_xtp_checkpoint_internals
SELECT * FROM sys.dm_db_xtp_checkpoint_internals;
--create a checkpoint
CHECKPOINT;
--view sys.dm_db_xtp_checkpoint_internals
SELECT * FROM sys.dm_db_xtp_checkpoint_internals;
--create a checkpoint
CHECKPOINT;
--view sys.dm_db_xtp_checkpoint_internals
SELECT * FROM sys.dm_db_xtp_checkpoint_internals;

image1