• SQL Provision
• Compliant Database Provisioning

Dealing with Server Objects, Settings and Features when Provisioning Databases

Often, we’ll use SQL Provision when we need to distribute lightweight copies of a large production databases to non-production servers, such as for development and testing work. In doing so, SQL Provision lets us define and run a set of data masking rules to ensure that any sensitive or personal data is obfuscated, before it leaves the security of the production server.

In other words, our priority is to ensure that the code and structures are copied, along with real, but masked, data. This helps ensure that the behavior and performance of the database, when tested, matches what will be seen in production. While this tackles a big part of the challenge, it is not the whole story, because no database exists in a vacuum. There are a whole host of other ‘dependencies’ that might affect the working or monitoring of each individual user database.

My previous article, How to Provision a Set of Databases to Multiple Azure-based Servers, dealt with the issue of database inter-dependencies. Here, I’ll use the same set up, and extend that work to discuss the server-level objects, such as linked servers, logins and agent jobs, to name just a few, which are often also required in order to reproduce a functional database system. I’ll demonstrate how we can mimic the production server settings so that our development and test server operate in the same way as their production counterparts.

Dealing with server-level objects, settings and features

We want our test servers to mimic as closely as possible the functionality and behaviors observed in production. Remember though, not every setting can be identical. If your test system only 16 GB of memory and production has 128 GB, you can’t make that setting be the same. We can expect some differences in behavior, certainly around performance, between production and non-production environments. It’s all about identifying those a server-level objects, properties and services that, if not replicated as part of the database provisioning, are going to negatively impact our development or testing.

Server-level objects

The Server Management Objects (SMO) Server class lists over 100 properties, many of which are properties of scriptable server objects, such as audits, collations, endpoints, events, linked servers, logins, triggers and a whole lot more. We need be concerned only with those objects that could be relevant to a database working properly, or perhaps being monitored properly, but there are still quite a few. Phil Factor’s article, over on Simple-Talk, Phil Factor shows how to script out a server instance, including all the major, scriptable server objects. Of those, these are the ones I most often see cause trouble, if they are not correctly accounted for on non-production servers:

• Linked Servers: we’ll be addressing these in the article, and they are probably the single most common server-level object that affects the behavior of a database
• Logins: you’ll also encounter problems if the server logins and database users are not mapped correctly. This used to be a much more difficult problem to solve. While solving it now is easy, and we’ll address it in the article as it’s still a frequent issue
• Agent Jobs: while these don’t directly affect the internal workings of a database, they do affect anticipated behaviors from either the application. We’ll show how to deal with an Agent Job in this article
• Server Roles: it’s possible that you have an anticipated behavior within your database that requires server-level permissions. For example, to perform a bulk load task the assigned database user needs not only INSERT permissions on the necessary tables, but also membership of bulkadmin fixed server role. Any requirement to assign users to server roles has security implications, of course, and needs strict regulation.
• Server Triggers: not many people use server-level triggers, but if you do then the anticipated behaviors within a database will certainly be affected if these objects don’t exist.
• Objects in the Master database: it’s generally regarded as a bad practice to do this, but if you do store your own stored procedures or views within the master database, and then consume them within your individual databases, then you’ll need to capture these as part of any provisioning scheme.
• Security Keys: if you have implemented transparent data encryption, and you’re provisioning test servers from a backup of the production database, then you’ll need also to script out the appropriate keys. Also, if you’re using certificates for things like backup to URL, from an encrypted backup, then you’ll also have to get them scripted (just remember to not put your Azure keys in a non-production environment).

There are other, either more obscure objects, such as startup procedures, or objects much less directly associated with database behavior, such as Extended Events, that you may want to also ensure are part of the provisioning process. These are much less likely to affect individual database behavior, but it’s all part of the system.

Instance-level configuration settings

You might also need to consider mimicking on your test servers the production values for certain instance-level configuration settings, as obtained from the sys.configurations system catalog view.

Some of these directly affect the functioning of the database while others affect the way queries perform when accessing it. Those that have possible security implications if enabled in an unauthorized fashion are covered in Phil Factor’s article Spotting Unauthorized Configuration Settings in SQL Server.

Here’s a short list of those that you’re likely to encounter:

