Category: Scripting

About Scripting

Subscribe Feeds

Using dbatools to improve your patching process

Posted on by marciolsf

One of the the key responsibilities of a production DBA is keeping SQL servers patched. You have to have a good process in place, however; it won’t do you any good to apply a patch that just broke your application.

sql server patching

How do I get started?

Generally speaking, you’re either running an in-house application, or a 3rd party one. 3rd party is relatively easy! You should always check with your vendor first to see what is their supported level. They might only specify a major version (2012, 2016, etc), or even a minimum Service Pack or CU level. Once you have that information, spin up a test server, patch it and test it. I like to give it plenty of baking time, at least a week, before I’m comfortable with the patch.

An in-house application takes a bit more work, but it should also (hopefully) have more eyes on it. You need to get that patch through every pre-prod environment before you can even begin to think of production. My workflow will usually follow the same path as the application itself:

Dev -> QA -> Stage ->Prod

I still take a week between each pre-prod environment, all the while communicating with the dev and QA teams so they can report back on any issues they notice. Ideally you also have metrics in place to help you spot drops in performance, where you can check against your per-environment baseline.

Taking inventory

Your first step is to figure out where things are at, and organize them in some fashion. I have lots of teams, each with their environments, and that’s a lot of servers! I’m a huge fan of SQL Server Central Management Servers and Registered Servers, they’re a must have if you have more than a few servers. I try to keep the organization simple, like this

central management server

Obviously, you can group this however you want! I’ve learned that a smaller number of folders makes it easier to find things quickly. It’s also important to keep your list up-to-date! It may seem like extra work, but it really pays off, particularly for the topic at hand.

dbatools to the rescue

Now that you have your servers organized, it’s time to collect everyone’s server levels. First we create a table to store all the information:

CREATE TABLE [dbo].[PatchLevels] (
    [SqlInstance] [NVARCHAR](MAX) NULL,
    [Build] [NVARCHAR](MAX) NULL,
    [NameLevel] [NVARCHAR](MAX) NULL,
    [SPLevel] [NVARCHAR](MAX) NULL,
    [CULevel] [NVARCHAR](MAX) NULL,
    [KBLevel] [NVARCHAR](MAX) NULL,
    [BuildLevel] [NVARCHAR](MAX) NULL,
    [SupportedUntil] [DATETIME2] NULL,
    [MatchType] [NVARCHAR](MAX) NULL,
    [Warning] [NVARCHAR](MAX) NULL,
    [Compliant] [BIT] NULL,
    [MinimumBuild] [NVARCHAR](MAX) NULL,
    [MaxBehind] [NVARCHAR](MAX) NULL,
    [SPTarget] [NVARCHAR](MAX) NULL,
    [CUTarget] [NVARCHAR](MAX) NULL,
    [BuildTarget] [NVARCHAR](MAX) NULL
)

Now to the good stuff! I absolutely love dbatools, and cannot express how grateful I am for the work they’ve done, it’s saved me many hours of work and frustration. I just run this script to update the PatchLevels table:

$datatable = get-dbaregserver -sqlinstance <your central management server> |test-dbabuild -maxbehind "2CU" 
Write-DbaDbTableData -SqlInstance <your DBA server> -InputObject $datatable -table dbo.PatchLevels -database DBA -AutoCreateTable -Truncate
  1. First we create a variable $DATATABLE to store the results of GET-DBAREGSERVER — this is your registered server list.
  2. Next up we pipe those servers through TEST-DBABUILD, which gives us our server’s build levels. -MAXBEHIND flags any servers older than 2 Cumulative Updates (CU) as not being compliant
  3. We then call on WRITE-DBADBTABLEDATA to save the values of our variable. It doesn’t apply a lot of smarts to the table creation, which is why the columns are all nvarchar(max), but you can definitely optimize the data types, and this will still work. This is the final results

This is the final result for the table

Look ma, no RDP

