sql server

Comparing Windows and Linux SQL Containers

By several measures, Windows SQL Server containers offer better enterprise support than Linux MySQL or Postgres containers. SQL Server containers provide more backward compatibility, and support for existing apps, storage arrays, and infrastructure.

Windocks has evolved as an independent port of Docker’s open source project to include database cloning, a web UI, secrets store, and other capabilities. These capabilities are customer driven, and seem to diverge from Linux mainstream development. This article takes looks at the capabilities being driven by Windows customers. Full disclosure, I am a principal of Windocks, and this article focuses on the Windows-based SQL Server containers provided by Windocks.

Original Link

How to Recover a SQL Server Database That Is Corrupt


In this article, we will see how to solve the problem to restore a SQL Server Database that is corrupt. To solve the problem, we will use a special software.

The software name is Kernel for SQL Database Recovery and you can download the software here:

Original Link

Running SQL Server on a Linux Container Using Docker for Windows

Recently, I have been investigating what all the fuss is about Docker and it has been well worth my time as Docker is pretty awesome for automating stuff.

My development environment has typically required installing SQL Server. SQL is a bit of a beast with lots of options and takes time to set up how you want.

Original Link

How to Attach a Pubs Database in SQL Server

In this tutorial, I will explain how to Attach a Pubs Database in SQL Server. The procedure is the same for every other database to. You will need Pubs MDF and LDF files to attach to your SQL server. 

Note that both Northwind and Pubs databases come together with the download file.

Original Link

How to Repair a Corrupted SQL Server Database


In our life, it is common to work on performance, create backups, design new tables, create new databases. Everything is fine in your DBA life, but suddenly, a user calls you and says that he cannot access to a report. After checking, you noticed that the database is corrupted. Why?

There are several reasons to have a database corrupted. Sometimes it happens when the hard drive is damaged, sometimes a virus attack can corrupt your database. Another common problem is a hacker attack. Some smart guys who want to show their power and attack to Servers just for fun. They can break the firewall and all the security and crash your database and even the backups.

Original Link

Highlighting Personally Identifiable Information (PII) Across SQL Server

With the recent regulatory pressures such as GDPR, being able to know what type of data we are holding onto within our business becomes a bigger challenge. At Redgate, we have been exploring ways to make this easier for our heavy SQL Server users and eliminate the problems around this area by making it easy to efficiently highlight where you are holding Personally Identifiable Information (PII) among your SQL Server estate.

We have been iterating on this area with a set of users we have been working very closely with as part of our Data Catalog Early Access Program (EAP), and the main pain we have identified is around the time it takes to complete the initial classification job to highlight what type of data we have within our business, which is mainly what I want to talk about in this post.

Original Link

SQL Prompt Code Analysis: Table Does Not Have Clustered Index (BP021)

With a few exceptions, every table should have a clustered index. However, they are not always essential for performance. The value of a clustered index depends on the way a table is used, the typical pattern of queries, and how it the table is updated. More important for a table is that it should have an appropriate primary key. If you cannot explain a good reason for avoiding a clustered index on a table, it is far safer to have one. Good reasons are hard to come by unless you know exactly how the table will be used.

Heaps and SQL Server

Heaps, which are tables without clustered indexes, are treated in SQL Server as the naughty sister of tables and they have, in the past, generally lived up to their reputation. In previous SQL Server versions, it was, for example, impossible to rebuild the indexes. The query response times increased as inserts and deletes happened to the table, because of forwarding pointers.

Original Link

Overview of SQL Joins in SQL Server

The ability to combine results from related rows from multiple tables is an important part of relational database system design. In SQL Server, this is accomplished with the SQL join clause. It’s the nature of traditional relational database systems where some table contains information related to other tables with a common key value. Using a SQL join, you can easily perform queries on related data-sets from multiple tables with these shared keys.

The aim of this article is to provide you with the basic knowledge and examples that you will need to use the SQL join effectively in any database environment.

Original Link

SQL Server Containers With SSRS

SSRS support has been among the most frequently requested new features and Windocks 3.5 introduces SQL Server containers with the database engine and SSRS running as Windows Services. Windocks 3.5 supports native mode on SQL Server 2008 through SQL Server 2016, with SQL Server 2017 slated for later this year. 

Setup and Planning

Windocks installs on Windows 8.1 or 10, Pro or Enterprise editions, or Windows Server 2012 R2 or Server 2016, with SSRS support for all editions of SQL Server 2008 through 2016 (SQL 2017 support is slated for later this year). Install on a machine that has one or more SQL Server instances that can be managed by the Windocks service, which clones the instance to create containers. SQL Server containers are lightweight (~300 MB), so can be stored on the system disk, or assigned to a separate disk. Database clone mages are each a full byte copy of the data environment and should be stored on a separate disk or network attached storage. The automated Windocks install takes 10-15 minutes to complete. 

Original Link

Why Did a Plan Get Removed from Cache?

I was recently asked if we could tell why a plan was removed from cache. If you read this blog, you know what I’m going to say next. I checked the extended events and there are actually two different events that will tell us information about a plan removed from cache; sp_cache_remove and query_cache_removal_statistics. Let’s talk about how these work.

Removed From Cache

Just so we can see ALL the activity, I’m creating an Extended Events session that captures a little more than just the two events:

Original Link

Fix SQL Server With One Click

Tempting headline, isn’t it? It might even seem like clickbait, but that’s not the intention.

The SQL Server default configuration is not recommended for production environments, and yet I have worked on many production environments that have been set up by people who don’t know that the default configurations are not recommended. These same people shouldn’t have to care either, because their job is to install SQL Server so that the vendor product they’ve purchased can work as required.

Original Link

Monitoring Changes in Permissions, Users, Roles, and Logins

Compliance means keeping a close grip on any changes to the permissions and access control of a database. Sadly, the law has had to acknowledge, from bitter experience, that it is not just external intruders who want to do this, but it could also be attempts at fraud or data theft from within the organization. Permission changes are certainly one of the things that security experts advise you look out for; you need the equivalent of CCTV trained on your servers.

If you have default trace running, then you should already have an impressive audit, with a record of what changes to permission, users, logins or roles were made, when and by whom.

Original Link

The Cause of Every Deadlock in SQL Server

First, a quick definition and example for those that don’t know what deadlocks are inside of a database.

A deadlock happens when two (or more) transactions block each other by holding locks on resources each of the transactions also needs.

Original Link

Changes to the SQL Server Servicing Model (Cumulative Updates)

On Monday of this week, Microsoft announced changes to the servicing model for SQL Server, starting with SQL Server 2017.

From today onward, we can expect to see the following during the first five years after release (known as Mainstream Support):

Original Link

Windocks SQL Server Containers on Windows

SSRS support has been among the most frequently requested new features and the Windocks 3.5 Beta release introduces SQL Server containers with SSRS running as a Windows Service. SSRS supports native mode on SQL Server 2008 through SQL Server 2016, with SQL Server 2017 slated for later this year. SSRS containers combined with database cloning provide efficient support for Development and Test, and for production reporting systems, and runs on any on-premise infrastructure or public cloud.

Setup and Planning

Windocks installs on Windows 8.1 or 10, Pro or Enterprise editions, or Windows Server 2012 R2 or Server 2016, with SSRS support for all editions of SQL Server 2008 through 2016 (SQL 2017 support is slated for later this year). Install on a machine that has one or more SQL Server instances that can be managed by the Windocks service, which clones the instance to create containers. SQL Server containers are lightweight (~300 MB), so can be stored on the system disk, or assigned to a separate disk. Database clone images are each a full byte copy of the data environment and should be stored on a separate disk or network attached storage. The automated Windocks install takes 10-15 minutes to complete.

Original Link

5 Keys to Modernizing Windows Development

Docker has emerged as a leading standard for application packaging and portability, garnering industry-wide support from Microsoft, AWS, Google, Red Hat, and others. Interest in Docker on Windows is accelerating, driven in part by Microsoft’s support of SQL Server 2017 on Linux containers.

Windocks was launched in 2016 as an independent port of Docker’s open-source project to Windows, with SQL Server database cloning enabling delivery of TB databases in seconds. These capabilities have generated widespread enterprise adoption and a growing understanding of the keys to success for successful Windows software development modernization. For full disclosure, I am a co-founder at Windocks, and the observations outlined here are from our support of these varied capabilities. 

Original Link

What Is a DBA Anyway?

Some time ago, we started a new series here called Database Fundamentals. The very first post in that series asked what a database is.

One of the major announcements at Ignite last month was that of SQL Server 2019 preview and major improvements to Azure SQL Database, Microsoft’s on-premises and cloud-based relational database systems, respectively.

Original Link

Repair SQL Database After an Abrupt Shutdown of SQL Server


Being a DBA in SQL Server can be simple, but it becomes stressful when you need to deal with sudden database downtime.

Sometimes it happens after a power outage, resulting in one of the databases being corrupted. Usually, it goes in suspect status and it is not easy to repair. In this article, we will show different ways to repair the database after an abrupt shutdown of SQL Server.

Original Link

Query Store and Log Backups

A question that came up recently around Query Store is what happens when there are log backups in use on the database. Let’s talk about it.

Query Store and Log Backups

The core of the answer is very simple. Query Store, like any other data written to a database, whether a system table or a user table, is a logged operation. So, when you back up the database, you’re backing up Query Store data. When you back up the logs, you’re also backing up Query Store data. A point in time will include all the data written to the Query Store at that point.

Original Link

Getting Started With SQLCMD

Hi All,

Recently, I was given a task that was a bit complex, as this involves Unix, Autosys, and calling SQL Server from Unix. In this task, my major challenge was how to connect to SQL Server instance from the Unix machine. Frankly speaking, this gave me goose bumps, as I have never done anything like this before.

Original Link

SQL Server Error 18456: A Solution


In this article, we will explain how to fix the SQL Server Error 18456. We will first describe the error and then we will show different ways to solve this SQL Server error using SQL Server commands, backups or external software.

The SQL Server Error 18456

A typical error message when you try to connect is the following:

Original Link

Measuring Query Execution Time: What Is Most Accurate

Probably the single most important factor when deciding which query to tune, or actively tuning a query, is how you go about measuring query execution time. SQL Server provides a number of different mechanisms (really, maybe too many) to get this done. However, all measures are not created equally. In fact, they frequently disagree with one another. Let’s take a look at this odd phenomenon.

Measuring Query Execution Time

Before we get into all the choices and compare them, let’s baseline on methodology and a query to use.

Original Link

Don’t Do These Things in SQL Server

Recently Brent Ozar posted a link to the PostgreSQL “Don’t do this” page, which I am shamelessly reproducing below, re-tailored for a SQL Server audience.

Don’t Use -P With sqlcmd

sqlcmd is a cross-platform interactive command-line utility that allows you to connect to a SQL Server (or Azure SQL Database) instance and perform database operations. One of the parameters is -P which allows you to supply a password.

Original Link

Algorithm of Checksum and Torn Page Detection [Snippet]

SQL server data page has three kinds of PAGE_VERIFY options since SQL Server 2005: NONE, Torn Page Detection, Checksum.

There is no algorithm of PAGE_VERIFY for NONE option.

Original Link

Scaling SQL Monitor to Large SQL Server Estates

Most organizations are finding that the size and number of databases that need to be monitored is increasing, unlike the number of people available to do the work. Now that it is possible for the organization to scale out to cloud-hosted databases, the constraints of the available infrastructure have ceased to govern the growth of the database estate.

SQL Monitor is designed to help you to cope with the demands of bigger databases and higher numbers of monitored SQL servers, because it supports multiple base monitors, and one base monitor will cope with up to 200 servers, with minimal overhead. Even for very large server estates, spread across different networks, the administration team still retain a simple picture of the overall health, performance characteristics, and security of the estate. This ensures they can still identify bottlenecks, check stress-points before they become problems, and assign priorities to problems.

Original Link

Repair SQL Server on Linux After an Ubuntu Distribution Upgrade [Snippet]

SQL Server 2017 is supported on Ubuntu 16.04 LTS (Long-Term Support), however that version of Ubuntu Linux is now more than two years old, so you may be tempted to update Ubuntu to a more modern release such as Ubuntu 18.04 LTS (Bionic Beaver). Unfortunately (as of this writing), SQL Server 2017 is not supported on later versions of Ubuntu.

(Note: Ubuntu releases new distributions every six months. The version number 16.04 means that it was released in April 2016.)

The more adventurous among us who use SQL Server 2017 in a development environment — where we need to keep the Ubuntu distribution as up to date as possible — might run into the following issue:

Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help resolve the situation: The following packages have unmet dependencies:
mssql-server : Depends: openssl (<= 1.1.0)
E: Unable to correct problems, you have held broken packages.

I would like to share this very handy answer written by langioletto that I found on Ask Ubuntu, which resolves this dependency issue.

WARNING: Downgrading packages is dangerous, especially in libraries like OpenSSL.  Do not run this in a production environment!

apt-get install -y libjemalloc1 libsss-nss-idmap0 libc++1 gawk curl
curl | apt-key add -
add-apt-repository "$(curl"
add-apt-repository "$(curl"
dpkg -i ca-certificates_20160104ubuntu1_all.deb
dpkg -i openssl_1.0.2g-1ubuntu4_amd64.deb
apt install -y libcurl3
apt-get update
apt-get install -y mssql-server

Once SQL Server is installed, you will have to run sudo /opt/mssql/bin/mssql-conf setup to make sure SQL Server is configured correctly. Fortunately, SQL Server 2017 on Linux is non-destructive with any databases you already had from previous installations, so the service should start up happily by itself and all your databases should be usable.

Thanks, langioletto!

Share your Linux development environment tips and tricks with me on Twitter at @bornsql.

Original Link

Migrate SQL Server 2008 Database to Azure

With an efficient database, it becomes easy for the organization to manage and store data. MS SQL Server is the first choice for users when it comes to data management. But nowadays, the users are migrating from SQL Server 2008 database to Azure SQL database. The reason behind this migration is that the end of life support for SQL Server 2008.

Therefore, the SQL Server 2008 database users are searching for a reliable and simple method to move data from SQL Server 2008 to Azure without any data loss issue.

Thus, in this article, we are going to introduce a step-by-step method to transfer data from SQL Server to Azure SQL database in a trouble-free way.

Steps to Migrate SQL Server 2008 Database to Azure

The migration process is divided into two steps; the user first has to create a blank SQL database and then migrate SQL Server to Azure database using DMA.

Step 1: Create a Blank SQL Database

  1. First, you have to log in to the Azure portal.
  2. Now, create a blank SQL database by clicking on the Create a resource in the upper left corner of the Azure portal.
  3. From the New page, select Databases. Select Create under SQL Database.
  4. After that, you have to fill out the SQL database by entering the correct information related to:

Database Name: mySampleDatabase

Subscription: Your subscription

Resource group: myResourceGroup

Select source: Blank Database

5. Now, to create and configure a new server for your new database, click on the Server. Fill out the New Server form details with the accurate information:

Server Name: Any globally unique name

Server admin login: Any valid name

Password: Any valid password

Location: Any valid location

6. Click on the Select button, which is present on the right side.

7. Now, to specify the service tier, number of DTUs, and the amount of the storage, click on the Pricing Tier.

