Reset-SqlSaPassword: Easily regain sa/sysadmin access to your SQL Servers

It’s obvious that I love PowerShell and SQL Server. Together, they allow SQL Server DBA’s to solve just about any problem. Most of my projects have come from questions posted in Reddit’s SQL Server subreddit, including my most recent project, Reset-SqlSaPassword, which allows you to regain syadmin access to your SQL Servers, most times, in less than 20 seconds.

The “sa” in this module name is more about sysadmin access, and not the actual SQL Server login “sa”. Using this module, you can easily regain access to local or remote SQL Servers by either resetting the sa password, adding sysadmin role to existing login, or adding a new login (SQL or Windows) and granting the login sysadmin privileges.

Salsapassword-general-1

“That seems totally insecure”, you may think. But this script will not work unless you have Windows administrator access on the server or workstation running the SQL Server instance. I’m actually running the script as a Domain Admin in the demo video, though it will also work with local admin privileges.

Initially, when I started the project, I was modifying the Startup Parameters using Microsoft.SqlServer.SqlWmiManagement but I found a huge bug that meant my script would only work using Microsoft.SqlServer.SMO using Version=11.0.0.0. If at all possible, I’d like my scripts to work across all environments, and this was a show stopper. Then I considered modifying the service itself (failed miserably) and even the registry. Then I realized I could do this all safely from the command-line, which meant the script would have no lasting impact since no permanent startup parameters are modified.

If you need to regain access to your SQL Server, just download Reset-SqlSaPassword from Microsoft Script Center and execute. It works on every Windows platform I tested, even Win XP (laugh, but sadly, some orgs still use it) and Windows Server 2016 Tech Preview. It also works on SQL Server 2005-2016, both clustered and stand-alone instances. It relies heavily on WMI calls and .NET, and does not require SMO or any admin tools. If you’re having access issues caused by unopened ports/strict firewall access, just run the script locally.

If you have any issues, please let me know. You can hit me up on email ([email protected]) or on Twitter (@cl). Or in this post’s comments.

Posted in PowerShell, Security, SQL Server

Easily Remove Black Bars on Side of iMovie Videos for Free using Handbrake for Mac or Windows

