Thursday, April 27, 2017

Installing SqlServer PowerShell module from

With the release of SQL Server Management Studio 17.0 (SSMS) the SqlServer PowerShell module has finally been decoupled and now can be installed independently. To take full advantage of this change I recommend using PowerShell 5.0 (if your on Windows 10 or Server 2016 then this is your default).

Full details of the SSMS 17.0 updates

The official installation details for SqlServer module are at but they are a bit light on currently so this post will walk through the process in more detail. You can also find the public listing of the SqlServer on PowerShellGallery.

Firstly check what modules you have installed.

Get-Module -Name SQL* -ListAvailable

If you have installed the SQL Server DBEngine then you should have SQLPS for that particular version. On my server I upgraded SSMS 2016 to SSMS 16.0 so I have both 130 and 140 versions installed.

What I don't currently have installed is the SqlServer PowerShell module, and my SQLPS module hasn't been updated with the upgrade of SSMS to version 17.0

So let's download and install the latest SqlServer module.

First, check your PowerShellGet is configured for the public gallery (the default setup).


You should have the PSGallery repository setup. By default it will be untrusted, this will just mean you get prompted before installing any modules from it. It is recommended public repositories should not be marked as trusted, but internal private repositories can be to make the installations easier.

If you don't have any other repositories configured then you won't have to supply the repository name to any future commands, but if you do then you can simplify the module searches by specifying the repository name. This is very useful if you want to host private custom copies of the publicly available modules on a private repository.

Now you can search for the module in the gallery to check what the most recent version is available

Find-Module -Name SqlServer

NOTE: If prompted to continue enter 'y'

Add the -AllVersions parameter to list all the available versions. Currently there is only one version in the gallery but this will change overtime.

To install the module from the gallery run Install-Module. Use the Scope parameter to install for either CurrentUser or AllUsers based on your needs. With PowerShell 5.0 it is also possible to install versions side-by-side. In my case I am installing the module for all users and requiring a specific version (the current one).

To avoid the error message like "A command with name '' is already available on this system" I will also use the parameter -AllowClobber.

Install-Module -Name SqlServer -Scope AllUsers -RequiredVersion 21.0.17099 -AllowClobber

Now check that the module is available for use on the server with

Get-Module -Name SqlServer -ListAvailable

Notice that the path is now one of the common $PSModulePath paths. This is also part of the improvements that these changes bring.

Now going forward to update the module you can use the Find-Module and Update-Module cmdlets. Look out for my post on automating that process next month.

Happy PowerShell'ing

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Tuesday, March 28, 2017

Cmder, enhance your Windows Terminal Console experience

One of the best new tools I have been using lately is Cmder. A Terminal Emulator for Windows which is great for a number of reasons:

  • Tabbed terminals 
  • Multiple languages can be emulated
  • Fully customisable
  • and best of all Quake mode
If you haven't seen it yet check it out