8. Select the Standard service tier and then select 100 DTUs(S3) and 400 GB of storage.

9. After that, accept the preview terms to use the Add-on Storage option.

10. Click on Apply after selecting the server tier, DTUs, and storage amount.

11. Select a collation for the blank database. Here, you have to use the default value.

12. After completing the SQL database form, click on Create to provision the database.

13. Now, on the toolbar, click on Notifications to check and examine the deployment process.

Note: Before moving towards step 2, you have to create a server-level firewall rule. Also, check SQL server connection information.

Step 2: Migrate SQL Server 2008 Database to Azure

  1. Open Data Migration Assistant. The user can run DMA on any system with the connection to the SQL Server instance contains the database. The user needs to install it on the system hosting the SQL Server instance that the user is migrating. The firewall rule that the user will create must be for the system on which he/she is running the Data Migration Assistant.
  2. In the menu on the left side, click on + New to create an Assessment project. Fill all the required details and then click on Create:

Project type: Migration

Project name: Migrating Database

Source server type: SQL Server

Target server type: Azure SQL Database

Migration Scope: Select Encrypt connection and Trust server certificate as per your environment.

3. Now, from your source server select a single database to migrate to Azure SQL Database and then click on the Next button.

4. Fill all the required detail on the Select target page. Then click on the Connect button:

Server name: Your fully qualified Azure Database server name

Authentication type: SQL Server Authentication

Username: Your login name

Password: Your password

Connection properties: Select Encrypt connectionand Trust server certificate according to your environment.

5. Select the database from the target server, which you created in the previous procedure, and then, to start the source database schema assessment process, click on the Next button.

6. Once the source database schema assessment process is completed, review the objects selected for the migration and review the object containing issue on the Select objects.

7. Now, to script the schema object in the source database, click on the Generate SQL script option.

8. After that, you have to check the generated script and then click on Next.

9. You have to click on Deploy schema and the schema migration process will begin.

10. After the completion of the migration process, review the results and then click on Migrate data.

11. Review the tables selected for the migration on the Select tables. Now, click on Start data migration.

12. The migration process will begin. Once the migration process is completed you will be able to access your SQL Server data in Azure.

Final Words

Most users search for a way to migrate the SQL Server 2008 database to Azure, but they are not able to find a reliable solution. Therefore, in the above section, we have explained a step-by-step process to move the complete SQL Server data to Azure. 

Original Link

Automated Database Deployments Using State-Based Redgate SQL Change Automation


My previous posts discussed why you need automated database deployments and tips for getting started down that path. Enough talk, it is time for action! This article will walk through setting up an automated database deployment pipeline using the state-based approach for Redgate’s SQL Change Automation. I picked this tool to start with because it is easy to set up, integrates with SSMS, and…well…I already had a demo set up. I’m also a little biased towards Redgate’s tooling. So there’s that.

The end goal of this article is for you to have a working proof of concept for you to demo.

Prep Work

For this demo, you will need a SQL Server instance running, an Octopus Deploy instance, and a CI server. I recommend using a Dev environment or your local machine for this proof of concept.

Tools Needed

You will need the following tooling. The examples given use TeamCity and VSTS/TFS. As you will see later, the core concepts and UI for all the CI tools will be very similar.

  • Octopus Deploy:
    • Get 45-day free trial for on-premise here.
    • Get 30-day free trial for Octopus Cloud here.
  • Redgate SQL Toolbelt
    • Get 14-day free trial here.
  • CI Tool (pick one):
    • Jenkins — download here.
    • TeamCity — download here.
    • TFS — download here.
    • VSTS — try here.
    • Bamboo — download here.
  • SQL Server Management Studio (SSMS):
    • Download for free here.
  • SQL Server:
    • SQL Express — download here.
    • SQL Developer — download here.

Installing Software

I’m not going to walk you through how to install those tools. This article would be 100 pages long if I did that. If you run into issues, please go to the vendor’s website for any help. If you need any help installing Octopus Deploy, please start with our docs or contact . 

Developer Machine

This is the machine where we will be making the schema changes and checking them into source control. When you install Redgate’s SQL Toolbelt you will be prompt to install quite a bit of software. You only need to install the following:

  • SQL Source Control
  • SQL Prompt (it isn’t required, but it makes your life so much easier)
  • SSMS Integration Pack

Build Server

Both Octopus Deploy and Redgate have plug-ins for the major build servers.

  • Jenkins:
    • Octopus — download here – Please note, you can have Jenkins interact with Octopus by using octo.exe. You can read more about that here.
    • Redgate — download here.
  • TeamCity:
    • Octopus — download here.
    • RedGate — download here.
    • Octopus — download here.
    • Redgate — download here.
  • Bamboo:
    • Octopus — download here.
    • Redgate — download here.

Deployment Target

Installing an Octopus Tentacle on SQL Server is a big no-no. The documentation goes into further details why. Instead, we will install the tentacle on a jump box which sits between Octopus Deploy and SQL Server. For security purposes, I recommend running the tentacle as a specific user account. This way you can make use of integrated security. Here is some documentation on how to configure that. Keep in mind that only works if you are using Active Directory. If you are not using that, you can still use this process, you will just need to use SQL Users instead.

For the jump box you will need to install the following items:

  • SQL Change Automation PowerShell 3.0.
  • SQL Change Automation.

Sample Project

For this walk-through, I modified the RandomQuotes project used in previous Will It Deploy videos. If you haven’t had the chance to check them out you are missing out. Do yourself a favor and watch them. Each episode is around 15 minutes. You can find the playlist here.

The source code for this sample can be found here. You will need to fork this repository so you can make modifications to it later in this article.

Configuring the CI/CD Pipeline

Everything you need is already checked into source control. All we need to do is build it and push it out to the SQL Server.

Octopus Deploy Configuration

You will need the step templates from Redgate to create a database release and deploy a database release. When you are browsing the step template you might notice the step template to deploy directly from a package. The state-based functionality for SQL Change Automation works by comparing the state of the database stored in the NuGet package with the destination database. Each time it runs it creates a new set of delta scripts to apply. Because of that, the recommended process is:

  1. Download the database package onto the jump box.
  2. Create the delta script by comparing the package on the jump box with the database on SQL Server.
  3. Review the delta script (can be skipped in dev).
  4. Run the script on SQL Server using the tentacle on the jump box.

Using the step template to deploy from the package prevents the ability to review the scripts.

This is the process I have put together for deploying databases.

I am a firm believer of having tools handle all the manual work for me. This is why my process will create the main SQL user for the database, the database, add the SQL user to the database, and the user to the role. If you want your process to do that, you can download those step templates from the Octopus Community Step Template Library.

You don’t have to add all that functionality. This is the beginning of your automated database deployment journey. Providing that much automation can be very scary without trusting the process. Completely understandable. The main steps you need from the above screenshot are:

Let’s go ahead and walk through each one. The download a package step is very straightforward, no custom settings aside from picking the package name.

The Redgate — Create Database Release step is a little more interesting. What trips up most people is the Export Path. The export path is where the delta script will be exported to. This needs to be a directory outside of the Octopus Deploy tentacle folder. This is because the “Redgate — Deploy from Database Release” step needs access to that path and the Tentacle folder will be different for each step.

What I like to do is use a project variable.

The full value of the variable is:


Other recommendations on this screen:

  • You will also notice I have supplied the username and password. I recommend using integrated security and having the Tentacle running as a specific service account. I don’t have Active Directory setup on my test machine so SQL Users it is for this demo.
  • Take a look at the default SQL Compare Options and make sure they match up with what you want. If they don’t then you will need to supply the ones you want in the “SQL Compare Options (optional)” variable. You can view the documentation here. If you do decide to go the route of custom options I recommend creating a variable in a library variable set so those options can be shared across many projects.
  • Use a custom filter in the event you want to limit what the deployment process can change. I wrote a lengthy blog post on how to do that here. My personal preference is to filter out all users and let the DBAs manage them. Even better, let Octopus manage them since it can handle environmental differences.

The next step is approving the database release. I recommend creating a custom team to be responsible for this. My preference is to skip this step in Dev and QA.

The create database release step makes use of the artifact functionality built into Octopus Deploy. This allows the approver to download the files and review them.

The final step is deploying the database release. This step takes the delta script in the export data path and runs it on the target server. This is why I recommend putting the export path in a variable.

That is it for the Octopus Deploy configuration. Now it is time to move on to the build server.

Build Server Configuration

For this blog post, I will be using VSTS/TFS and TeamCity. The build should, at least, do the following:

  1. Build a NuGet package containing the database state using the Redgate plug-in.
  2. Push that package to Octopus Deploy using the Octopus Deploy plug-in.
  3. Create a release for the package which was just pushed using the Octopus Deploy plug-in.
  4. Deploy that release using the Octopus Deploy plug-in.

VSTS / TFS Build

Only three steps are needed in VSTS/TFS to build and deploy a database.

The first step will build the database package from source control. The items highlighted are the ones you need to change. The subfolder path variable is relative. I am using sample Git repo which is why I have the “RedgateSqlChangeAutomationStateBased” folder in the path.

The push package to Octopus step can be a little tricky. You need to know the full path to the artifact generated by the previous step. I’m not 100% sure how you would know without trial and error.

Here is the full value in case you wish to copy it.


The Octopus Deploy Server must be configured in VSTS/TFS. You can see how to do that by going to our documentation.

The last step is to create a release and deploy it to dev. After connecting, VSTS/TFS with Octopus Deploy, you will be able to read all the project names. You can also configure this step to deploy the release to Dev. Clicking the “Show Deployment Progress” will stop the build and force it to wait on Octopus to complete.


The TeamCity setup is very similar to the VSTS/TFS setup. Only three steps are needed.

The first step, the build database package step, has similar options to VSTS/TFS. You will need to enter in the folder as well as the name of the package.

The kicker is you have to enter in a package version in the advanced options. If you don’t then you will start getting random errors from the Redgate tooling saying something about an invalid package version.

The publish package step requires all three of the options to be populated. By default, the Redgate tool will create the NuGet package in the root working directory.

The final step is creating and deploying the release. Very similar to before, you provide the name of the project, the release number and the environment you wish to deploy to.

The CI/CD Pipeline In Action

Now it is time to see all of this in action. For this demo, I will be creating a new database, RandomQuotes_BlogPost_Dev.

As you can see, I do not have any databases with that name. I have used this SQL Server as my test bench for automated deployments.

Let’s take a quick look at the tables stored in source control.

If we open up one of those files we can see the create script generated by Redgate’s SQL Source Control.

Kick off a build and let’s see that whole pipeline in action. The build looks successful.

No surprise, the deployment was successful in Octopus Deploy. The VSTS/TFS build was set to wait on Octopus Deploy to finish deploying the database. If the deployment failed the build would’ve failed.

Going back to SSMS and we can now see the database and the tables have been created.

Changing the Schema

Well, that is all well and good but that was a project already created. Let’s make a small change and test the whole process. There is a bit more setup involved with doing this.

  1. Clone your forked repo to your local machine.
  2. Open up SSMS and create a random quotes database on your local machine or dev.
  3. In SSMS bind the source controlled database to the newly created database. You can read how to do that in the documentation.

When linking the database to source control you need to provide the full path to the folder where the source control is stored. I store all my code in a folder called C:\Code.git. So the full path is:

Here is the text of that path for you to copy:


Once you are finished you should see something like this:

Now we can make the change to the database. For this test, let’s just add in a stored procedure which will return a value.

Now we can commit that change to source control.

And assuming the CI/CD build is set to fire on commit you should see that new sproc appears in Dev!


Automating database deployments does require a bit of prep work, but the payoff is well worth the effort. Having the auditing alone is well worth it. With this tool, I can now see who made a change, when a change was made, and when that change went into production. In the past, that was kept in another location with a 50/50 shot of it being updated.

As you start down this journey, my recommendation is to add the manual verification step to all environments until trust has been established. This will ensure you don’t accidentally check in a change that blows away half the team’s database changes.

Until next time, happy deployments!

Original Link

Understanding Backup and Restore Operations in SQL Server Docker Containers

In this article, we’ll cover the concepts of database backup-and-restore operations using SQL Server Docker containers. This is certainly the era of containers era, and it is buzzing right now, so let’s understand the importance of the backup-and-restore function on the databases on the Docker containers.

This article discusses the following topics:

  • Prerequisites
  • Initiate database backup
  • Copy the backup file to another container
  • Restore database
  • Pull SQL Server 2017 container images
  • Run SQL Server 2017 docker container image using docker run
  • Create the database 
  • Explain data persistence

Continue reading…

Backup and Restore operations with SQL Server 2017 on Docker containers using SQL Operations Studio

See More

To manage SQL Server backups, consider ApexSQL Backup, a tool that offers automation of backup, restore, and log shipping jobs, stores details of all backup activities, and enables easy cross server backup management and maintenance.

Original Link

The ”Right to be Forgotten” and Data Masker for SQL Server

The right to be forgotten is one of the main features of the new data protection legislation across the globe. Under Article 17 of one such piece of legislation in Europe, the GDPR, individuals have the right to have personal data erased from all systems and also data the company may be storing about them. Similarly, in the USA, the California Consumer Privacy Act will require companies to respond to requests for data access, deletion, and portability within 45 days.

While there may be many pieces of unstructured data littering our systems that hold customer data, perhaps one of the hardest problems to tackle is the erasure of personal information from our structured data and how we also handle database backups. whenever we need to restore this information either back into Production or other environments.

In some cases, we may be able to simply remove a user from the database manually or by other means on an ad-hoc basis, but where we have important data referentially depending on these contacts e.g. order history, which we may be using for analytics or other important workflows, it is not possible to do so.

So how do we “redact” user information while maintaining database integrity, especially where we may restore sensitive data over the top of this redaction?

For the purposes of this article, I’m going to be using an example of my Forums-Redgate-Com database, specifically the Contacts table, as the target to tackle with the right to be forgotten:

Figure 1: The top 10 rows of the Contacts table.


There are several approaches we can take to tackling this problem. The first such solution is never restoring from a backup that was taken prior to receiving the request to be forgotten. This in itself is the least ideal workflow because it renders all prior backups useless and effectively means we can just delete them.

The second approach is to write a script and maintain this moving forward that we run against environments once the backup has been restored:

UPDATE dbo.Contacts
ContactFullName = 'REDACTED',
PhoneWork = 'REDACTED',
PhoneMobile = 'REDACTED',
Address1 = 'REDACTED',
Address2 = 'REDACTED',
Address3 = 'REDACTED',
CountryCode = NULL,
JoiningDate = NULL,
ModifiedDate = NULL,
Email = 'REDACTED',
Photo = NULL,
LinkedIn = 'REDACTED'

But this approach also has several drawbacks. We are only running a SQL script and in case of audit it is generally preferred to have some kind of log or report that demonstrates what steps were taken to comply with the request. It would also be beneficial to have something that we can programmatically keep updated instead of having to maintain a full “Right to be Forgotten” script in the future.

The first step we should take is to have a central list of which users have requested the right to be forgotten. By keeping a list of not-personally-identifiable identifiers, like Customer_IDs, we can easily update this as and when new requests come in. This could be in the form of a CSV file, a table in a database, or however else we can securely store it. For this example, I have created a simple table on a separate database:

