How to Check SQL Server Security Part 1

You know you need to be thinking about SQL Server security, but maybe you’re not sure where to start. Topics like firewalls and ports and port scanners and such may be dancing your mind. Those are good things to think about, but they are not under your sphere of influence as a data professional in charge of SQL Server. So, what can you do?

Your first place to start is by looking at the Logins, which as I’ve explained in a previous  post, are at the level of the SQL Server instance level.

The internet is a vast ocean of scripts on this topic so let me boil the ocean down to a couple of options for you.

Option 1:

A simple script to get the server Logins and their roles, ignoring the built in accounts that start with NT Service.

/*
Server principals and their role.
*/
Use master;
GO
SELECT  roles.principal_id AS RolePrincipalID,  
roles.name AS RolePrincipalName,  server_role_members.member_principal_id  AS MemberPrincipalID,  
members.name AS MemberPrincipalName

FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals AS members 
    ON server_role_members.member_principal_id = members.principal_id
WHERE members.name NOT LIKE 'NT SERVICE%';

The above script is a starting point. It’s also simple to understand and easy to use; i.e. it’s not overly complex and you see results like below.

Output Server Role Script

 

Option 2: Kenneth Fisher’s sp_SRVPermissions

This is a far more complex stored procedure than the script in option 1 and it digs deeper and provides more information. The output also provides some scripting that will save you time. Below are a couple of examples of how to run it from the DBAUtility database that I tend to install on SQL Servers. You can pass it a number of parameters but the most common ones are going to be either a role name you’re interested in knowing more about, Like SysAdmin, or a Principal name that you are curious to know more about,

/*  
Server permissions by role you're interested in.
*/
exec DBAUtility.dbo.sp_SrvPermissions @Role = 'sysadmin';

/*  
Server permissions by server principal you're interested in.
*/
exec DBAUtility.dbo.sp_SrvPermissions @Principal = 'MAPToolkit';

Here are some abbreviated results.

Results when passing a role name
Results when passing a Principal name

Some observations:

  1. Passwords and sids are hashed and he says in his script comments that this works fine in SQL Server 2008 and above.
  2. There are several other parameters for this procedure. Some let you look for things like SQL Logins, a Windows Group, Windows login and others. Some change the output  or allow the use of wildcard LIKE searches.
  3. Note the DROP and the CREATE scripts in the shown output. I love this because it saves you time if you decide you want to drop or need to put a principal back.

 

Next Steps to Take

  1. Download the stored procedure from Kenneth Fisher and experiment with it’s output to hep you manage SQL Server security.
  2. If you have an idea for an improvement, make that known on his GitHub
  3. If you have questions or comments, leave me a comment on this post, or message me on Twitter.

 

 

 

How to Build a SQL Server Inventory Using PowerShell

You’re a data professional and you’ve been given the keys to a new SQL Server environment. You know you need to build a SQL Server inventory so you know what is in your environment, but how do you get that information?

One of the things I have talked about in other posts is how to create a SQL Server inventory. I’ve discussed using the MAP Toolkit and building your own inventory database using T-SQL. Today, we’ll see another way to generate a list of SQL Servers in your environment.

 

PowerShell to Find Your SQL Servers

There is a beautiful PowerShell module called DBATools that, somehow, still not everyone knows about. It has hundreds of commands that let you do just about anything you can do in SSMS in terms of showing SQL Server properties. In our case, we want to use the cmdlet Find-DbaInstance.

 

WARNING: BEFORE YOU RUN THIS CMDLET, NOTIFY YOUR SECURITY PEOPLE THAT A NETWORK SCAN WILL TAKE PLACE, AND ASK PERMISSION TO DO THIS. IF YOUR ENVIRONMENT IS LARGE THE SCAN MAY TAKE A VERY LONG TIME.

As is my custom when working with a new command, I look at the help system in PowerShell.

Help Find-DbaInstance -Detailed

This gives you a full output of the help for the command. One thing I want to draw your attention back to is the length of the scan. you can help limit this by using the -IPADDRESS switch and put in a range to scan like this:

-IPADDRESS “10.1.1.1-10.1.1.5”

OR

an IP Address and subnet mask

-IPADDRESS  “10.1.1.1/255.255.255.0″”

One of the simplest ways to get started with this command is to use the -ComputerName parameter.

Find-DbaInstance -ComputerName SQLServerComputerName

By default, this will output a basic set of data:

  1. ComputerName: Exactly what it sounds like.
  2. InstanceName: The named instance
  3. SQLInstance: Complete instance name. For a named instance this will combine ComputerName\InstanceName
  4. Port: The port SQL Server is communicating on.
  5. Availability: Whether a connection could be made
  6. ScanTypes: This command has a lot of ways it tries to discover whether a SQL Server exists, thus the bolded, all caps warning above about talking to security before you run this cmdlet. Here’s a list: Browser, SQLService, SPN, TCPPort, DNSResolve, PING, SQLConnect, ALL. The “default” value in the output for ScanTypes means it will try all of these.
  7. Confidence: The degree to which results can be trusted. There are 4 levels.
  • High: A SQL Connection was established. This includes rejection of the login for bad credentials.
  • Medium: A reply from the browser service or both a TCPConnect AND and SPN test.
  • Low: Either a TCPConnect OR an SPN test was successful.
  • None: There’s a computer on the end of the scan, but no SQL Server instance could be found.