This is one of the things I still get giddy about — the actual patching without needing to login to each server. Like everything in life, there are a few caveats:

  • We keep all our installation media and patches on a network share, organized by major version, so we need to run a script for each major version I support. I make that easier by just using a variable.
  • The UPDATE-DBAINSTANCE command doesn’t know how to use your logged in credentials, so you have to prompt the user for them.

And here’s the actual script

#create a list of servers that you want to patch. Change the group name to match your target
$ServerList = get-dbaregserver -sqlinstance mssqlmgmt -Group QA
 
#create a variable to pass in to the Update-DbaInstance command; this will prompt you for your password
$cred = Get-Credential 
 
#Set the version that you want to update to
$version = '2017'
$UpdatePath = '\\<some server>\SQL\'+$($version)+'\updates'


#Start Patching! The -Restart option will allow it to restart the SQL Server as needed
Update-DbaInstance -ComputerName $ServerList -Path $UpdatePath -Credential $cred -Version $version -Restart

And that’s it! Like, really, that’s it. Just this last weekend, I updated 17 servers across 2014, 2016 and 2017 in about 30 mins, most of which I spent playing videogames while I waited for the patches to complete installing. The installs are not serialized, they’re run in parallel, so that’s part of the magic.

Once the scripts are completed, I just run the script to update the patch levels table. Now it’s time to let the proper teams know that updates are completed.

Thanks for reading! Here’s a few more links for your enjoyment:

dbatools website

SQL Server builds – wonderful list of every SQL server version and build numbers, along with links for their respective updates

Other things I’ve written

Get the size of all your objects

Posted on by marciolsf

I try to stay in touch with the internals of my databases, including the size of things. Growth patterns are a good indicator of where most of your database activity lies, and that in turn can help you locate activity hot-spots. For today’s post, I have a simple script will help you get all your object sizes, including tables, heaps and indexes.

As an added bonus, it’ll also give you suggestions of which indexes are just giving you maintenance overhead. Every DML also updates the indexes, but you have the potential for index overhead if you have more updates than reads. To identify such candidates, I use the formula below:

( user_seeks + user_scans + user_lookups )  / (user_updates)

The idea here is simple: if the combined seeks, scans and lookups add up to more than the updates, then the index is probably good! If you’re having more updates than reads, then the index is probably just baggage, and you should consider tossing it overboard.

That’s a lot of baggage!

There’s a few caveats to consider, however

  • Index stats are not preserved during service restarts.
  • Index stats can also be manually flushed.
  • It is very possible that an index is used for just one report, which only runs once a year! And of course, this one index shaves off several hours of report time execution.
  • sys.dm_db_index_usage_stats only holds information for indexes that have been used since the last service start up.
  • sys.dm_db_index_usage_stats does not return information about memory-optimized indexes (see here).

And here’s the code itself


SELECT 
    ds.name AS filegroupname,
    df.name AS filename,
    df.physical_name,
    SCHEMA_NAME(o.schema_id) AS SCHEMAName,
    OBJECT_NAME(ps.object_id) AS objname,
    i.name indexname,
    o.create_date,
    i.index_id,
    ps.row_count,
    SUM(ps.used_page_count) / 128 AS used_mb,
    SUM(ps.reserved_page_count) / 128 AS reserved_mb,
    SUM(ps.in_row_data_page_count) / 128 In_row_mb,
    SUM(ps.in_row_used_page_count) / 128 AS row_used,
    SUM(ps.in_row_reserved_page_count) / 128 AS row_reserved,
    SUM(ps.lob_used_page_count) / 128 AS lob_mb,
    SUM(ps.lob_reserved_page_count) / 128 AS lob_reserved,
    us.user_updates AS [Total Writes],
    us.user_seeks + us.user_scans + us.user_lookups AS [Total Reads],
    case
		when user_updates > 0 then
		Convert(decimal(6,2),( user_seeks + user_scans + user_lookups )  / (user_updates* 1.0)) 
	else 
		Convert(decimal(6,2),( user_seeks + user_scans + user_lookups ) )
	end as ReadToWriteRatio
