Automate Glenn Berry’s DMV Scrips With PowerShell

Dont reason with it, just automate
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.

 

 

Enable changes that require table re-creation

prevent_changes
Standard

The Problem

When you’re developing on an instance you might want to change something in a database where the change might require to re-create the table. By default, the SQL Server Management Studio (SSMS) will prevent saving changes that require the table to be recreated.

Examples of changes that require table re-creation:

  • Change a column to no longer allow NULL values
  • Adding columns in the before another column
  • Moving a column

In earlier editions it was possible to allow some changes to be done directly using the GUI. What SSMS does in the background is unloading data of the table into a temporary table (not a # temp table), then deleting the original table and renaming the temporary table to the original table name.
You can imagine that actions like that would have a huge impact on a large (maybe production) table.

When you apply such a change you’ll get the following error message:

prevent changes error

The solution

Again, be very careful when enabling this option when you regularly work in production environments. I very much hope so that you never make these kind of changes using the GUI especially not in production. Just don’t!

To enable the changes in SSMS go to the menu “Tools” and click on “Options”.

Go to option “Designers” and uncheck the checkbox at “Prevent saving changes that require table re-creation”.

prevent_changes_solution

Click on “OK” and you’ll be able to apply the changes you couldn’t do before.

Remember that this option is set for your SSMS installation and that this will be used for every instance you connect to.

Happy developing!

Does Log Shipping Transfer Schema Changes

schema_change_api_problem
Standard

Recently I got a project to implement log shipping in SQL Server. The project was to implement log shipping for multiple databases. I’ve configured log shipping before but that was years ago and I bet stuff has changed since then to make it easier to implement.

Log shipping existed partially in SQL 6.5 and 7 and was officially released in SQL Server 2000. Log shipping today is still being used pretty often as a warm standby or for readable secondary databases being used for reporting.

The Problem

One of the concerns was that in case of a schema change like, adding a table to the database or altering a column in an existing table, would affect the continuity or even break the log shipping.
If you look at replication for instance, it’s reliant on the schema to work and if the schema changes you’ve got to make sure the schema is the same on the secondary server.

I was pretty sure that schema changes would be applied in log shipping but I didn’t want to assume and tested it out to make sure it wouldn’t cause any problems.

I wanted the following cases to get tested:

  1. Create a table
  2. Adding a user
  3. Add a stored procedure
  4. Change the data type of a column

The Test

I created a database called “LogshippingTest” and set up log shipping to a database called “LogshippingTest_DR” on the same machine. For this test it was fine that both databases resided on the same machine but in the real world you’ll probably want to have different machines.

I created a table called “Test”:

logshipping_schemachanges_database

There are three columns and I want to see what happens when I change the column with the VARCHAR data type from 30 characters long to 130.

Keep in mind that there are caveats to changing the schema with log shipping. When changing a column from NULL to NOT NULL will result in a change where you have to recreate the table and import the data.

The DR database looks like this after implementing log shipping:

logshipping_schemachanges_databasesecondary

 

 

 

 

 

 

It all looks good now let’s execute the jobs for the log shipping and make the changes.

To change the table I executed the following script on the primary database:

Let’s see if the changes are in the transaction log:

This gave the following result:

logshipping_schemachanges_tlogdata

That looks pretty good let’s check the DR database to see what happened after log shipping.

logshipping_schemachanges_resultcolumnchangeAs expected, after the log shipping was executed, the change was transferred to the secondary database.

 

Conclusion

The same results could be seen when creating the other objects like the users and procedures.

We can safely say that schema changes are applied on the secondary database in log shipping.

Happy log shipping!

Backup Databases Using Workflows

backuptapes
Standard

This post will show you how to backup databases using workflows in parallel. I will show a scenario where an instance will backup all the databases at almost the same time.

You probably heard about the new PowerShell module for SQL Server called “SqlServer”. If not you don’t do much with PowerShell or you’re probably not a member of the PASS PowerShell VC. If you haven’t heard from it you can become a member of the of the virtual chapter for PowerShell and if you like instant messaging, become member of the Slack SQLServerCommunity.

The Challenge

I wanted to test something since my last presentation where I discussed using workflows in PowerShell. One of the strong features of a workflow is to go parallel which could potentially reduce the time backup all the databases.
Of course we need to take into account that starting backups for all the databases on an instance could be a possible problem due to performance on the disk or over the network.

Let’s explore the new PowerShell module for SQL Server and see if there is a backup cmdlet.

And there is a cmdlet for that:

backupparallel_backupcmdletfind

Let’s see some examples of the cmdlet:

There are quite a lot of examples so let’s pick one:

backupparallel_backupexample

While I can execute this script to backup all the databases one after the other, I could also use some techniques to backup all my databases at the same time.

The Script

The script will allows you to enter an instance name from which it will connect to and backup all the databases present:

I could go even further than that by including a server parameter to execute the backups on multiple my servers at the same time. This seems very nice in theory but I think nobody will ever implement such a solution because that would be too intense for most environments.

The Result

The ISE will not show you that things are executed simultaneously but it does show the current state of a backup.

backupparallel_resultise

When I look in SSMS I can retrieve all the commands executed with BACKUP in it.

backupparallel_resultssmsAs you see the server is pretty busy backing up all the databases.

Conclusion

It’s possible to use a workflow to execute your backups. You have to take into consideration that there is a downside. If you execute all the backups at once you’ll probably get issues with throughput if you’re dependent on a slow network for example.

You could always add another parameter to filter out specific databases to make sure you execute it as a specific set.

Hope this helps you out and maybe inspire you to alter some traditional scripts to get more work done at the same time.

 

New PowerShell And Workflow Session

workflow
Standard

Next Wednesday is going to be 20th of July and that’s the day I’ll do another session for the PASS PowerShell Virtual Chapter about Workflows.

If you want your processes to go light speed instead of turtle, please come and register for the session.

We will talk about the definition of a workflow, why to use a workflow (and also when not to), the requirements, the different parts and of course there will be demos my favorite part of a session, demos.

Did I say it’s free?!

For more info please visit the PowerShell VC website.

No One Listens To Me! Why? Part 1 – Management

broken
Standard

What if you’re in a situation where you see that something needs fixing but no one listens to you about what you have to say.

It seems unreal but I’ve been in situations where I had the hardest time convincing managers, colleagues and even third parties that there was something seriously wrong.

I’m going to share my experience with the three of the parties and how I made sure I was always in the clear when something would go wrong.

This is not an article how to spin the wheel of blame because that’s never a healthy situation. This article will give you tips and tricks how to make sure you get your things done and how you can avoid to become the victim of a situation where you’re to blame.

These are actual real situations I have been in so there is no fiction in the stories that are coming.

Management

I start fresh at a new to the company as either a DBA and I was the only DBA a.k.a. the Lone DBA. The former DBA had left (I never got to know the reason) and I had little to no documentation.

The first things I did was do checks on backups and collect information from all the instances.
I noticed that all the backups are full backups. The backups ran for hours and there is a lot of contention due to other processes wanting to process their data.
It seemed all the databases are all in simple recovery mode. Normally that’s not a good sign but there could be a good explanation.

I send an e-mail to my colleagues in the IT department if anyone knew why this is set up the way it is. Of course nobody knows and it’s a dead end. Documentation is there and I’m already happy that the backups ran for the last few weeks.

I was the only DBA and therefor responsible for the situation. Unfortunately because of costs of disk space and other processes I wasn’t able to implement my solution without the consent of my manager who was also the change manager at the time.

I go to my manager have the following conversation:


Me: “Are you aware of the fact that all our databases are in simple recovery mode and that we make full backups of all our databases which take a considerate amount of time and make other processes run longer than needed?”

Manager: “Yes! I know why we did that. The transaction log backups were too difficult to recover so we make full backups all the time. It’s easier right?!”

Me: “You’re also aware that due to the fact that we run in simple recovery mode we have no way of recovering to a point in time and in the case of data loss can only return to the full backup?”

Manager: “Yes I’m aware of that but it doesn’t matter because we have calculated that it doesn’t matter if we lose 24 hours of data because we’ll just redo all the work that’s lost.”

Me: “That’s ridiculous because it’s not that difficult to implement a backup strategy that could avoid that situation. Why wouldn’t we want to do that?”

Manager: “Because we don’t need it and why put in the extra effort if nobody in the company cares.”

Me: “We’re clearly not on the same page and I think you underestimate the situation.”

I stop the meeting and walk out of the room to think of a plan to make sure this doesn’t get back to me when all hell breaks loose.


So let’s evaluate what went wrong:

  1. Nobody in the company knows why things were set up the way they are.
  2. The company has no idea what the impact of a data disaster could be.
  3. Nobody cared about the fact that situation could actually happen.
  4. I couldn’t convince my manager at that moment with good arguments.

First of all let’s be blunt, if shits hit the fan you as the DBA are ALWAYS, read it again, ALWAYS, responsible to recover the data even if you’re not responsible for the situation at hand. Management will not care about the fact that you mentioned this all months ago, they want everything fixed yesterday. It could even backfire (I had that situation) because you could be blamed not to take responsibility.

So how could we act on the points in the evaluation.

Nobody in the company knows why things were set up the way they are

Start documenting the backups, the schedules, the databases and the servers. If you don’t have something like that already read my article how to document all your servers in minutes.

Also document the architecture of the different applications and their relationship with each other. What interfaces are running between the systems etc etc.
Are there any financial applications that rely on interfaces on the main database for instance. What processes are running during the day that could be impacted.

Make a diagram of the connected applications/processes that are dependent on the database(s). Most people understand things better when they’re made visually.

Try to make sense of the current situation and make sure you have everything in writing. If it’s not documented you can’t prove that something is wrong.

I know this all sounds like a lot of work, but if nobody knows, you should. In the end this will save you loads of time and let you become the person that took the responsibility.

The company has no idea what the impact of a data disaster would be

Here is where documentation is important to get the facts straight. I’ve seen a lot of companies underestimate the situation that there is a real problem. Like I said before, if nobody knows, make sure you do.

If there is a disaster recovery plan see if it’s still up to date and if not make it so. Based on that information try to estimate how long it would take to recover all the dependent databases/processes when the main database is down.

Make sure you know how long it takes to get everything back in order and make sure you have a procedure ready when it does happen. This not only shows you’re proactive in your work but that you can also act when needed.

And one thing you should do is test your DR plan. You plan is worthless if it doesn’t work. Test it periodically to see if it’s still up-to-date.

Nobody cared about the fact that situation could actually happen

One thing I would do is manage expectations. I want everyone in the company to be on the same page with this that in the case of an emergency.

The manager in this situation thought the loss of one day of data was good enough for the other departments. These decisions were made years ago and the entire landscape had changed and the DR didn’t.

I asked the same questions I asked my manager to the managers of several departments and their reaction was a little different. Several managers explained that they would be in a lot of trouble if the application was even offline for half a day and others even for several hours.

Because this was not going to be a healthy situation I called for a meeting with all the managers and me. In this meeting I would explain the situation by the documentation (like the diagrams) and come up with a plan to get the DR up-to-date.

I did everything but still they won’t listen

If you did everything to convince the people and they still don’t want to set up things you would like to, either because of costs or other reasons, I would protect myself.

Make sure all the decisions that were made are in writing, the good and the less good decisions. I would send an e-mail to my manager with the decisions and explain the consequences. After that I would ask my manager to acknowledge the e-mail.

You don’t want a decision outside of your capabilities to come back to haunt you. I’ve been in such a situation and you don’t want to end up there.

Conclusion

It all starts with taking responsibility. If you don’t take responsibility for the data as a DBA I suggest you go look for another kind of job. After that it’s important to get the facts straight. You can’t build a solution based on assumptions. One of the quotes I use: “Assume” makes an “Ass” out of “U” and “Me”, AssUMe.

It’s very important to feel comfortable in your work environment and you should do everything to make sure you go to work with a good feeling. You spend more time at work than you would spend at home (remote workers excluded 😉 ).

 

 

 

 

TempDB configuration SQL Server 2016

configuration
Standard

As you may have read by now, in SQL Server 2016 you’ll be able to set the TempDB configuration during the installation. Whoehoe!

We’ve waited long for that option and I’m very glad that Microsoft implemented it. It’s certainly a time saver because now I don’t have to go into SQL Server afterwards and setup the TempDB, but….the settings could be better.

I created a virtual machine with 1 CPU and 4 cores, 2 GB (not that much but for testing it’s enough) and some disks for data (30GB), log (20GB) and tempdb (25GB).

The screen for the TempDB configuration look like this:

tempdb configuration screen

Number of files

The number of files is based on the amount of cores that’s available up to 8. In my case I have 4 cores and I would create 4 files in total, one for each core.

Initial size

The initial size is set to 8MB. I would never create TempDB data files with a size of 8 MB which in my opinion is really small for a data file even for small systems.

In most cases I would look at the size of the TempDB disk (because you create a desperate disk for TempDB right…) and fill up the entire disk with data files. In this case I would create 4 x 5 GB files to leave some space for the log file.

To properly set the initial size of the TempDB you still need to go through some checks how much your system might need.

Autogrowth

The next field “Autogrowth” is set to 64 MB and I don’t know why, maybe it’s the initial size multiplied by 8. The disk is 25 GB in size and growing the files of the TempDB with 64 MB every time would be inefficient. I did not create a separate disk for the TempDB log because this is just a test machine.

I like the fact that in SQL Server 2016 you no longer need to enable traceflag 1117 to enable to grow all data files in a file group when one data file grows. You can also see that when you change the values in the autogrowth settings. When you change the value it will multiply is with the number of files you put in.

Data directories

The next field is where the files are going to be located. It’s possible to use multiple locations. The SQL Server installation will split your files up accordingly. As a test I put in two directories on the same drive to see what would happen. The configuration looks like this:

tempdb config multiple data directories

What happens is that the installation will place each individual file by going round robin through the directories. The result will look like this:

tempdb config result multiple data dir

So that’s really nice because you don’t have to think about where to place those files and the installation will do that for you.

TempDB log

The same settings for the data files are applied to the log file settings with the exception of the data directories because you only need one log file.

Again I think the initial size is pretty small but that’s up to your needs for your system.

Conclusion

I like this new setting in the installation but you still have to think about your configuration. There are some default settings which in my opinion in larger systems wouldn’t work.

Maybe in some small environments these settings will work perfect but in the environments I managed these settings would not be a good choice.

Maybe this is why we still need professionals who can interpret the needs for a database or application and make configure the instance to achieve the best results 😉

 

Avoid Spaces In BIML

Queen_Hot_Space
Standard

I recently started to learn more about BIML because it intrigues me. To learn more about BIML, I started a little project to extract information from a database for data warehouse purposes. I followed a tutorial to create a meta driven SSIS solution. Perfect!

The Problem

I got through the article and after some debugging (due to having way newer software) I was able to generate my packages. The sun was shining and I was pleased I got it to work until….

I got this error when checking the packages:

biml_tutorial_error

After going into the error I found out that it had something to with illegal characters:

biml_tutorial_error2

Apparently SSIS doesn’t agree with my code. So opening the editor of the raw file connection, changing the access mode to “File name” showed me this:

biml_tutorial_symptom

There are spaces and tabs in front of the path! SSIS doesn’t work well with spaces and that’s one of the reasons why you should not use spaces in file names in the first place.

The Solution

It turns out the fault was in this piece of code:

biml_tutorial_error_location

Which should have been:

biml_tutorial_solution

After changing the code the errors were gone.

I hope this helps you out and avoids you having to go through the same frustration I experienced.