By default, PowerShell doesn’t show you all the possible columns in the output. One way to get that is to pipe to SELECT *, like I’m doing below.

Find-DbaInstance -ComputerName skolarlee-pc | SELECT *

You can also do things like output the data to a file using out-File.

Find-DbaInstance -ComputerName SQLServercomputerName | SELECT * | Out-File 'C:\DBATools\FindInstanceOutput.txt'

 

Here is sample output. Click to enlarge.

Find-DbaInstance OutPut Build a SQL Server Inventory
Test file of Find-DbaInstance OutPut

Or, you can send it to a pop up window using Out-GridView

Find-DbaInstance -ComputerName SQLServerComputerName | SELECT * | Out-GridView

here is sample output. Click to enlarge.

Find-DbaInstance Out-GridView
Find-DbaInstance Out-GridView

 

For something more suited to finding SQL Servers across an enterprise environment, you can query Active Directory looking for computers with “sql” in the name and then return their properties.

Get-ADComputer -Filter { name -like 'sql*' } | Find-DbaInstance | Select *

 

Build a SQL Server Inventory Using PowerShell

Putting all of this together, you could get this data into an Excel format and then insert it into an existing table using Write-SqlTableData. To do this though, you’ll need to import a module called ImportExcel.

This module will allow you to import or export to the Excel .xlsx format. You can pipe that to the standard PowerShell cmdlet Write-SqlTableData. Using the switch -Force will cause the table to be created for you. A bit of a warning though, the data types will be things like NVARCHAR(max) when something smaller will do. So, it might be worth it to make the table yourself ahead of time.  You could do this with the cmdlet New-DbaDbTable. Run the help cmdlet against that to see how to leverage that to make your table ahead of time.

Another word of caution, this code connects to Active Directory to get a list of computers with SQL in the name. That part does not cause any issue. However, after it does this, it will likely do repeated scans of computers to detect the existence of a SQL Server.

Talk to your Security team before running this code.

Install-Module ImportExcel
Get-ADComputer -Filter { name -like 'sql*' } | Find-DbaInstance | Select * | Export-Excel 'C:\DBATools\FindInstanceOutput.xlsx'

(Import-Excel -Path 'C:\DBATools\FindInstanceOutput.xlsx') | Write-SqlTableData -ServerInstance "SQLServerInstanceName" -DatabaseName "DBAUtility" -SchemaName "dbo" -TableName "SQLInstances" -Force
Read-SqlTableData -ServerInstance "SQLServerInstanceName" -DatabaseName "DBAUtility" -SchemaName "dbo" -TableName "SQLInstances"

 

Next Steps To Take

  1. Experiment with New-DbaDbTable to see how to make tables with it.
  2. Install the ImportExcel module.
  3. Talk to your Security team about using these commands to find SQL Servers.
  4. Try out the code in the last section.
  5. If you have questions or comments, leave me a comment on this post, or message me on Twitter or DM me on LinkedIn.

How to Build a SQL Server Inventory Using T-SQL Scripts

You need to create a SQL Server inventory. Without this information, you’ll be unaware of critical information about the environment your responsible to manage.

We’re on a journey to make sure that you as a DBA know what SQL Servers are in your environment so you know what you’re responsible for managing.  As your guide, I discussed in a previous post, how to get started with using the MAP Toolkit from Microsoft to get a list of SQL Servers in your environment and some key metadata about them.

Today, we will be looking at using T-SQL and a Central Management Server to create a SQL Server inventory. Let’s say that you’re  new at this company or in this role and all you have right now is the list of SQL Servers that people know about. you haven’t been able to run the MAP Toolkit or maybe you’ve been told that you can’t run it for some reason. the list of SQL Servers that people “just know about” probably isn’t anywhere near complete, but you have to start somewhere.

 

How To Create a Central Management Server

Open SQL Server management Studio. If you don’t see an area called Registered Servers, go to the View menu at the top and left click. Then left click on Registered Servers. Now Right click the “Central Management Servers” folder and choose “Create Central Management Server.” I recommend choosing a SQL Server in your environment that is on the newest SQL Server in your environment. The SQL Server must be on at least SQL Server 2008 in order to create a Central Management Server.

 

Register Central Management Server in SSMS

 

Once you do this, the fill in the details of the server registration dialog, then click Test and then Save. If you don’t get a successful test, then check your server registration information and try again.

Central Management Server New Registration Dialog

Now, right click the name of the registered server you just created and select “New Server Group.” create groups that will organize your SQL Servers. That cold be done by product name, SQL Server version, or by things like Production, Staging, Development. Now right click a folder and select New Server Registration and register the appropriate servers

Using a Central Management Server to Build a SQL Server Inventory

Right click your Central Server name and select “New Query.” Now copy the code below, paste it into the window. Let’s talk about what it does before you run it.

  1. If it doesn’t exist already, it creates a database called DBAUtility on each of the servers it connects to.
  2. The script creates a database view in the DBAUtility database. That view allows you to collect some basic information about the SQL Server instance. the view returns the machine name, SQL Server instance name, product version, Cumulative Update level, edition, and product version number. This will be enough information to help you identify critical things like, how many SQL Servers are on unsupported versions or old cumulative updates.
  3. The script creates a table to store the data for each SQL Server and a procedure to insert that data.
  4. There is a call to the database view to retrieve the data for each SQL Server that your Central management Server connects to. You could also query the table in each DBAUtility database as well.

 