FROM sys.dm_db_partition_stats ps
    left JOIN sys.objects o
        ON o.object_id = ps.object_id
    left JOIN sys.indexes i
        ON o.object_id = i.object_id
    left JOIN sys.data_spaces ds
        ON ds.data_space_id = i.data_space_id
    left JOIN sys.database_files df
        ON df.data_space_id = ds.data_space_id
           AND df.data_space_id = i.data_space_id
    left JOIN sys.dm_db_index_usage_stats us
        ON us.object_id = i.object_id
           AND us.index_id = i.index_id
GROUP BY 
SCHEMA_NAME(o.schema_id),
         OBJECT_NAME(ps.object_id),
         us.user_seeks + us.user_scans + us.user_lookups,
         us.user_updates - (us.user_seeks + us.user_scans + us.user_lookups),
         ds.name,
         df.name,
         df.physical_name,
         i.name,
         o.create_date,
         i.index_id,
         ps.row_count,
         us.user_updates
ORDER BY objname,
         filegroupname,
         i.name;

For a fresh restore of a StackOverflow database (thanks Brent!) this is what the output will look like. You can save the results into a table (along with a time-stamp) so you can review your usage trends overtime.

Other links!

sys.dm_db_index_physical_stats – get fragmentation values!

Where in the disk are my indexes? – similar to this, but without the usage values

Add a SQL subscription with just a script

Posted on by marciolsf

SQL Replication is one of my favorite things about SQL Server. For the most part, and as long as it’s properly maintained, it’s rock solid! It’ll just churn along without too much fuss. In this post, we’ll show you how to add a subscription with just a script. Why? There’s two main reasons:

  • The default UI for adding a subscription is too screen-y! There’s too many steps for something that is relatively simple.
  • If you’re sending data to a Always-On subscriber, you can’t even use they UI! That’s because the wizard will perform some validation on the host name. Since your AG listener is different from your nodes, the listener name will fail the validation since it isn’t a “true” host.
You’re REALLY not going to pass

So the actual solution here is to use a script! It always surprises me that SSMS doesn’t offer an out-of-box option to generate this script, like it does in so many options. Lucky for you, I have just the script you need, right here:

--RUN THIS FROM YOUR PUBLISHER DATABASE!
--Not from the distribution or subscriber database
USE [Publishing database]

DECLARE @DBName sysname
DECLARE @PubName sysname
DECLARE @SubDB sysname
DECLARE @Subscriber sysname


SELECT @DBName	= DB_NAME() --current server
DECLARE @pubName2 sysname = 'YourPubName' --publication name
SELECT @PubName = COALESCE(@PubName, @pubName2)

SELECT @SubDB	= 'YourSubscriptionDatabase' --subscription database
SELECT @Subscriber = 'YourSubscriptionServer' --server name


IF (SELECT COUNT(name) FROM dbo.syspublications) =1
	SELECT @PubName = name FROM dbo.syspublications
ELSE
IF @pubName2 IS null
BEGIN
	PRINT 'This database has multiple pubs! You need to set the variable of @pubname2 above!'
	RETURN
END	

IF (DB_NAME() <> @DBName)
BEGIN
	PRINT 'you are in the wrong database!'
	RETURN
end

DECLARE @error VARCHAR(100)

SELECT @error = 'The publication ' + COALESCE(@PubName,@pubName2) + ' does not exist!'

IF NOT EXISTS (
SELECT 1 FROM dbo.syspublications WHERE name = @PubName)
BEGIN
	PRINT @error
	RETURN
end
--No more validation, this is the real thing!
--First we add the subscription
EXEC sp_addsubscription @publication = @PubName,   
       @subscriber = @Subscriber,   
       @destination_db = @SubDB,   
       @subscription_type = N'Push',   
       @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0;  

--This is for push subscriptions only!
EXEC sp_addpushsubscription_agent @publication = @pubname,   
       @subscriber = @Subscriber,   
       @subscriber_db = @SubDB,   
       @job_login = 'LogReaderServiceAccount', @job_password = 'LogReaderServiceAccountPassword'
	   , @subscriber_security_mode = 1;  
GO