I do a lot of work with PowerShell, Git and usually have many things on the go at once. What I really like about Cmder is that you can enable Quake mode which hides the terminal window at the top of your monitor screen and allows a keyboard short cut (Ctrl+`) to show/hide it. Making it very quick to open it over the top of other applications your working in (e.g. VS Code) and run commands. For example, when I am working on development projects I usually have VS Code open to edit the source code files and then I will have a PowerShell or Bash terminal emulated in Cmder for all my Git commands on the repo. Of cause you could just use the Git feature of VS Code in this case, but I usually always find there is some conflict I have to resolve at the Git console so just prefer to work in that. Plenty of other reasons I use Cmder too.

To get you started here is my quick setup steps which will set it up at least with Quake mode and your favorite terminal as default.

  1. Download and install Cmder from
  2. Open Cmder.exe from the location you extracted it to.
  3. Click on the 3 bar menu item in the bottom right

  4. Select Settings

  5. I have a Surface Book as my laptop which comes with a very large resolution (3000x2000) so I found I needed to change the "Size & Pos" settings, but obviously this is personal preference.

  6. Click the Main > Quake style option in the setting menu. Ensure "Quake style slide down" is enabled and any other options as you require.

  7. Select the Startup option from the settings menu and select the desired default task in the "Specified named task" drop down.

    This will give you the option of setting one of the default tasks as your default startup terminal. Such as Cmd.exe, PowerShell, Bash, etc. If you require anything more specific then use the Tasks section to define your own custom task. This is handy too for extending the functionality of Cmder, like setting up a SQLCMD terminal or specific PowerShell terminals for different technologies (e.g. Azure, SQL, Exchange, etc).
  8. If you want to have Cmder start at logon, something I have found increasingly important as I grew to depend on Cmder for my terminal use. Select the Integration > Default term option in the settings menu. Then check the "Register on OS Startup" option. NOTE: For this setting to work you also need to set the "Force ConEmu as default terminal for console applications", and you will be prompted to do so if you don't check it first. This setting will have no effect without that setting also enabled.

  9. Once you have configured the required setttings, such as Task Bar minimisation and other desired behaviors, click Save Settings.
  10. Now return to your other applications, when you need a terminal press Ctrl+` (that is Ctrl plus tilt character) and the cmder terminal will load.
For documentation of any of the setting see:

Some handy keyboard shortcuts, for the full list see

Ctrl+` : Show/Hide the cmder terminal
Ctrl+Numer : Switch to that active terminal tab
Shift+Alt+Number : Fast new tab (e,g, Shift+Alt+1 for Cmd.exe or Shift+Alt+2 for powershell)

For the official documentation see

Happy terminal emulating

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Why VSCode has replaced Management Studio as my default SQL Database and Query editor

Firstly let me start by stating that when I originally set out in an IT career I was heading down a developer path, and certainly had a number of developer type roles over the years, or found ways of continuing development projects while working in infrastructure roles..... probably why I have an interest in DevOps. So taking that into account it's no surprise that for my entire career I have always been comfortable working in code and not relying on GUIs. Even for all the years as a SQL DBA armed with SQL Management Studio (SSMS), yet I was always most comfortable working in TSQL rather than the wizards. Probably comes from the days of Enterprise Manager and Query Analyser (ahhhhh nostalgia). Now the MS Product Team has done a great job at improving the wizards in SSMS and making tasks as easy as they can be in the tools. I will also state that this post is by no means saying SSMS is dead because there are just some things where it is better positioned.

What I will cover in this blog post is why my go to TSQL editor and tool for general database work is now VSCode with the MSSQL extension.

Please don't take this as a statement that I have now uninstalled SSMS or Visual Studio with SQL Data Tools (SSDT) from my laptop, I wish, but I have always found those tool a bit bloated with memory consumption when all I want to do is connect to a database, run some queries, or make some basic changes. What I will show is why/how I now perform those tasks with VS Code, but for anything more in depth like designing SSIS packages or performance troubleshooting I still rely on the existing tools (for now).

Another factor that is driving this adoption of a text based editor is that a large amount of my work is now with Azure and other cloud solutions, and for the majority of the work you need to do it is largely console or script based.

Now that you know why I have arrived at this place, lets get into how I setup and use VS Code for this purpose. I look forward to healthy discussions with people around this because I am not a believer of the "one size fits all" approach to a tool set either so it is always great to hear what others use.

Setup and configure your environment
Here are the steps I use to setup my VSCode environment:

  1. Download and install VSCode
  2. Open VS Code
  3. Press Ctrl+Shift+X (on windows)
    1. Alternatively use the View > Extensions menu item
  4. Locate and install the following extensions
    1. vscode-icons
    2. mssql
    3. powershell
    4. c#
  5. Configure the extensions
    1. From File > Preferences > File Icon Theme select "VSCode Icons"
      This will ensure that any files you open and access have nicely displayed icons to make your experience easier.
  6. Configure the environment settings
    1. From File > Preferences > Settings
      1. VS Code works in two setting modes, User and Workspace. User should be personal preferences and Workspace should be used for project specific settings that will ship with the repo.
        User settings are stored in the file C:\Users\\AppData\Roaming\Code\User\settings.json but you shouldn't have to edit that manually as the VS Code window provides the best method for working with these files.
    2. I don't change too many settings at this time from the default, but some to consider depending on your needs are:
      mssql.splitPaneSelection = "current|next|end"

      IntelliSense will help you complete the values if you need to see what is available.
  7. Now you should be ready to start working inside VS Code. However, I recommend reading the release notes when new updates are made as the developer community is extremely active improving VS Code and there is always new and useful features being added.

While VSCode has a built-in integrated terminal, I like the cmder tool for my terminal use. If you aren't familiar with cmder check it out, very versatile, run multiple terminals and languages. Best of all a Quake mode. 

Connecting to a database and executing SQL queries
There are many tricks and ways to work within VS Code but here is a simply walk through on the basics to get you started.
  1. Open VS Code if you haven't already
  2. You do not need to open a folder or save files just to run queries but it could be beneficial. Think of a folder like a Project/Solution, but in a simplier (faster) format. This works great with Git and cross platform collaboration.
    For the case of this walk through just create a new file (click New File on the welcome page)
  3. Without saving the file, lets make sure we are in the right language mode.

    Click the current language in the tray menu (e.g. Plain Text)

    This will open the command palette with prompts to select the language. Either browse or type to find your language and select it.

    Now the correct SQL language is shown in the tray menu


    Now the color coding and formatting, along with IntelliSense, will be suitable for SQL Server development.

    TIP: When you save a file then the language mode is automatically detected based on the file extention.
  4. Press Ctrl+Shift+P to open the Command Palette
  5. Type "mssql" and select the mssql: Connect option or press Ctrl+Shift+C

    TIP: Make sure your focus is in a file with the SQL language set and not any other areas of VSCode when you press Ctrl+Shift+C as otherwise it will open a console as per those keyboard shortcuts default.
  6. Select an existing connection profile or select the Create Connection Profile to create a new one. So lets create one.
  7. Follow the wizard filling out your server/instance, database (optional), authentication etc.

    Once you start to connect the status is shown in the tray menu

    Any errors connecting will be shown with an overlay

    Once connected VS Code will update intellisense dictionary and perform other operations set by the extension.
  8. Now write your query in the file
  9. When ready you can execute the query in a few methods

    Use the Command Palette and the MSSQL: Execute Query command.

    Right click in the editor and select

    or my favorite just simply press Ctrl+Shift+E
  10. The query results tab will open. By default this opens in a new split window column, or the next one if you have multiples. The idea here is so you can see the query and result all in one window.

    You can put the query results at the bottom of the screen which might be a more familiar view to those use to SSMS. To do this select the Toggle Editor Group Layout from the View menu, or press Alt+Shiftt+1.

    Now the results are below the query you executed.

    Alternatively you can also set the query results to display in the current split window column (e.g. new tab)

    So as you can see you can customise where the results are displayed just like in SSMS.

    Something to keep in mind is that a new result tab will open for every file you execute a query from, but if you re-run a query or a new query from the same file then it will use the existing results tab for that file.
  11. Now just like the query editor in SSMS, it will either execute the entire file contents or what you have selected. So like in this example it will just execute the selected query and not the entire file contents.

    This is why I like the keyboard shortcut Ctrl+Shift+E to execute queries because it becomes really quick to work from a file and execute different selected queries as desired.
Obviously some people will really miss Object Explorer to understand the schema's of databases they are not familiar with, but keep in mind VSCode is designed for developers and so typically you would have a folder that contains all the scripts for creating the database and therefore your schema to refer to, or you would be familiar with the schema. However, as we all know there are plenty of views you can easily query to get that data (because after all that is all Object Explorer does). 

Happy SQL Scripting.

Registering your SQL Server connections
VS Code has a json based configuration system. SQL Connections can be saved in the User Settings file, think along the lines of "Registered Servers" in SSMS. I have already briefly touched on how to create a new profile when you connect. However here is how to register them ahead of time and manage existing connection profiles.

Keep in mind though, these connections are not unique to a project/solution/folder, they are unique to your user settings. So you make sure you give them meaningful names to easily identify which databases/projects they belong to.
  1. Press Ctrl+Shift+P to open the Command Palette
  2. Type "mssql" and select the MSSQL: Manage Connection Profiles option

  3. The Command Palette will then prompt you with some more options.

    Create: This will walk you through creating a new profile via the Command Palette prompts
    Edit: This will open the User Settings JSON file and allow you to manually edit the connection profiles. NOTE: Passwords can be saved in an encrypted form but are not stored in this file for security.
    Remove: This will walk you through removing an existing profile via the Command Palette prompts

    This is an example of the JSON configuration provided with the Edit option.

  4. Once you have configured the profile you can then simply select it from the list provided under the MSSQL: Connect command.

VS Code official site
VS Code opensource repo

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Thursday, February 2, 2017

Fixing Merge conflicts in VS Code projects and VSTS (Visual Studio Online) repos

I use a combination of GitHub and Visual Studio Team Services (Visual Studio Online, VSTS, etc) repositories. GitHub is pretty straight forward for resolving merge conflicts of pull requests through it's online portal. Typically when I have come across merge conflicts in my VSTS repositories it has been with Visual Studio projects and so I could resolve them with the VS GUI. However I recently experienced a merge conflict with a pull request for one of my VSTS repositories where the code was written in Visual Studio Code. For working with the VSTS GIT repo I use the posh-git which comes with the GitHub Desktop install, but the logic here should transfer.

The situation I faced was that I had been working in a branch and editing a file, which had also been edited by a colleague in the master branch (I know, I know...). So when I pushed my changes from my local repo to VSTS and then used the web portal to create a Pull Request to merge the changes back into the master branch I received an error that there was a merge conflict blocking the pull request.

As I mentioned normally in this situation I am use to there being options provided to select which file/change you want to be kept. In VSTS i was not able to find any such option, and all articles online seemed to indicate how to fix this in Visual Studio GUI. While I do have VS installed I didn't want to use that as I am trying to just move to using VS Code and GIT command line where possible. I never did think to try the git menus in VS Code, or the GIT GUI..... and to be honest there is probably a better way to resolve this but this is how I resolved the conflict.

First I abandoned the Pull Request using the button in the screen shot above.

Next I ran git status to make sure that the "working tree is clean"

Then by looking at the history of the file in the VSTS portal within my branch, I tracked down the original commit id that the file was initialised with. Taking that identifier I ran git reset InitialCommitId PathToFile (e.g. git reset e99a################################c60 XML\9.xml

Now when you run git status it should so the file is unstaged. It will also show that there are changes for the same file to be committed. We need to discard the changes in the working directory (resetting it to the initial version the branch was created from). So run git checkout -- XML\9.xml

Now git status should show a change to be committed. Commit the change with git commit -m "description"

Your working directory should now show it is ahead of the origin branch, so push the changes upstream with git push.

Now your branch in VSTS does not contain any conflicts. I wasn't able to reactivate the existing pull request and have it succeed (it still showed the conflict), so I created a new fresh pull request for the branch and it was no longer blocked by a merge conflict.

Hopefully this helps others. If you have other suggestions on working around this please comment below.

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Tuesday, December 20, 2016

Why is the with_copy option critical when restoring a database configured with StretchDb

StretchDB is a new capability in SQL Server 2016 which provides the ability to effectively partition your data between warm and cold data, and more importantly allow SQL Server to automatically host that data in an Azure SQL Database.... sounds great in concept and in cases where you have a large amount of cold data which just needs to be queried and not updated (e.g. archived data for reporting).

Now, there is one critically thing you really need to be aware of and that is when it comes time to Restore or more specifically Migrate the on-premises database that is stretched. Let's take this scenario for example.

You are planning to upgrade the production SQL Server to SQL Server 2016 SP1. The business needs do not allow for an in-place upgrade, so you have built a new server and are planning to migrate production database to the new server.

It could be a DR scenario but more likely the issues I am talking about will be during migrations.

The issue you need to be aware of is that after restoring the on-premises database you then need to reauthorize the connection to the Azure SQL Database (official details at When you perform this step, you have to specify the parameter WITH_COPY which can either point the database to the existing remote table in the Azure SQL Database, or create a new copy of the remote table to use. The recommended approach is to use a new copy of the remote table (with_copy = 1). I would also recommend this approach for database migrations, and then manually clean up the old table. The reason I recommend this is as I will show in this post, if your old/current database is still in use and you point a new database to the same remote table, you can experience conflicts between the data reconciliations and this will result in cold data loss.

So let's explore the issue.

Firstly here is the environment I am working in:

On-premises SQL Server 2016 SP1 (13.0.4001.0). I have one database StretchDBDemo which contains the following tables:

  • dbo.Department
    • DepartmentID, DepartmentName, OfficeLoc
  • dbo.People
    • PersonID, FullName, IsEmployee, PhoneNumber, EmailAddress

The full schema is available in the script at the end of this post.

We are going to use the dbo.People table to demonstrate this issue. So lets start with some data.
INSERT INTO [dbo].[People] ([PersonID], [FullName], [IsEmployee], [PhoneNumber], [EmailAddress]) VALUES (1, 'Matt', 1, '0','');
INSERT INTO [dbo].[People] ([PersonID], [FullName], [IsEmployee], [PhoneNumber], [EmailAddress]) VALUES (2, 'Steve', 0, '0','');

Next step is to setup StretchDB feature. This has to be done through the GUI and is pretty straight forward but here are the steps I have used:

  1. Right click on the Database StretchDBDemo in Object Explorer
  2. Select Tasks > Stretch > Enable

    1. In the GUI select Next
    2. Place a tick in the box next to the dbo.People table. Notice the warning icon. Important to note as it does have some relevance to the behaviour we will explore.
    3. Click the "Entire Table" link under the Migrate column to launch the filter wizard

      1. Setup a filter called IsEmployee False, with the predicate "IsEmployee = 0". Click the Check button and then Done.

        NOTE: If you don't have any rows in the dbo.People table that match this predicate you won't be able to proceed past this point.
    4. Click Next and then authenticate to Azure. 
    5. Select either an existing Azure SQL Database Server or create a new StretchDB server.
    6. Follow the wizard to completion. I recommend taking a screen snippet of the summary page for reference.
  3. You can then check that the StretchDb feature is working by using the Monitor from the Stretch menu on the Database object.

    You could also query the following tables to check this data manually
    SELECT * FROM sys.dm_db_rda_migration_status
    SELECT * FROM sys.dm_db_rda_schema_update_status
  4. You will need to check the configuration of the StretchDb, this is critical as we need the Azure SQL Server address.

    -- the db config
    SELECT * FROM sys.remote_data_archive_databases
    -- the tables config
    SELECT * FROM sys.remote_data_archive_tables
    -- the data source
    SELECT * FROM sys.external_data_sources
  5. Now that you have the remote server's address, in SSMS Object Explorer connect to the Azure SQL Database server
    1. Expand the Databases branch in Object Explorer, expand your stretched remote database (the name is in the tables queried above). Then expand Tables. Note how no tables are displayed here. Any stretched tables are deliberately hidden and you should not query those tables in general practice. However we are troubleshooting/demonstrating an issue so we will query the tables directly.
    2. Query sys.Tables to find the name of the stretched table
    3. Now query the stretched Table in the Azure SQL Database. You should have 1 record for 'Steve'.
  6. Lets add a new record to our on-premises database that will be stretched to the remote server.
    INSERT INTO [dbo].[People] ([PersonID], [FullName], [IsEmployee], [PhoneNumber], [EmailAddress]) VALUES (3, 'Chris', 0, '0','');
  7. Using the Monitor or the TSQL queries from earlier check on the status of the StretchDb feature. After some time query the stretched table in the Azure SQL Database again to make sure the new record is stretched there.

Now the fun part starts. Lets simulate a database migration. In my lab I am just using the same server, but the experience is the same.
  1. Take a backup of the on-premises StretchDbDemo database. You could elect to disable StretchDb first and bring all the data back on-premises, but we trust the Azure backups to keep our data in the cloud safe.
  2. Next restore the backup to the new database we are migrating the application to. I have used the database name StretchDbDemoNew.
  3. Once the database is restored it isn't immediately Stretching database as it needs to be re-authorized to use the remote server. This is outlined in
    1. First get the name of the credential that exists. If you were migrating this to another server you will need to recreate the credential on that server. The name of the credential needs to match the remote server address.
      SELECT * FROM sys.database_scoped_credentials
    2. Now use the following statement to
      -- reauthorize using the existing credential information
      USE [StretchDBDemoNew];
      EXEC sp_rda_reauthorize_db 
        @credential = N'',
        @with_copy = 0;

Here is where the problem begins. 
When you execute sp_rda_reauthorize_db and specify with_copy = 0, it uses the existing Azure SQL Database as the endpoint. So now we actually have two on-premises databases StretchDBDemo and StretchDBDemoNew, both pointing to the same Azure SQL Database table for stretched data. If you specify with_copy = 1, it actually creates a copy of the data in a new table and therefore the old and new on-premises databases have different stretch data end-points.

So why does this matter. Well lets add some data to the new on-premises database because well we want to make sure it's all work as you would in any migration.

INSERT INTO [StretchDBDemoNew].[dbo].[People] ([PersonID], [FullName], [IsEmployee], [PhoneNumber], [EmailAddress]) VALUES (10, 'Bill', 0, '0','');

HINT: Any rows I insert into the new database I will use a PersonID >= 10 as this helps with the demonstration.

Now this should get stretched to the Azure SQL Database. So switch to that query window and check it's there. 
NOTE: it might take a few minutes for the reconciliation to occur.

Here is where things get interesting. After a few minutes try querying the data again and see if there is any change. While writing this post the remote record disappeared, however when writing the scripts it didn't. So what is happening..... to find out lets compare local and remote data more.

With the new record still in Azure, query the old on-premises database. If your lucky you will find the record inserted into the new database which has been stretched to Azure also returned when you query the original database. Your experience here may differ as it's all a matter of timing.

Just to add fuel to the fire I inserted another record into my new database that would be stretched to Azure.

INSERT INTO [StretchDBDemoNew].[dbo].[People] ([PersonID], [FullName], [IsEmployee], [PhoneNumber], [EmailAddress]) VALUES (11, 'Bart', 0, '0','');

But what about our existing on-premises database. Remember in my scenario we are only testing the migration so you would assume there are still active connections querying that database and potentially data being updated which would then stretch into Azure. So lets insert two records into that database which will be stretch.

INSERT INTO [StretchDBDemo].[dbo].[People] ([PersonID], [FullName], [IsEmployee], [PhoneNumber], [EmailAddress]) VALUES (4, 'Chad', 0, '0','');
INSERT INTO [StretchDBDemo].[dbo].[People] ([PersonID], [FullName], [IsEmployee], [PhoneNumber], [EmailAddress]) VALUES (5, 'Dan', 0, '0','');

So in our existing database we now have this data being returned

What about in our new database.

Wait a minute, those records 'Chad' and 'Dan' were just inserted into our existing database, and where did our records for 'Bill' and 'Bart' go. 

Here is the issue, with both on-premises databases pointing to the same remote database through the with_copy = 0 option, it means that the reconciliation processes in the databases will conflict. One of the databases will effectively become the master and overwrite what records the other sends. As I mentioned both times I ran through the scripts I got different experiences where one time the new database was the master, and the other time (writing this blog) the original database was the master and overwrote the records.

So, the good news is that if you use with_copy = 1 option then the databases use separate remote tables and therefore do not conflict. So my recommendation is ALWAYS USE "WITH_COPY = 1" when reauthorizing a database to the remote server.

In saying that a few things to keep in mind, over time if you move the database a lot you could end up with lots of tables in the remote database so best to monitor that if you want to keep your costs down.

Also if at any stage you are querying the stretched tables in the on-premises databases and you have this conflict situation you could experience a number of cases of this error.

I hope this blog post helps when it comes time to planning your migrations. Any questions or feedback please leave them in the comments section. 

Get the complete demo scripts here.

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Sunday, August 21, 2016

New SQL Server Builds API.... improve your health checks with version and support information

Things have been a bit busy for me personally over the last few months but that is another story. What I have done is use some of this time to get back to a little project I started a while back.

Introducing the SQL Server Builds API..... providing version information including release dates, and known support life-cycle information.

The SQL Server Builds API provides SQL Server Version information and known support life-cycle information. As it is a personal project of mine the data is currently manually maintained based on known sources. The main objective of the tool is to enhance health check and project planning type processes, it is not intended for licensing or other legal auditing processes.

In prior roles and particularly in my current role one of the most common checks I perform on a SQL Server is the version and also the patching process. To aid in this what I would do is run reports based off my own custom database of version numbers to check and validate the version of the instance being checked.

Querying the API
To query the API it is pretty simple. The base URI is

The API takes two parameters

  • Version - This is a string representation of the version of your SQL Instance returned by SELECT SERVERPROPERTY('PRODUCTVERSION') e.g. '12.00.2342.00'
  • MissingUpdates - A boolean value (True/False) of if to include any updates released since the current patch level in the results

So if I wanted to query for information about the version number 12.00.2342.00 my completed URI would be something like:

NOTE: The MissingUpdates param defaults to false so you could omit it from the URI above.

Where the data comes from
The official source of truth with life-cycle data has to be Microsoft Support Life-cycle (, and this API/tool is by no means trying to replace that. In fact if you are doing any official auditing for licensing or any other legal means you MUST use that source over this API's data. At least perform your own validation of the data for each record.

Most DBA's and SQL admins would refer to I am no different and I would previously refer to that site as well. So, it is only natural that it is still one of the sources I go to for building my API's database.

The database behind this API is currently manually maintained, and even if it was somewhat automated mistakes can happen. If you find I am missing any updates, or if there is any incorrect data please make sure you reach out and let me know.

Suggestions for using the API
The API could be used in any number of tools and checks. Here are just some I can think of:
  • Custom health check scripts
  • Enhance on-premise CMDB data
  • PowerBI reports
  • Other custom Applications that interface/manage SQL Server instances

PowerShell Examples

#1 - Get version information (static version number)
# Set the version number to test
$VersionNumber = '12.00.2342.00' # SQL 2014

# Call the API to get the version information
$VersionData = Invoke-RestMethod -Uri "$($VersionNumber)";

# at this point simply return the $VersionData variable for the raw info

# Want to improve your Health Check script, calculate the health of Support Status
if ($VersionData.ExtendedSupportEnd -le (Get-Date)) {$SupportStatus = 'Critical'}
elseif ($VersionData.MainstreamSupportEnd -le (Get-Date)) {$SupportStatus = 'Warning'}
else {$SupportStatus = 'Ok'}

# format the output data string
$OutputData = @"
Instance = $($Instance.Name)
Version = $($VersionData.Version)
Product = $($VersionData.Product)
Branch = $($VersionData.Branch)
Update = $($VersionData.Update)
MainstreamSupportEnd = $($VersionData.MainstreamSupportEnd)
ExtendedSupportEnd = $($VersionData.ExtendedSupportEnd)
SupportStatus = $($SupportStatus)

# Return the hashtable
ConvertFrom-StringData -StringData $OutputData;

#2 - Get Missing updates (static version number)
# Set the version number to test
$VersionNumber = '11.00.2342.00' # SQL 2012

# Call the API to get the known missing updates (released since current version)
$VersionData = Invoke-RestMethod -Uri "$($VersionNumber)&MissingUpdates=$true";

# return the output, it is already formated as an Array

# or as a table
$VersionData | Format-Table -Property Version,Branch,Update,@{Name="Released";Expression={Get-Date $_.Released -Format 'yyyy-MM-dd'}},Description -AutoSize

Other Examples
There are more examples in my GitHub repository -


  • Simple static value examples like above
  • Both Single and Multiple Instance examples with live version number data direct from a SQL Instance
  • c# Windows Form client
Just want to see it in action, I have an MVC form available at

Where is the source code for the API
At this time I am not making the API opensource.

Want to partner with me
This is a personal project. If you have a similar project or a project that you would like to use this API in please reach out and lets improve the world together.

If you have any questions around this API please make sure you comment or reach out to me through any of my contact means.

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Friday, April 15, 2016

TSQL To identify queries which are candidates for Parameterization

While discussing the concepts of 'optimize for ad hoc workloads' and 'Forced Parameterization' in SQL Server I decided to modify an existing query I wrote to analyse the query performance from the cache (more on it here) to now analyse the adhoc queries for candidates which could benefit from parameterization.

;WITH CTE(QueryCount, StatementTextForExample, plan_handle, QueyHash, QueryPlanHash, CacheObjType, 
    COUNT(query_stats.query_hash) AS QueryCount
    , MIN(query_stats.query_text) AS StatementTextForExample
    , MIN(query_stats.plan_handle) AS plan_handle
    , query_stats.query_hash AS QueryHash
    , query_stats.query_plan_hash AS QueryPlanHash
    , query_stats.CacheObjType
    , query_stats.ObjType
      , qs.query_plan_hash 
      , qs.plan_handle
      , cp.cacheobjtype as [CacheObjType]
      , cp.objtype as [ObjType]
      , SUBSTRING(qt.[text], qs.statement_start_offset/2, ( 
                       WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2  
                       ELSE qs.statement_end_offset  
                   END - qs.statement_start_offset)/2  
               ) AS query_text 
               sys.dm_exec_query_stats AS qs 
      INNER JOIN sys.dm_exec_cached_plans cp ON cp.plan_handle = qs.plan_handle
               CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt 
           WHERE qt.[text] NOT LIKE '%sys.dm_exec_query_stats%' 
     AND cp.objtype = 'AdHoc'
     --AND qs.last_execution_time BETWEEN DATEADD(hour,-1,GETDATE()) AND GETDATE() --change hour time frame
       ) AS query_stats 
 GROUP BY query_stats.query_hash
  , query_stats.query_plan_hash
  , query_stats.CacheObjType
  , query_stats.ObjType
 HAVING COUNT(query_stats.query_hash) > 1
   , CTE.CacheObjType
   , CTE.ObjType
   , CTE.StatementTextForExample
   , tp.query_plan AS StatementPlan
   , CTE.QueyHash
   , CTE.QueryPlanHash 
   OUTER APPLY sys.dm_exec_query_plan(CTE.plan_handle) AS tp 
--ORDER BY [Total IO] DESC; 

When you identify these candidates you then need to look at what is the most appropriate resolution such as.

  1. Rewrite the query at the application layer to ensure it is called with parameterization from the data provider
  2. Rewrite the query as a stored procedure
  3. Enable 'optimize for ad hoc workloads' on your SQL instance
    EXEC sys.sp_configure N'optimize for ad hoc workloads', N'0'
  4. Enable 'Forced Parameterization' on the affected database
    USE [master]
Of cause the appropriate option really depends on a larger view of your environment and applications that only you can determine. If you do have any thoughts on this please feel free to add them to the comments below to help others.

Word of caution too. Before adjusting any sp_configure settings with RECONFIGURE make sure you run the following to check for any settings which are not yet active. It is expected to see 'min server memory (MB)' in the results of this though if you leave the setting default at 0 as SQL must reserve the minimum memory possible which is 16mb.
SELECT * FROM sys.configurations
WHERE Value <> value_in_use

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.