/*Make the DBAUtility database if it doesn't exist.
This will put the database in the dfault data and 
log directories for the SQL instance.
*/
IF NOT EXISTS(SELECT * from sys.databases where [name] = 'DBAUtility')
  BEGIN
  CREATE DATABASE DBAUtility
  END

USE DBAUtility;
GO

CREATE OR ALTER VIEW GetSQLServerProperties
AS
SELECT  
  SERVERPROPERTY('MachineName') AS [ServerName],
    CASE WHEN SERVERPROPERTY('InstanceName') IS NULL THEN 'MSSQLSERVER'
    ELSE SERVERPROPERTY('InstanceName')
    END AS [SQLServerInstanceName], 
    CASE 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'     
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017' 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL2019' 
     ELSE 'unknown'
    END AS ProductVersion,
  SERVERPROPERTY('ProductUpdateLevel') AS CULevel,
  SERVERPROPERTY('Edition') AS [Edition],
  SERVERPROPERTY('ProductVersion') AS [ProductVersionNumber];

GO
DROP TABLE IF EXISTS dbo.SQLServerProperties
CREATE TABLE dbo. SQLServerProperties
(
Id INT IDENTITY (1,1) NOT NULL,
ServerName VARCHAR(20) NOT NULL,
SQLServerInstanceName VARCHAR(50) NOT NULL,
ProductVersion CHAR(7) NOT NULL,
CULevel VARCHAR(4) NOT NULL,
Edition VARCHAR(40) NOT NULL,
ProductVersionNumber VARCHAR(15) NOT NULL,
CONSTRAINT PK_SQLServerProperties PRIMARY KEY CLUSTERED (Id),

/*Unique index below prevents the same server name and instance 
name from being entered into the database twice
*/
INDEX IX_ServerName_InstanceName UNIQUE NONCLUSTERED (ServerName, SQLServerInstanceName)

);

GO

CREATE OR ALTER PROCEDURE dbo.SQLServerPropertiesInsert
AS
INSERT INTO SQLServerProperties

SELECT CONVERT(VARCHAR(20),[ServerName]), 
 CONVERT(VARCHAR(50),[SQLServerInstanceName]), 
 CONVERT(CHAR(7),[ProductVersion]), 
 CONVERT(VARCHAR(4),[CULevel]), 
 CONVERT(VARCHAR(40),[Edition]), 
 CONVERT(VARCHAR(15),[ProductVersionNumber])
 FROM dbo.GetSQLServerProperties;
 
 /*
 Example call to insert the data
 exec dbo.SQLServerPropertiesInsert

 Example call to retreive the data inserted
 SELECT *
 FROM GetSQLServerProperties;
 */

 

You can get started building a SQL Server inventory if you follow these steps. The inventory makes assessment of next steps  easy.

Next Steps To Take

  1. If you have a lot of SQL Servers to register in a Central Management Server, consider this post which shows you how to do it with PowerShell.
  2. Compare your collected data against a known list of currently supported SQL Server versions to find out of support SQL Servers. This is your migration project list.
  3. Compare the SQL Server product version numbers to a known list of current patches and see what needs to be patched. This is your immediate patching list.
  4. If you have questions or comments, leave me a comment on this post, or message me on Twitter

 

Is it ok to use proprietary database features

 

I was recently thinking of SQL Server temporal tables and how there is a perspective that you shouldn’t use proprietary features of a product because it locks you into that product. I want to be your guide on this matter.

 

Database platforms are typically long term decisions

First of all, that product was chosen for a reason so it’s unlikely that any time soon it’s going to be replaced. This is especially true if we’re talking about database platforms. Doing that type of wholesale swap out is expensive because you’re paying you’re employees to do the long, tedious work to do that platform switch.

Lowest Common Denominator Effect

Second, by not using platform specific features, you’re actually “locking yourself in” in a different way. You’re locking yourself into the lowest common denominator for features, “just in case” your company decides to switch platforms. Your company spent $16,000 dollars per core for Enterprise SQL Server. Refusing to use all of it’s capabilities is actually a HUGE waste of money. Would you buy a Ferrari to drive in a race and limit yourself to the horsepower of a Chevy Pinto?! No way! Why would you do that?

Similarity of features across platforms

Third, many platforms have similar features. This means that implementing “x” in SQL Server could be done in another platform by doing “y”. In at least some cases then, you’re not locking yourself into a platform by using a specific feature.

For example, here is a page talking about an extension for PostgreSQL that allows you to use temporal tables, and it’s implemented VERY similarly to the feature in SQL Server.

Paying developers to build an alternative

Fourth, what are you going to do to give yourself that needed feature that you’re not going to use from your existing platform? Are you going to pay good money for your team to develop something from scratch that likely won’t be battled tested like the platform’s version of the feature? this is like rolling your own monitoring when you could instead by any number of tried and true platforms instead.

So, yes! It’s perfectly fine to fully utilize the benefits of the platform you’re on and spent money on. This is especially true when you have parity in a feature set across platforms.