• Cost Threshold For Parallelism: The default value for this setting is unlikely to be the same in your test or development instances as on your production server. If you expect to see the same behavior outside of production, ensure that this setting is replicated.
• Max Degree of Parallelism: Speaking of parallelism, having queries that behave one way in a test environment and a completely different way in your production system can negatively impact testing. This setting must be part of your provisioning
• Default ANSI Settings: Changing these can result in different execution plans and therefore different behavior in your test environments.
• Common Language Runtime (CLR): If you have CLR running on your production environment, you’re going to need to ensure that it’s included in any non-production system where the databases being provisioned consume CLR functionality.
• Cross-Database Ownership Chaining: if you have queries that require the user running them to access objects owned by a login in another database, then of course those queries won’t run unless you also configure the correct ownership chaining in your test servers. This setting be enabled for specific databases only, using the SET DB_CHAINING ON, rather than enabling at the server-level.
• External Scripts Enabled: We’ll talk a little more about services like R or Python in just a little bit. To ensure their functionality in a non-production environment, this setting must be the same.
• xp_cmdshell – if a setting such as this (or OLE Automation and others) is enabled on production then you might need to enable it on your test servers. Of course, non sysadmin users should never be able to execute xp_cmdshell directly, rather just execute stored procedure that make calls to it.
• Nested Triggers: there are other trigger settings as well that you may have to consider, depending on your code and structures.

Features and services

You will need to make sure any SQL Server features on which the database relies are installed on the server to which you’re deploying a clone, or restoring a database, as the resulting database won’t function correctly without them. If, instead, you’re trying to build the latest version of a database during development, and then fill it with data, prior to creating clones, then the build will fail if the target server doesn’t have the required features installed.

For example, your queries against Python or R for example are entirely contained within a given database, but if the services are not installed, those queries will fail. The same thing goes for more old school services like Full Text Search. These must be installed and configured with your non-production instances if they are part of the fundamental behavior of the systems you’re developing or testing.

Scripting out server objects

We need to ensure that we can recreate the behavior of our production servers, in our non-production servers, but do it in such a way that we can recreate it at will. This is the goal of automated provisioning and self-service provisioning.

In this section I’ll show examples of how to script out three important types of server object (linked servers, Agent jobs and user logins), and then I’ll show how we add those scripts to our automated provisioning process, using SQL Clone.

Linked servers

Let’s assume that, in production, there are queries or load processes that reference a linked server. That linked data is likely to be the kind of information that we can’t have in a non-production environment.

Let’s say that we’ve provisioned of test cell with clones of all the local SQL Server databases we need. However, SQL Server defines linked-server mappings at the server instance level, so they are not stored with the database and if these mappings are missing then your links won’t work, and you’ll see errors during testing. In other words, the proper functioning of the code within the database, or within our load process, requires that there be a linked server in place with the right name.

As an example, in my Azure test environment, I have an Azure PostgreSQL database that contains additional credit card information, used to load a table in my SQL Server instance, through a linked server. I also have a couple of queries that call that same linked server within one of my databases.

How do I set up a non-production server for SQL Server development and testing? Does it mean I also need to set up a PostgreSQL server, and have access to the data in that database? Thankfully, no. Instead, I simply set up another SQL Server database and create a linked server with the correct name, as follows:

On my Azure test cell, my remote server is called 'EXTERNALSALES', and I have a SQL Server instance called DevSupport, on which I have a database called DummyData, accessed via a login called SalesDataLoad.

The two keys to making this work are, firstly, to use the same name for the remote server, in this case 'EXTERNALSALES', as is used on production, and secondly to make sure that any tables referenced in the database on the remote server exist and have the same structure as those on the real linked server.

For my DummyData database, I used SQL Data Generator to create some fake information that can be called through the linked server. However, to the code running in all my development instances, this behaves the same as if it were the original production linked server to PostgreSQL.

You don’t have to migrate entire databases and because of how linked servers work, neither do they have to be in their original format. This is a case where simply providing a dummy database that looks good enough gets the job done. This is not to say that you can’t also build out full blown external systems, but that may not be possible for any number of reasons (security, externally owned systems, size, functionality, ability to support, and so on).

SQL Agent Jobs

It’s very rare to encounter a production SQL Server database that contains no Agent jobs. We use them for all sorts of tasks, such as running background processes, maintenance tasks, backups, and data loading (ETL) jobs.