Figure 2: The table of forgotten Customer_IDs.

From here, we can use Redgate’s Data Masker for SQL Server to define a masking set, which will redact all users that appear in this table:

Figure 3: We have defined a new substitution rule for the Contacts table to carry out a similar operation to the code we wrote above.Figure 4: Next, we apply a SQL WHERE clause to fetch the redacted IDs from the relevant table. This could be from another database as in this example or could have been loaded into the database programmatically as part of the restore job.

Now we can update the RedactedUsers table, and if we go to run the Data Masker tool, it will carry out the redaction for us. In this case, let’s try ContactID number 6:

Figure 5: Customer 6 has requested the right to be forgotten.Figure 6: After the run of the masking rules, we can see only the relevant row has been redacted.

Data Masker outputs reports as part of every masking operation, and we can see below that as part of this run, only one row was processed. As we know that there is only one row in the “Right to be Forgotten” table with 12 columns to be redacted, we know that the masking has completed successfully.

Figure 7: An extract of the Data Masker Masking Report

Now that we have the foundation of our redaction process, all that is left to do is automate it as part of a restore. In this case, we can script out the calling of the Data Masker command line and call it as part of the SQL Agent job:

start/wait "" "C:\Program Files\Red Gate\Data Masker for SQL Server 6\DataMasker.exe" "C:\Users\chris.unwin\Documents\Data Masker(SqlServer)\Masking Sets\RedactForgottenUsers.DMSMaskSet" -R -X

This means that once the database is restored to a state before we initially redacted the information, we can then immediately redact it again (but we can also just maintain the list of forgotten users. In this case, we’ve added rows 2 and 5 over time):

Figure 8: Result of the redaction after running the command line for Data Masker after restoring.


In summary, there are many ways we can handle actioning the “Right to be Forgotten” when it is requested, whether this is handling the update manually, scripting the change, or using Redgate’s Data Masker for SQL Server to achieve the end result with the level of logging and reporting we would like to retain for audit purposes.

The goal is always to ensure that we are compliant with the necessary legislation, yet we can respond to requests we receive with the lowest possible overhead both to the data subject and our own teams. The solution outlined in this article is one such approach to achieve this.

Original Link

Getting Started With Database Development Using SQL Provision

Developers, when working on databases rather than the application code, often find they have less freedom to experiment than they are used to. It’s not necessarily easy to set up a database for testing, especially if the process isn’t automated. They’ll need to dig around in source control, build the database at the correct version, and then fill it with a set of anonymized test data. Once it’s done, it tends to be a “protected” asset, to avoid repeating the whole process. However, during the early “proof-of-concept” phases of development, this is precisely what it required. Developers need to feel free to experiment, and if they break something, simply tear down the database, rebuild it in its previous consistent state, and try again, just as they would with any application code.

If the database is large and they need to test with realistic data in realistic volumes, then to recreate it on demand in seconds rather than minutes requires a different approach. SQL Provision, which integrates SQL Clone and Data Masker for SQL Server, handles this use case very well. SQL Clone builds one full copy of the source database, called an image. From that image, developers can create multiple clones very quickly. You can think of a clone as a virtualized database copy that functions just like a normal database.

Migrating an existing development database to a clone can seem a bit cumbersome the first time you try it, but it’s a one-time operation per database, and this article will demonstrate how easy it is to automate the process, using a little PowerShell. I’ll use only SQL Clone in this article, but if you need to perform database masking before deploying clones to your development machines, then my previous article demonstrates the basics of how to do that.

The Process

Having installed the SQL Clone server, I started to migrate all my development work over to using clones. What I really like is that I can reset a clone in seconds after any destructive data changes, and then immediately continue with my work. Getting the databases moved is a multi-stage process that varies only by the name of the database and the SQL Server instance. This makes it a great candidate for a scripted solution.

This is the high-level process that I follow:

  1. Create an image from the existing database
  2. Drop the existing database from the instance
  3. Create a clone with the same name as the database

I’ll demonstrate how this process works for one database on my system, called PrestigeCars. This is a sample database used in a series of T-SQL articles from Adam Aspin on, and in his book, Query Answers with SQL Server. Of course, you can just as easily use any other database.

As defined by the above process, we need to perform these specific steps:

  1. Create the PrestigeCarsBase image from the PrestigeCars database and store this in c:\SQLCloneImages
  2. Drop the PrestigeCars database
  3. Create a PrestigeCars clone on the instance using the PrestigeCarsBase image.

That’s it. Once this is complete, I can easily take an existing database and migrate it to a cloned copy in a rapid and consistent fashion. Let’s look at the details.

Using PowerShell

Listing 1 shows the complete code for a PowerShell function that I wrote, called Create-DevDatabase, which implements the previous three steps to migrate a development database to a clone. As you will see, it’s designed for easy reuse; it accepts a set of parameters, the values for which you simply amend, as required, each time. This section will walk through the code, but if you want to just see the process in action, skip ahead to the Migrating Databases to Clones section.