If you’re interested, join the conversation about this topic over on LinkedIn.

 

 

 

 

What Are SQL Server Logins and Users

You’re a data professional learning about managing SQL Server and you’ve been asked to grant permissions for SQL Server to an individual or a group of individuals. What do you need to understand in order to accomplish this? I’ll be your guide to getting started with handling access to SQL Server.

Let’s start with an analogy.

SQL Server Logins and users

Keys and fobs are common place these days for vehicles. Sometimes if you have a fob it ties into the ignition allowing for push button start so no key is needed for that. That’s not always the case though. Many people still have key and fob combos on their key ring. For our purposes, we’re going to assume that is the case.

Your fob let’s you access your vehicle’s doors. The fob gives you what I’ll call an initial, top layer of access to your vehicle. With it you can unlock the doors so you can get inside. You can now sit in the seats, open the glove compartment and access other surface level features of the car, but you can’t do much else.

Your key to the ignition lets you start the car, giving you access to the engine, radio, power seats and other features in the vehicle.  With the key in the ignition, you can now take your car for a spin.

What is a SQL Server login?

A SQL Server login is much like the fob to your car. The login, by itself with no other permissions having been applied, gives the holder of the login access to the initial, surface layer parts of SQL Server. The login allows for connecting to an installation of SQL Server on a computer. A login can be a Windows authenticated account or a SQL Login. One uses the Windows operating system authentication method and the other uses just data within SQL Server itself to authenticate or verify the ability to connect to a SQL server instance.

SQL Server Logins

SQL Server Fixed Roles

Roles are sets of permissions that can be assigned to a security object, like a SQL Server login. These permission sets grant certain abilities to the logins that have been assigned those roles.

  • bulkadmin: Allows for running the BULK INSERT statement
  • dbcreator: Allows for creating, altering, dropping or restoring a database. Databases created by logins in this role are owned by that login and essentially have db_owner rights in the database.
  • diskadmin: Allows for the management of backup devices
  • processadmin: Allows for use of the KILL command to end session connections
  • public: Allows for access to certain metadata system catalog views and is granted to all logins by default.As a best practice, you should never change the permissions applied to the public server role because then every login connecting will have those permissions.
  • securityadmin: Allows for the management of access to the SQL Server. It manages logins and associated permissions such as grant, deny and revoke.
  • serveradmin: Allows for changing server-wide options, can shut down the server, clear the SQL Server procedure cache, as well as a few other things.
  • setupadmin: Allows for adding and removing Linked Servers using T-SQL
  • sysadmin: Allows for full control of the SQL Server. Logins in this role can do anything.

What is a database user?

A database user object is much like the key to your car. The database user (the key) is a database level authentication object that is typically associated with a Server level login (the fob). This user object is what provides for connecting to one or more databases.

SQL Server Database User

SQL Server Database Fixed Roles

  • db_accessadmin: Allows for adding or removing access to a database
  • db_backupoperator: Allows for backing up the database
  • db_datareader: Allows for reading the data in a database
  • db_datawriter: Allows for using insert, update or delete in a database
  • db_ddladmin: Allows for creating, altering or dropping (deleting) database objects
  • db_denydatareader: Denies the ability to read (SELECT) data.
  • db_denydatawrtier: Denies the ability to write (add, update or delete) data.
  • db_owner: Allows for full control of all database objects and data.
  • db_securityadmin: Allows for managing access to a database. Has control over database users and their permissions.
  • public: A role granted to al users in a database by default. It is best practice not to alter the permissions of this role.

 

The login (fob) allows for connecting to SQL Server and in turn is almost always associated with a database user (the key). That user allows connection to one or more databases. The server roles grant permissions for group members to do things with the SQL Server installation. The database roles grant permissions to members to do things with one or more databases.

Using Windows Active Directory to Manage SQL Server Access

To make managing SQL Server easier over the long run, whenever possible, it is best to use Windows Active Directory groups and appropriate server and database fixed roles. For example, if you need to grant read only access to the accounting database for the accounting department, or even just a couple of people in Accounting, then either ask the SysAdmins about Windows groups for the Accounting department and who is in those roles, or get that information from Active Directory Users and Groups yourself. Now, add windows groups to SQL Server and assign permissions. For more on that see this post about the topic.

 

Next Steps to Take

  1. See this for MS Docs on how to create a login.
  2. See this for MS Docs on how to create a database user.
  3. If you have questions or comments, leave me a comment on this post, or message me on Twitter

Building a SQL environment inventory with the MAP Toolkit

 

The MAP Toolkit, also called the Microsoft Assessment and Planning Toolkit, is a standard way to obtain information about your Microsoft environment. This includes your SQL Server. The tool is so common that a link to it is included in the SQL Server media. This tool will scan your network and provide a fairly comprehensive report on SQL Servers it finds. The direct link to the MAP Toolkit download is located here. Be sure to download all the files from that page.

MAP Toolkit
MAP Toolkit Link In SQL Server Installer

As a DBA new on the job, the MAP Toolkit can be a lifesaver. You have no idea how many SQL Servers there are or what versions and editions exist, or where they are installed. Sure, you asked these questions during the interview, but unless you were talking to a DBA team who has been there awhile, you likely didn’t get correct answers. You see, most places don’t really know how many SQL Servers they have. I’ll be your guide to getting that first SQL Server environment inventory up and running so you know what you’ve inherited.

