SQL Server Administration through SQL Server Management Studio

Goal

To create an interface that contains the up to date information and know how when it comes to manage SQL Server databases using SSMS and to prepare for further certification or just to refresh the knowledge or improve them with something new.

My way of achieving it

By duplicating the SQL Server Management Studio interface using static information and using Tool Tips. Before anything else I want to mention that:

  • I am not a programmer
  • The information is taken from other DBAs that were kind to share it online

When I started to learn SQL Server it was obvious ( and I am speaking only about the database engine) that it will be hard to always remember anything and this became more clear throughout my jobs since every company has its own vision and they are doing things in their own way. On top of these, most of the time, depending on the position and the job description we are loosing the knowledge and the know how that we learned during a SQL Server certification or during a job where we had to do only performance troubleshooting or during a job which required only database maintenance and user creation and so on. Because of this I decided to start making this simple application in C sharp in order to use it as a repository of knowledge and best practices when it comes to SQL Server.

The main purpose of this application is to

  • help someone that just started to learn SQL Server by having some of the information at one click
  • for more experienced DBA it might be just a repository containing the experience he accumulated throughout his jobs. Every ToolTip can have a legend section  where we can put  how we solved a similar issue or what was the root cause.
  • for others might be just a place containing the answers that we have to provide when speaking, solving SQL Server problems.

Below is a video that I made showing some of the things. Ignore how the application looks it is just to show how good would have been if Microsoft would have provided an argument when starting SSMS that will allow some ToolTips and some best practices.

Below you can watch some of the things that I manged to create and do till now.

 

SQL Server clustered instance dependencies – verification – using tsql, xp_cmdshell

Goal

To check and verify if the existent dependencies for sql server resources are in place

My way of achieving it

Using tsql, xp_cmdshell. From time to time due to adding more disks to an instance or performing other activities we end up having a resource that is not tied to the parent resource and when it comes to sql server disks some of the databases residing on those disks are not accessible. In order to check if we have any of the resources not being tied to the right parent resource I have created the script below that will tell us which resources are not tied. The output of the script looks like below

sqldiskdep

The code can be downloaded from here

Display folder size in a graphical way using SQL Server

Goal

To obtain the size of folders and their path in graphical way. To have the output of tree command, dir command and the folder size in only one output.

My way of achieving it

Using tsql, xp_cmdshell in order to call the tree, dir and powershell.exe command s.  The desire to have something like that came when I have to troubleshoot and see who is the biggest consumer in case of free space issues. Of course that multiple approaches and solutions can solve this question but I stopped on using the tree command because I liked the look of the output. The first output shows the information as the tree command does while the other tries to sort them by size in order to easy see the biggest consumer. One problem with this is that for locations where sql server doesn’t have access the size returned is 0. Even so I decided to finish the script and use it like this because of the output. The script has also a deep switch that will let you decide how deep you want to traverse the folders, maximum is 3.

The output of the script looks like this

sqlservertreelikeoutput

The code can be downloaded from here

Load balancing for database files among the mount points

Goal

To load balancing the database files among the number of mount points or paths that we have available for this database.

My way of achieving it

Using tsql, xp_cmdshell and powershell command. From time to time, because databases grow, more databases are created we need more and more space for storage and because of this we end up adding more disks or mount points (the case that this script, post will address) but these are not used evenly by the databases from our instance. Some of the databases that were created  in the beginning will use only the mountpoints available when the database was created. Because of this from time to time we need a cleaning method or a method to load balancing the files among the mountpoints. The script below is my attempt to load balancing those files. By default is displaying information for databases that have 4 database files. You must be aware that the script is not checking for the available free space at destination. The script is only displaying the information without executing anything. The output of the script for a test database will look like below

loadbalancingdbfiles_17July2016

The code can be downloaded from here

Duplicate objects belonging to a schema to another schema

Goal

To duplicate all the tables, views, storedprocedures, userdefinedfunctions that belongs to a schema to another schema.  Of course that there are more objects that may need to be duplicated but as a start, those are the main ones that the script does. Other objects can be added with little modification to the script.

My way of achieving it

Using powershell and scripting

The script tries to do what generate script option at the database level does but on top is trying to address the following

 

  • It tries to resolve the name of the objects inside of all the generated code to point to the objects in the right schema( new schema) – this is where the script might have an advantage over the generate script option at the database level.
    • Meaning that if the object definition was in dbo schema – SELECT * from software_definition – it will transform it in SELECT * from testuser.software_definition. As you can guess this string replacement might take a lot of time if we have a lot of objects that are scripted and the object definition is long. More than that, that string replacement might be wrong sometimes. This is why some more tests needs to be conducted.
  • It tries to decrease the need of the manual work

The output of the script looks like below

duplschema2 duplschema1

Please use it first on your virtual machines or your test instances, like adventure works sample databases or others. As I said it is not a replacement for generate script option it is just an attempt to avoid some

– clicking operations

– selecting some scripting options

– finding and replacing some strings, schema.name to newschema.name

– adding new schema name for the objects that are missing the schema prefix ( this needs to be adjusted to work as good as possible)

The code can be downloaded from here

SQL Server clustered instance dependencies – verification

Goal

To check and verify if the existent dependencies for sql server resources are in place

My way of achieving it

Using powershell. From time to time due to adding more disks to an instance or performing other activities we end up having a resource that is not tied to the parent resource and when it comes to sql server disks some of the databases residing on those disks are not accessible. In order to check if we have any of the resources not being tied to the right parent resource I have created the script below that will tell us which resources are not tied. The output of the script looks like below

