SQL Server and Other Topics


I got my start with SQL Server in 1997 with SQL Server 6.5 at BellSouth Cellular. From that point on I've had the opportunity to work on Versions 6.0 to 2014. I specialize in Performance Tuning, High Availability and SQL Development. Contact me through this site or through my Twitter handle @SQLDiver

Wow, so unexpected to get the same session picked twice in a row.

 I submitted multiple sessions this year with the expectation of one or two of the others being chosen. 

I'm truly honored to be chosen again to submit at the PASS Summit, but also humbled with so many of my close friends missing the cut. Several of my closest friends have been speaking for awhile at SQLSaturdays and besides lighting talks haven't been chosen over the past 5 years. I pray every year that they get picked.

I have a lot of work to do on the presentation, as I have a lot of ideas I want to implement from all of the times I have presented it. Every time I present the session I find another thing I can do to improve it... every time. It is definitely a great learning process as I see the expressions on the attendees faces and the questions asked.

 I hope it goes as well as last year! See everyone there!

Features I'm looking forward to:

  1. Always Encrypted

    Daily an enterprise organization has made a bad decision of not keeping their sensitive data protected. Why? Well, the excuse is its too expensive, too difficult, will take too long, will slow down the database, yada, yada. You're not going to get away with the whining any more. Always Encrypted is a solution that should make the process of encrypting sensitive data at rest and in motion at a point the much of the data enters the database (the application). Yes, of course, just like other encryption technologies, you need to plan the process of encrypting your data.

  2. Stretch Database

    One of the coolest ideas for "stretching" data rarely used to the cloud. There are lots of limitations currently as well as some cost issues that make it very difficult for the average Joe to afford the Stretch Database feature. I'm hoping they get this one fixed fairly quickly. Please?

  3. Query Data Store!!!!

    Ever since seeing Conor Cunnigham talk about this feature  at Summit 2014 my mouth has been watering. I started playing with it as soon as CTP 1 was released. The best article I found at the time was by Enrico van de Laar called The SQL Server 2016 Query Store: Overview and Architecture. This is going to make performance tuning more easier with more detail data available, the tools that monitor performance will begin to use the Query Store for its monitor data. Can't wait until all databases are on 2016... oh well... guess I'll be retired by then.

  4. Live Query Statistics

    I've been playing with this one a lot. Since it works with SQL Server 2014 (what? Yep!) as long as you're using SSMS 2016+ you will get Live Query Statistics for SQL Server 2014. I've been showing this one in my Performance Tuning for Mere Mortals sessions and pre-cons with Ed Watson. IT is a beautiful thing.

    Did you ever have someone bring you a @#%! stored procedure that takes forever to complete? You would have to wait hours for the graphical execution plan to complete in order to see where the bottleneck is. Not any more! Now you can see the operators percents, and information as the query is running.

  5. Dynamic Data Masking!

    Ok, there are a lot of issues in V1, you can easily get around the dynamic data masking, and can accidentally overwrite the actual data with the mask... but it is a good start. I won't use it in V1..until they have the issues worked out, but nice start.

  6. Row Level Security... woohoo.
    1. Now were getting somewhere with security. We can control what a user or group has access to at a row level. This would have been very useful a few years ago for several projects I had. Maybe. I still need to evaluate it closely to determine if it will be beneficial.
  7. Ok, Data Scientists... R, yes R is in 2016. It too has a long way to go to be fully usable (as I hear).

And it doesn't stop there... but I'm going to have to stop here for now. See you soon!


