An in-depth look at SQL Server Memory–Part 1

I know that memory management in SQL Server is one area that’s a bit of an enigma for a lot of people, and most of us only tend to know as much about memory as is related to our day-to-day activities. In this post(and others in this series), I shall seek to do a deep…

25

How To : SQL 2012 Filetable Setup and Usage

One of the cool things about my job is that I get to work on the latest technologies earlier than most people. I recently stumbled upon an issue related to Filetables, a new feature in SQL Server 2012. To start with, a Filetable brings you the ability to view files and documents in SQL Server,…

19

Why the registry size can cause problems with your SQL 2012 AlwaysOn/Failover Cluster setup

I recently worked on a very interesting issue, where one of the cluster nodes in an AlwaysOn environment became unstable, and the administrators ended up evicting the node from the Windows cluster as an emergency measure. Ideally, since the primary node/replica was no longer available, the Availability Group should have come up on the secondary…

18

SQL 2008–Service fails to come online with “a valid certificate could not be found, and it is not possible to create a self-signed certificate”

You might run into this situation where SQL Server fails to come online (either with a new install or an existing one). Looking at the application event logs, you see these messages: Event Type: Error Event Source: MSSQLSERVER Event Category: Server Event ID: 17182 Date:  05/08/2012 Time:  5:03:40 AM User:  N/A Computer: SQLTest1 Description: TDSSNIClient…

9

SQL Server Cluster Failover Root Cause Analysis–the what, where and how

I know many of you get into situations where SQL Server fails over from one node of a cluster to the other, and you’re hard-pressed to find out why. In this post, I shall seek to answer quite a few questions about how to about conducting a post-mortem analysis for SQL Server cluster failover, aka…

4

Migrating TFS from SQL Server Enterprise to Standard can cause problems due to compression

When migrating a Team Foundation Server from SQL Server Enterprise to Standard , you might run into this error: Restore Failed For Server ‘<Servername>’, (Microsoít.SqlServer.SmoExtended) Additional information: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer ,Connectionlnlo) Database ‘<TFS Database name> cannot be started in this edition of SQL Server because part or…

18

An interesting issue with SQL Replication and a rogue system spid

I recently came across this interesting issue with SQL Replication. We were trying to create a new publication, and the new publication wizard would just hang. Upon doing some investigation, we found that we were hitting the connect article mentioned here. However, the connect article mentions that the bug is closed as “won’t fix”, so…

4

An in-depth look at Ghost Records in SQL Server

Ghost records are something that are a bit of an enigma for most folks working with SQL Server, and not just because of the name. Today, I’ll seek to explain the concept, as well as identify some troubleshooting techniques. The main reason behind introducing the concept of Ghost records was to enhance performance. In the…

16

The most interesting issue in DB Mirroring you will ever see

I recently worked on a very interesting “issue” in DB mirroring, relevant to a very specific scenario. Read on to find out more. Basically, we have a setup which looks something like this: Initial setup with machines A, B and C A principal B mirror C witness Take down the principal A (network disconnect or…

9