Function Create-DevDatabase { <# .SYNOPSIS This function replaces a database with a cloned version .DESCRIPTION This function takes a database name and builds an image from that database. It then drops the database and recreates it as a clone. .EXAMPLE Create-DevDatabase "http://dkrspectre:14145/" "PrestigeCars" "dkrSpectre\SQL2017" "c:\SQLCloneImages" .PARAMETER CloneServerURL The URL of the SQL Clone Management Server .PARAMETER DatabaseName The name of the database that is to be imaged and cloned .PARAMETER MachineName The name of the SQL Server host machine .PARAMETER InstanceName The name of the SQL Server named instance. Blank for no instance .PARAMETER ImageLocation Path to the image location storage for the SQL Clone server #> Param ( [Parameter (Mandatory=$True)] [string] $DatabaseName, [string] $CloneServerURL = "http://dkrspectre:14145", [string] $MachineName = "dkrSpectre", [string] $InstanceName = "SQL2017", [string] $ImageLocation = "c:\SQLCloneImages" ) $Proceed = $True try { Connect-SqlClone -ServerUrl $CloneServerURL } catch { $Proceed = $false Throw ("Error: " + $error[0].Exception) } # Check for Image name clash $ImageName = "${DatabaseName}Base" Write-Host("Checking Image: [" + $ImageName + "]") try { $ImagePath = Get-SqlCloneImageLocation -Path $ImageLocation $CheckName = "${ImageName}*" $SqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName $MachineName -InstanceName $InstanceName } catch { $Proceed = $false Throw ("Error: " + $error[0].Exception) } If ($Proceed -eq $True) { try { $clones = Get-SqlCloneImage -Name $CheckName # Create New Image if (!$clones) { write-host("Creating: " + $ImageName) $ImageOperation = New-SqlCloneImage -Name $ImageName -SqlServerInstance $SqlServerInstance -DatabaseName $DatabaseName -Destination $ImagePath Wait-SqlCloneOperation -Operation $ImageOperation } else { $Proceed = $false } } catch { $Proceed = $false write-host("Error:" + $error[0].Exception) } } # Drop Database If ($Proceed -eq $True) { write-host("Dropping: " + $DatabaseName) try{ $Query = "Drop database " + $DatabaseName Invoke-Sqlcmd -ServerInstance "${MachineName}${InstanceName}" -Database "master" -Query $Query } catch { $Proceed = $false write-host("Error:" + $error[0].Exception) } } # Create new database clone from image If ($Proceed -eq $True) { write-host("Creating Clone: " + $DatabaseName + " from Image: " + $ImageName) try { $image = Get-SqlCloneImage -Name $ImageName $image | New-SqlClone -Name $DatabaseName -Location $SqlServerInstance | Wait-SqlCloneOperation } catch { $Proceed = $false write-host("Error:" + $error[0].Exception) } } }

Listing 1 — The CloneFunctions.ps1 script, which creates the Create-DevDatabase function

This function accepts several important parameters that are needed for creating an image and a cloned database as follows:

  • $CloneServer — This is the URL path to your SQL Clone Management Server
  • $DatabaseName — The name of the database to be migrated. In my example, I’ll use PrestigeCars here.
  • $MachineName — The hostname of the machine that is running SQL Server and contains the database that is being migrated.
  • $InstanceName — The name of the named instance, if one is being used.
  • $ImageLocation — This is the URL of the location where the SQL Clone images are being stored. This is usually a file share, but if a developer wants to work with a clone for a short period, during proof of concept, then it could just be a local folder.

Since I always run this script on my local system, I’ve set some default values for a few of the parameters. Just change these to suit the needs of your own system. Having set up all the parameters, the script implements each step in our process, allowing for any errors that might occur. In the event of an error, I set a variable, $Proceed, to false, which prevents the rest of the script from running. This is useful in debugging, but it also allows me to find an error and perhaps finish the process manually.

After connecting to the SQL Clone Management Server, I set the image name to be the database name plus the string “Base” string. You can change this or even add a date to the name of your image if that’s appropriate for you. Since most of the SQL Clone cmdlets don’t take strings as parameters, I need to create some objects, such as $ImagePath and $SqlServerInstance, and then use them to look for an existing image of the same name. If one exists, the process stops. If this happens, I’ll choose a new image name and re-run the script. Otherwise, I create the image from the database. It’s at this stage that I could extend the process, modifying the data to mask any sensitive or personal information, as shown in my previous article, before creating the image. In this case, my image is just an exact copy of this database.

Having created the image, the script calls the Invoke-Sqlcmd PowerShell cmdlet, to run the DROPDATABASE command. There are other ways to run this command in PowerShell, but Invoke-Sqlcmd and should be fine here, since this script is intended for sysadmins ( i.e. developers who are sysadmins on their own machine). If not, then you’ll need to have CONTROL permission on the database, or the ALTERANYDATABASE permission, or be a member of the db_owner role for the database, for this to work.

The last part of the script then creates a new clone, using the original name of the database. Since we’ve just dropped the original database, there should be no naming collisions.

That’s it for the code, so how does this work in practice?

Migrating Databases to Clones

I’ll use this PowerShell function to migrate my PrestigeCars database to a clone. Figure 1 shows the database in SSMS Object Explorer, on my development SQL Server instance. It has all the data I need for testing already in it.

Figure 1 — The existing database in SQL Server

Once we’ve migrated this database to a clone, it will still look and work just the same, which is what we want. The way to tell whether you’re working on a clone or a real database is to check its extended properties, which is currently blank for this database, as you can see in Figure 2. The SQL Clone process will add an extended property to the cloned database, as we’ll see later.

Figure 2 — Extended Properties for PrestigeCars database

Now let’s start the migration process. I saved my PowerShell function from Listing 1 in a file called CloneFunctions.ps1. Then, from a PowerShell command line shell, I can run this to load my function into the current session:

. .\CloneFunctions.ps1

Note that this is dot-sourcing, and I use a period followed by a space and then a period, backslash, and my filename. This doesn’t return any result but simply imports the functions into the session, as shown in Figure 3.

Figure 3 — Dot Sourcing my function

Alternatively, in the PowerShell ISE, simply hit F5 to run the function. Now, I can call the Create-DevDatabase function, and it recognizes it and interactively fills in the function and parameter names. Here, we just need to supply the database name as a parameter, since all the others have default values, set as appropriate for my development system.

Figure 4 — Executing the function

As soon as I execute the function, I can switch to the SQL Clone Server dashboard to see the image being created.

Figure 5 — The image being created

Once this is complete, the PowerShell command line displays the messages that the function writes to the console, at each step. Figure 6 shows the output on my system for the entire PoSh session.

Figure 6 — PowerShell command line messages

Back in SQL Clone Server, we can now see that the image exists along with one clone. You can see the default values from the script have been used when building the image and cloned database.

Figure 7 – The image and clone details

On the SQL Server instance, we can see that SSMS Object Explorer looks the same, but if we look at the properties of the database (shown in Figure 8), we can see that there is a new extended property called IsSQLCloneDatabase that has been set during the process of creating the clone from the image.

Figure 8 — PrestigeCars database properties

The database has been migrated to a clone, and I can now do any work I need to with this database and rebuild a clone if I need to reset to the base image. I can alter the schema, change the data, and more. If I want to reset my database and undo the changes, I can drop the clone and rebuild a new one from the existing image, in seconds, as shown in this article.


This article has shown how a developer, who is a sysadmin on their own SQL Server instance, can migrate their existing development databases to clones using SQL Provision. Using a simple PowerShell function, we have seen a repeatable process that is quick and consistent.

I’ve been using it to migrate my standalone databases to cloned copies. The great advantage of working with a clone is that it takes seconds to reset the data and schema back to how it was at the time I created the image. If I’ve changed any objects in the meantime while working on the clone, I save those changes using SQL Source Control or SQL Change Automation, drop and recreate the clone, and then re-apply my development changes.

I hope you’ll start to follow my example and migrate all your databases to clones, which you can tear down and recreate on demand. The ability to treat our development databases more like all our other code is another step in the journey to implementing database DevOps.

Original Link

Understanding Data Sharing in SQL Server Docker Containers

This article talks about shared volumes and the preferred mechanism for data persistence using the Docker containers.

This article covers the following topics:

  1. Introduction of the Docker volumes.
  2. How to manage volumes using Docker CLI commands or Docker API.
  3. How the sharing of data between containers and between containers and hosts takes place.
  4. Sharing database files across containers.
  5. And more…


Containers are independent of the storage engine and volumes. Let’s discuss briefly on how Docker accomplishes the idea of images and containers. How does the layer organized in storage and keeping them isolated, let them be stacked on top of each other as layers?

We are in the era of rapid development and faster deployment; we also know how to do a release faster. Now, we will see how to build the application and port it as a micro-services using containers with data persistence.

Before jumping to the demo, let’s understand the available Docker solutions. Let’s talk about sharing data between containers and between containers and hosts.

Docker offers the solution of data externalization, and this feature is called volumes. The volumes are like a shared folder or the directory structures of the file system. They’re virtual discs that you can store data in and share them between the containers, between containers and the host, or both. So they have two main varieties of volumes available within Docker.

We’ve got the persistent ones, where we can place the data there, and it will be available to the host. When the container goes away, the data will still be available. Another one is ephemeral volumes. They exist as long as the container is using them. But when no container is using them, they evaporate. So they’re ephemeral. They’ll stick around as long as they’re being used but they’re not permanent. These are not part of images. No part of volumes will be included when you download an image and no part of volumes is going to be involved if you upload an image. They’re for your local data, local to this host. So first let’s talk about sharing data between the host and a container.

The underlying file systems manage and determine which bits on the drive are part of which file and it’s up to the kernel with the file-systems to keep track of every file. Now on top of all that, you can take programs, and programs can pretend to be file systems. We call this FUSE file system. For a detailed example, you can refer here.

Let’s see an example to understand the how the layers work in Dockers. For the demo, I’ve created two folders in the home directory named SQL Server and MongoDB also created few files in each directory.

The following list of the commands ensures the creation of the files in the respective directories.

$ docker-machine ssh
$ pwd
$mkdir SQLServer
$ cd SQLServer/
~/SQLServer$ touch Feature1 Feature2 Feature3 Feature4 Feature5
~/SQLServer$ ls
Feature1 Feature2 Feature3 Feature4 Feature5
~/SQLServer$ cd ..
$ mkdir CosmosDB
~$ cd CosmosDB/
~/CosmosDB$ touch MongoFeature1 MongoFeature2 MongoFeature3
~/CosmosDB$ cd ..
docker@default:~$ ls –R$ docker-machine ssh $ pwd $mkdir SQLServer $ cd SQLServer/ ~/SQLServer$ touch Feature1 Feature2 Fe

Now, mount the SQL Server directory on top of the working directory using the following sudo mount command:

sudo mount -o bind <Source : name for the source process or directory> < Target: name of the destination directory or process>
$sudo mount -o bind SQLServer CosmosDB
$ls -R

Let’s take a look in the CosmosDB directory. CosmosDB has the same files of SQL Server. I didn’t copy the files; instead, I just layered one set of files on top of the other. In fact, we can take a look at that layering by executing the df -a command. The -a option binds mounts as well.

There’s just another directory that SQL Server mounted on top of the CosmosDB directory. When I unmount, they’ll come right back again.

$sudo umount CosmosDB

Now, let’s take a look at the directory structure. Everything is exactly the way it was. I just placed the file system temporarily on the other file system layer. This is how Docker does the shared folders between a container and the host.

Let’s discuss sharing data back and forth between this host and the Docker containers.

First, let’s get into the Linux host using:

$ docker-machine ssh

  1. Create a directory to store some example data, for example, demo.
  2. $mkdir demo
  3. The pwd (Present Working Directory) is /home/docker/demo.
  4. Run the container with docker run-it, and new part, – v/home/docker/demo:/sharedfolder.

Inside the container when I run it, the data is going to be found under /sharedfolder.

$docker run -it -v /home/docker/example:/sharedfolder centos bash
# ls /home/docker/demo/ -l
#cat > /home/docker/demo/test1
#ls sharedfolder/ -l

Let’s go ahead and create a file named test1. Now, when I exit this container and browse the demo folder, there are test and test1 files.

$ ls /home/docker/demo/ -l

So the file is shared with the host.

Now, let’s talk about a more interesting case of sharing data between the containers. This introduces a new argument to the Docker run, called volumes-from.

These are shared discs that exist only as long as they are being used, the scope is limited and its existence is limited. When they’re shared between the containers, they’ll be common for the containers that are using them.

Let’s create a volume for the container which is not shared with the host.

$ docker run -it -v /demoshare centos:latest bash

docker run -it -v /demoshare creates the folder and runs it on the centos:latest container with a bash console.

Let’s create a file with some data on the container using the cat command.

# cat >demoshare/share_between_containers
# ls demoshare/ -l

Now, start up another Docker container, call it has the second session. First look up the name of that container that I just started by running Docker ps -l.

$docker ps -l

$ docker run -it --volumes-from gracious_heyrovsky centos:latest bash

Put the docker run command, then -volumes-from (this is the new part), then paste in the name of the machine that has the volumes I want to use. The image, centos:latest and the console to start with the container is bash. Now if I go look in /demoshare using the following command, it will list the file layered on the new container.

# ls demoshare/ -l

Create another file, share_between_containers_1 and add some content to the file.

# cat >demoshare/share_between_containers_1
#ls demoshare/ -l

Now exit the original container, the demoshare is still there. Let’s go ahead and start up a third container, repeat the same steps that we followed for creating a second container.

$docker ps –l
$ docker run -it --volumes-from brave_goldwasser centos:latest bash
# ls /demoshare/ -l
# cat >demoshare/share_between_containers_2
#ls /demoshare/ -l

We can see that the data file, originated in one container, was inherited by a second container, and then inherited by a third container, even though the machine that created it is gone.

Now, when I exit both of these containers, this is the important part to understand, right then when I exited the last container using this volume, it went poof! And now it’s gone. That’s what volumes are for. They are ephemeral. They can be passed from one container to the next, but they are not saved. They will eventually go away.

To view the list of all the volumes that are attached to the host machine use docker volume ls:

$docker volume ls

We can also find out where the volume exists on the host machine using the docker inspect command:

docker inspect -f "{{json .Mounts}}"

For example,

$ docker inspect -f "{{json .Mounts}}" voltest

We can also get the similar data using the docker volume inspect command:

$docker volume inspect 89441820476a4fe62477f44e81b968187e2994d75e4ca4484ec8959607ddafa8

To delete the volumes, use the docker rm <Volume Name> command.

$docker volume 89441820476a4fe62477f44e81b968187e2994d75e4ca4484ec8959607ddafa8

Docker Volume

In this section, we will discuss how the application database data externalization is possible using the Docker volume option. As long as the containers remain intact with the host, the data will remain safe even if the container is stopped or restarted. However, if you remove the container, your databases get removed with it and it’ll be gone forever.

Let’s discuss the Docker’s solution that keeps the data safe across containers using a Docker data volume. It is that simple, during the SQL Server container creation, map file directory to the SQL Server database using the -v parameter.

Let’s create a new container:

#docker run -e'ACCEPT_EULA=Y' -e'MSSQL_SA_PASSWORD=thanVitha@2015' --name SQLdemo –v sqlservervolume:/var/opt/mssql -d microsoft/mssql-server-linux:2017-latest

#docker ps –a

-v, this is where we’re going to specify the volume information. Create a new volume sqlservervolume and map it with the internal directory structure of the SQL Server instance, or the Linux instance, where SQL Server stores its data and log files. In this case, the default volume location /var/opt/mssql is mentioned.

Now we’ve created the Sqldemo container. Now, instead of storing database files within the container, the data files are stored in the /var/opt/mssql directory, the container data is now externalized. Its stored in the sqlservervolume data volume. We can take a look at volumes using docker volume ls.

#docker volume ls

Let’s go into the container and create a database using the docker exec command and sqlcmd.

#docker exec –it sqldemo bash
#/opt/mssql-tools/bin/sqlcmd –U SA –P thanVitha@2015
1>create database sqlvolumetestDB;

So far, we’ve created a database sqlvolumetestDB. Let’s go ahead, quit SQL shell and exit out of the Linux shell. Now, go ahead and remove the container. Before that, stop the container using the docker stop command. Once it’s stopped, remove the sqldemo container using docker rm command but the volume still exists.

Now, create a new SQL container and link the existing volume to the SQL container. The new SQL instance is going to be Newsqldemo.

#docker run -e'ACCEPT_EULA=Y' -e'MSSQL_SA_PASSWORD=thanVitha@2015' --name Newsqldemo –v sqlservervolume:/var/opt/mssql -d microsoft/mssql-server-linux:2017-latest

Let’s go into the Newsqldemo container and make sure that we still have the database that we’d created earlier in the demo.

#docker exec –it sqldemo bash
#/opt/mssql-tools/bin/sqlcmd –U SA –P thanVitha@2015
1>select name from sys.databases
1>use sqlvolumetestDB
1>create table dummy(id int, name char(20));
1>insert into dummy values(1,’Prashanth’),(2,’thanVitha’);

Once we get into the SQL shell, query the sys.databases system object. This will list all the databases of the Newsqldemo instance.

That’s all for now…Stay tuned for more updates.


Docker containers definitely a choice and talking point for many developers for its rapid development and seamless deployment process.

Docker doesn’t give you a way of saying mount this guest file system into the host, preserving what was on the guest. It always chooses to mount the host filesystem over the guest filesystem.

You can also have multiple containers linked to this single volume in order to have them share a common storage resource. With Docker volumes, you can save data across multiple Docker containers or share data between several containers at the same time.

Original Link

My First PowerShell: A Simple Story With Dramatic Flair

I needed an automated way to restart remote servers in my test environment for SentryOne. I knew that this sounded like a PowerShell-ish thing, but I lack what most people call “experience” when it comes to PowerShell. (Maybe that’s what everyone calls it?)

First Attempt

First, I came across Restart-Computer -ComputerName $ComputerName -Credential $Credential -Force, which seemed to be exactly what I needed.

Next, I opened PowerShell and gave it a try:

Image titlePowerShell Restart-Computer with Login Popup Window

PowerShell Restart-Computer with Login Popup Window

As shown above, this prompted for credentials to complete the task. Technically, this was what I wanted. However, I wanted to place Restart-Computer in a loop; therefore, the popup asking for credentials wouldn’t suffice.

Avoiding Popup for Credentials

I discovered that I could use a secure string for the credentials, however, none of the initial examples I found explained how to create the secure string. Way to make me feel even more like a foolish newbie. 😉 A little more searching and I was on my way.

Creating a Secure String

 Read-Host "Enter Stuff" -AsSecureString | ConvertFrom-SecureString | Out-File "C:\PowerShell\mysecurestring.txt" 

PowerShell Create a Secure String

PowerShell Creates a Secure String.

Voilà! A secure string is created from a string that totally isn’t my password. Seriously, I’m not *that* trusting.

PowerShell the Secure String

PowerShell the Secure String.

Using a Secure String

Now that I’ve created a secure string, I can use it in a credentials object to avoid the popup.

$username = "mconnors" $password = cat C:\PowerShell\mysecurestring.txt | convertto-securestring $cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $password $strComputer = "OA-SQL-100" Restart-Computer -ComputerName $strComputer -Authentication default -Credential $cred

Clearly, this is something that looks more loop-friendly:

PowerShell Using the Secure String

PowerShell Using the Secure String.

Now it was loop-ready and I wrapped it in a simple for-loop.

The PowerShell Loop

$username = "mconnors" $password = cat C:\PowerShell\mysecurestring.txt | convertto-securestring $cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $password $strComputer = "OA-SQL-" For ($intInc = 1; $intInc -le 100; $intInc++) { $serverNameOrIp = $strComputer + $intInc Write-Host $serverNameOrIp Restart-Computer -ComputerName $serverNameOrIp -Authentication default -Credential $cred }

One of the first problems I saw was the computer name was OA-SQL-1 instead of OA-SQL-001. I had a formatting issue to resolve. Padding the zeros into the formatting was easy enough:

 $serverNameOrIp = $strComputer + "{0:000}" -f $intInc 

Windows PowerShell ISE

Another problem was that pasting the loop into the PowerShell command window and hitting enter didn’t look right. It wouldn’t run, either. There were ‘>’ for all new lines and it appeared to simply hang when I would execute it. A coworker asked where I was running it and suggested that I use the Windows PowerShell ISE window instead of the more old-school PowerShell command window. I tried that and my loop executed without requiring any changes. Of course, I’d nearly lost my mind at that point…


Once it was working, I made some tweaks to output the computer name and timestamp so that I could see the progress. I added a sleep timer so that the host machine wouldn’t flounder while restarting 100 VMs. I ran into some issues with the SQL Server service timing out on restart across a few random machines. Better to wait a little longer to know they’re all safe than discover that during test execution.

As seen above, I was using Write-Host in my script, until I was advised against it. It turns out that Write-Host is kind of bad.

The Solution (So Far)

The following script is what I’m executing now for various ranges of servers. It gets the job done. Could it be improved? Probably. I still have much to learn about PowerShell.

$username = "mconnors" $password = cat C:\PowerShell\mysecurestring.txt | convertto-securestring $cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $username, $password $strComputer = "OA-SQL-" For ($intInc = 1; $intInc -le 10; $intInc++) { $serverNameOrIp = $strComputer + "{0:000}" -f $intInc $CurrentDate = Get-Date $CurrentDate = $CurrentDate.ToString('hh:mm:ss') $restartString = '{0} {1}' -f ($serverNameOrIp,$CurrentDate) Write-Output $restartString Restart-Computer -ComputerName $serverNameOrIp -Authentication default -Credential $cred Start-Sleep 30 }

Windows PowerShell ISE showing Restart-Computer Loop

Windows PowerShell ISE showing Restart-Computer Loop.

What I Learned

What I believed would be a quick foray into a line or two of PowerShell turned into a larger learning experience.

Things I Know Now

  • To use Windows PowerShell ISE instead of just PowerShell.

  • How to store and access a secure string for credentials.

  • Formatting output, such as the padded 0s and desired date/time.

  • Speaking of output, why I should use Write-Output instead of Write-Host (thanks, Allen! (b|t)

Finally, PowerShell might *actually* be cool. I’ve been hearing that for years, but now I’ve experienced it!

What’s Next

So I have this small amount of PowerShell knowledge now. It’s created intrigue for me. I’m sure it can make my life easier and that I can integrate it into Visual Studio Load and Performance Testing to automate a few more steps for me.

Original Link

Fetch T-SQL With Passed Parameter Value Using Stored Procedure


Using “sys.objects,” we can get a list of tables and stored Procedures created in the Database. SQL Server now supports table-valued parameters, which allow us to send data tables as parameters to Stored Procedures. It still uses the same ADO.NET API. Using SYSOBJECTS and SYSDEPENDS, we can get all the tables, stored procedures, and other database object-related information.


You can apply the OBJECT_ID, OBJECT_NAME, and OBJECTPROPERTY() built-in functions to the objects shown in sys.objects. It contains a row for each user-defined, schema-scoped object that is created within a database. sys.objects does not show DDL triggers because they are not schema-scoped. Table Valued Parameter allows a table (i.e multiple rows of data) to be passed as a parameter to a stored procedure from T-SQL code or from an application. It was not possible to pass a table variable as a parameter to a stored procedure in old versions of SQL Server.

Step 1

Create one table named Employee.

The table script is given below.


Step 2

I entered some dummy data.

select * from Employee


Step 3
Create one stored procedure named Sp_EmployeeFullQuery.

Create procedure Sp_EmployeeFullQuery ( @ID VARCHAR(200) ) as begin DECLARE @FullQuery NVARCHAR(MAX) set nocount on; SET @FullQuery='select * from Employee where Employee.ID=' + CAST(@ID AS VARCHAR) + '' end PRINT @FullQuery EXEC (@FullQuery) 

In this procedure, I defined one parameter called @ID.
Declare one variable @FullQuery.


Now, put all SQL query inside @FullQuery variable.

SET @FullQuery='select * from Employee where Employee.ID=' + CAST(@ID AS VARCHAR) + '' 

At last, whatever parameter value is passed, this will show through @FullQuery variable by using the last two parts.

PRINT @FullQuery EXEC (@FullQuery) 


exec Sp_EmployeeFullQuery 1 

Here, 1 means AutoGenerate ID column value, which will show the related column data by filtering this ID value.

You will get all the details of SQL query with the already passed parameter value.


Create procedure.
Using primary key, autogenerate ID column value showing the records.
Also, generate SQL query, which is defined in a stored procedure by passing parameter value.

Original Link

Dates and Times in SQL Server: DATENAME()

Last time, we looked at DATEPART(). This post is all about the DATENAME() function.

So Many Similarities

There are many similarities between DATEPART and DATENAME. Where DATEPART returns the date or time part as an integer, DATENAME returns the part as a character string.

This DATENAME function also takes two parameters: the date or time part we want back and the input date. Just as we saw with   DATEPART , the documentation indicates the input date parameter must be an “expression that can resolve to one of the following data types: date, smalldatetime, datetime, datetime2, datetimeoffset, or time.”

Similarly, the date and time parts that can be returned look much like those in DATEPART, which gives us another opportunity for the reminder that we should avoid using the available abbreviations in order to help with writing clearly understandable code.

datepart Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
TZoffset tz
ISO_WEEK isowk, isoww

Settings and Formatting

The  LANGUAGE DATEFORMAT , and DATEFIRST settings will affect the output, just as with DATEPART.

Speaking of date formatting (which as we’ve learned previously is easy to get wrong), the documentation addresses the Year-Day-Month format:

In SQL Server 2017, DATENAME implicitly casts string literals as a datetime2 type. In other words, DATENAME does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

This is a good thing for consistency across regions. Enforcing the use of the DATETIME2 data type removes ambiguity.

Example code and output

Using the built-in function SYSUTCDATETIME() to get the current date and time in UTC format as a DATETIME2(7) data type, we can get the following output for each date or time part:

SELECT @dt AS [Current UTC Date and Time]

And here is the output:

Remember that all of these values are being returned as strings. We should keep this in mind if we want to manipulate the values.

Share your favorite date or time part with me in the comments.

Original Link

Troubleshooting Blocking in SQL Server using SQL Monitor

A call comes into the DBA from the Help Desk. There is an urgent problem with a reporting application; unhappy users say that their screens are “frozen,” and not because of an overactive A/C vent. Some have reported seeing a timeout issue related to SQL. Is it a blocking problem?

As a DBA, if blocking problems catch you off-guard, you’ll need to do some reactive investigative work to piece together a picture of what processes are, or were, blocked, what sessions caused the blocking, what SQL was running, what locks were involved, and so on.

Ideally, though, you’ll have SQL Server monitoring in place and will have received an alert, providing all the required diagnostic data so that you’d resolved the issue before the Help Desk even picked up the phone.

What Causes Blocking?

In a busy database, many user transactions compete for simultaneous access to the same tables and indexes. Generally, SQL Server mediates access to these shared resources by acquiring various types of lock. Blocking occurs when one or more sessions request a lock on a resource, such as a row, page, or table, but SQL Server cannot grant that lock because another session already holds a non-compatible lock on that resource.

Assuming use of the default isolation level ( READCOMMITTED), let’s say that session A runs a transaction that modifies a few rows in a table. SQL Server acquires Exclusive (X) locks on those rows. This lock mode is incompatible with other lock modes, so if a second session, B, wishes to read those same rows, which requires the acquisition of a Shared (S) mode lock, it will be blocked until the transaction holding the X lock commits or rolls back.

Locking and the blocking it causes is usually fleeting and is a perfectly normal and desirable operational characteristic of a database. It ensures, for example, that a transaction doesn’t read data that is in flux ( i.e. it prevents “dirty reads”), and that two transactions can’t change the same row of data, which could lead to data corruption. However, when blocking occurs for extended time periods, it can impact the performance of many user processes.

In severe cases, multiple sessions can be blocked at various points of a long blocking chain. The responsiveness of SQL Server degrades dramatically, and this situation is often mistaken for a deadlock.

The big difference is that a deadlock causes a specific error, and one of the transactions will be rolled back. With blocking, however severe, no error is raised. The session at the head of a blocking chain will not be waiting for a lock. It may be waiting for a latch, a memory allocation, or IO, but the blocking chain will clear as soon as the resource becomes available and the head blocker can complete its work.

Investigating Historical Blocking Issues

Let’s say that you’re investigating what caused blocking after the issue has resolved itself or are performing a more general investigation on a database where extended periods of blocking are an ongoing issue. For example, perhaps the aggregated wait statistics in the sys.dm_os_wait_stats Dynamic Management View (DMV) reveal large accumulated wait times related to locking waits.

Figure 1

One way we can investigate the possible cause is to use the index usage statistics in the sys.dm_db_index_operational_stats DMV to look for indexes that have high accumulated locking waits, as demonstrated for example by Jason Strate.

Figure 2

If we’re also seeing a high historical level of waits such as PAGEIOLATCH_SH (as in Figure 1), then this indicates that sessions are experiencing delays in obtaining a latch for a buffer page. Does this mean that the root cause of the blocking is session waiting to perform I/O i.e. a disk I/O bottleneck? Maybe, but it could equally well be a reporting query that accesses the table using that index regularly reads gigabytes of data from that table.

In other words, while these DMVs might reveal one or more tables and indexes that are “locking” hotspots, it’s not necessarily easy to find out why without a lot of further investigation.

Diagnosing Current Blocking

If we’re made aware of a potential blocking problem while it is still occurring, then SQL Server provides a wealth of information to help us investigate and resolve the problem.

We’ll review briefly the information available to diagnose current blocking using the DMVs, or PerfMon, and then move on to the most common technique, which is to capture using blocked process report, ideally using Extended Events (but SQL Trace if that’s not possible).

Adam Machanic’s sp_whoisactive is also a very popular tool for investigating ongoing blocking issues, but I won’t cover it in this article.

Investigating Waiting Tasks Using the DMVs

If a request is active, but waiting to acquire a resource in order to proceed, it will appear in with the sys.dm_os_waiting_tasks DMV. This view will tell us the type of wait, the resource on which the request is waiting for access, and more. If blocking is the source of our problem, we’d expect to see waits to acquire locks on rows, pages, tables and so on.

There are plenty of different ways to query this DMV, joining to other DMVs for details of the blocked and blocking sessions and the queries being executed. I adapted the query provided in Listing 1 of SQL Server Performance Tuning using Wait Statistics: A Beginner’s Guide (free PDF download). Figure 3 shows some sample output.

Figure 3

The wait type for locks has the form LCK_M_<lock type>, so, for example,LCK_M_SCH_M is a wait to acquire an SCH_M (Schema Modification) lock. Figure 1 shows a blocking chain involving four sessions (IDs 79, 80 and 74 and 78). At the head of the chain is session 79, which is blocking session 80, which in turn is blocking session 74, which is blocking 78.

Session 79 is the only session that is not waiting to acquire a lock. It is running a transaction against the SalesOrderHeader table. Typically, this might be a transaction that for whatever reason that is taking a long time to modify data, or perhaps has experienced an unexpected error that caused the batch to abort but left the transaction open (uncommitted).

The locks held by session 79 are blocking session 80 from acquiring the lock that it needs in order to modify the clustered index on SalesOrderHeader (in this case, to perform an online index rebuild). At the bottom of the chain, session 78 is blocked, waiting for session 74 to complete its work, so that it can acquire a shared read lock on the SalesOrderHeader table, in order to read the required rows.

If we need further details on the types of locks held by each session in the chain, on which resources, as well as locks that sessions are waiting to acquire, we can use the sys.dm_tran_locks DMV, supplying the session IDs acquired previously. DMVs such as sys.dm_tran_active_transactions DMV will provide a list of all transactions that are active at the time the query is executed; sys.dm_exec_sessions will reveal the owner of any blocking sessions, and so on.


A tool such as Performance Monitor (PerfMon) provides some counters for monitoring for cases of excessive locking and blocking. For example, SQLServer:General Statistics object will show the number of blocked processes detected; the SQL Server: Locks object can provide Avg Wait Time (ms), Lock Waits/sec and more. However, again, these only provide an indication of a possible problem.

Monitoring for Blocking Using the Blocked Process Report

If blocking is causing issues, we can run an Extended Events event session, to log occurrences of blocking that exceed a specific time threshold, and capture the blocked_process_report event.

By default, the “blocked process threshold” is zero, meaning that SQL Server won’t generate the blocked process reports. We need to configure by the “blocked process threshold” to a specific value (in seconds) using the sp_configure option. For example, if we set it to 15 seconds, then the event will fire three times if a session is blocked for 45 seconds. Erin Stellato shows how to configure the threshold then define an extended event session to capture the report.

In Figure 4, the event has fired 6 times. Each of the three blocked processes in the chain fired the event after being blocked for 15 seconds, and then again 15 seconds later.

Figure 4

To open the first blocked process report, simply double-click on value column for this field, in the lower pane. Listing 1 shows the truncated output for one of the reports, showing the online index rebuild ( spid80) blocked by an uncommitted transaction ( spid79) on SalesOrderHeader.

<blocked-process-report monitorLoop="89390"> <blocked-process> <process id="process1f2cd7c7c28" taskpriority="0" logused="168" waitresource="OBJECT: 5:1586104691:0 " waittime="558361" ownerId="23292709" transactionname="ALTER INDEX" lasttranstarted="2018-06-12T18:43:20.853" XDES="0x1f27aa04490" lockMode="Sch-M" schedulerid="1" kpid="1928" status="suspended" spid="80" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2018-06-<em>…output truncated…</em>"> <executionStack> <frame line="1" stmtend="194" sqlhandle="0x01000500cb55c82e705e3880f201000000000000000000000000000000000000000000000000000000000000" /> </executionStack> <inputbuf> ALTER INDEX PK_SalesOrderHeader_SalesOrderID ON Sales.SalesOrderHeader REBUILD WITH (ONLINE = ON); </inputbuf> </process> </blocked-process> <blocking-process> <process status="sleeping" spid="79" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2018-06-12T18:43:12.377" lastbatchcompleted="2018-06- <em>…output truncated…</em>"> <executionStack /> <inputbuf> BEGIN TRANSACTION SELECT FirstName , LastName , SUM(soh.TotalDue) AS TotalDue , MAX(OrderDate) AS LastOrder FROM Sales.SalesOrderHeader AS soh WITH (REPEATABLEREAD) INNER JOIN Sales.Customer AS c ON soh.CustomerID = c.CustomerID INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID WHERE soh.OrderDate &gt;= '2014/05/01' GROUP BY c.CustomerID , FirstName , LastName ; --COMMIT TRANSACTION; </inputbuf> </process> </blocking-process> </blocked-process-report>

Listing 1

The next blocked process report in the list show very similar outputs, but this time, the index rebuild is the blocking process ( spid80), and the blocked process ( spid74) is another query on the SalesOrderHreader table. In the final report spid74 is the blocker and spid78 is blocked.

While the blocked process report provides all the information we need to troubleshoot cases of blocking, it’s hardly in an easy-to-digest format, and you’ll often find an overwhelming number of reports generated because a single blocking chain can generate multiple reports if it exceeds the value for the threshold multiple times.

Monitoring and Troubleshooting Blocking Using SQL Monitor

Ideally, we need to set up a less-reactive monitoring solution; one that alerts us to severe blocking immediately, as it occurs, provides enough information to diagnose and resolve it quickly, but allows us to see what sort of blocking occurs, and where, during busy periods. This allows us to act before it gets severe enough to start causing performance degradation and alerts.

Blocking Process Alerts

SQL Monitor raises the blocking process alert against any SQL process that has been blocking one or more other processes for longer than a specified duration. By default, it raises a Low severity alert when blocking exceeds 1 minute, but as with any alert in SQL Monitor, we can adjust the threshold and set multiple levels of alerts for different thresholds. We’re also likely to see Long-running query alerts relating to any blocked-process queries.

The Blocking process alert in Figure 4 relates to the head blocker in our chain, session 79. It has three blocked descendants. The session isn’t executing any SQL; it is simply a transaction that has failed to commit and is still holding locks on the target table.

Figure 5

Switch from the Details tab to the Processes tab, and you’ll see the blocking process at the top followed by the complete chain of blocked processes.

Figure 6

You now have the details of the application and user that issued the blocking statement and can investigate why the transaction apparently “hung” without committing. To resolve the blocking, you may be able to simply kill the offending process, like in this case.

In the Performance Data section of the alert page, on the Top queries tab, we see any queries that ran around the time of the alert, revealing the query that session 79 was running, which failed to commit, with its plan handle.

Figure 7

SQL Monitor also provides as contextual information’s all sorts of snapshots, aggregations, and summaries of resource usage on the server around the time of the alert. In this case, we can see the spike in lock waits around the time of the alert (the green line at around 19:20).

Figure 8

Blocking Processes (Top 10 by Time)

On the Overviews page of the SQL Monitor interface, we can use the resource usage timeline at the top to select a window of high activity on the server and investigate any blocking that may have been occurring over that period.

In Figure 9, I’ve moved the sliding window to focus on the period of activity around the time we received the blocking process alerts. We can see spikes in Disk I/O and Waits around that time, as well as a bump in memory use. Below that, the Blocking Processes (Top 10 by time) view has captured the blocking process.

Figure 9

If you click on one of the blocking processes, you’ll see the full blocking chain along with the details of which application issued the process, what resource it was waiting to acquire, the SQL text it was executing, and more.

Figure 10

Resolving Blocking

Very often, resolving blocking issues requires tuning inefficient queries and data modifications that run longer than necessary, and therefore, cause blocking. Sometimes, queries run within transactions, when there is no real need for them to do so. Transactions need to be kept as short as possible, without compromising transactional integrity. Sometimes indexes will help. In our example, session 79’s query had to scan the entire SalesOrderHeader table due to a lack of appropriate index on the search column ( Orderdate). This means it likely read more data than was necessary, took longer to run, and so held locks longer than it needed to.

There are other possible issues to look out for. Figure 8 shows that session 79’s query ran under the REPEATABLEREAD isolation level. Aside from finding out why the transaction failed to commit, we could investigate whether the query truly requires this restrictive isolation level, where locks are held until the transaction commits. By rewriting to use the default READCOMMITTED isolation level, S locks would be released after statement completion, meaning that the index build would not be blocked once the query had completed. Alternatively, we might consider using one of the snapshot-based isolation levels, such as READ_COMMITTED_SNAPSHOT. It prevents any read phenomena, and in this level, transactions don’t take S locks when reading data, so don’t block other processes. Kalen Delaney’s SQL Server Concurrency eBook (free download) provides a lot more details on the advantages, and possible drawbacks, of snapshot-based isolation levels.

A final option in this specific example might be to use WAIT_AT_LOW_PRIORITY option (SQL Server 2014 and later) for the online index rebuild. This would allow some waiting sessions to skip past it in the queue if the index rebuild itself was blocked. In this example, using this option, session 74 would not be blocked since it could skip past and acquire the S lock it needed on the SalesOrderHeader table since this lock mode is compatible with the S lock held by session 79.


Locking and blocking is a normal and required activity in SQL Server. However, if we execute long, complex transactions that read lots of data and take a long time to execute, then locks will be held for extended periods, and blocking can become a problem. Likewise, if our databases lack proper design characteristics, such as keys, constraints, and indexes, or if our transactions use restrictive transaction isolation levels (such as REPEATABLEREAD or SERIALIZABLE).

If blocking becomes severe, database performance grinds to a halt, and as a DBA, we need to act very quickly before an icy atmosphere develops among users.

Original Link

SQL Server — Database Backup Report Using PowerShell and T-SQL

In the previous articles, we discussed several ways of taking backup and testing the restore process to ensure the integrity of the backup file.

In this article, we’re going to discuss the importance of the database backup reporting. This kind of reporting will be designed to eliminate any unnecessary risks that may arise with the safety and security of information. This is the reason, the high-level report; Daily Health Check report will be generated and sent to the SME or to the DBA responsible for IT Infra management.

Database backups are a vital component for most of the database administrators regardless of what backup tool is in place and how the data backup process is all about backing up the data to the disk, to the tape, or to the cloud.

In general, administrators are very much concerned with getting the report daily and also any alerts as per the defined SLAs. Administrators rely on the backup report to understand how the backups are doing and always wanted to safeguard the data.

The other area we will cover is the online backup. It is increasingly becoming the default choice for many small businesses’ databases. A database backup report is an important document that reveals the specific piece of information about the status of the data. In general, the generation reports have become a standard practice for any business who values their data.

A backup report should be produced after each backup job has run. The report provides detailed information including what was backed up, what can be restored and information about the backup media. Some of the information provided is specific to each backup type.

The following are the three important parameters that need to be reviewed at regular intervals of time:

  1. Backup failure jobs: This is the most important metric to be measured and in most cases, it requires immediate attention and action. For test or dev environments, this could wait for being taking an action based on the SLA.
  2. Capacity planning and forecasting: The other report generally circulated every day is the disk space utilization report. Proactive monitoring of storage would prevent most of the backup failures and it helps to forecast the data growth. In turn, this may reduce many unforeseen disk space-related issues.
  3. Performance Backup performance is the important metric to measure the overall health of the system. It gives a heads-up for many hidden issues such as hardware resource problems, device driver issues, network throughput problems, software problems, etc.

Further reading: Backup report

Original Link

Database Cloning for Docker SQL 2017 Linux Containers

Windocks has evolved over the past two years from an independent port of Docker’s open-source project to Windows, to providing a complete SQL Server data delivery solution. Windocks supports the creation of cloned data environments based on SQL Server backups or storage arrays, with delivery to Microsoft’s SQL Server containers, instances, and Kubernetes clusters. In this article, we look at how database clones are delivered to SQL Server 2017 on Linux containers.

SQL Server 2017 in Linux containers is drawing attention for the speed and agility of containers, as well as database performance. The approach outlined for here should be particularly useful for upgrade testing, as well as general dev/test use. Clones enable delivery of Terabyte databases in seconds, with full read/write operation, while consuming minimal network and storage.   

Building SQL Server Images With Dockerfiles

Dockerfiles are plain text configuration files that define the SQL Server container, and in Windocks 3.0, can be applied at runtime. Each image can include scores of databases with scripts applied for user/group permissions, data masking, and other needs. 

Windocks 3.0 supports external storage arrays and Windows file system database cloning. Windows-based SQL Server images are built with Full or Differential backups, or database files, with each being a full byte copy of the data. Once created, an image supports creation and delivery of clones in seconds with full read/write support, with each requiring less than 40 MB on delivery.

This Dockerfile defines an image that delivers cloned databases to a SQL Server 2017 Linux container.

Image title

ENVUSE_DOCKERFILE_TO_CREATE_CONTAINER ensures the Dockerfile is applied at runtime for each container/environment. Delivery to SQL Server 2017 Linux containers is accomplished with RUN TargetAttach_MSContainerSqlLinux with the parameters shown. SQL Server clones are built with SETUPCLONINGFULL, DIFF (differential) backups, or RAW database files.  

Support for the delivery of database clones over the network is based on SMB, with a file share created on the Windocks host mapped to the Linux host (c:\windocks\data to /windocks/dataexternal as shown above). The Linux setup involves installing SAMBA, and the Docker daemon configured to allow for remote commands.  

A one-time build creates an image that supports an unlimited number of clones. The example below shows the build, followed by a command, to deliver the clone to a new SQL 2017 container. Most of the parameters involved, including the host IP address and target SQL Server image, are included in the image. Only two parameters are required for container creation, including the target port and SQL SA password. 

Image titleManagement of the combined environment is handled by the Windocks container. When it’s time to refresh the environment, the removal of the Windocks container removes the Linux container and associated mounts.    

Working With Sensitive Credentials

Windocks 3.0 introduces encrypted credential support for Windocks images and containers. The workflow described above involves clear text SQL SA passwords, which is the current practice in the use of SQL Server 2017 on Linux. When working with the Windocks SQL Server containers, credentials can be secured using the following methods:   

  • Windocks containers support Windows authentication.
  • Windocks Windows SQL Server containers are created by cloning a SQL Server instance that is configured for use by the Windocks service. Each container inherits SQL logins configured on the parent instance, enabling users with these accounts.
  • Windocks also includes configurable SQL SA credentials for each created SQL container, including an option for no SA passwords, encrypted SA passwords, or passwords in clear text. The three options are configured in the Windocks config folder, node file. SHOW_SA_PASSWORD=”0” or 1, or 2, for no password, encrypted, or clear text, respectively. Restart the Windocks Service following changes to the Windocks configuration.

Windocks encryption is based on the Windows Data Protection API (DPAPI). To encrypt a password, navigate to \Windocks\bin and open a command prompt and enter “encrypt.” The program prompts for a credential string and writes the hashed result to encrypted.txt in the same directory. Open the text file and copy the string into the Dockerfile, in this example we reference the password for an ArrayPassword parameter:

ArrayPassword|1,0,0,0,208,140,157,223,1,21,209,17,140,122,0,192,79,194,1,0, . . .

By incorporating encrypted passwords, either directly in the Dockerfile, the Dockerfiles can be saved and used securely. Once a credential is encrypted the hashed result or environment variable needs to be used in any references to that credential. 

When configured to deliver encrypted credentials, Windocks SQL container SA passwords are delivered in standard Docker client return strings (image below). To unencrypt the credential copy the complete string and save as an encrypted.txt file. RDP to the Windocks server, and copy the encrypted.txt into the \windocks\bin directory. Open a command prompt and enter “decrypt.” 

Image title

The program decrypts the text file and presents the password:

Image title

Working With a Subset of Databases

Users can work with a subset of the databases from an image by using a runtime environment variable: SQL_DB_NAME_OVERRIDES=”dbname1, dbname2”

>docker create -e SQL_DB_NAME_OVERRIDES=”dbname1, dbname2” <imagename>

Working With a Web UI

The Windocks web UI simplifies use for developers and other users. Open a Chrome or Firefox browser and point to the IP address of the Windocks server (local: Images are displayed with required parameters, including the option to work with a subset of desired databases. The image targeting Linux SQL containers only requires user input on the target port and SQL SA password and includes a drop-down selector for working with a subset of the databases in the image.

Image title

Database Cloning for SQL Server Containers and Instances

SQL Server 2017 Linux containers are drawing a lot of attention in a world that is increasingly embracing Linux and open-source technologies. Regardless of the form of SQL Server you use, database cloning is key to enabling an efficient workflow for development and test. Windocks database cloning enables efficient upgrade testing and work with large and complex data environments on the new SQL Server 2017 Linux containers.

You can start exploring these capabilities today by downloading the free Windocks Community Edition

Original Link

Dates and Times in SQL Server: DATEPART()

In my previous posts in this series, we’ve seen reference to Transact-SQL (T-SQL) functions that are used to get the specific part of a date and/or time (year, month, day, hour, minute, second, etc.). This week we’ll go through one of them and see how it works.

Introducing,DATEPART , a built-in function that takes two parameters: the date part we want back and the input date. Per the documentation, the input date parameter must be in a format “that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value.” In a future post, we’ll see just how vague this definition is, however, for this post, just know that almost anything goes if it looks remotely like a date or time.

DATEPART can return a lot of parts, and the result is returned as an INT.

What’s in a Number?

Conveniently, our clock and Gregorian calendar system mostly revolve around fairly small integers. There are 60 seconds in a minute, 60 minutes in an hour, 24 hours in a day, up to 31 days in a month, 12 months (and up to 366 days) in a year, and so on. These numbers are whole numbers and can be expressed easily in words and writing.

When it comes to weekdays, it gets more complicated. We have weekdays that (depending on the country and language) might start on a Sunday or a Monday. This can have a knock-on effect on weeks of the year, not to mention the ISO week (see below).

The following table, taken from the documentation, shows an abbreviation we can use in place of the datepart parameter. I recommend getting out of the habit of using the abbreviation, as there is very little additional effort expended in typing the full parameter name, and avoiding the abbreviations help with writing clearly understandable code.

datepart Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
TZoffset tz

isowk, isoww

The documentation also says:

To avoid ambiguity, use four-digit years.

Follow this advice. It is good advice. We can be lazy, but we can’t be negligent.

What’re Four Bytes Between Friends?

As we learned some time ago, an INT (integer) in T-SQL uses four bytes and has a maximum value greater than zero of over 2 billion (there are more than 4 billion values in an integer if we take the negative values into account).

Why then are date and time parts expressed as an INT, which have a lot of overhead for values like 24, 31, and 60?

There are two reasons:

  1. Integers make things simpler. When writing arithmetic in T-SQL and other programming languages, the default data type is usually an integer. Having to memorize which data types are returned from built-in functions becomes unnecessary when we know it will be an INT. Yes, it uses extra memory, but four bytes is a reasonable trade-off against complexity.
  2. One of the return values is nanoseconds. Although DATETIME2(7) only has a granularity down to 100 nanoseconds, DATEPART allows us to return this value, which requires a data type large enough to contain an integer up to 1 billion (nanoseconds can range from 0 to 999,999,900 in increments of 100).

What’s the Catch?

There are some considerations when dealing with DATEPART that are affected by server configuration.

  • Language: Because the input date can be any value that could be interpreted as a date, the language environment matters. We should take care that our input parameter matches the default language of the instance, otherwise, we have to set the LANGUAGE setting for the session to ensure compatibility.
  • Date format: If the input date is a string literal, it is also affected by the DATEFORMAT setting. Additionally, the documentation tells us that “SET DATEFORMAT does not change the return value when the date is a column expression of a date or time data type.”
  • Date first: As noted previously, the first day of the week can affect the result provided by the week or weekday output. The default first day of the week is set using the DATEFIRST setting.

ISO_WEEK Considerations

In 2011, one-off alarms on iPhones across the world stopped working for the first two days of the year. This was a very curious bug that happened to be related to the International Standards Organization (ISO) standard 8601, which deals with date and time.

In the ISO week standard, a week starts on a Monday, and when matched to the Gregorian calendar, the first week of the year begins with the week that contains the first Thursday. In January 2011, the new year started on a Saturday, thus the first week of the year according to the ISO standard was not the same as the first week in the calendar. January 1st and 2nd were in a strange limbo, so phone alarms didn’t ring.

This kind of uncommon scenario (what we call an “edge case”) might cause wide-reaching bugs in our own T-SQL code if we are working with the ISO 8601 standard, especially across regions.

The documentation states:

European countries / regions typically use this style of numbering. Non-European countries / regions typically do not use it. […] The numbering systems of different countries/regions might not comply with the ISO standard.

Hopefully, this gives you a better understanding of the DATEPART function in T-SQL. Share your ISO bugs with me in the comment section. 

Original Link

Virtual Log Files: 200 or 1000?

Image title

Last week, I had the privilege of reviewing possibly the best SQL Server production environment I’ve seen in Canada. During the follow-up meeting, the senior DBA and I had a discussion about Virtual Log Files (VLFs), disagreeing on the maximum number of Virtual Log Files a transaction log should have. I said 200, and he said 1000.

Both numbers are arbitrary, so let’s explore why VLFs exist and why we might prefer one over the other.

To give you a succinct refresher, here’s what I wrote about VLFs in my book:

A transaction log file is split into logical segments, called virtual log files. These segments are dynamically allocated when the transaction log file is created, and whenever the file grows. The size of each VLF is not fixed and is based on an internal algorithm, which depends on the version of SQL Server, the current file size, and file growth settings.

In a transaction log with too many or too few VLFs, we might experience performance issues under a normal workload as well as during the backup and restore process.

So what is the “right” amount? In customer engagements, I follow a guideline proposed by Glenn Berry of in his Diagnostic Information Queries, to keep the number of VLFs at or below 200. In my opinion, any number higher than that is cause for concern.

On the other hand, Brent Ozar Unlimited has a popular script called, which proposes a maximum VLF count of 1000. To Brent, a number higher than that is cause for concern.

Who’s Right?

It doesn’t matter because when we consider the number of VLFs in a transaction log file, what we care about are excessive counts. I’ve seen databases with more than 15,000 VLFs — clearly too high. If on the other hand a transaction log file has 201 VLFs, I’m not going to insist that the DBA shrink and resize the log immediately. Instead, I’ll raise a flag and ask questions about file growth settings.

As a baseline, 200 VLFs is a reasonable maximum. If a transaction log file is small and has a lot of VLFs, that points to a problem with file growth settings, which is a relatively easy fix. If a transaction log file has more than 1000 VLFs, that should really set off alarm bells. Periodically keeping your eye on the VLF count or using more in-depth health checks like dbSnitch can go a long way to being proactive about identifying growing problems, rather than reactive firefighting.

In my customer’s case, where they have very fast storage, low CPU utilization, and mostly small databases (below 100 GB), having 200 VLFs is a manageable target amount and doesn’t appear to be causing noticeable performance issues. With good file growth settings, the VLF count won’t even matter.

Share your VLF counts with me on Twitter at @bornsql

Photo by Bernard Hermant on Unsplash.Original Link

Extended Events, the system_health Session, and Waits

I advocate for, use, document, teach, and just downright love, Extended Events. They are so much better than the old Trace Events (aka, Profiler) that it’s sometimes difficult to keep from just gushing. Let’s talk about a common situation that you’re going to run into on your servers all the time and how you can put Extended Events to work to help you without actually doing any work at all.

What’s that? Be lazy and get rewards? Yes.

The Extended Events system_health Session

On your servers, any of them that are SQL Server 2008 or newer, right now, unless you’ve performed actions to prevent this, you’re running the Extended Events system_health session. It’s just happening, currently, on all your servers. Nothing you need to do about it at all.

If you follow the link, you can see all the various types of information being gathered by the Extended Event system_health session. I won’t detail all of it here, let me just provide a little context around how the session works. First and foremost, similar to the error log, this session consists of four files, each 5mb in size, rolling over as they get filled. For systems with a very high degree of activity, that means the information here may only be hours old. However, for most of us, this provides days, if not weeks worth of information about the behavior of your system.

You’ll need to know where the files are located so that we can query them or open them up in the Live Data window. Here’s a simple query that will give you the path on any system:

SELECT dosdlc.path
FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc;

Now there are a ton of reasons why you should be taking advantage of the system_health session (deadlocks for example), but I’m going to focus on one, blocks.

Waits In The system_health Session

To see this information in action, we can set up a really simple query. Run this from two different query windows and let it sit for about 45 seconds or so (it only needs 30, but for our purposes, add a little padding):

UPDATE dbo.ErrorLog
SET ErrorTime = GETDATE()
WHERE ErrorLogID = 1;

After a little while, roll back both scripts. Then, we can run this script to take a look at the system_health information specifically on the waits caused by these two queries blocking one another:

--to retrieve the local path of system_health files SELECT @path = dosdlc.path
FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc;
SELECT @path = @path + N'system_health_*';
SELECT CAST(fx.event_data AS XML) AS Event_Data,
FROM sys.fn_xe_file_target_read_file(@path,
WHERE fx.object_name = 'wait_info';

The results on my system look like this:

<event name="wait_info" package="sqlos" timestamp="2018-06-01T12:04:18.915Z">
<data name="wait_type">
<data name="opcode">
<data name="duration">
<data name="signal_duration">
<data name="wait_resource">
<action name="sql_text" package="sqlserver">
UPDATE dbo.ErrorLog
SET ErrorTime = GETDATE()
WHERE ErrorLogID = 1;
<action name="session_id" package="sqlserver">
<action name="callstack" package="package0">

You have the query, the wait_resource, the duration, and in short, everything you need to start identifying why you had excessive blocking on one of your servers, and all you had to do was reach out and grab it.

I’ll leave parsing the XML to others (check out this book, just starting to read it).


Really, there was a query that ran unusually long yesterday? I wonder if it was blocked and waiting on resources? Let me take a quick look at the Extended Events system_health session. It really is that easy. This is a free, easily accessed resource that is available to you right now.

Original Link

How to Decrypt Views in SQL Server

“I am using SQL Server 2014. I have made views and I want to migrate those views from one server to another server. But, I need to decrypt my SQL Server 2014 view (encrypted view) as I want to modify the views as per my database requirement.”


Sometimes we don’t want anyone to make changes to our views or don’t want anyone to make changes to our database object.

You may come around the situation when you have to make encrypted database objects for security purposes or need to edit your encrypted records.

Companies that rely on SQL Server need to look at or edit encrypted stored procedures, views, functions, etc. So, here, we will be focusing on encrypting and decrypting view in SQL Server Database.

How to Encrypt Views by Using the SQL Server Management Studio:

The encryption of database object can be done by using the WITH ENCRYPTION option while creating the database object. So let us understand how to encrypt view in SQL Server.

 Here is the script for creating the view:

 create view [sample] as

select dbo.test.Employee_Name,

Employee_ID from Test;  

create view

Now encrypt your database by using the WITH ENCRYPTION option:

 alter view [sample] With encryption as

select dbo.test.Employee_Name, Employee_ID

from Test; 

Now, run sp_help command to check whether your view gets encrypted or not. You will get a pop-up of the successfully encrypted message.

encrypted view

Similarly, you can encrypt other database objects like functions and stored procedures in SQL Server.

How to Decrypt View Using the SysTools SQL Decryptor:

Once encrypted, it becomes quite difficult to decrypt your database object. For this, you can try SysTools SQL Decryptor Tool. Once installed, decrypting an object becomes simple and fast. You can decrypt multiple SQL Database objects at a time. You can decrypt SQL database object of any file size.

The tool provides two different authentication option for decrypting SQL Database object, ie Windows Authentication & SQL Server Authentication. The software supports SQL Server version 2014, 2012, 2008, 2005,2000. You will get two different options for exporting decrypted object ie SQL Server Database & SQL Server Compatible Script. 

Lets us know how you can decrypt view using SQL Decryptor Tool.

SQL Decryptor Tool

enter credentials

preview decrypted view

  • You will get two different options to export your decrypted database object: SQL Server Database or SQL Compatible Script. Choose according to your need. Here, I have exported the decrypted database view in SQL Compatible Script so that I can use it for further use.

  • Click on Export.

export decrypted view

Similarly, in this way, you will be able to decrypt other database objects like stored procedure, functions, etc.


In this article, I have discussed how can you secure your database object by encrypting them using the WITH ENCRYPTION option. You can easily edit or modify your database view by decrypting the encrypted view as discussed above. 

Original Link

Database Connectivity and Transaction in Python

It is very easy to establish a connection to a database and execute various DML (PL/SQL) statements in Python. Here, I am going to explain two different modules through which we are going to connect to different databases. The first module is “cx_Oracle” for Oracle Database, and the second one is “pyodbc module” to connect to MS SQL server, Sybase, MySQL, etc. 

So, my first example is with “cx_Oracle.” I am not going to describe this module in detail, but my focus will be mainly on how to connect to the database and execute different SQL in it. For detailed documentation, please refer to

In our first example, we will simply connect to a database and execute a SQL statement. 

First, install “cx_Oracle” package in your python. For that, refer to the document mentioned above.

import cx_Oracle username=”test”
dbName=your oracle SID connectionString = username + "/" + password + "@" + dbName sqlstmt=”select fname, mname, lname, age from student” db_connection = cx_Oracle.connect(connectionString)
cursor = db_connection.cursor()
cursor.arraysize = 5000
cursor.execute(sqlstmt) dataset = cursor.fetchall()
iflen(dataset) > 0:
for row in dataset :
print(“First name : ”, row[0])
print(“Mid name : ”, row[1])
print(“Last name: “, row[2])
print(“Age :”, row[3]) cursor.close()

The second example is with module pyodbc. For details please refer to 

In the first example with pyodbc , we will connect to a SQL server with Microsoft credential

import pyodbc driver= '{SQL Server}'
db_environment=Your database environment connectionString = (('DRIVER='+driver+';PORT=1433;SERVER='+server+';PORT=1443;DATABASE='+db_environment+';Trusted_Connection=yes;')) db_connection = pyodbc.connect(connectionString)
cursor = db_connection.cursor()
cursor.arraysize = 5000
cursor.execute(sqlstmt) dataset = cursor.fetchall()
iflen(dataset) > 0:
for row in dataset :
print(“First name : ”, row[0])
print(“Mid name : ”, row[1])
print(“Last name: “, row[2])
print(“Age :”, row[3]) cursor.close()

For without windows authentication:

driver= '{SQL Server}'
db_environment= Your database environment username=”test”
password=”test123” connectionString = (('DRIVER='+driver+';PORT=1433;SERVER='+server+';PORT=1443;DATABASE='+ db_environment +';UID='+username+';PWD='+ password)) db_connection = pyodbc.connect(connectionString)
cursor = db_connection.cursor()
cursor.arraysize = 5000
cursor.execute(sqlstmt) dataset = cursor.fetchall()
iflen(dataset) > 0:
for row in dataset :
print(“First name : ”, row[0])
print(“Mid name : ”, row[1])
print(“Last name: “, row[2])
print(“Age :”, row[3]) cursor.close()

The fourth example is for Sybase database:

driver= '{Adaptive Server Enterprise}'
db_environment= Your database environment username=”test”
connectionString = (('DRIVER='+driver+';PORT='+port+';SERVER='+server+';PORT='+port+';DATABASE='+db_environment+';UID='+username+';PWD='+ password)) db_connection = pyodbc.connect(connectionString)
cursor = db_connection.cursor()
cursor.arraysize = 5000
cursor.execute(sqlstmt) dataset = cursor.fetchall()
iflen(dataset) > 0:
for row in dataset :
print(“First name : ”, row[0])
print(“Mid name : ”, row[1])
print(“Last name: “, row[2])
print(“Age :”, row[3]) cursor.close()

Thank you.

Original Link

Adding Python Packages to SQL Server 2017

SQL Server 2017 allows for the use of Python scripts called external scripts. SQL Server comes with some Python packages by default. Today, I wanted to talk about adding Python packages to SQL Server 2017.

To get started with Python in SQL Server 2017, we must enable the use of external scripts.

Enable SQL Server for Python Scripts

You run Python “inside” of SQL Server through the use of the sp_execute_external_script system stored procedure. To use this procedure, you must enable your instance to allow for remote script execution. That’s an easy configuration change:

EXEC sp_configure 'external scripts enabled', 1

Here’s a diagram that better helps to explain what happens when you call this external procedure (full article located here):

You’ll note the use of Launchpad.exe. If that service is not running, you will see an error:

Msg 39011, Level 16, State 1, Line 4
SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service.

If you are using SQL Server for R and Python, it’s a good idea to set the Launchpad service to automatic startup.

Now, we are ready to execute a script. Let’s take a look at the Python packages installed by default.

Find All Python Packages Installed in SQL Server 2017

This is easy with a script such as this one:

EXEC sp_execute_external_script
@language = N'Python',
@script = N'
import pip
import pandas as pd
installed_packages = pip.get_installed_distributions()
installed_packages_list = sorted(["%s==%s" % (i.key, i.version) for i in installed_packages])
df = pd.DataFrame(installed_packages_list)
OutputDataSet = df '
WITH RESULT SETS ( ( PackageVersion nvarchar ( 150 ) ) )

This returns 128 rows; here’s a quick look:

Find Specific Python Package Installed in SQL Server 2017

There’s a way to search for one package, too. We just filter for a specific package name, like this:

EXECUTE sp_execute_external_script @language = N'Python', @script = N'
import pip
import pkg_resources
pckg_name = "revoscalepy"
pckgs = pandas.DataFrame([(i.key) for i in pip.get_installed_distributions()], columns = ["key"])
installed_pckg = pckgs.query(' 'key == @pckg_name' ')
print("Package", pckg_name, "is", "not" if installed_pckg.empty else "", "installed")'

This is what the result looks like:

One word of warning here: Python is very particular about indents. If you are going to be using Python scripts with SQL Server, my advice is to use a true Python editor, like VS Code, and not rely on using SSMS. If your indents are incorrect, you will see an error message similar to this:

Msg 39004, Level 16, State 20, Line 24
A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 24
An external script error occurred: Error in execution. Check the output for more information.
Traceback (most recent call last): File "", line 3, in import pip ^
IndentationError: unexpected indent SqlSatelliteCall error: Error in execution. Check the output for more information.

The error is clear: “unexpected indent.” The error message also points to the exact spot. But you could have many such errors in your script. That’s why using VS Code would be handy, and you can then cut and paste the script into your procedure.

OK, so what if our package can’t be found? Not a problem; we can install new ones using pip.

Adding Python Packages to SQL Server 2017

To add a Python package to your instance of SQL Server you need to use either a command line or Visual Studio using the Python Environments window. I will use the command line for this example.

The first thing we must know is the location of packages used by Python for SQL Server. If SQL Server was installed with default settings, the directory will be similar to this:


If you need to manually find this directory, here’s the Python command to return the information:

EXEC sp_execute_external_script @language =N'Python', @script=N'import sys; print("\n".join(sys.path))'

Now that we know in which directory Python.exe resides, we can open a command line there and use pip. I have two warnings for you. First, you must be running the command line as an account with sufficient permissions to write to this directory. Second, you want to be using the pip.exe found in the /Scripts directory. If you just call pip, you will use whatever pip is defined in your PATH from previous Python installations.

So, to install the Keras package, I navigate to the /Scripts directory and execute this command:

>pip.exe install keras

And I can run the script above to verify that Keras is now installed.


Using Python from SQL Server is easy. It’s as simple as configuring your instance to allow for external scripts and then calling an external script with a stored procedure.

The caveat I have with using Python and SQL Server is that this gives you YAPI (Yet Another Python Install). It can be difficult keeping track of your Python environment.

But if you have a need to use Python and interact with relational storage, then SQL Server is now an option. I could see scenarios where you might take advantage of SQL Server in a container, storing temporary results, letting Python focus on the data science stuff. For someone who lives inside of Visual Studio, this is probably an ideal scenario to blend Python and SQL Server storage.

Original Link

The State of SQL Server Monitoring 2018

Over 600 technology professionals who work in organizations that use SQL Server recently responded to our survey to discover the current state of SQL Server monitoring.

We asked people across a range of sectors, in organizations of every size around the globe, about how they monitor SQL Server, the technologies they work with, and what they thought the biggest challenges were for them and their estates over the next 12 months.

Into the Cloud

One of the major themes to emerge from the survey was migrating to the cloud. Although only 48% of respondents are currently using cloud technologies, principally SQL Server on VMs or Azure SQL DB, cloud is growing in importance. Migration to the cloud is expected to be the biggest challenge this coming year.

If our respondents’ expectations are correct, then we may reach a tipping point this year, where most of our peers are deploying daily to cloud-based servers, at least for part of their estate.

Alongside this almost 50% of organizations are already deploying changes multiple times per week, and those operating in the Finance and Technology sectors often deploy multiple times per day.


This demonstrates quite a dramatic shift from just a few years ago, when deployments were perhaps less frequent major events to locked-down servers hosted on-premise.

Data Privacy and Protection

Whilst migrating to the cloud is seen as the biggest challenge in the next 12 months, perhaps unsurprisingly concerns around security and data protection emerged as a major challenge for estate management right now.


The arrival of the General Data Protection Regulation (GDPR) in May 2018, coupled with HIPAA, PCI-DSS, SOX and many other forms of legislation across the sectors, having confidence in your organization’s compliance is a clear priority, and something monitoring can definitely help with.

Once you have a data protection policy in place, it’s essential to keep track of your estate to ensure compliance. With a monitoring tool, you can detect activity that risks data breaches caused by unauthorized access, restore availability and access to personal data in a timely manner when errors occur, and get the insight you need to respond before it impacts your business.

Resolving Issues and Monitoring Health

Most respondents said they spend an average of one hour per day looking at the health of their SQL Servers, with quite a high proportion (30%) spending less than an hour. Those using a monitoring tool actually said they spend longer, between 1.75 and 2 hours per day, looking at the health of their servers.

From this, you could infer that monitoring manually or not monitoring at all takes less time than using a tool, but in fact, respondents who spent less time looking at the health of their servers had fewer servers to manage or belonged to organizations with smaller estates. Monitoring tools come more into play as server numbers increase and it’s no longer possible to do everything manually.

Furthermore, a monitoring tool can make it much easier to see performance information and identify problems, which means if you are using one you’re more likely to catch issues and then spend time addressing them.

The time spent resolving issues followed a similar pattern to health monitoring, with over half of the respondents reporting that they spend one hour a day or less on this task.

Those using a paid-for or in-house tool spent longer, between 2 and 2.25 hours on average, which again reflects the increased visibility of issues using a tool brings to monitoring processes.

With the ability to see data trends that indicate a recurring issue, for example, a monitoring tool can help you take proactive steps to eliminate future problems. Therefore it may take more time now, but it will reduce the numbers of issues in the future, freeing up time for more productive tasks.

Read the Full Report

This post merely scratches the surface of our 2018 State of SQL Server Monitoring report. To delve into all of the data and insights download your free copy today.

Original Link

SQL Audit Not Showing Full SQL Statement

I noticed some MSDN forum posts regarding SQL Audit not showing the full SQL statement. To the end user, it appears that SQL Audit is truncating of SQL statements. I decided to write a quick post to help clear up the confusion for large SQL statements and how they appear in SQL Audit.

No, it’s not a bug, but yes, the statements may appear trimmed. Let me explain.

First, let’s set up an audit that will capture a large statement. How large is large?

The fn_get_audit_file documentation defines large as “too large to fit in the write buffer.” This is referring to the size of the statement column, defined as NVARCHAR(4000). For any statement larger than that, it will need to be broken into distinct lines, identified by the sequence_number column.

Let’s see what this looks like in action.

I will create the Server Audit first, which outputs to a flat file on my laptop:

I have also included the code for you here:

TO FILE (FILEPATH = N'C:\TeamData\AuditLogs\'
,MAX_ROLLOVER_FILES = 2147483647

OK, next we create a Database Audit Specification. I will capture any SELECT statement executed against the sysobjects table:

And here is the code for that database specification:

ADD (SELECT ON OBJECT::[sys].[sysobjects] BY [dbo])

Now, we need to write a statement that will be larger than 8k. I will use dynamic SQL for this task. Here’s the sample code that I’ve used for… a long time. Now get off my lawn:

DECLARE @LongString VARCHAR(8000)
, @Replicate VARCHAR(8000)
, @From VARCHAR(8000) SELECT @LongString='SELECT TOP 1 name,'''
,@From=''' FROM sysobjects' exec(@LongString+@Replicate+@From)

After enabling the database specification and the server audit, execute the code. Then, open the audit log viewer:

We can see there are three rows for this one statement. You can also see the sequence number column to the right in the output window. And it is also in the text box below.

If you are using the fn_get_audit_file function, the sequence number is there, too.

I hope this clears up the confusion for SQL Audit and showing large SQL statements. If you happen to be in Antwerp for Techorama later this month, I have a session on SQL Audit you might be interested in attending. We’ll talk about SQL Audit for both Earthed and Cloud versions of SQL Server.

Original Link

Using Microsoft SQL Server With Scala Slick

This post shows simple CRUD operations on Microsoft SQL Server using Scala Slick version 3.2.3. You might be thinking, What’s really great about it? Duh! But until Scala Slick 3.2.x was released, using commercial databases was within the horizon of an additional closed-source package know as Slick Extensions, which supported Slick drivers for the following databases:

  1. Oracle
  2. IBM DB2
  3. Microsoft SQL Server

Library dependency used for Slick Extensions:

libraryDependencies += "com.typesafe.slick" %% "slick-extensions" % "3.0.0"

But with the newer version of Slick, these drivers are now available within the Slick core package as an open-source release that can also be seen from the changelog, as well.

If you find yourself struggling with a setup to make Microsoft SQL Server work with Scala Slick in your project, maybe because of the lack of resources available on the web, then read on!


SQL Server database configurations:

sqlserver = { driver = "slick.jdbc.SQLServerProfile$" db { host = ${?SQLSERVER_HOST} port = ${?SQLSERVER_PORT} databaseName = ${?SQLSERVER_DB_NAME} url = "jdbc:sqlserver://"${}":"${sqlserver.db.port}";databaseName="${sqlserver.db.databaseName} user = ${?SQLSERVER_USERNAME} password = ${?SQLSERVER_PASSWORD} }

Database instance:

val dbConfig: DatabaseConfig[JdbcProfile] = DatabaseConfig.forConfig("sqlserver")
val db: JdbcProfile#Backend#Database = dbConfig.db

SBT Project Setup

For the example used in this post, the following dependencies and versions of respective artifacts are used:

  1. Scala 2.11.11
  2. SBT 0.13.17
  3. Slick 3.2.3
  4. HikariCP 3.2.3
  5. MsSQL JDBC 6.2.1.jre8

Which, inside our build.sbt file, will look like the following set of instructions:

name := "mssql-example" version := "1.0" scalaVersion := "2.11.11" libraryDependencies ++= Seq( "com.typesafe.slick" %% "slick" % "3.2.3", "com.typesafe.slick" %% "slick-hikaricp" % "3.2.3", "" % "mssql-jdbc" % "6.2.1.jre8"

And the instructions of the file will be:

sbt.version = 0.13.17

The settings required to configure Microsoft SQL Server should go inside the application.conf file, whose instructions would be to specify the details of our database

sqlserver = { driver = "slick.jdbc.SQLServerProfile$" db { host = ${?SQLSERVER_HOST} port = ${?SQLSERVER_PORT} databaseName = ${?SQLSERVER_DB_NAME} url = "jdbc:sqlserver://"${}":"${sqlserver.db.port}";databaseName="${sqlserver.db.databaseName} user = ${?SQLSERVER_USERNAME} password = ${?SQLSERVER_PASSWORD} }

Where it can be seen that SQLSERVER_HOST, SQLSERVER_PORT, SQLSERVER_DB_NAME, SQLSERVER_USERNAME, and SQLSERVER_PASSWORD are to be provided as environment variables.

Now, moving onto our FRM (Functional Relational Mapping) and repository setup, the following import will be used for MS SQL Server Slick driver’s API:

import slick.jdbc.SQLServerProfile.api._

And thereafter, the FRM will look the same as the rest of the FRMs delineated on the official Slick documentation. For the example on this article, let’s use the following table structure:

CREATE TABLE user_profiles ( id INT IDENTITY (1, 1) PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL

Whose functional relational mapping will look like this:

class UserProfiles(tag: Tag) extends Table[UserProfile](tag, "user_profiles") { def id: Rep[Int] = column[Int]("id", O.PrimaryKey, O.AutoInc) def firstName: Rep[String] = column[String]("first_name") def lastName: Rep[String] = column[String]("last_name") def * : ProvenShape[UserProfile] = (id, firstName, lastName) <>(UserProfile.tupled, UserProfile.unapply) // scalastyle:ignore }

Moving further up with the CRUD operations, they are fairly straightforward as per the integrated query model provided by Slick, which can be seen from the following UserProfileRepository class:

class UserProfileRepository { val userProfileQuery: TableQuery[UserProfiles] = TableQuery[UserProfiles] def insert(user: UserProfile): Future[Int] = += user) def get(id: Int): Future[Option[UserProfile]] = userProfileQuery .filter( === id) .take(1) .result .headOption) def update(id: Int, firstName: String): Future[Int] = userProfileQuery .filter( === id) .map(_.firstName) .update(firstName)) def delete(id: Int): Future[Int] = === id).delete)

Lastly, in order to get the database instance using the configurations provided in application.conf file, the following code snippet can be used:

val dbConfig: DatabaseConfig[JdbcProfile] = DatabaseConfig.forConfig("sqlserver")
val db: JdbcProfile#Backend#Database = dbConfig.db

The working codebase of this example is available at the following repository: scala-slick-mssql.

Also, if you’re interested in knowing how data can be directly streamed from PostgreSQL to a client using Akka Stream and Scala Slick, then you might find the following article useful: Streaming data from PostgreSQL using Akka Streams and Slick in Play Framework.

This post was inspired by an endeavor to make Microsoft SQL Server work with Slick and an answer on StackOverFlow, which is the reference of the configurations.

Original Link

Microsoft BCP Performance on Sqoop EXPORT to SQL Server From Hadoop

We’ve gotten everyone connected to SQL Server using Progress DataDirect’s exclusive support for both NTLM and Kerberos authentication from Linux with Sqoop. Now, we plan to blow your minds with high-flying bulk insert performance into SQL Server using Sqoop’s Generic JDBC Connector. Linux clients will get similar throughput to the Microsoft BCP tool.


So far, Cloudera and HortonWorks have been pointing shops to the high-performance DataDirect SQL Server JDBC driver to help load data volumes anywhere from 10GB to 1TB into SQL Server data marts and warehouses. It’s common for the DataDirect SQL Server JDBC driver to speed up load times by 15-20X, and Sqoop will see similar improvement since it leverages JDBC batches that we transparently convert into SQL Server’s native bulk load protocol. Moving data out of Hadoop and into external JDBC sources are exciting projects that represent the democratization of big data for downstream application consumers. You’re definitely doing something right if you are ready to read on!

Get Started With Fast Performance for Sqoop EXPORT to SQL Server

  1. Download the DataDirect Connect for JDBC drivers and follow the quick-start guides supplied with the download.
  2. Copy the sqlserver.jar file to the $SQOOP_HOME/lib directory on your client machine. (This will be /user/lib/sqoop/lib if you installed from an RPM or Debian package). The JDBC driver needs to be installed only on the machine where Sqoop is executed; and not on each node in your Hadoop cluster.
  3. Verify the database’s recovery mode per the MSDN article on Considerations for Switching From the Full or Bulk-Logged Recovery Model. To verify the recovery mode, the database user can run the following query:
    SELECT name, recovery_model_desc
    FROM sys.databases
    WHERE name = ‘database_name’ ;

    Note the recovery_model_desc returned by this query (expect to return, ‘BULK_LOGGED’).

  4. From the command line, run the Sqoop export command using similar properties as below. Or specify the equivalent using the Hue web UI for Sqoop jobs.
sqoop export --connect 'jdbc:datadirect:sqlserver://nc-sqlserver:1433;database=test;user=test01;password=test01;EnableBulkLoad=true;BulkLoadBatchSize=1024;BulkLoadOptions=0' --driver com.ddtek.jdbc.sqlserver.SQLServerDriver --table 'blah_1024MB' --export-dir /user/hdfs/blah_1024MB/ --input-lines-terminated-by "n" --input-fields-terminated-by ',' --batch -m 10


  • –batch mode is used for underlying insert statement execution.
  • –driver must be specified when using a Generic JDBC connector.
  • –connect is the JDBC URL. EnableBulkLoad=true authorizes the DataDirect SQL Server driver to utilize the bulk load protocol for the inserting of rows. The BulkLoadBatchSize value indicates to the driver the number of rows it will attempt to bulk load on a single roundtrip to the server. If this value is less than the sqoop.export.records.per.statement value, then each call to executeBatch will result in more than one round trip to the server in order to insert the batch of rows.
  • –table: The table to be populated in the target relational database as data is transferred from HDFS.
  • –export-dir identifies the HDFS directory that contains the Hadoop table to be exported.
  • –input-lines-terminated-by identifies the character which separates rows in the HDFS files.
  • –input-fields-terminated-by identifies the character which separates columns in the HDFS files.
  • -D sqoop.export.records.per.statement is not recommended nor the equivalent of JDBC batch size. Rather, it specifies the number of rows per SQL statements for data sources that support multi-row inserts such as Postgres.
INSERT INTO films (code, title, did, date_prod, kind) VALUES ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'), ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

View the Sqoop user’s guide for complete reference.

Special thanks to Mike Spinak, Principal Software Engineer, and Danh Huynh, Systems Administrator, for their help with setup and testing in the six-node Cloudera CDH5.2.0-1.cdh5.2.0.p0.36 cluster to export data into SQL Server 2008 R2.

Show Me the Numbers

Results are still coming in from several shops and the best to date is a load of 40 GB into SQL Server within ten minutes. In the above system, we were loading 37 GB in 18.5 minutes. There are several properties across Hadoop, Sqoop, JDBC driver and SQL Server you can tune to improve performance even further.

Original Link

Schema Compare for SQL Server

SQL Server Management Studio (SSMS) does not offer the ability to do a schema compare between two databases. Therefore, you have two options to do a schema compare for SQL Server databases. The first is to use Visual Studio. The other is to use a third-party tool. However, there are two issues with either of those options that affect a lot of data professionals and DBAs:

  1. You aren’t allowed to install tools on your work machine.
  2. You don’t get any budget to purchase tools.

Today, I’m going to show how to do a schema compare using only SSMS, dacpac files, and the FC (File Compare) command available in Windows. As always, you’re welcome.

Create a DACPAC

First, create a dacpac for each database. I am going to use AdventureWorks2008 and AdventureWorks2012 as my examples for this post. You create a dacpac by right-clicking on the database name in SSMS, selecting Tasks, then Extract Data-tier Application…. No, I don’t know why they couldn’t name it something easier, like Create DACPAC. But I digress. Here’s what it looks like:

Click through the wizard, pick a location to save the dacpac files, and wait for it to finish.

The dacpac files aren’t useful in their current format at the moment. We will fix that next.

Crack Open DACPAC Files Using This One Weird Trick

That’s right, all you need to do is rename the files from .dacpac to .zip, then extract the files. It’s that simple. Once you do, you will find a handful of files. We are going to focus on model.xml today. I encourage you to poke around the origin.xml file to see the goodness inside there.

Open a Command Prompt

Yes, I said a command prompt. I know this is possible with PowerShell. More on that later.

Windows comes with the FC command, available from the command line. Open a command line and run ‘help FC’ to see the list of available switches.

We will use the /L and /N switches and compare the two schemas, outputting to a file.

FC /L /N AdventureWorks2008\model.xml AdventureWorks2012\model.xml > output.txt

The compare produces an output file that looks like this:

A quick check confirms that the PK name for the HumanResources.Employee table did change between the 2008 and 2012 versions of AdventureWorks.


If you need to do a schema compare but are limited in your ability to purchase or install tools, the FC command is a brilliant option that you have available in Windows.

If you are a PowerShell aficionado then you don’t need me to tell you that the Compare-Object and Get-Content cmdlets can get the same job done. I will leave you with two thoughts on that subject. First, the FC command is a lot less typing, and I’m lazy. Second, PowerShell isn’t the answer here, and neither is a command line. The real answer is to use Visual Studio to do the job right.

Original Link

How to Solve SQL Database MDF File Not Opening

The MDF files are the files that contain database information in SQL Server. They contain the startup information. The extension used is .mdf and it is the primary data file. The databases can contain one primary data file and several secondary data files or none. 

In this article, we will show you how to work with errors related to the MDF file not opening.


You can use any SQL Server version starting with SQL Server 2008 until the last version.

It also applies to any SQL Server edition.

Getting Started

It is a common problem to have a corrupted database. The reasons are multiple. It can be a hard disk failure where the data file is stored. The hard disk failure can be caused by the usage of the disk over the time. If multiple queries are heavily used in the database, it may fail after some years. A blackout can also produce a hardware failure. You can also have problems produced by viruses and worms. In 2003, the SQL Slammer affected to 75,000 servers in ten minutes (and it returned in 2017 to attack some distracted DBAs).

Many viruses attack not only the data files but also the backups. That’s why it’s strongly recommended to store your backups in a server offline and isolated, if possible. To prevent these problems, you need to use a good firewall, change the default port, and check the shared files and emails. Make sure that the emails are not infecting.

It is usually marked as suspect and the database cannot be used because it is corrupted.

There are several ways to solve this problem. The best way is to use a backup to restore the database. You can combine multiple types of backup to restore your database. It is recommended to check your database backups in case they fail under disaster scenarios. It is also recommended to have a backup in a secure location. There are viruses that attack to data files, log files, and also the backups.

However, if the database is too old you may lose some information from the current days. Another way to restore a database is to use repair commands.

One of the solutions is the DBCC CHECKDB command. This command can show you the integrity database errors, but it also includes options to repair the database.

These commands check the allocation integrity, table integrity, and catalog integrity. It creates a snapshot of the database to do the analysis and contains the following repair options:

  • REPAIR_REBUILD can be used when you want to repair. Use it as your first option to repair the database.
  • REPAIR_ALLOW_DATA_LOSS is used if REPAIR_REBUILD cannot repair the database. This option will lose some data if it cannot be repaired.
  • Finally, you have REPAIR_FAST, which is included for backward compatibility only.

Stellar Phoenix SQL Database Repair is another option that can be used to repair in case that your database is corrupt. This software creates a new MDF file and can be used to recover table data, views, functions, etc.

The first step to repair the database is to download the product. You can download the product using this link.

Once that the product is downloaded, you can install it. Downloading and installing it takes few minutes. To repair, you can use the software. It may require stopping the service and then you will be able to repair the database. It is necessary to specify the path of the corrupted MDF file and if you do not know the location, you can use the tool to locate the file and then you will be able to repair. You can also choose your objects to recover and it can create reports in different formats.


If you cannot open the MDF file and it is in suspected status, your database is corrupt. The first option would be to restore the database. However, your database can be corrupted, or you may not have a current backup. In those cases, it may be necessary to repair using the DBCC commands. In the article, we show different options to repair the database.

There are multiple reasons that can produce this error included hardware problems, virus attacks, and hacker attacks. Viruses and hacker attacks can be closely related. We also gave some recommendations to avoid virus attacks and to secure your database.

Finally, if all the other options fail, you can use third-party tools to repair. We mentioned the Stellar Phoenix SQL Database Repair that can be used to repair a database if it is corrupted. 


For more information about database corruption and data files, and how to check, monitor and repair the files, you can check out the following related links:

Original Link

  • 1
  • 2