Monday, March 5, 2018

Getting Replication Snapshot Delivery Times

Getting Replication Snapshot Delivery Times

We use transactional replication quite heavily in our environment. We have multiple publishers, distributors and subscribers. Currently we have three subscribers overseas, one in London and two in Sydney. When we were setting up the second subscriber in Sydney I was asked how long it was going to take. My answer was, "More than an hour but less than two days". Really I had no idea, we had only ever reinitialized a publication or two out to Sydney but never tried to setup all sixty four international subscriptions and initialize them all at once. I started around Midnight on Friday night setting up replication (from scripts of course, you never want to use the GUI for a go live event) and it finished around 10 pm Saturday night. I decided I wanted to know which publications took the longest.
Normally I figure out the times by opening up Replication monitor, expanding out the publication, right clicking on the subscription and selecting "View Details". Then I look for the following lines.

First I look for the start time:

Then I look for the completion event:

I grab the date and times from these two events and using a little math (or excel) and I can figure out how long the snapshots took to deliver. 

So, I opened a new excel sheet, created a row for the first publication and started logging the data. I looked down the page and realized I did not want to do this process sixty three more times. I figured the data in replication monitor had to be stored somewhere on one of the servers involved and there must be a way to query it. I assumed (correctly) that I could get the information from the distributor and after poking around I found the tables I needed to use. Below is the completed query 

USE distribution_Research
DECLARE @mysub sysname,
    @mystart DATETIME
-- Variables - change these you will
SET @mysub = 'SubscriberDB-PRD'
SET @mystart = '2018-02-24 00:00:00.000'
-- Query - touch these you will not
    msh.time AS endtime,
    CAST((msh.time - msh.start_time) AS TIMEAS duration,
FROM dbo.MSdistribution_history AS msh
JOIN dbo.MSsubscriptions AS ms
    ON ms.agent_id = msh.agent_id
JOIN master.sys.servers AS pub
    ON ms.publisher_id = pub.server_id
JOIN master.sys.servers AS sub
    ON ms.subscriber_id = sub.server_id
JOIN dbo.MSpublications AS mp
    ON ms.publication_id = mp.publication_id
AND = @mysub
--AND ms.subscriber_db = 'Returns'
AND msh.start_time > @mystart
AND (msh.comments LIKE 'Delivered%')
ORDER BY mp.publication, msh.start_time DESC

Using this I was able to get the the times for all the publications at once and throw them into excel. I combined the data with some row counts and tables sizes and was able to make some pretty nice reports to show my boss. Hopefully this will help someone else do the same.

Friday, February 16, 2018

Getting Started with Dbatools

Getting Started with Dbatools

When I started using PowerShell with SQL Server I used a module from CodePlex named SQLPSX. The module provided quite a few functions that made working with SQL server easier. But today Codeplex is gone and I don't think anyone has updated SQLPSX in years. You can still get it on github but some of the folders have update dates like "3 years ago". Thank goodness for DBATools ( I attended a pre-conferece session this year at SQL Pass Summit that was run by three people on  the team and their module does everything that I was using SQLPSX for and more. Click the link above and go to the download page for installation instructions then use this page a quick primer on some of the things you can use it for.

Getting Data In and Out of SQL

One of the things I use PowerShell for is to gather information on all my database servers and store that data in my central DBAAdmin database. Dbatools has a function, invoke-DbaSqlQuery, that can both read and write to the database. SQLPSX used two different functions to do this. In this first example I am going to use the function to get a list of all servers from my server table
import-module dbatools
$server 'DBAAdminDB-PRD'
$db 'DBAAdmin'
$qry 'select Server_name from dbo.vw_ServerListing'
invoke-DbaSqlQuery -sqlinstance $server -database $db -query $qry
Very easy, no need to load .NET assemlies or create objects like you do when using SMO. The first line imports the module, then I create some variables and finally execute the statement. I could have done the whole thing in 2 lines by not using any variables.
Next lets use the function to insert some data into a SQL Server table. Suppose my server table only has three columns:  Server_name, Server_Env and Description
import-module dbatools
$server 'DBAAdminDB-PRD'
$db 'DBAAdmin'
$qry 'insert into dbo.server (Server_Name, Server_Env, Description) 
         VALUES ('SERVER1','PRD','Mission Critical Server')' 
invoke-DbaSqlQuery -sqlinstance $server -database $db -query $qry
Again very simple. Now lets look at some more interesting things you can do with the module.

Getting Information about your Server and Databases

Here are a few commands you can use to get information about your SQL Server and it's Databases
import-module dbatools
get-dbasqlinstanceproperty -SqlInstance TESTSERVER |select-object Name, Value
This command lists instance level information such as build number, edition, etc. You can see the output here

Now lets get some information on the databases on the server
import-module dbatools
Get-DbaDatabase -SqlInstance TESTSERVER
Here is the output

And finally lets get some information about the datafiles of one of the databases.
import-module dbatools
Get-DbaDatabaseFile -SqlInstance TESTSERVER -Database admin

These are just some very basic things you can do with Dbatools. There are over 300 commands in the module that you can use for gathering information, backing up SQL, or even for server migration. PowerShell is a very powerful language and is getting used more and more by SQL and Windows Admins. Using modules like dbatools is a great way to start using PowerShell easily and effectively