I had the hardest time finding this information. I’m currently making a whole bunch of Animated Gifs to demonstrate the functionality of some of my SQL Server/PowerShell scripts (using a combination of Camtasia and iMovie. I’m trying to keep the size of the file (and thus the video dimensions) small but the version of iMovie I’m using only supports widescreen. This causes black bars to appear on some aspect ratios.

To fix this, I simply load the video into Handbrake. I don’t even make any adjustments. Just select the source and the output, then hit Start. Handbrake is an an awesome, open source kind of project. Like VLC.

The video/gif below was made using this method, and initially had a thick sidebar.
Handbrake-new-1

Lately, I’ve been going to online-convert.com to change the resulting videos into animated gifs.

Oh, and expect more animated GIF demonstration videos in our blog. I’m addicted.

Posted in General

Getting Total Number of Rows Copied in SqlBulkCopy Using PowerShell

Getting the total number of inserted rows for SqlBulkCopy should be easier, but I believe the easiest (and fastest) is by using reflection and some C# code within your PowerShell script. Thanks to user601543 at stackoverflow, I was able translate his code for use within one of my own scripts.

rowscopied2

The Code

Here’s an approximation of how I did it. First, I added the code using Add-Type. Then I called it using [System.Data.SqlClient.SqlBulkCopyExtension]::RowsCopiedCount($bulkcopy)

As the user noted: “This count does not take into consideration the number of rows actually inserted when Ignore Duplicates is set to ON.”

Posted in PowerShell, SQL Server

Simplifying SQL Server Migrations using PowerShell

Recently, I did a webcast for the PowerShell Virtual Chapter of PASS on Simplifying SQL Server Migrations using PowerShell. If you’re planning for a SQL Server migration, I wrote a bunch of scripts that can help, and put them all on github.

Check out the sqlmigration repository, and the accompanying PowerPoint Slides.

If you use the scripts and run into any issues, or have any feature requests, let me know. I can be contacted on Twitter at @cl or by email.

Posted in PowerShell, SQL Server

Sudden High CPU Utilization and Query Performance Decrease | When Query Plans Go Bad

Each morning when I get into the office, I check performance graphs in SolarWinds Database Performance Analyzer (DPA – formerly Confio Ignite).  Monday mornings are usually the most important, because I run full system maintenance on a specific monitored database on Sunday.  Monday is the first day of heavy utilization by users since the completion of that maintenance.  The reason that maintenance is important is because updating statistics will often cause SQL Server to generate new execution plans.  Sometimes the new plan is worse than the old plan and you see changes in system performance.

The great thing about SolarWinds DPA is that I have a graphical representation of all of my data and I can drill down into timeframes.  I can also look backward in time to compare system performance.  Since I look at the graphs so frequently, I am familiar with which queries generate the most wait time, and what I should expect from my CPU utilization.  One morning the graphs showed an extreme spike in wait time for a single stored procedure called GetIDXIntegratedTaskList.  I also quickly realized that the CPU on the database was running at double the usual (23% instead of 10%).

Below are two graphs from DPA.  The graph on the left shows my system performance a few days prior.  The graph on the right shows system performance the morning of the incident.  Focus on the 8AM hour, and the scale of the graphs.  What I deem to be “normal” system performance on the left shows an overall wait period of about 14,000 seconds at the 8AM hour.  But on the right, the wait is over 50,000 seconds.  You will also notice the vast majority of the wait time is coming from that pretty blue block.  Also important to note is that this pretty blue block (which represents a query) is not present in the normal graph on the left, even though there is another color block that is very similar (trust me, it’s not the same query).

normal_chart_dpa       abnormal_chart_dpa

 

A mouse-over of the block shows the name and the specific part of the query that is responsible for the wait.  I am also able to drill down into the hour and see what kind of wait times the top queries are experiencing.  In this case, it turned out to be completely due to CPU/Memory consumption.  You can also see that this query is using much more CPU than the other queries.

cpu_waits_dpa

Looking back in time, I also wanted to verify my theory that this procedure probably started acting up after maintenance.  This was confirmed by the graph from Sunday, which shows the pretty blue blocks popping up right after the update statistics jobs completed during the 9AM hour.

planchanges_dpa

DPA is not necessarily saying that the query is waiting on the CPU, but also that the query is utilizing CPU time.  So what I know at this point:

  • This query/procedure is not normally on my graphs as a top resource utilizer
  • The CPU is running hot
  • This query/procedure is using a lot of CPU, more than the other individual queries.
  • The issue began after maintenance completed, which indicates a possible bad execution plan.

 

What if I don’t use DPA?

Up until now, I’ve relied heavily on SolarWinds DPA to help me track down the query.  It’s not the only way to do it.  If I did not have access to DPA, I probably would have ran a duration trace to find anything over 1000ms or 1500ms and adjust from there.  I did actually run a duration trace against the database looking for exec dbo.GetIDXIntegratedTaskList% to see if there were any patterns to be identified.  Perhaps someone was executing the query with bad parameters, or a web server went haywire and is flooding the database with requests.  In this case, I did not see anything unsual about the queries, other than the fact that the execution time was averaging 1.5 seconds and the matches were rolling in at a high rate.  A more general duration trace would have also matched up a lot of this data and would have alerted me to an issue with the procedure.

In addition to duration traces, you can also use Activity monitor to find Recent Expensive Queries and sort by Executions/minute or CPU.  This isn’t always a sure-fire way to identify the issue, but it’s a good idea to at least try.

The last way I would attempt to find the query is by querying DMVs for query and procedure execution statistics.  The problem here is that this information is cumulative since the system started.  You can reset wait statistics, but you would have to clear out all of the procedure cache in order to reset the stats on the queries and procedures.  In this case, the issue would have magically disappeared, but you would not know what the problem was if you were to blindly clear the cache.

Luckily, there is a creation_time column in sys.dm_exec_query_stats and a cached_time field in sys.dm_exec_procedure_stats.  You could attempt to sort by these columns and filter down to cached plans that were created around the time the issue started.  If you are able to identify which plans are new and also have a very high total_worker_time, you can narrow down potential candidates for high CPU utilization.

I actually used the sys.dm_exec_procedure_stats to confirm my other findings and I will cover some of that here now.

Continuing

At this point, I am pretty sure that I know what the problem is.  The procedure got a new execution plan after updating statistics and it’s bad.  But just for the sake of it, I still want to get more information from the procedure stats DMV.

There are only three basic SQL statements I will need to use at this point, since I know the procedure I want to address.  The first statement will query the sys.dm_exec_procedure_stats DMV for all the information on the stored procedure.  For the sake of space, I’ve limited the rows in the screenshot to what I think is most important.

dmvquery1

We can see that there are two cached plans for this stored procedure.  It’s quickly apparent that the first one in the list has been executed the most and has the most total_worker_time.  So, I will grab the plan_handle for the first entry and then query the sys.dm_exec_query_stats DMV for more information on the queries within that plan.

 

dmvquery2

Now we can see that there are a number of queries being executed within this stored procedure.  The statement offset start and end positions are specified.  If you really wanted to look at the statement, you could use these offsets to help isolate that specific portion of the stored procedure.  You also see the plan_generation_num.  If this number is high, it indicates that the procedure or statement is being recompiled.  The important data here is the creation_time, execution_count and the total_worker_time.

As we see, there are 8 SQL plans for this 1 procedure.  The second row has the highest total_worker_time, meaning it is using more of the CPU than the rest of the procedure.  We also see that the creation_time for this plan is different than the rest of the procedure.  All the other plans in this procedure have been using the same execution plan since 04-11-2015.  Our issue didn’t start until the 19th of April.  We see that this coincides with the start of our problem and it’s using the most CPU out of the group.

From here you could check out the execution plan in depth, if desired, but I already know that it used to work better than it is working now.  The decision I made was to clear the plan cache for this stored procedure and hope that the system would choose a better plan.  To do that, simply grab the same plan handle and run:

(I originally tried to clear out only the SQL plan, but it purges the entire plan handle)

As soon as I cleared the procedure out of cache, the CPU utilization on my server dropped by 50% and my duration trace stopped displaying matches, since the query began to execute much more quickly.

cpu_decrease

I was also able to grab another screenshot from DPA showing the total wait time per minute.  All of the CPU waits from this procedure disappeared once the new plan cache was calculated.  I admit that there was a good chance that this would work, but it was not guaranteed.  The system could have chosen the same plan again, or maybe even a worse one.  But it all worked out for me… and for the users who were reporting slowness.

result_chart_dpa

Posted in SQL Server

Making Bootable USB sticks on Windows

Just because I always forget.. All of my workstations are Macs, which I then use to connect to my vSphere and Hyper-V labs. This can make it a challenge to create bootable Windows-based USB sticks for my servers. I used to use unetbootin for Mac, but I found it worked well for my ESX servers (sometimes), but not Windows.

After trial and error, I’ve found that running a Windows VM on my Mac, then burning a Windows ISO to USB works best when using Rufus.

Posted in Windows

Get SQL Server Product Keys for Local and Remote Servers using PowerShell

I just released a new PowerShell script, Get-SQLServerKeys.ps1. The script works works with several versions of SQL Server (2005-2014), and can use Central Management Server, a text file, or just a string of servers as input.

sql6

Like many of the Windows Key scripts available, this one relies on Remote Registry being enabled. However, if you’ve disabled this service, you can run Get-SQLServerKeys.ps1 on the SQL Server itself and all keys will be extracted for all local instances by accessing the registry directly.

sql7

Thanks goes out to Jakob Bindslet for providing the key decode function.

Get-SQLServerKeys.ps1 is one of several scripts that help DBA’s migrate SQL Servers. Check out my sqlmigration repository on github for more scripts.

Posted in PowerShell, SQL Server

Creating a WCF Net.TCP Service and Client using PowerShell

I’m currently attending Regis University for my grad degree, and just finished up a networking class. For my final, I chose to explore the Windows Net.TCP Port Sharing Service. I had this glorious vision of translating some C# code to 100% PowerShell code, but eventually gave into the fact that PowerShell doesn’t support reflection, and a bit of the code had to be written in C#.

Ultimately, I also learned that TCP Port sharing is really just a port proxy facilitated by SMSvcHost.exe, and the differentiator, instead of being a port number, is a namespace. Below is the code I used for proof of concept. You may find it useful when testing your own C# code.

Thanks goes to Allan and this user for getting me started with the WCF PowerShell code.

Posted in PowerShell