Lets get all Posh! Log Shipping

Standard

This month’s T-SQL Tuesday is brought to us by my good friend Rob Sewell (b | t) and the subject is “Let’s get all Posh – What are you going to automate today?

My post will be about log shipping because setting up log shipping using the GUI is a very tedious task and doing it with TSQL scripting is no fun either. So let’ see how we can do this with PowerShell.

 

 

Log shipping explained

If you’ve been a SQL Server DBA for a while you probably heard about log shipping.

Log shipping makes it possible to increase the database’s availability by automatically backing up the the transaction log, copying the backup file and restoring the backup file to another database instance (or the same instance with a different database name).

Log shipping is an older technology but still has a purpose in lots of environments where it’s being used for reporting, ETL or to give developers and users access to production without the risk of them breaking anything.

Why use PowerShell

If you’ve ever had to setup log shipping in the SQL Server Management Studio (SSMS) than you know how tedious it can be. I actually counted the amount of clicks to setup a single database for log shipping and you need about 40 of them to get the job done.

So one day we decided to create a warm standby for one of our production servers. I had setup log shipping before and if you know what to do it’s not that difficult.
It becomes a problem when you have to setup log shipping for more than 20 databases ranging from 10 GB to 800 GB in size.
I ended up spending about a week setting it up (between other tasks I do have more work to do) and for me that’s way too much time for something as simple as this.

So why use PowerShell, because anything I can do with the SSMS can be done with the SMO. This would save a lot of time for me and anyone using the the functionality.

How did I do it

I contacted Chrissy LeMaire who is the original brainchild of the dbatools PowerShell module and she was instantly excited to have this functionality within the module.

The first thing I had to do was to find out how SQL Server in the background setup the log shipping when I used the GUI. Yeah I know using the GUI is bad when you use a lot of PowerShell but sometimes I like to do it old school.

I quickly found out that SQL Server does a lot of things to set up log shipping. It creates jobs, job steps, job schedules, uses stored procedures to set values in specific tables etc etc.
I went to the dbatools module and used the Find-DbaCommand function to see if there was any functionality to create the agent objects (jobs, steps and schedules) but they weren’t there so the first step was to create a full set of functions to manage those objects.
A couple of thousand lines later I finished the agent functionality and could continue to developing the log shipping function.

That function started pretty small, but like the agent functionality, quickly grew to be some sort of monster with over 70 different parameters that would support every kind of situation.

After about a couple of hundred iterations it was finally committed to the development branch of the dbatools module to get tested. I have great respect for the people who did the testing of that functionality because I didn’t envy them. I knew how complicated it was to build everything and to test all that must have been a lot of work. Thank you to all the testers that accepted the challenge.

The solution

The entire log shipping functionality is now separated between 5 functions. Four of them are used internally and are not visible as a public function because you can easily break stuff if it’s not being used correctly.

The main function, Invoke-DbaLogShipping, is available in the dbatools module for anyone to use.

If you open the GUI for the log shipping you have lots of choices but most of them are already supplied in the GUI itself and you can decide whether to use them or not.
The whole idea behind the functionality was that yit would allow you to quickly setup log shipping using a lot of defaults like you can in the GUI, but if you’re more experienced you can change any setting to your preferences.

it will take you about a minute to set all the parameters and after that the only action that will slow you down is the size of you database files when you use a restore.

Enough talk I want to see some action!

I will demonstrate how to setup log shipping using the least amount of parameters. You actually need six parameters to make it work:

  1. SourceSqlInstance – Which instance need to be used as the source server)
  2. DestinationSqlInstance – Which server needs to be used to log ship the database too
  3. BackupNetworkPath – Where do the backup need to be placed
  4. Database – Which database, or databases, needs to be log shipped
  5. GenerateFullBackup or UseExistingFullBackup – Generate a full backup or use an existing one

The command below will log ship a database from server1 to server2.
It will log ship one database called db1.
The network path for the backups is \\server1\logshipping\backup.
The backups will be created with backup compression.
No new backup will be created, instead it will use the last full backup
In this example the suffix for the database is “LS” because I want to differentiate the log shipped databases.

Below is the result of the log shipping.

What else can be done with this function?

When I developed this function it had a lot of iterations. I’m a developer that will have a good idea what to build and during the process will think of other functionality that needs to be put in.
I had a lot of iterations where I constantly thought of new ideas that would make other people’s work easier.