The script starts with a bunch of data validations: This is just to ensure that you’re in the correct database, and that the pub exists. Once we complete the validation, we add the subscription to the proper system tables, and then we create the actual SQL Agent job that pushes the data to that particular subscriber. This script is meant only for push subscriptions,  where the jobs run from a centralized replication server. This differs from a pull subscription, where the pull jobs run from the subscribers. Neither approach is “better”, it just really depends on your environment.

Documentation!

sp_addsubscription

sp_addpushsubscription

Other scripts!

Create a backup report

Create a SQL Backup Report

Posted on by marciolsf

One of the most important duties of a production DBA is to ensure backups are happening regularly. No one wants to face a manager and tell him/her “I’m sorry, but the most recent backup I have is from a week ago”. Create a SQL backup report, so you can review your situation!

My production environment is rather large, so it would be easy to rely on backup failure alerts. Don’t fall into that trap!

You are trusting that both your backup system, your notification system AND your email system are all in working order, and that’s a lot of trust. I much prefer being proactive, which includes verifying manually (as often as i can afford) that my backups ran.

To address that need, I wrote the following script. Once a week, I run it against a Registered Server list (you have one of those, right?). After a few seconds, you will have a complete list with the last time each database was backed up, as well as backup type, and age of the backup.

 SELECT DISTINCT DatabaseNames.NAME                                 AS NAME,
                Max(bs.backup_start_date)                          AS
                [Latest Backup],
                Datediff(dd, Max(bs.backup_start_date), Getdate()) AS
                [Backup Age],
                CASE
                  WHEN type = 'D' THEN 'Full'
                  WHEN type = 'I' THEN 'Differential'
                  WHEN type = 'L' THEN 'Log'
                  ELSE 'Some other type'
                END                                                AS
                Backup_Type,
                CASE
                  WHEN Max(bs.backup_start_date) IS NULL THEN 'NEEDS BACKUP'
                  WHEN Datediff(dd, Max(bs.backup_start_date), Getdate()) > 1
                THEN
                  'NOT UP TO DATE'
                  WHEN Datediff(dd, Max(bs.backup_start_date), Getdate()) = 1
                THEN
                  'DATABASE BACKED UP'
                  WHEN Datediff(dd, Max(bs.backup_start_date), Getdate()) = 0
                THEN
                  'DATABASE BACKED UP'
                  ELSE 'PLEASE REVIEW MANUALLY'
                END                                                AS Status
FROM   sys.databases AS DatabaseNames
       LEFT OUTER JOIN msdb.dbo.backupset AS bs
                    ON bs.database_name = DatabaseNames.NAME
WHERE  databasenames.NAME NOT IN ( 'tempdb', 'reportservertempdb' )
GROUP  BY DatabaseNames.NAME,
          type
HAVING Datediff(dd, Max(bs.backup_start_date), Getdate()) > 0
--more than 1 day since last backup
ORDER  BY NAME,
          backup_type
Look at all those backups!

From here, there’s a couple of directions you could go. You could save the output somewhere, if that’s part of your audit requirements! Or you could automate this report to yourself, as long as you have enough discipline to still review it… I find that emails are easy to ignore when you get > 100 emails/day, so if that’s you, I would keep on running it manually.

Useful Links!

Backup Documentation

BackupSet documentation

Other Scripts!

Document your IP

Get Session Progress

Script – Remove old AD logins from SQL Server

Posted on by marciolsf

SQL Server Security should be in the top of your priority list. In the perfect world, once a user has left the company, you’d receive a notification to remove the login from your SQL server. Unfortunately, that doesn’t happen often enough. Too many stale users can increase your security footprint and make SQL server security harder to manage. Also ideally, you’d be manage your security via groups, instead of users. That way, once an AD account is disabled/dropped, then the group is automatically updated.

I’m big on being proactive and keeping my servers clean and secure, so I wrote the following script. This script builds a list of AD logins, check their status, and optionally drops them. If ActiveDirectory returns any kind of errors, then it drops the login. If you feel like this is a bit too drastic, then you can comment out line 25! Then all the script does is print out the list of candidates. Once you have that list, you can verify the candidates before you actually pull the trigger on them.