disk_dep_powershell

The code can be downloaded from here

Sqlcmd formatting for better vizualization

Goal

To keep when possible the same display as SSMS GUI has for the output of a SELECT statement.

My way of achieving it

By creating a SP that will generate a dynamic tsql for the select statement or by rewriting the select statement.

While working in SQLCMD we face the problems whenever we issue a select statement or we run an internal stored procedure. The problem is that the output is not displayed or aligned in an way that is easy to read. Below are some outputs of connections using different switches in sqlcmd. As you can see in the below picture I tried the same query with no switch, with -W, -Y and -y.

sqlcmd_format_output

 

No matter of the switch used the display is either truncating the columns or is hard to read. The last display is the one that I believe is easier to read. Of course that this simple example is only the tip of the iceberg and depending on the number of the columns that we want to select the generating of the dynamic tsql might be impossible but even so it is worth to add this type of output to our internal stored procedure for a better visualization in sqlcmd.

The SP for converting the select is not yet provided in the scripts below but I provided the code used to format the output.

The scripts used to create the output above and the difference between running xp_fixeddrives and the code for generating what I call a better display can be downloaded from here .

 

 

Check what type of storage your SQL Server is attached to

Goal

To group the storage of our sql server in 4 main categories that will help us when seeking for information about the speed of the storage, how and if the storage can be increased.

My way of achieving it

Using powershell.

The easiest and more convenient way was to create an oneliner that we can run in powershell and output the desired information. I don’t know how good will work with all the other hardware configurations since the info that I used to create the oneliner was containing only the following information

Disks

  • NETAPP LUN  Multi-Path Disk Device
  • HP LOGICAL VOLUME SCSI Disk Device
  • PowerDevice by PowerPath
  • HP LOGICAL VOLUME SCSI Disk Device
  • SanDisk SD7TB3Q-256G-1006 ATA Device
  • HGST HTS545050A7E380
  • VMware Virtual disk SCSI Disk Device

Controllers

  • QLogic Fibre Channel Adapter
  • Emulex OneConnect OCe11100, iSCSI Storport Miniport Driver
  • QLogic Fibre Channel Adapter
  • EMS Device Bus
  • Microsoft Multi-Path Bus Driver
  • Smart Array P410i Controller
  • Microsoft Storage Spaces Controller

but I will update the script in time.

The onliner is this

Get-WmiObject Win32_DiskDrive | group-object model | foreach {if($_.Name -like “*virtual*”) {“1. Virtual Machine”} else {Get-WmiObject Win32_SCSIController | group-object name |foreach {switch -Wildcard ($_.Name) {“*smart array*” {“2. Smart Array”}; “*fibre*” {“3. HBA’s”}; default {“4. Buit-in Controllers”}}}}} | group-object $_ | select Name |sort-object Name

Below is a picture of running the script on 3 different servers

groupcontrollers

SQL Server comparison script

Goal

To obtain specific comparison information  for the most common DBA specific tasks.

My way of achieving it

Using powershell.

This is not a final script is merely an example that can be used to create something that can be used when we need to compare 2 logins, 2 database users, 2 instances and so on. The script can contain other type of comparison and this example contains only what I am used to compare most of the time.  Another benefit is that provides an output that can be easily exported and filtered further in excel and provided to other people in an easily accessible format .

The output of the script look similar to this

comparison_script

 

The script can be downloaded from here

Task Manager and Activity Monitor for SQL Server instances

Goal

To obtain the same information that Activity Monitor in SQL Server and Task Manager in the Windows provides for all the SQL Server Instances running on our server.

My way of achieving it

Using powershell and registry we can find out information about the sql server services that are running on our server and with that information we can construct the counter names that we want to gather. The counters might be added or removed as needed but I tried to put what Activity Monitor and Task Manager display. The main benefit is that we can have these for all the instances and taking advantage of the out-gridview cmdlet we can sort and order values as we like without worrying that the information displayed after sort will contains processes that we don’t want. For example the output created for the performance counters below

\Memory\Available MBytes
\Processor(_Total)\% Processor Time
\LogicalDisk(C:)\Current Disk Queue Length
\LogicalDisk(C:)\Disk Read Bytes/sec
\LogicalDisk(C:)\Disk Write Bytes/sec
\LogicalDisk(D:)\Current Disk Queue Length
\LogicalDisk(D:)\Disk Read Bytes/sec
\LogicalDisk(D:)\Disk Write Bytes/sec
\Process(sqlservr)\% Processor Time
\Process(sqlservr)\Private Bytes
\Process(sqlservr)\ID Process
\Process(sqlservr)\IO Read Bytes/sec
\Process(sqlservr)\IO Write Bytes/sec
\Process(sqlservr)\Elapsed Time
\SQLServer:Memory Manager\Total Server Memory (KB)
\SQLServer:Memory Manager\Target Server Memory (KB)
\SQLServer:SQL Statistics\SQL Re-Compilations/sec
\SQLServer:Buffer Manager\Buffer cache hit ratio
\SQLServer:Buffer Manager\Page life expectancy
\SQLServer:SQL Statistics\Batch Requests/sec
\SQLServer:Databases(_Total)\Backup/Restore Throughput/sec
\SQLAgent:Jobs(_Total)\Active jobs

is

perfmonamtm1

 

while the output for a cluster might be the one below

perfmonamtm

Of course that for some counters depending on the SQL Server Edition we might receive these type of errors which are normal and I display them.

get-counter : The \SQLAgent:Jobs(_Total)\Active jobs performance counter path is not valid.

The script can be downloaded from here