So, say you're at a new client site, or maybe even an old client site, and someone brings to your attention that they have this old SQL server that's used almost every day, but the guy who set it up left six years ago... And he/she was the only one set as sysadmin, and nobody knows the SA password. Believe it or not, this happens more often that I could have imagined. I'll share my method of gaining access to a "lost" SQL server. First I want to start the SQL services in mai

Running CheckDB regularly is important for the integrity of your database. It's suggested that we run this as often as possible, so that we know right away if we have a corruption issue. (this is also a good reason to take regular database and log backups). The only problem is that CheckDB is very intensive on the storage of your servers. Not only that, but it can take a LONG time for it to complete on very large databases (VLDBs). This can cause some conflicts with your SAN

In the past we're used to enabling Traceflags 1117 and 1118 in order to help with TempDB Contention. With the release of SQL 2016, these were no longer needed...however Now it's been suggested to enable Traceflag 3427. Why? Because a new type of contention has emerged called "Metadata contention". The quick explanation of this is that Metadata contention is contention on system objects in tempdb that are used to track temp tables. Whenever we remove something from cache, we

What is a session timeout? This is a property that dictates how many seconds an availability replica waits for a (ping) response from another replica in the Availability Group considering the connection to have timed out and thus failed. The default settings is set to ten seconds to wait for a ping response from a replica. This communication only occurs between the primary and secondary replicas. Now, Microsoft does not recommend that you make this setting lower than ten sec

Page Life Expectancy is the number of seconds the average page of data has been in the buffer pool.SQL server keeps data pages in memory for quicker access instead of having to read from disk every time. Now, keep in mind that Page Life Expectancy doesn’t prove or disprove anything on its own. It’s simply a symptom of potential memory pressure. If you think that your server is suffering from some sort of memory pressure, this is just one piece of a bigger puzzle that can help

So, your manager wants you to figure out how to encrypt sensitive Data? Well Microsoft has introduced a fairly easy way to configure feature called Always Encrypted. What is it? Always Encrypted was introduced in SQL Server 2016. It is an encryption feature that is intended to protect select sensitive data such as credit card numbers and social security numbers. The data stays encrypted not only at rest, but in memory and in transit. When should I NOT use it? Not good for en

Why should I use Query Store? Query store can be used for a number of things. For example: Find out what was happening on my server last night? Identify and fix queries suffering from parameter sniffing or plan regression Testing (ie - when upgrading to a new version of SQL server, or development) What happened on my server last night? Query store is great for a historical view into what your server was doing. It's often referred to as "a flight data recorder". There were so

It's becoming less surprising, but no less scary when I find shops not taking regular database backups. This happens quite often in situations where there is no dedicated DBA. Meaning someone else just kind of took over those responsibilities ("accidental DBA", if you will). Why can't I just include the .mdf and .ldf files in a system wide backup? Some of the common explanations I get is that "Well we run a file system backup, so the database files are backed up". Unfortunat

It's amazing that we never had a built in feature that logged historical record of queries. Sure we had the plan cache, but that was cleared with a service restart or reboot. How many times have you walked into an environment and they would say the server was slow yesterday, or at 2:00am last night, what happened? Well, it's tough to pin point if you didn't create some means of logging (hint: try logging the output of sp_whoisactive periodically!) or a third party tool. What

If you're storing any of your database backups off site or in the cloud, you might want to look into encrypting your backups. This feature was first introduced in SQL 2014. Since we're working with encryption on SQL server the first step is to create a Database Master key. Then we create a certificate. Then run your backups with the correct syntax and specify the algorithm you'd like to use (AES 256,128, 192 or Triple_DES_3Key). #sqlserver #backups #DBA #Encryption