USE master

DECLARE @users TABLE ( pk INT IDENTITY(1,1), 
username sysname)

INSERT INTO @users ([username])
SELECT name FROM sys.server_principals 
WHERE type_desc = 'windows_login'

DECLARE @username sysname
DECLARE @i INT = 1

WHILE @i <= (SELECT MAX(pk) FROM @users) 
begin 
SELECT @username = username 
FROM @users WHERE pk = @i 

BEGIN try 
EXEC xp_logininfo @username 
END TRY 

BEGIN CATCH 
IF ERROR_NUMBER() >0
PRINT @username
EXEC ('DROP login ['+@username+']')
PRINT ERROR_NUMBER()
PRINT ERROR_MESSAGE()

END CATCH

SELECT @i = @i + 1
end

 

 

Another thing to keep in mind is that this script does not remove the users from the databases, so the permissions in the database itself are preserved. This presents a bit of a fork on the road, and the direction you go depends on your security policies (or paranoia)

  • If your requirement is to just totally clean up old users, then you should loop through each database and remove the respective users from there as well.
  • If there’s a chance the login should not be removed (maybe an old service account?), then preserving the user within the database would preserve the permissions. That way, if you have to add the user back, most of the database-level permissions are still there.

Useful links:

XP_loginInfo documentation: Link

Previous scripts:

Remove multiple sessions!

Where are my indexes?

Terminate user sessions for fun and profit

Posted on by marciolsf

This is a bit of a drastic script: every now and then I’ll have a misbehaving user or application that will create way too many sessions, causing all sorts of issues. I could kill the sessions one at a time, but that’s such a drag! So let’s terminate those user sessions!

Even inactive sessions can be an issue: For every open session, SQL needs to allocate a bit of RAM and CPU to maintain that connection. If you have a ton of sessions that are just idle, then you could be wasting memory that other sessions could use. This is basically how DDOS attacks work: Overwhelm the server with connections until you run out of resources.

That’s why I created the script below — you give it a user name, and it kills every session associated with the login. You can even filter it down to just one database! Or not, that’s totally your call. All I can tell you is that it does what the name says: it slays all, and that’s just metal.

DECLARE @spids TABLE (pk INT IDENTITY(1,1), spid SMALLINT)
DECLARE @i INT = 1

insert INTO @spids(spid)
SELECT s.spid FROM sys.sysprocesses s
INNER JOIN sys.server_principals p ON s.sid = p.sid
WHERE p.name = 'username' --kill all logins for that one user
--and DB_NAME(dbid) IN ('dbname') --kill all users in that one database

DECLARE @sql VARCHAR(1000)
WHILE @i&amp;amp;amp;amp;lt;=(SELECT MAX(pk) FROM @spids)
BEGIN
SELECT @sql = 'kill '+ CAST(spid AS VARCHAR(100)) FROM @spids WHERE pk = @i
PRINT @sql
EXEC (@sql)
SELECT @i = @i + 1
end

Helpful links

Kill documentation – here

Other Scripts!

Get session progress

Script – Document your IP addresses

Posted on by marciolsf

You should always keep detailed documentation on each of your servers as part of your DR scenario.This document will become your holy grail in case you ever have to recover your servers.

True story: Our BI team had 3 servers, and one of them was 2 weeks away from being retired. Suddenly, in the middle of the afternoon, the server went off-line. I contact our infrastructure team, and ask them to take a peek into the server itself, in the server room, and tell me what happen. After 10 harrowing minutes later, they return and deliver the grisly news: the motherboard had fried, and the box was dead. Lucky for me I had current backups of all the databases, but not of the replication Publications. Those can be rebuilt, sure, but it would have saved me a bunch of time if I had those pubs scripted out.

Again, your documentation should be thorough. Here’s a few things that should be part of your documentation:

  • What are my driver letters?
  • How much disk space do I have?
  • Who are all my local admins?
  • What are my server IPs?
  • If using SQL Replication, save your publication scripts
  • etc….