A couple of examples:

  • To monitor or not to monitor log shipping
  • Enabled or disabled log shipping jobs
  • Naming convention of the jobs
  • Standby or read-only mode for the secondary database
  • To compress or not to compress the backup files
  • Generate a full backup for initialization
  • Log ship multiple databases in one command. This will be executed in serial.
  • Force every setting and discard anything that’s there

The last one needs a little more explanation. I want this function to work really easy and with existing settings I want it to work. The -Force parameter is a really powerful parameter which will assume a lot of the defaults but will also overwrite existing settings like jobs, schedules, database settings etc.

Conclusion

I will never ever use the GUI again to setup log shipping again and I hope you will too. It’s just way too much work and I’ve had it fail on my lots of times not knowing what went wrong having to set everything up from scratch.

There is more functionality coming for the log shipping functions like status checks and fail-over initialization.

If you find that something is missing, or works inefficient or doesn’t work (I hope not), in this function please let us know. You can find us on Slack, GitHub, Twitter, YouTube, LinkedIn. You can find me on on the Slack channel most of the time. If you have any questions you know where to find me.

The last thing I want to say is that dbatools module is a community driven module that enables database administrators to work more efficient. It’s because of the hard work of all the volunteers that this module is such a success.
It’s a group of very hard working and smart people who I have great respect for. It is because of the enthusiasm of all those people, and especially Chrissy, that I love working on the module.

Thank you again Rob for hosting this month’s TSQL Tuesday, it was really fun.

Testing Log Shipping with PowerShell and Pester

Standard

Thanks to my good friend Rob Sewell (b | t) I got into testing with PowerShell with Pester.
He showed me how to use the Pester module to test about anything you can test with PowerShell.

In my environment I have lots of databases being log shipped. To see the current status of the log shipping I either have to execute a procedure or execute a standard report from SSMS. And I know there are other solutions but in most cases it comes back to these two.

The procedure returns all the databases, the primary and the secondary, with the values for last backup time, copy time, restore time and the thresholds. This doesn’t show me the problems right away but it’s fast.
The report shows me in nice green and red colors which which is good and what’s not but I’d have to do it for every server manually. So it’s pretty but it’s slow.

Why isn’t there anything that has the best of both worlds where I have the speed of the query with the clear results of the report?

That’s where Pester comes in!

I can use Pester to test the values that come back from the procedure and do basically the same thing the report does.

The procedure returns all the databases, the primary and the secondary, with the values for last backup time, copy time, restore time and the thresholds.
There is also an overall value called “status” which shows a 0 when everything is fine and a 1 when something is wrong.

I wanted to keep the check as simple as possible. If everything is fine, I don’t need to go into much detail. If there is something wrong I want to know which part of the log shipping failed, like the backup, copy or restore.

The Overall Test

This test only looks at the the marker the log shipping sets to indicate if everything is OK or not.

Log Shipping Pester Overall

Ooh oh! Something failed. Good thing we have our test.

The Detailed Test

The detailed test goes into the detail of the last backup, last copy and last restore time. We want to know what went wrong and where.

In this case I only want to zoom in on the database with the failed test so I use the -Database parameter to give the name of the database.

Log Shipping Pester Detailed Single DB

Of course it’s possible to execute the detailed test for all the databases. Just remove the database parameter and the same tests are run for all the databases.

Log Shipping Pester Detailed All DB

The entire test took less than two seconds and made it possible for me to quickly see if everything is fine with the log shipping.

The code can be downloaded from github. I hope this helps you out and make your life a little easier as it did for me.

 

 

T-SQL Tuesday: The times they are a-changing

Standard

sql tuesdayThis months T-SQL Tuesday is is inspired by the blog post Will the Cloud Eat My DBA Job? by Kendra Little. Technology has changed a lot in the past years, especially with cloud/globalization/automation. What an impact has this had on your job? Do you feel endangered?

Over the years I’ve seen a lot of things change with SQL Server. I can remember that somebody told me when I started with SQL Server 200 that T-SQL was never going to be a big thing. How wrong was that guy right?!

I personally have not yet had the chance to do a lot with the cloud like Azure SQL Database.I did get the change to fiddle around with a trial period of Azure to see what I could do and to get a feel for the interface. Unfortunately this was just a trial and after it ended I didn’t pick it up again. I should because I like to learn new skills.