Why Use the MAP Toolkit?

First, using this MAP Toolkit will automate the process of gathering important SQL Server environment information for data professionals. Even if you only have a a few SQL Servers, using this tool will save you time versus manually connecting to each SQL instance to get the same data.

Second, there are almost always more SQL Servers in an environment than people realize and this application will locate them for you.

Third, the MAP Toolkit will help you find computers where Standard or Enterprise Edition is installed other than production. You have to have licensing for Standard or Enterprise edition when installed on people’s laptops or PCs. I’ve found plenty of employee computers running Enterprise edition SQL Server! Also, non-prod servers should not have Standard or Enterprise edition installed for the same reason. In non-prod, use Developer edition, as it’s free.

Getting Started with the MAP Toolkit

A full tutorial on using the MAP Toolkit is available here on Microsoft Learn. MAP Toolkit installation is fairly straight forward but is also included in the  tutorial from Microsoft that I linked to above. You can install it on your own desktop and then scan Active Directory for SQL Servers. Please be sure to notify your System Administrators BEFORE you run the scan as it will set off intrusion detection alarms.

Here is a screen capture of what some of the inventory methods are.

MAP Toolkit scan options
MAP Toolkit – Scan options

 

Generating Excel Reports from the MAP Toolkit

Once SQL Server scan is complete, you will see a screen like this one.

MAP Toolkit - Post Collection Screen
MAP Toolkit – Post Collection Screen

Click anywhere in the box under “SQL Server Discovery”, then you will see a screen like this. Click each excel icon in the Options section to generate the Excel for what was captured.

MAP Toolkit - Generate Excel Output
MAP Toolkit – Generate Excel Output

 

The SqlServerAssessment file will have the list of the SQL Server instances it found, the computers those instances are on the product version name, Service Pack number and edition. The data will also contain the OS version of the computer and some general hardware information. Importing at least these columns into a SQL Server database using the Import/Export Wizard in SQL Server is your first step to understand what your SQL Server environment looks like.

If you want to see what the assessment files might look like before you run your first scan, be sure to extract the file “MAPSample_Documents.zip” and locate the SQL Server samples. These will give you an idea of the kinds of information you can expect from the tool.

Why Import This Data to a SQL Server?

Importing this data to SQL Server affords you at least four advantages to keeping it just in an Excel file.

  1. The data recovery abilities of a SQL Server database are superior as opposed to trying to recover a file that has been deleted, which is not always possible.
  2. The data is accessible to other data professionals who have proper permissions on the SQL Server. This makes it easy for other people on the data team to review, analyze and maintain the data.
  3. Having the data in a database also allows you to query the data for a variety of things, like how many of each product name(SQL Server 2005, 2008…2017, 2019), that you have.
  4. As you maintain this data over time. You can see how the environment changes as you upgrade older SQL Server products to newer ones, for example. This can be great information to have when it comes to updating your resume or when approaching review time.

There is certainly other useful information in the various tabs of both spreadsheets created by the reports. I strongly encourage you to explore that data to learn what you can about your SQL Server environment.

 

Next Steps To Take

  1. Now that you seen what the MAP Toolkit can do for you, download and run your first scan. Then import it into SQL Server. You’ll likely be surprised at what it turns up.
  2. Analyze the scan results to see how many SQL Servers you need to dump upgrade to get onto a supported version of SQL Server. Upgrading to a modern version of SQL Server can be a key goal for you as a DBA.
  3. If you have questions or comments, leave me a comment on this post, or message me on Twitter

6 Ways to Advance Your IT Career

The “Great Resignation” is upon us, or so the internet tells us. People want better working conditions, better wages, just… better. Large swaths of people are trying to advance their careers. Here are 6 ways to advance your IT career.

  1. Accept that long term loyalty to a small to mid-sized company is likely keeping your salary low and limiting opportunities.
  2. Use the wizards at salary.com to plug in the job titles you want and discover what you should be making.
  3. Focus on building technical skills, especially early in your career
  4. Work on your interviewing skills
  5. Start blogging
  6. Start presenting

How to Advance Your IT Career: Here is How I did It

  1. I realized that long term loyalty to my small to mid-sized company was keeping my salary low.

Career growth opportunities were limited. The company I was in when I launched my IT career probably had 200-300 people in their corporate office and there were 8-12 local offices that had maybe 5-10 people working out of each of them. So, less than 500 employees. We weren’t big.

There were some opportunities for growing your career there, but those opportunities to be promoted to a new or different role came around once every 3-5 years, and sometimes less frequently than that. There was maybe one more title change in sight so upward mobility was now limited.
I had been in relatively the same role for 4 years. Annual increases were  my primary means  of increasing my salary.

Also, in my scenario, it didn’t seem like we were exactly swimming in money. These two things combined to keep my career opportunities limited and my wages below average.

“I needed to change jobs if I was going to advance.”

 

I had been at this company about 9 years already before I started looking for other work. Loyalty wasn’t going to help my career. I needed to change jobs if I was going to advance.

  1. I used the wizards at salary.com to plug in the job titles I wanted and discover what I should be making.

 