Every year SQLSaturday Nashville is the kick off of the year of SQLSaturdays (this year #2 behind SQLSat Atlanta BI). Any way, the event is always one of my favorites.

This year we were on a new campus at Middle Tennessee State University in Murfreesboro, TN. I joined a great group of guys Jan. 16th, most I would call great friends many close acquaintances.

 The best part of speaking this year was my session was picked as an hour and a half session. My session on Execution Plans needs at least 90 minutes to cover the most important information. I have to skip over so much of execution plans it is hard to comprehend some parts.

When I ask the question, who doesn't know what parallelism is in SQL Server, many raise their hands. These guys are beginners who need to full story to understand the operator in the graphical execution plan.

I like to give the analogy of a law office (my law office). And all of the attendees (the threads) in the room are contract legal assistants hired to redact a line in every case. And the Room Monitor is the synchronization thread. But that is another story for another post.

Some of the sessions I enjoyed was Rodney Landrum's session about the 4th dimension. Awesome. Brandon Leaches, Data Pages, Allocation Units, IAM chains... Oh My!

 It was a good time had by all and the feedback I received was awesome. I love when eople come up at the end and tell me how much the session si going to help them.

Early 2015 I submitted one of my favorite sessions, "Execution Plans for Mere Mortals", that I present at SQL Saturdays. At that time I had presented it 3 times so I had a jump start on what to expect. This is a session for beginners to give them a jump start on how to read and understand what is important about graphical execution plans. 

I've read through Grant Fritchey's book, SQL Server Execution Plans, Second Edition  and Benjamin Nevarez's book Inside the SQL Server Query Optimizer, multiple times. I had to crack out the books to make sure I worded everything correctly and didn't miss anything important. 

Both of these books are phenomenal reads, and are valuable resources for reading execution plans. 

Grant, in the photo on the right, responds to me saying "my execution plans session is better than yours". Just Kidding.

I presented "Execution Plans for Mere Mortals" eight times before I was to present at PASS Summit 2015, from January 17th, 2015 to October 24th, 2015. I was able to fine tune and improve every time I presented, even improving it the day I was to present at Summit 2015, Friday, October 30th at 3:30 PM.

My only fear was my time slot and location. My room was in The Conference Center (TCC)... I call it the annex, because it was so far from the main conference hall. I was presenting the last day and the last session of the day. Certainly everyone is going to be gone by this time? I'm betting on only a handful of attendees for my session.

Boy was I pleasantly surprised when people started pouring in 15 minutes prior to my start time and kept coming until minutes after I started. Even more surprising was when Hugo Kornelis came in and sat in the front row. Hugo is well know for his knowledge about SQL Server internals especially execution plans. Although this made me a little apprehensive, it also was a great opportunity to get expert feedback. I stopped counting attendees close to 60... so off to a great start! 

My presentation can be found here -> "Execution Plans for Mere Mortals"

Having a microphone attached to my body was a little disconcerting due to how loud it was so I had the crew adjust the microphone. It worked well to get some laughs. 

The presentation went fairly smoothly, some very good questions were asked as I was presenting, and there were a few I had to think about, always scary. I had a lot of attendee participation which was exciting. My timing was really good as I finished right on time, kudos to PowerPoint's time tracking. 

As it wrapped up, I had a line in front of the podium wanting business cards and Pluralsight cards, gave away half of my box of Linchpin People business cards... too awesome.

All in all, lessons learned, don't worry about location and time, people will show up if the topic is an excellent topic. Make sure you get plenty of rest (I did). Remember to restate the question being asked so everyone hears it (or get a microphone for the crowd. Ask a friend to take pictures ... I don't have any good photos of my speaking at Summit. :(

I have more work based upon some feedback from Hugo, and I'm planning on putting together an advanced session on Execution Plans.


Part 1 > Part 2 > Part 3

 In the last post, part 2, I talked about the the plan and project for implementing Availability Groups.Today I will discuss the creation of an unattended install.

First, what is an unattended install, well, simply it is the ability to use a "template" configuration file to automatically complete all of the possible parameters required to install, in this case, SQL Server. It is critical to adopt a standard configuration setup, where to put the data and log files, etc., so you're unattended install will configure all servers the same.

 The easiest way to create the configuration file "ConfigurationFile.ini" is to do an install, make sure you chose all of the parameters as you would like them to be saved and used for every install. Once you get to the end of the install, on the "Ready to Install" screen prior to clicking the Install button you will see the path to the "ConfigurationFile.ini". Copy the path and file name from the text box so we can get to it later. You can now cancel the install as you've captured all of the parameters need to install SQL Server.


That was easy!

 Now lets make sure all of the configuration settings are correct. At this point paste the path in File Explorer to open the file in Notepad (or your favorite text editor). It should look something like the following, keep in mind that I would not copy my example as it is for another environment.

 Unattended Install ConfigurationFile.ini

Look at each parameter in the configuration file and change the ones that you want to change. You will want to test the file until it is exactly what you want, then you can put it on a network drive somewhere and run it quiet when you want to install SQL Server.

   ;SQL Server 2014 Configuration File  
; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.  
  ;Accept Terms <- Saves you from having to accept the license terms   
  ; Use the /ENU parameter to install the English version of SQL Server on your localized Windows operating system.   
; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.  
; Setup will not display any user interface. (when set to true)   
  ; Setup will display progress only, without any user interaction. (when set to true)   
  ; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found.    
; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature.  
; If this parameter is provided, then this computer will use Microsoft Update to check for updates.  
  ; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server Data Tools, and other shared components.    
  ; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services.  <-- where are the SPs or CUs?   
; Displays the command line parameters usage  
  ; Specifies that the detailed Setup log should be piped to the console.  <- Good for when the install errors   
; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.  
  ; Specify the root installation directory for shared components.  This directory remains unchanged after shared components are already installed.    
 INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"  
 ; Specify the root installation directory for the WOW64 shared components.  This directory remains unchanged after WOW64 shared components are already installed.   
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server" 
  ; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS).    
; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature.  
; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance.  
  ; Specify the installation directory.    
 INSTANCEDIR="C:\Program Files\Microsoft SQL Server"  
  ; Agent account name  
; CM brick TCP communication port  
; How matrix will use private networks  
; How inter brick communication will be protected  
; TCP port used by the CM brick  
; Startup type for the SQL Server service.  
; Level to enable FILESTREAM feature at (0, 1, 2 or 3).  
; Set to "1" to enable RANU for SQL Server Express.  
; Specifies a Windows collation or an SQL collation to use for the Database Engine.  
   ; Account for SQL Server service: Domain\User or system account. (Remember your password will be clear text) <- these parameters can be passed in the command execution   
   ; Windows account(s) to provision as SQL Server system administrators. (Remember your password will be clear text) 
; The default is Windows Authentication. Use "SQL" for Mixed Mode Authentication.  
  ; The Database Engine root data directory.    
  ; Default directory for the Database Engine user databases.    
  ; Default directory for the Database Engine user database logs.    
  ; Directory for Database Engine TempDB files.    
  ; Directory for the Database Engine TempDB log files.    
; Provision current user as a Database Engine system administrator for %SQL_PRODUCT_SHORT_NAME% Express.  
; Specify 0 to disable or 1 to enable the TCP/IP protocol.  
; Specify 0 to disable or 1 to enable the Named Pipes protocol.  
; Startup type for Browser Service.  

Pre-Installation Script File

I usually create a script that runs prior to the install and one that runs after the install. This will allow you to automate some prep work. For instance, create folders for the data and log files on the drive.

I named my file PreInstallationScript.ps1 and this one contains the creation of the file folders for the data and log files. This script can be used to set many different configurations.

 New-Item D:\SQLServer\SQLData -type directory 
New-Item D:\SQLServer\SQLLogs -type directory 
New-Item F:\SQLServer\SQLData -type directory 
New-Item F:\SQLServer\SQLLogs -type directory 
New-Item G:\SQLServer\SQLData -type directory 
New-Item G:\SQLServer\SQLLogs -type directory 
New-Item G:\SQLServer\TempDb\SQLData -type directory 
New-Item G:\SQLServer\TempDb\SQLLogs -type directory 

Post-Installation Script File

(Uses PoSh script by Eric Humphrey (http://www.erichumphrey.com/category/powershell/)

 # Set the Windows Power Options to High Performance
set-location $PSScriptRoot
#Set Trace Flags 1117, 1118, 3226, 4199
.\Add-SqlServerStartupParameter.ps1 '-T1117'
.\Add-SqlServerStartupParameter.ps1 '-T1118'
.\Add-SqlServerStartupParameter.ps1 '-T3226'
.\Add-SqlServerStartupParameter.ps1 '-T4199'
$compname = $env:computername
Write-Host $compname
Enable-SqlAlwaysOn -ServerInstance $compname -Force
Invoke-Sqlcmd -InputFile ".\AddTempDBFiles.sql"
Invoke-Sqlcmd -InputFile ".\Configs.sql"
Invoke-Sqlcmd -InputFile ".\GlenBerryCriticalAlerts.sql"
Invoke-Sqlcmd -InputFile ".\AlwaysOnNotifications.sql"
Invoke-Sqlcmd -InputFile ".\OlaMaintenanceSolution.sql"
read-host "Press enter to continue"

Then I wrap it Up in a Bow!

I create an UnattendedInstall.bat file to kick off the install. 

 net localgroup administrators /add yourdomainname\SQLEngineSVC 
cd "Z:\Apps\SQLServer2014" 
Setup.exe /ConfigurationFile=ConfigurationFile.INI 


 The unattended install can be customized to your environment using the scripts. I have left out some of the scripts here because the method of calling them is more important that the code.

This concludes Part 3 of this post. Next time we'll look at the Availability Groups.

Part 1 > Part 2 > Part 3


My current client has a very complex environment where up time is extremely important. They are a payment processor handling credit card payments 24x7x365.

They currently are using a combination of SQL Server Mirroring and Failover Cluster Instances for their high availability and disaster recovery across a WAN between data centers.

Current EnvironmentA mirroring failover is known to be a much faster recovery than clustering. This is mostly due to the disk arbitration that takes place during a failover to protect the data on the disk.

For this reason, the client was unhappy with Failover Clustering Instances. They wanted the failover to always be a mirroring failover to result in the smallest downtime.

The Windows Failover Cluster was a multi-subnet cluster traversing both data centers. There were two environments R-710s and R-720s. The R-710s were coming to the end of life. Each had a Fusion-IO solid state drive for the data. The free space on the drives was dwindling.

The mirroring witness resided in Bermuda which encountered irregular network issues causing loss of quorum frequently.

The client decided to rebuild the servers, re-installing Windows Server 2012 R2 so they have a "clean" environment.

The client had the following requirements:

  1. Re-install Windows on the servers.
  2. Increase redundancy.
  3. Use existing equipment.
  4. Keep license cost to minimum.
  5. Failover must be automatic with zero loss of data.
  6. Complete the project with near zero downtime.

 With this information I recommended the following:

  1. Consolidate the two environments, preventing the need to purchase additional licenses1 for the new environment (new licenses hadn't been purchased for SQL Server Enterprise on the R-720s).
  2. Evict the passive nodes from the cluster, re-install, team the NICs for redundancy, create a new cluster (named properly).
  3. Add Fusion-IO drives from R710s to R720s.
  4. Log ship databases to rebuilt machines.
  5. Create Availability groups for each business region.
  6. Flip from Log Shipping to Availability Group.
  7. Tear down the old cluster.
  8. Rebuild the last set of servers.
  9. Add to new Windows Failover Cluster.
  10. Add servers as new availability replicas using PowerShell to minimize downtime.

Each product will have 4 replicas, 2 synchronous (local and across WAN2). Against best practices of only using asynchronous availability mode across the WAN, because the application was unable to handle the prevention of the loss of transactions during failover. The transaction(s) could possibly be committed or rolled back locally but not on the replica. They can't afford for a $100,000 transaction or batch of transactions to be committed at the client but not committed in the database..i.e. the credit card transactions were accepted but not recorded (or be paid for).

1The machines were licensed for SQL Server Enterprise on 2 - 8 core CPUs. With two active and two passive nodes, that is 32 core (16 - 2 pack licenses). They had an extra 2 pack license, so they had to purchase an additional license.

 The client approved the design and the migration plan.

Over the next few weeks I will go into the details about how we migrated the 25 databases with less than 5 minutes of down time. Keep in mind I'm not a PowerShell guru, this is the most PowerShell I've ever written.


This script is very handy to make sure you have your replicas set synchronous and have an automatic failover partner when needed. Put this script in your arsenal!


SELECT ar.replica_server_name, ag.name,
CASE WHEN hags.primary_replica = ar.replica_server_name THEN 'PRIMARY - ' + ar.failover_mode_desc ELSE
CASE WHEN ar.failover_mode_desc = 'AUTOMATIC' THEN ar.failover_mode_desc ELSE ar.availability_mode_desc END
END  as Mode
FROM sys.availability_replicas ar
INNER JOIN sys.availability_groups ag ON ag.group_id = ar.group_id
INNER JOIN sys.dm_hadr_availability_group_states hags ON ag.group_id = hags.group_id
) src
--for name in ([agMISC],[agHK],[agAP],[agEU],[agUS],[agSA],[agPPTables],[agIPAY],[agCSP])
for replica_server_name in ([P-MSSQL01-EF],[P-MSSQL02-DE],[P-MSSQL02-EF],[P-MSSQL01-DE])) piv;

Part 1 > Part 2 > Part 3


In part 1 I started building the framework for moving from SQL Server Database Mirroring (mirroring) to SQL Server AlwaysOn Availability Groups (AGs).

Inventory what you have in all environments, what are you going to need to migrate to the new server (remember this is about moving to new (rebuilt) servers. There are many blogs about how to move mail configuration, sql jobs, users, alerts, operators, database and server configuration, etc., etc.  Don't forget anything. Get the team together to brainstorm all of the things that must be moved and configured.

Complete Inventory

Create a design document. This design document should cover all aspects of what the environment will "look" like after the migration. It is critical to think through the licensing, the availability replica modes, failover  policy, etc. It gives you a chance to think deeply about what needs to be done to configure all aspects of the migration. Know the roadblocks, the requirements for an Availability Group and where AGs will not work. Here are a few:

  1. DTC - Don't do AGs if you're application uses DTC.
  2. Cross Database Transactions - Don't do it! If you have a failover and your app is doing a cross database transaction, the primary could come back in suspect mode. This arfticle will help confirm if you're using cross database transactions.
  3. Domain - A domain is required for the WSFC (that changes in Windows Server vNext).
  4. Cluster - The AG replicas must all be in the same cluster.

 There are more issues you will encounter with Ags... see here.

Determine what the business SLAs are for Recovery Time Objective (RTO) and Recovery Point Objective (RPO) and document in the design document.  You want to be able to tell management how much downtime to expect.

Use Visio to create diagrams, for your own benefit as much as everyone Else's. These diagrams will help keep your head straight with what your design is. It will allow you to plan the number of licenses for management, and it will be your "evidence" after your done when management says, you didn't tell us... oh yes I did, a a matter of fact it is right here in the design document that you approved. Ok, so there is another critical element. Make sure management approves the design document (put a spot for signatures on the cover page), it will prompt them to sign it. Don't deliver the design document until you have tested the migration in a lower environment first. It will expose gotchas that you didn't think about (hopefully you tested prior to getting buy in from management). 

Approved Design

Now that we have the design approved by the client (management), its time to work on the most important step...planning. Without a proper plan not only will you not succeed, but you will probably fail horribly. Let's put that down as a critical component.

Complete Plan

The plan should be a step by step plan with dependencies,  start time of each step (as a calculation based upon the previous step duration), estimate duration step, step status (Completed, In Progress, Cancelled, etc.), responsible party, day of week (calculated from date time column) and step number for quick reference. I usually put my plans in Excel and have a conditional format for the row based upon the status.

This plan should include all steps include server builds, network, stress test, benchmark testing, etc. If you want to be a plan Nazi include the regression testing, etc. Hopefully your company or client has an experienced  project manager for the project. It will take a little longer, but if you have a good project manager your project will have a complete project plan avoiding gotchas at a later date.


I know this is a lot of work, but it will save you in the end. Your job is to eliminate all possibility of accidental downtime and minimize the length of downtime as well as have a highly available environment that works for your company or client. Please take your time and do the work.

Every year PASS has elections for board members to determine who the next directors will be. Being on the board requires a time commitment many are unable to commit to. There are a few who are willing to give up a large chunk of their time to serve the PASS members. We should all applaud the volunteers who take up the calling to help improve the entity us database professionals owe so much to.

 This year two of my friends have been nominated and are fully qualified for this position:

Ryan Adams

Ryan is one of my colleagues at Linchpin People who I have been following and learning from for years (prior to Linchpin People). The folloRyan Adamswing are the top reasons I am voting for Ryan for the PASS Board of Directors:

  1. Ryan is a natural leader with professional experience as a leader.
  2. Ryan is very active in the SQL Server community, being a leader in his local chapter the "North Texas SQL Server User Group", an organizer of SQLRally, president of the Performance Tuning Virtual chapter, member of the PASS Summit program committee, etc. etc. In other words, he's been deeply involved for many years.
  3.  Performance Palooza, do I need to say more?
  4. He's also an active member of his community volunteering for a great program "Watch D.O.G.S" at his kids school.

I aspire to be as involved and impactful as Ryan has been to so many people.

Argenis Fernandez

I've known Argenis for several years. He's one of the guys who will remember your name, give all #SQLFamily members hugs when he sees them and spread his infectious positiveness to all around him.

 Argenis FernandezWhen I had a question that needed answered, he didn't hesitate to give me his mobile phone number to call and talk about it.

Argenis is passionate about helping others, and doing what he can to improve the lives of others when he can. He seems to be involved in everything and has great opinions about many things including PASS.


I know that all of  the candidates would be excellent choices, but for the reasons described above and the fact that I trust the honesty and integrity of both of these guys completely and expect that the PASS organization will be very stronger with them as PASS Directors you should seriously consider them (no, just vote for them).