What an impact has this had on your job?

Some things have had a great impact on my work. Take PowerShell for instance. If I didn’t start with that when it first came out I would still be clicking around SSMS like crazy. I like to automate everything that’s repetitive because I like to spend my time efficiently doing stuff that gives me energy and not do things over and over again.

At this moment cloud has not an impact on me. Employers in the past did not see any benefit of it at that moment and my current employer does not yet see use of it either. Maybe something will change in the near future but for now it’s not going to happen 🙁
I would really love to start working with the cloud and move some of our databases because I think it is a valuable addition to traditional architectures.

Do you feel endangered?

No! And I’ll tell you why.

When I first started to work with servers and databases you had a physical server where you had to put in a CD or DVD and run the install from a console to get Windows Server installed. As soon as everything was set up you could remotely log in and do the rest of the work from the office.

The server room would look a little like this:

Servers in serverroom

Nowadays you, or your system administrator either logs in to VMWare or Hyper V. He or she creates a new server, and if they work smart, it’s done with cloning or scripting and the server is done within minutes.
The last time I physically touched a server to do database administrator work was about 8 years ago. If you have the same situation you kind of work in a cloud-like environment.

The second reason I don’t feel endangered, is because as a DBA I have to deal with everything within and around SQL Server. Bluntly said a VM for me is nothing more than a box of resources where my instances do their work. I know people will disagree with me but if you’re not the system administrator the underlying hardware layer is invisible to you.

If your employer is a company with one or more database servers there will always be a need for a DBA. There will be performance issues, new installations, high availability, reporting etc etc.

Or do you have more exciting features/toys to work with?

In the last couple of years so much has changed in SQL Server that it’s impossible to comprehend everything.

I’m excited to work with SQL Server vNext on Linux for example. There are a lot of new features for Business Intelligence with Analysis Services and Power BI.

You can create stretched databases where parts of the database are in the cloud and others are on premise. Imagine a hybrid environment where parts of the network are locally/on premise and other parts are deployed in the cloud.

Microsoft has embraced PowerShell for SQL Server and has now made it open-source. How cool is that! There are more and more people developing in PowerShell and creating modules like dbatools.

There is so much new stuff that I have to cherry pick what to do first.

Do you embrace the change and learn new skills?

I embrace change and love to learn new skills.

The world is changing for the DBA and we as DBAs have to change with it. We’re no longer the strange guy in the cubicle that only shows up when something goes wrong. I see more and more situations that we have to be on the forefront taking action and be visible to our colleagues.

If your employer wants to work in the cloud don’t be afraid of it, embrace it, learn everything you can about it.If you have processes that are inefficient, automate/optimize them and make your life easier.

Be the one that has the vision to get the company to higher level and you’ll see that everything will work out.

 

 

Testing of backups updated

Standard

Last week I showed how you can test your backups using the Test-DbaLastBackup function in the dbatools module.

The script makes it easier to iterate through multiple servers, export the data and to send the results to you using e-mail.

My good friend Rob Sewel wrote a nice post to take the function Test-DbaLastBackup a little further. In his example he uses COM objects to create the Excel document. I personally don’t like COM objects because they’re slow and you need the application installed on the running machine. Although COM objects have these disadvantages, they’re pretty solid en work smoothly most of the time.

I wasn’t satisfied with my script exporting to CSV because I would open the CSV file with Excel, so why not export it to an Excel file right away. I usually use the ImportExcel module to export data to Excel.
There was also a need to add the data and log directory to the command to make sure you would be able to use other drives to temporary save the database files.

To run the script you need the dbatools and ImportExcel module.

The last thing that was done is that this function is now available on Git and available for anyone to download via this link.

The function also can be seen below:

 

 

Testing your backups with dbatools

Backup Yourselves Data Loss Is Coming
Standard

 

It has always said, you’re as good as your last restore, not your last backup. How many of you make your backups and think that everything is OK. There comes a time that you have to restore your database from a backup and you find out that one or more backup files is corrupt.

Testing your backups is a tedious job and it takes a lot of time which I as a DBA don’t have. I don’t have the time to restore a database, run a DBCC command for every database that’s backed up.

There is a solution and it’s called “Test-DbaLastBackup” which is part of the dbatools module.

It goes through the following steps:

  1. Restore the database with the name “dbatools-testrestore-[databasename]” by default. The prefix can be changed.
  2. Run a DBCC check on the database
  3. Remove the database