Salary.com What am I Worth
Salary.com What am I Worth

You can use the site to compare different job titles to one another in terms of experience generally required, job descriptions, and other details.Below is some data for Austin, TX for Database Administrator

Sample DBA Salary Information - Austin TX

TitleAVG Years ExperienceLow End Salary YearlyHigh End Salary YearlyMedian Salary Yearly
DBA I0-2520008700067000
DBA II2-47100011800094000
DBA III4-695000140000118000
DBA IV7+109000152000131000

Once I had revelation #1 above and saw how much I was below the average salary, I was fairly motivated to make an employment change. That first job change resulted in a 10% increase in my salary!

  1. I spent the better part of 2 years building technical skills.

Classes at a university training center

There were two parts to this approach. First, I took IT classes at a local education and training center. To be clear, these were NOT boot camps. In my case I was focused on database technology, but there were and still are, certificates for programming and web development. These were generally 1 -2 day classes offered through my state university. They cost anywhere from $300-$600. My employer was reimbursing me the cost of the classes.

They involved lectures on IT topics paired with in-class labs where we would “do the stuff” that was just discussed. By doing this, I took enough courses to earn a Database Technology certificate. I could be trained and gain experience on database technology rapidly in a single 1 or 2 day course.

Regular self study

Second, I would also typically spend 1.5-2 hours of self-study every night after work Mon-Thur. On Sat/Sun I would study usually 3-4 hours each day.

I did both of these approaches for the better part of two years. This all culminated in my first Microsoft Certification in May 2013. To be clear, the goal in all of this was to learn new skills. Later, I focused on the certification as a means of learning specific new skills and knowledge that would help me earn the certification. My thought was that, alongside my resume, the certification would help demonstrate knowledge and possibly help open a few doors early on in my career.

Because of the classes and the extra work after hours, I was able to handle more and more complicated scenarios at my job. That was, after all, the real goal.

  1. I worked on my interviewing skills.
Confident Interviewing
Photo by Sora Shimazaki from Pexels

Reading blog posts and watching videos about interviewing became a regular habit. I created a Word doc of questions I wanted to ask potential employers during an interview. The interview was as much for me as it was for the employer. Interviewing the potential employer to find out what their IT environment is like, what the IT team is like and what the company mindset is about certain things, are critical parts of you assessing the employer.

Now that I’ve been in IT officially since 2010 and officially a DBA since 2014,  here are the next steps I’m taking to advance my career right now.

5. I started blogging.

Once you have even a year or two of experience, blogging can be a great way to grow your career. If you’ve solved a few work problems in the 1-2 years you have been in your career, and I’m sure you have, then you have something to share with others.

Providing good technical information on how to solve a problem not only helps you remember and create a reference for you later, but forces you to learn more about the topic so that you can explain it to others. Thus, blogging is a great learning tool for you as well as an opportunity to get your name out into your technical community.

As you consistently post material, you can become known as the “go to” person for technology “x”. Then, when people want to know about technology “x” they find and read your blog posts and refer other people to your blog. This sort of activity can open doors for you professionally.

For example, Anthony Nocentino is well known for talking about Kubernetes. Brent Ozar and Erik Darling are synonymous with performance tuning. Hugo Kornelius is known for his instruction on SQL Server execution plans. Erin Stellato is known for many things, but most recently she has focused on the use of Query Store in SQL Server. SQL Server Statistics is strongly associated with Kimberley Tripp.

You can get started blogging for free using WordPress, or Weebly. There are probably other free options as well.

6. I started presenting.

public speaking advances your career
Photo by mentatdgt from Pexels

Back in July of 2020 I gave my first technical presentation outside the walls of my company. I am a co-organizer for a SQL Server Meetup. A speaker for one of our up-coming sessions had a conflict and had to cancel. There was about a week to find a speaker and so I decided that speaker would be me.

I put together a presentation called “Modern SQL Server Features That Make Life Better.” I presented it to my own user group and one other user group. Then I was accepted as a speaker to New Stars of Data. That event has opened up two more opportunities for me and I’m pursuing other opportunities through the features at Sessionize. They have a feature called “Discover Events” that allows you to see upcoming speaking opportunities.

Much like blogging, when presenting you have to learn something fairly well so that you can confidently present the information. Asa result, you build your reputation as an authority on your topic. Also, presenting gets your face and name in front of people in a way that even blogging doesn’t. When presenting, people can ask you questions in real time about your topic and get to know you a bit as they listen to you or watch you present. They get a real life sense of who you are. they also get a feel for your level of expertise and you can build a good reputation for yourself, all the while helping other people learn.

Next Steps To Take

  1. Figure out which of the 6 steps you need to take to move your career forward and do it.
  2. Leave me a comment on this post to share which step you’re going to take or message me on Twitter with your plan. Sharing your next step with others reinforces your commitment.

Error Messages 8114 or 22122 When Performing Change Tracking Cleanup

 

You’re a data professional and you’re trying to keep up with patching a wide range of SQL Server versions and editions. How do you know what’s in the CU and whether you should apply it or not? My favorite way to read up on CUs is to go to SQLServerUpdates and click around there.  It will take you to the latest CU pages as well as give you a way to see previous CUs that are available.