Agent jobs are usually T-SQL batches and aren’t stored within a database, although job steps can and, of course, will reference specific databases and may run within those databases by default. To capture the build script for your Agent jobs, and associated alerts, operators, schedules, Proxy accounts, and so on, you can use a PowerShell script to iterate over all the collections of the JobServer class, as described in the previously-linked article on how to script out a server instance, although Phil Factor’s Database Deployment: The Bits – Agent Jobs and Other Server Objects goes into more details on the specifics of scripting Agent jobs, in particular.

In my example, I have a SQL Agent job that performs a data load, which I’ll need to run on my test cell, in order to test the application. Here’s the T-SQL build script for this job, to recreate it on the test servers:

This will completely recreate my agent job on any target server. I also make sure that I check for the existence of the agent job so that the script can be run against servers where I have already provisioned my non-production environment.

User logins

When you deploy a clone database, or just restore from backup, you’ll have all the database principals (users and roles) and associated permissions. However, you’ll still need to set up the server logins that map to these principals, or perhaps remap database users and roles to their non-production logins.

As an example, one of my local databases has the local user, SalesProd. However, my local server might not have the login, so, my script has a check in place to see if it exists. If the login doesn’t exist, we create it. Then, whether the login exists or not, we ALTER the SalesProd user to relink its SID to the local login.

This ensures that the login can successfully connect to the database and is not orphaned due to differences between the SIDs. Additionally, this login is created locally and has a different password than production helping to ensure that we aren’t exposing production systems and logins in our non-production environments.

Adding T-SQL Scripts to our automated provisioning process

My previous article demonstrated one way to automate provisioning of multiple databases across multiple servers. Now, we just have to add our T-SQL code, for scripting the required server objects, in the appropriate place. In my example, I only need a single script and I only need to add a single set of statements to my PowerShell script (shown in Listing 2 of my previous article) to make this work. After I create all the clones, I simply add the following PowerShell command as an additional post-deployment step:

This will connect to each of my development and test servers, and run each of the scripts outlined previously, to ensure that all my server objects are available on the non-production servers to which I’ve provisioned clones.

Certain considerations could change the way you put this code together. For example, you might need to remove some objects from the server prior to provisioning your databases. I’m thinking along the lines of replication objects or availability groups. Again, you can take advantage of T-SQL to create the scripts for tearing down or removing any necessary server-level objects, and you can run that script as a pre-clone deployment script.

Additional maintenance considerations

One issue that we need to consider is how we capture changes to our production server objects, such as to modify the Agent data load job, in our provisioning scripts. Similarly, if we add additional linked servers, logins, or make any other changes to our production environment, we’d want to also update our provisioning scripts. This adds some additional maintenance overhead that must be considered during our planning and coding. The best way to deal with this would be to make all this a part of our DevOps processes, and ensure that appropriate tests are in place to validate that systems are set up appropriately. Put simply, identify when you’re changing a server level object and incorporate that change into your provision scripts.

Another approach would be to check for existence of each of the server objects and then drop and recreate them every time. This could make maintenance of the processes easier in some ways but could cause additional headaches. You would have to ensure that the server objects are dropped and recreated in a particular order. For example, you’d need to ensure that a given linked server existed before you created a stored procedure in the master database.

For most systems I’ve worked with, most changes occurred within databases, not within server objects, so the number of times they changed was minimal. With that in mind, using scripts that creates the objects, as shown in our examples, and then maintaining those scripts as server-level object changes, would serve the purpose most of the time.

Conclusion

Once you start to automate your databases provisioning processes, dealing with any kind of server-level variations, or special circumstances, is just a question of setting up the code to handle them and then incorporating it into the process.

By combining the capabilities inherent within T-SQL with those within PowerShell and then adding all that to the functionality that a tool like SQL Provision offers, will make it much easier to automate and refine the provisioning of your development and test systems. Further, that automation helps to add to the protection, and documentation of that protection, to your systems.

As a result, you’ll be much better able to support one of the fundamental goals of database DevOps, which is to “shift-left” critical database build, test and deployment processes, so that they are performed frequently from very early in the development cycle, and are performed in a way that you can have confidence that the results will mimic what happens you come to release to production.

Tools in this post

SQL Provision

Provision virtualized clones of databases in seconds, with sensitive data shielded

Find out more