Test Backup Progress

You’ll see a progress bar how far the database restore is.
After the restore the DBCC command is executed. You’ll not see any progress for that step.

When the entire process is complete the command will output the results:

Test Backup Progress

But for me that’s not enough. This is one database and some of my servers have more than 20 databases on it with sizes ranging from 50 GB to 500 GB (not that large yet but large enough). I want to create a job that executes the test on all the databases and send the results to me.

It’s not going to be a SQL Server Agent job but a Windows Scheduled Task. I don’t need the SQL Server Agent for this and it makes more sense to do this outside of SQL Server.

To start testing I created a file called “backuptest.ps1” and put in the following code:

I added a try/catch block to make sure I would be able catch what went wrong.

If you don’t know how to execute a PowerShell script from the Windows Task Scheduler please read the following blog post: Use the Windows Task Scheduler to Run a Windows PowerShell Script.

After the setup my action looks like this:

Test Backup Task

Make sure your task is set to run under an account that has privileges to access the SQL Server and write the file.

Test Backup Privs

A couple of things that could be in this script:

  1. Execute the script over multiple servers
  2. Mail the results
  3. Add checks and error catching to make sure everything runs

Unfortunately the command “Test-DbaLastBackup” doesn’t allow us to supply servers. I could copy the row that tests the backup but that’s not me. I want things to run in one go and repetitive things don’t work out for me.

I don’t want to log into my server and lookup the results. I want them in my e-mail box when I check my e-mail i the morning. For that you could use the Send-Message commandlet.

The original script is removed because the script is updated and is now available on GIT via this link. Also check the new post about the updated version of the script.

Executing the script:

The script works well although, I would have liked to put in functionality like values from pipeline etc.

I hope you can use this script to your advantage and that testing your backups is no longer a something to avoid but to embrace.

 

Export-DMVInformation Module Update

DMV
Standard
Last Friday I had the chance to show the Export-DMVInformation module to the Dutch Powershell user group. After the presentation I got a couple of suggestions and wanted to put them in place them into the module.

Changes:

  1. Possibility to execute the module using the pipeline
  2. Get all the databases in one statement by assigning the value “ALL” to the database parameter.
  3. Replaced messages with verbose
Changes 1 and 2 reduce the amount of time needed to get information from multiple instances and multiple databases. Before you had to execute the module in a external loop and for each database . Now you can get the information from all the databases across multiple instances in a single line!

Change 3 gives the ability to choose whether to show all the messages or not. Use the switch “-Verbose” to see the various messages.

An example how the new module works is shown below:

Export DMV result

Please take a look how to install or use the module on the module page.
 
Happy exporting!

 

My SQL Server journey with Linux

Standard

Linux, where did it all begin

About a couple of years ago there was an April fools joke on SQLServerCentral.com about SQL Server coming to Linux. Now we know this isn’t a joke and SQL Server for Linux is a fact.

I started to explore Linux around 1998 on my AMD K6 266 Mhz pc. I bought Linux because back then I could download that entire distribution over a dial-up connection going 5Kb a second. If I would have downloaded that from the internet the phone bill would blow your mind.

My first Linux distro was Redhat Linux version 5.1 (Manhattan) and back then there was no nice GUI for the install so it took me a while to get things started. It wasn’t like I had loads of documentation on the internet back then so I did what I could using books and small pieces of information I found on the internet.

I didn’t have the luxury of owning multiple computers like some class mates had and I decided to do a dual install next to my Windows 98 installation (oh yeah I was really early to upgrade from Windows 95).

Nowadays I can download a Linux distro in a couple of minutes and within 30 minutes I have a virtual machine installed and ready to go.

Preparation

Where do we start? Let’s first download a distribution that’s supported my Microsoft. I decided to download Ubuntu Server 16.4 because I don’t need all the fancy GUI stuff that’s in the Desktop version and don’t to have too much software I’m never going to use.

I installed the distribution in a VM and installed the open SSH server to be able to connect to my VM using Putty. I use Putty because I want to easily copy and paste and I don’t like to be connected to the console all the time.

Installing SQL Server

I can give you a complete overview what I did to install SQL Server but Microsoft already did that for you.

Installing SQL Server using the “Install SQL Server on Linux article from Microsoft is as boring as can be, which is a good thing. If you go through the setup from that page you’ll notice that it all works.