While doing this recently, I discovered this for CU 26 on SQL Server 2017.

Known Issues SQL Server 2017 CU 26

How to check If Change Tracking Is Enabled

SQL Server 2017 CU 26 has a known issue with change tracking. Before you apply that CU, be sure to check that you’re not using Change Tracking somewhere. Well, how would you do that if you have a larger environment and don’t immediately know if you’re using Change Tracking somewhere?

You could connect to your Central Management Server in SQL Server Management Studio and then run the below to return the SQL Server instance and database where the Change Tracking Feature is enabled. In this case, we don’t care what tables are involved in the Change Tracking, so there is no need to look at sys.change_tracking_tables. We only need to know that Change Tracking is enabled. This is enough to know that we shouldn’t update to SQL Server 2017 CU26 for that SQL instance.

SELECT db_name(database_id) AS DBname
FROM sys.change_tracking_databases;

You might see this:

Msg 8114, Level 16, State 1, Procedure sp_add_ct_history, Line LineNumber

Error converting data type numeric to int.

Or this error:

“DateTime spidNum     Error: 22122, Severity: 16, State: 1.

DateTime spidNum     Change Tracking autocleanup failed on side table of “table_name”. If the failure persists, use sp_flush_CT_internal_table_on_demand to clean up expired records from its side table.

The solution is to either stay on CU25 or lower, or go up to CU 27 for SQL Server 2017. Microsoft specifically says on this page that CU27 has a fix. You could also uninstall CU26 for SQL Server 2017. You might need to do this if you’re already on this version and can’t update to CU27 for some reason. To do this you can go to Control Panel > Programs and Features > View Installed updates

If you’re on SQL Server 2016 SP3 and experiencing this problem, there is a hotfix here listed as KB5006943.

 

 

Thoughts On Pass Data Community Summit 2021

 

Pass Data Community Summit was November 10-12 and was hosted and sponsored by RedGate along with other supporting sponsors like Microsoft and smaller companies like MinionWare. Having been to last year’s virtual Summit, I think this one was done much better.

Pre-conference Sessions

There were a big collection of pre-conference sessions. I attended “AWS for SQL Server Professionals” because my current employer is entirely in AWS. Erin Stellato had an all day session on Query Store that I attended as well. For a shorter, one hour version of this presentation, click here to go to a YouTube presentation by Erin at the Saint Charles SQL Server Meetup. I learned a lot in both all day sessions and I plan on attending pre-conference sessions next time as well.

Thoughts on Pass Data Community Summit
Thoughts on Pass Data Community Summit

Things I liked about Pass Data Community Summit:

  • The main three day Summit was free.
  • Pre-conference sessions on Monday and Tuesday were accessible due to the low price for the sessions. Sessions were $200.00. I like that the speakers were getting paid for their sessions. This might always be the case. I don’t know because this is the first time I had paid for and scheduled any pre-conference sessions. Some speakers donated the money to a charitable organization.
  • Spatial chat app: This app was intriguing because it would drop you into a virtual room that had the appearance of an actual room with tables, chairs, a water cooler, etc. For people reading this who have played the Sims, the virtual lobby was very Sim-like. The application displayed a circular icon with the attendees face in it and video/audio could be turned on and off. Much like a scenario in a real building, the further your circle was from other people the lower the volume. If you couldn’t hear someone well, or just saw someone across the room that you wanted to chat with, you could move across the room by dragging your circle over next to someone.  There were a number of rooms available in addition to the lobby.
  • The session catalog: The catalog had a number of ways to find content. You could filter by day, day/time, type of session and even search for content by a speaker’s name.
  • Brent Ozar’s keynote on Friday morning. He gave a great presentation on 5 ways that the cloud impacts your career as a data professional.

Things that could have been better about Pass Data Community Summit:

  • There was widespread confusion about the 30 minute times on the schedule Wed-Fri and the need to watch pre-recorded content. As a solution, perhaps when someone is building their schedule, have a pop up on the session catalog screen that clearly tells attendees that they need to watch the pre-recorded video before the Q&A session they are scheduling.
  • Pre-recorded content that had to be watched ahead of time meant I had to spend extra time watching presentations prior to Q&A sessions. My schedule didn’t allow for a lot of that and so I opted for mostly live sessions. This also necessitated a lot of schedule changes in the My Schedule page.
  • In the Q&A sessions I did attend, which I believe I made it to 3 or 4, the attendees didn’t come with questions in hand so getting a conversation going was often slow to achieve. Maybe next year there could be a mechanism provided that would allow questions to be submitted ahead of time. These questions would go to presenters prior to their Q&A sessions.

 

Next Steps to Take

  1. Watch the RedGate website for additional announcements about the next Summit. They already announced that next year’s Summit will be a hybrid event.
  2. Start presenting. You don’t have to be an expert to explain how you solved a recent problem. You just describe your problem, solutions you tried and what ended up working. Someone has either had that problem and couldn’t solve it, or they will like your solution better, or someone may offer an enhancement to your solution to make it even better. Who knows, maybe we’ll see you presenting at a Pass Data Community Summit in the future!

 

 

6 Steps to Build a Training Plan

 

