HOW TO: Know What That Installer Did to Your Database

Remember when I told you I had come across and old script that checked your SQL Server configuration setting? Well, here’s another old script of mine I think is worth sharing. The idea was that I wanted to verify what changes (if any) were made to my SQL Server instance as a result of a 3rd party vendor install.

Let’s just say that after taking part in many vendor application installs in my career as a production DBA it became necessary for me to put this script together. It became an important script for me to use before signing off on any vendor install, as it gave me a reasonable level of comfort that the instance had not been altered to fall outside of our standards. In many ways this script served as a sort of application software installation checklist for our team.

The process we had in place is that someone on our team would run the script, save the results to a text file, have the installation completed, restart the instance (IMO, you should ALWAYS restart the instance after installing any application for the first time), run the script again and then compare the results. I never bothered to automate this but I suppose I could have found a way to streamline things a bit. Around the time I was putting this script together we started making heavier use of Systems Center Operations Manager and I had built dashboards that would monitor many of these items in real time. But for anyone out there that has suffered through a vendor install doing horrible things to your instance, feel free to use this script as the start of building your own vendor install process check.

You can find the script below, and you can download it from here as well. What I did was to take the older script and updated it for use with SQL 2012 and SQL 2014, but you could modify it to work on older versions if desired. To me the code isn’t as important as the concepts about what you want to look for with regards to vendor installs. So, feel free to take my script and add in your own items you want to check for after doing a vendor install.

Here’s a summary explanation of the things I liked to check for to know what the installer did to my database server:

Check for @@SERVERNAME

I would always check to make certain that the value for @@SERVERNAME has not been changed. How would this even happen? The most common way was through the creation of a “loopback” linked server, something that will leave your instance in a (technically) unsupported state. If a loopback linked server is part of the vendor architecture, then you should use a dedicated SQL Server instance for their software. Or, consider the use of a DNS entry and a local SQL alias as a workaround, if the vendor allows for this. Also, this is a good time to tell you to use the SERVERPROPERTY function to return the name of the server in your scripts, so your code doesn’t break on servers that have loopback linked servers.

Check for Linked Servers

I would want to know if any new linked servers have been created, or existing ones modified. While some applications would try to create and use a linked server (see above), others would make use of OPENDATASOURCE or OPENROWSET in order to access remote servers. I can’t change vendor code, but I should have a say on the use and configuration of linked servers, especially the security context used.

Check for SQL Agent Jobs

The creation of jobs within SQL Agent was always a concern for me. I was most concerned if they were database backup jobs as I didn’t want such jobs to interfere with our standardized backup process. Perhaps my favorite example here was the time a vendor install created a job named “DBCC FREEPROCCACHE“, something they said was “needed for performance reasons”. I will give you two guesses as to what the job did for performance.

Check for databases

No doubt the vendor will want to create a database (or two) for their software, but what I want to know is if they have created the database by placing files on non-default drives. Yes, I’ve seen vendor software set explicit file paths for their database data and log files, and it drives me crazy. No, I don’t know why they would do this. But it happened to me at least once and hence it is something I will check for from now on.

Check for logins

I want to check to see if any new logins have been created or modified. If I find any, then I would manually examine the permissions. Often times the account being used to do the install ends up a member of the sysadmin fixed server role.

Check server configuration

I would also check the contents of sys.configurations to see if any changes have been made using the sp_configure system stored procedure. I also have this handy script I put together for a TechEd session in 2012 that will check to see if my current configuration values are non-default.

Check for SSIS packages

I would want to know if any SQL Server Integration Services (SSIS) packages have been created as the result of the install. Chances are if a package has been created, then so has a job (likely in SQL Agent) to run that package. And guess who is assumed to be responsible for fixing things should the job or package fail? That’s right, it’s you. Best to know about this before you need to update your resume and leave town.

Check for backups

If the install has performed backups of any of my databases, including new ones, I will want to know about this. I once had a system that created a database on a non-default drive, backed it up to the same directories, and created a job that would truncate the transaction log file at 8PM on the 1st day of the month. It was the perfect trifecta. And by “perfect” I mean “nightmare”.

Check for changes to system databases

I would want to look for any new objects created inside of master, model, and msdb. You don’t want changes being made to your system databases without you knowing why and when they happened. Thinking about this a bit more I should probably also check the model database properties, too. Maybe I’ll add that in as a v2 for this script…someday.

Check for triggers

My disdain for triggers is well known, and it’s why I check for them at the server instance level. I do not believe I have ever had a vendor install a database trigger on an instance, but that hasn’t stopped me from running this check each and every time because I hate triggers. Did I mention that already?

Check default data and log file locations

This is the part I updated below for SQL 2012 and SQL 2014, to take advantage of two new SERVERPROPERTY options, ‘instancedefaultdatapath’ and ‘instancedefaultlogpath’. Too bad they didn’t include the default backup path as well! Previously I would have used the undocumented xp_instance_regread stored procedure to get such details, but I try to avoid the use of undocumented procedures when possible.

