SQL Server 2016 has added a couple of nice new options to the setup experience. First they added the ability to have multiple tempdb files on install, a nice time saver for later. And now, with CTP 3.0 they have added the ability to enable IFI on install. What is IFI, and how do I…
Category: Installation/Upgrade/Configuration
Patching Master Data Services
I recently installed my first deployment of Master Data Services (MDS). I installed the SQL Server 2014 (Enterprise Edition) MDS front-end on a new server with the intent to use an existing SQL Server 2014 (Enterprise Edition) database engine for the back-end. Unsurprisingly, I ran into some issues, and as luck would have it, they…
FusionIO and the Go Faster Button
Nobody has ever said that FusionIO cards were slow (because they aren’t). Especially if you compare their performance to regular spinning media, or high performance SANs. After all, no SAN will allow you to measure storage write latency in microseconds. Anyone that has had their database reside on a FusionIO card has had nothing but…
Rolling Upgrades With Availability Groups – A Warning
One of the great options provided by Availability Groups, in SQL Server 2012 Enterprise Edition and newer, is the ability to perform rolling upgrades to new Service Packs or Cumulative Updates. The basic idea is that you apply the update to one of the AG secondary servers and then perform a failover of SQL to…
Lock Pages in Memory in SQL Server on VMware – Why or Why Not
Two weeks ago I presented my session entitled “Squeezing Top Performance from Your Virtualized SQL Server” at the SQL PASS Summit in Charlotte, North Carolina. One comment that I made during my presentation that seemed to spark some debates is how I normally enable the ‘Lock Pages in Memory’ feature of SQL Server as part…
Changing SQL Agent Job Subsystem Queue Lengths
In the event you are running a lot of jobs on your SQL Server, or you happen to have a great number of jobs that kick off close to each other and use the same subsystem (i.e. PowerShell) then you might receive a warning in the SQL Agent Error Log stating that the job step…
Good Best Practice Advice from the Pensacola Pre-Con, Round Two
Yesterday, I wrote about some advice that I received at the pre-con titled “Demystifying Database Administration Best Practices” which was presented by Microsoft Certified Masters Robert Davis (twitter|blog) and Argenis Fernandez (twitter|blog) from SQL Saturday #132 in Pensacola, Florida. Here are some more gems: Instant File Initialization is critical for performance, read more from Kimberly L. Tripp (twitter|blog). If you…
Who Owns Your Databases And Jobs?
Quick question, who owns your databases? And how about your jobs? Don’t know? Go find out…now find out without using the GUI. Querying sys.databases will give you the owner_sid. The trouble is that you have to relate that owner_sid back to a user. The same with sysjobs, that sid needs to relate to someone. You…
Best Practicing, the Results Part Dos
This week, we have been talking about running the Microsoft Baseline Configuration Analyzer 2.01 with the SQL Server Best Practice Analyzer for 2008 R2. Today I want to talk about one of the errors that occurred on one of the new boxes I just stood up in a new cluster. You may encounter a prerequisite error stating that the…
Configuration Data Best Practice
After my recent Health and Risk Assessment visit from my Microsoft Premier Field Engineer, I learned some new and exciting SQL Server best practices and I thought that I would share them with you in this blog space. Our topic-o-choice today is all about backing up your configuration data. I must admit that I have…