As an athlete there is a certain amount of preparation that goes into being selected to be on a team, competing for a starting role and then being competitive with the opponent. For tennis players to be competitive and go to the finals in Wimbledon, it takes hours and hours of training, practice, and discipline to eat well before ever stepping on the court in a tournament. To make a basketball team, takes hours of practice by yourself on a basketball court and competing with others and your team mates. To make the starting line up takes hard work, outside of game day. A certain hunger and desire to grow and be more than you currently are is important as an athlete.


Photo by Markus Spiske from Pexels

In his retirement speech in 1993, Michael Jordan had this to say, “When I lose the sense of motivation and the sense to prove something as a basketball player, it’s time for me to move away from the game of basketball…. I went through all the stages of getting myself prepared for the next year, and the desire wasn’t there.”

Tom Brady is arguably the best quarterback of all time. He has accomplished more than basically every other quarterback, but he’s still hungry for more accomplishments and he still wants to do the necessary work that makes it possible to compete at his best.

If a successful athlete stops training outside of game day, their game eventually begins to slip. You can’t perform at a high level on the tennis court, basketball court, football field, ice rink, etc. , if you aren’t putting in the preparation time outside of game day.

So, what does this has to do with you as an IT worker? You can no more perform well on “game day” at your job without adequate and continuous preparation, than great athletes can play well if they stop doing the hard work of game prep.

Take a Personal Assessment

Let me ask you some  questions.

  1. Are you still hungry to learn technology?
  2. Are you putting in the work necessary in order to be the best technologist you can be ?
  3. When was the last time you spent time during the week to learn something new?
  4. Are you regularly talking about how you’re not good at certain things, but then also not putting in effort to improve?
  5. Do tasks regularly take you extra time at work compared to others in your same role?

If your answers to the above questions indicate that you might not be putting in the work necessary to stay current in your area of technology, then maybe you need to ask yourself why. Maybe, you’re coasting and you have been for awhile. Why? Maybe you’ve had a very rough time since the beginning of 2020 because of the pandemic. Ok. I completely get it. What are some things getting in the way of building a training plan that you can execute?

Burn Out will stop “Game Day Prep”

Burnout Prevents You From Building a Training Plan
Burnout – pexels-pixabay-42230

Maybe you’re burned out and need to recharge. That is very common. The last 18 months or so have been especially hard on most people. I usually see at least 5-7 tweets a week about this topic. I have a post about this as related to myself. Burnout is real and it’s hard to go through and hard to recover from. It takes time to deal with burnout and may likely involve finding others who will support you. Also, realize that no one should expect you to be 100% on your game every day, all the time. That’s just not reality.

Athletes have a lot of people around them to help make them successful. They have coaches, trainers, medical personnel , executives, family members and teammates to help keep them going. There is no reason to think that you also don’t need a support network. Possibly find yourself a therapist to talk to and find people to encourage you and help you.  Try switching up your routine so you can take care of yourself better physically and mentally. Right the ship, so to speak. Then, get back to prepping for “game day.”

 

Long Term Coasting Will Stop “Game Day Prep”

Perhaps you’ve recently been in a period of extra after hours learning because an important project required new skills. Maybe you were gearing up to pass a certification. Good for you. After that you took a break. Ok. That’s reasonable. Be cautious though about your “little break” from game day prep turning into long term coasting where you don’t put in additional effort to learn and grow. If 3, 6, 9 months or more goes by and you’re still coasting, ask your self why and what, you might want to do about that.

 

Other Priorities Will Stop “Game Day Prep”

Everyone has priorities, whether we are aware of them or not. We all make choices and those choices prioritize our time. If you’re spending endless hours in front of the TV, the XBox or the PS3, then you’re prioritizing that activity over others. Perhaps you like to host social events with friends and family over good barbecue and a game of Frisbee in the back yard. Possibly you’re taking care of a sick family member. Certainly no one will fault you for a little down time via entertaining yourself with games or a movie. I certainly hope you are making time for friends and honoring your commitment to your family. I would just encourage you to take stock of whether other, unfruitful priorities are keeping you from being the technologist you could be.

 

6 Steps to Build a Training Plan and Execute On It

  1. Open up your favorite text editor and make a list of the top 3 things you want or need to learn in the next 6 months. You likely know what they are.
  2. Do an internet search for links that explain those 3 topics and add those links to their corresponding topics in the text editor. Be sure to have at least 3 links per topic you want to learn.
  3. Save the file to your desktop so you see it every day as a reminder to work on your skills.
  4. If you have a whiteboard, write your plan on the whiteboard so you see it every day.
  5. Work your personal training plan by reading one article a day from your training plan.
  6. Now, rinse and repeat. Start the process over with those same three topics.

As a bonus, hopefully at least some of those articles have demos. Do the demos yourself on your own local install of SQL Server. Don’t just read the demos. After you’ve done these things, you will have read and worked through the content of 6 articles for each of your three topics that you wanted to learn. That’s a great start!

Next Steps to Take

  1. If you want to chat about any of this, hit me up on Twitter or post a comment here. I’m happy to discuss.
  2. Build a training plan using the steps above.
  3. If you’re struggling to find good resources, check out my post about how to find reliable resources for learning.
  4. Leave me a comment on this post to share your training plan, or message me on Twitter with your plan. Sharing your plan with others reinforces your commitment to the plan.