Sure, you can have a lot of this information from database backups. But always hope for the best, and plan for the worst!

For this post, this is the script we use to document the IP addresses for our server farm:

SELECT
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
CONNECTIONPROPERTY('client_net_address') AS client_net_address

In future posts, we’ll go into more details on our DR documentation strategy.

Other Scripts!

Remove old logins

Get session progress

Script: Where in the disk are my indexes?

Posted on by marciolsf

Index maintenance is one of those things that you have to stay on top of, in order to keep your data flowing smoothly. In no particular order:

  • Index defrags/rebuilds
  • Removing unused indexes
  • Optimizing existing indexes
  • Saving index definitions (ever had someone complain an index “went missing?”)
  • Move them to a separate filegroup/disk
  • Etc…

A few months back, we were having issues with heavy I/Os, so one of our plans was to move all the indexes into a separate filegroup, located in a different disk. This way, we’d end up with separate threads: One for the update itself, and one for the index update. The snag was that, overtime, new indexes were added to the original data filegroup, so now they were all over the place.

In an attempt to figure out the lay of the land, I wrote the following query. This script will tell you the filegroup an index lives in, which will help me identify what needs to be moved where. It does not include heaps (which are not indexes) and system indexes (because you should probably leave those alone).

SELECT ds.name AS FileGroupName,
       s.name AS schemaname,
       o.name AS TableName,
       i.name AS indexname
FROM sys.objects o
    INNER JOIN sys.indexes i
        ON o.object_id = i.object_id
    INNER JOIN sys.data_spaces ds
        ON ds.data_space_id = i.data_space_id
    INNER JOIN sys.schemas s
        ON o.schema_id = s.schema_id
WHERE o.is_ms_shipped = 0
      AND i.index_id > 1
ORDER BY ds.name,
         o.name,
         i.index_id;

Once I had them identified, it was easy to write some dynamic sql to move rebuild them into a new location. The syntax is easy:

CREATE INDEX [indexname]
ON table
WITH (ONLINE = ON, DROP_EXISTING = ON)
ON [filegroupname];

You have to write out the entire index definition as it currently stands, but the Drop_existing = ON will instruct SQL to recreate the index in the specified filegroup, instead of just complaining that the index already exists. Online = ON (if you’re on Enterprise edition) will allow you to run this without dropping the index first, or additional blocking.

Mini-script – Get Session Progress

Posted on by marciolsf

Don’t you wish to know how far along that backup/restore has come? If you’re running it manually, and you remembered to add the STATS parameter, you can see it in the default output, but what if it’s coming from an enterprise tool, like Commvault?

It would also be great to get the progress on other operations, like shrinks, DBCC CheckDB, etc. While not every operation will report its progress, this little script will report progress on whatever operations do provide it.

SELECT  r.session_id 
         ,DB_NAME(r.database_id) 
         ,r.status 
         ,r.command 
         ,r.wait_type 
         ,r.percent_complete 
         ,CASE WHEN estimated_completion_time < 36000000 THEN '0'
              ELSE ''
         END 
         + RTRIM(r.estimated_completion_time / 1000 / 3600) + ':'
         + RIGHT('0' + RTRIM(( r.estimated_completion_time / 1000 ) % 3600 / 60),2) 
         + ':' + RIGHT('0' + RTRIM(( r.estimated_completion_time / 1000 ) % 60), 2) 
         AS TimeRemaining 
         ,GETDATE() + ( CASE WHEN estimated_completion_time < 36000000 THEN '0'
                            ELSE ''
                       END 
                       + RTRIM(r.estimated_completion_time / 1000 / 3600)
                       + ':' + RIGHT('0' + RTRIM(( r.estimated_completion_time / 1000 ) % 3600 / 60), 2)
                       + ':' + RIGHT('0' + RTRIM(( r.estimated_completion_time / 1000 ) % 60), 2) ) 
         AS EstimatedCompletionTime
FROM    sys.dm_exec_requests r
WHERE   r.percent_complete > 0