Lastly, I would also review the SQL Server error log for anything out of the ordinary. Since the instance was restarted as part of this process (in an ideal world, of course) then it is fairly easy to review the current log as well as the tail of the previous log (during the time the install was happening). You never know what information you may find in there.

It’s not that I didn’t trust these vendors (and of course now I *am* one of those vendors), it’s just that I have found that the vendor documentation is often not up to date with the version of the software you are installing. Thus the need to do these checks myself.

Script

Here’s the script, which you can also download here. Also, here’s my usual disclaimer:

Script disclaimer, for people who need to be told this sort of thing:

DISCLAIMER: Do not run code you find on the internet in your production environment without testing it first. Do not use this code if your vision becomes blurred. Seek medical attention if this code runs longer than four hours. On rare occasions this code has been known to cause one or more of the following: nausea, headaches, high blood pressure, popcorn cravings, and the impulse to reformat tabs into spaces. If this code causes your servers to smoke, seek shelter. Do not taunt this code.

/*=============================================
  File: SQL_Server_vendor_install_check.sql
 
  Author: Thomas LaRock, https://thomaslarock.com/contact-me/
  

HOW TO: Know What That Installer Did to Your Database

  Summary: This script will assist in determining the status
  of these specific server instance items:

    * Check status of @@SERVERNAME
    * Creation/Alteration of linked servers
    * Creation/Alteration of new jobs in SQL Agent
    * Creation of databases, and check for non-default drives
    * Creation/Alteration of server logins
    * Altering server configurations - sp_configure
    * Creation of SSIS packages - look in the msdb store   
    * Performing database backups (and to non-default locations)
    * Creation of objects in master, model, msdb
    * Creation/Alteration of server triggers
    * Altering database data and log file default locations

 Here are the steps necessary to run this script:

 1. Run this script prior to the vendor installation and save the output
        (in SSMS, you can output directly to a text file)
 2. After the vendor install is complete, reboot the server (just in case)
 3. Run this script again and compare the output to the original

  Date: August 12th, 2014
 
  SQL Server Versions: SQL2012, SQL2014
 
  You may alter this code for your own purposes. You may republish
  altered code as long as you give due credit. 
 
  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
  OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
  LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
  FITNESS FOR A PARTICULAR PURPOSE.
 
=============================================*/


/*=============================================
Check value of @@SERVERNAME
=============================================*/

SELECT @@SERVERNAME as [ServerName]


/*=============================================
Check for linked servers that are created or altered
=============================================*/

SELECT * 
FROM master..sysservers
WHERE schemadate > DATEADD(dd, -1, GETDATE())
ORDER BY schemadate


/*=============================================
Check for jobs in SQL Agent that are created or altered
=============================================*/

SELECT * 
FROM msdb..sysjobs
WHERE date_created > DATEADD(dd, -1, GETDATE())
OR date_modified > DATEADD(dd, -1, GETDATE())
ORDER BY date_created DESC, date_modified DESC


/*=============================================
Check for new databases created and also check
the location of the data and log files
=============================================*/

SELECT *
FROM master..sysdatabases
WHERE crdate > DATEADD(dd, -1, GETDATE())
ORDER BY crdate

SELECT *
FROM master..sysaltfiles
ORDER BY dbid DESC, fileid


/*=============================================
Check for server logins that are created or altered
=============================================*/

SELECT * 
FROM master..syslogins
WHERE createdate > DATEADD(dd, -1, GETDATE())
OR updatedate > DATEADD(dd, -1, GETDATE())
ORDER BY createdate DESC, updatedate DESC


/*=============================================
Check server configurations
=============================================*/

SELECT *
FROM sys.configurations


/*=============================================
Check if any SSIS packages have been created
=============================================*/

SELECT *
FROM msdb..sysssispackages
WHERE createdate > DATEADD(dd, -1, GETDATE())
ORDER BY createdate DESC


/*=============================================
Check if any databases have been backed up, especially if
they have been backed up to a non-default location
=============================================*/

SELECT database_name, physical_device_name
FROM msdb..backupset bs
INNER JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_creation_date > DATEADD(dd, -1, GETDATE())
OR bs.backup_start_date > DATEADD(dd, -1, GETDATE())


/*=============================================
Check if any new objects created in system databases: master, model, msdb
=============================================*/

SELECT *
FROM master..sysobjects
WHERE crdate > DATEADD(dd, -1, GETDATE())
ORDER BY crdate DESC

SELECT *
FROM model..sysobjects
WHERE crdate > DATEADD(dd, -1, GETDATE())
ORDER BY crdate DESC

SELECT *
FROM msdb..sysobjects
WHERE crdate > DATEADD(dd, -1, GETDATE())
ORDER BY crdate DESC


/*=============================================
Check for server triggers that are created
=============================================*/

SELECT *
FROM sys.server_triggers
ORDER BY create_date DESC


/*=============================================
Check the database data and log file default locations
These parameters were introduced in SQL 2012
=============================================*/
SELECT SERVERPROPERTY('instancedefaultdatapath') AS [DefaultData]
SELECT SERVERPROPERTY('instancedefaultlogpath') AS [DefaultLog]