Conclusion

Microsoft did a really good job providing SQL Server for Linux because the installation was boring as as can be. I know of course that this is still the CTP and that most configurations were done for us but still this is a really good introduction of SQL Server to a complete different platform.

So if you haven’t already tried to install SQL Server on Linux go try it out because it’s a piece of cake.

 

 

 

 

 

Automate Glenn Berry’s DMV Scrips With PowerShell

Standard

Last week I attended one of the 24 hours of PASS sessions by Glenn Berry about diagnosing performance problems with DMV’s. If you don’t know what the 24 hours of PASS is, it’s an initiative by PASS where there are 24 sessions, one session every hour for an entire day.

Glenn Berry is also knows as Dr. DMV because he made some genius scripts to analyze your instances and databases using the dynamic managed views (DMV).

I’ve used Glenn’s DMV scripts for years but always found them tedious to execute because there are about 70 individual scripts that either query instance or retrieve database information. Glenn did make it easier for you by creating Excel templates to save the information in.
There are separate scripts for each version of SQL Server that updated every month. Glenn only updates the versions for 2012 to 2016 with new features. The scripts are very well documented and even contain hints about how to solve some issues.

As almost every DBA I’m very lazy when it comes to repetitive tasks and when something takes a couple of minutes to do manually, I’ll make sure I don’t have to do it again by automating it.

So that’s what I did and what I created is a PowerShell module to export all the information from the DMV queries to an Excel sheet automatically.

The hardest part of automating this module was to parse the entire script into individual queries. Fortunately Glenn has made this easy for me by placing a marker at the end of each query.

Note: Because the scripts are only updated from version 2012 and forward I made some changes to the older scripts to make sure the files could be parsed. If you let the script download the DMV files you’ll have no issues executing the module.

How it works

The module has one cmdlet called “Export-DMVInformation”.

The module is dependent on the module “ImportExcel” to make it possible to export the data to an Excel file. You can find the ImportExcel module here.

The module can be executed simply by supplying the name of the instance, but there are other options to make the end result more satisfying.

The cmdlet has the following parameters:

  1. instance: name of the instance. If it’s a default instance just use the server name
  2. database: the name of the database to query. If no name is given the master database will be used.
  3. username: The username in case sql authentication is needed
  4. password: The password that goes with the username if sql authentication is needed
  5. dmvlocation: The location of the dmv queries. The default is “My Documents\dmv\queries”
  6. destination: The destination where the Excel files need to be written to. The default is “My Documents\dmv\results”.
  7. excludeinstance: A flag to exclude the instance queries. Default is 0 or $false.
  8. querytimeout: Timeout in seconds for the query to not let the query take up too much time. The default 300 seconds (or 5 minutes).

The heart of the cmdlet is the dmv file because without it doesn’t work. You don’t have to worry about which version needs to be used because the cmdlet will find that out itself.
If there are no DMV files, or you couldn’t bother do download them, don’t worry. The cmdlet will initiate a download and download the DMV files for you to the default location set in the parameter “dmvlocation”.

The result

A partial result of the cmdlet can be seen below:

Export-DMVInformation Result

There will be a Excel file with data for the instance and a separate file for the database when the cmdlet is finished.

How to install

The easiest method to install the module is by copying the code below and entering it in a PowerShell window:

Alternative installation method

Alternatively you can download the module from here.

Unzip the file.

Make a directory (if not already present) named “Export-DMVInformation” in one of the following standard PowerShell Module directories:

  • $Home\Documents\WindowsPowerShell\Modules (%UserProfile%\Documents\WindowsPowerShell\Modules)
  • $Env:ProgramFiles\WindowsPowerShell\Modules (%ProgramFiles%\ WindowsPowerShell\Modules)
  • $Systemroot\System32\WindowsPowerShell\v1.0\Modules (%systemroot%\System32\ WindowsPowerShell\v1.0\Modules)

Place both the “psd1” and “psm1” files in the module directory created earlier.

Execute the following command in a PowerShell command screen:

Import-Module Export-DMVInformation

To check if the module is imported correctly execute the following command:

Get-Command -Module Export-DMVInformation or Get-Module -Name Export-DMVInformation

If you see a list with the functions than the module is installed successfully. If you see nothing happening something has gone wrong.

I hope you enjoy the module and that it can help you to get performance information fast.