There are a lot of programs that ship with SQL Express these days. For example, when you install development tools like Visual Studio or SharePoint it is common practice to bundle it with an express version of SQL Server. Most of the time, SQL Express is intended to be used for small development databases. However, once a SQL instance is made available to someone they will undoubtedly start using it for other reasons. The major problem with SQL Express is that it has no SQL Agent which is generally used to schedule maintenance jobs like a full backup. Before you know it, you have a mission critical application running off your SQL Express instance with no backups. So how do we schedule a backup without using the SQL Agent? Well, we could shut down the database services and copy the mdf and ldf files but that means we have to take an outage. If the applications running on express are mission critical then this method will not go well with your ... [More]

LINQ (Language Integrated Query) to SQL is a great tool because it allows developers to concentrate on business problems instead of worrying about writing SQL. Unfortunately, generated code typically comes with a catch. My general rule of thumb is that "I never trust any tools that have a wizard or generate code". After all, every time you release code into production you are putting your reputation on the line. Therefore, don't you think it is important to know what your code is really doing under the covers? Since I started getting involved with Database Administration about 3 years ago I have become extremely conscious of the SQL that my code generates. After all, most database performance problems stem from the fact that developers test on empty databases and everything seems to work fine until millions of records trickle into the system. Then ugly problems like missing indexes, functions in the where clause and poorly written queries bubble to the surface. The occurrenc... [More]

In my humble opinion, one of the best features of SQL 2008 is the MERGE command. In a nutshell, the MERGE statement allows you to insert, update, or delete data based on certain join conditions in a single statement. Traditionally this type of work was accomplished by creating a cursor, looping over each row and running some conditional logic to determine if you needed to insert, update or delete some rows. If you view the syntax for the MERGE command with SQL books online you may be a little overwhelmed. The 100+ line syntax sample is not easy on the eyes. Therefore I was afraid that some people may be discouraged from using it, and I felt obligated to give an example that is a little easier to understand. First we will start by creating two tables and mocking up some data to test the MERGE command with: 1: CREATE Table Planets ( 2: ID int not null, 3: Name varchar(25) not null, 4: Comments varchar(50) null 5: ) 6: GO ... [More]

I have known about SQL database mirroring since SQL 2005 SP1. However, I never really had an occasion to use it until recently. I figured while I was setting it up that I would document the steps in case someone else needed them later on. In my particular case, I setup database mirroring in high performance mode with no witness. Therefore the steps below are specific to my needs. However, they can be slightly modified to accommodate other mirroring modes: Pre-requisites Check to see if the servers participating in mirroring are running under the same service account. If not you will need to add theservice account for the principal server as a sysadmin on the mirror instance and vice versa. Make sure the instances are running the same edition and patch level of SQL Server. This can be checked with the SQL Statement: select @@version Also make sure that your instance supports database mirroring. For more information review this MSDN article How to Setup High Performance (async... [More]

Once you start developing enterprise scale applications you will start to notice that things perform a little bit differently. Dealing with VLDBs (Very Large Databases) force a developer to learn about performance tuning. Therefore, I thought it would be good to outline some of the high level perfmon counters for those developers out there who are new to SQL Server. Memory Related Buffer Manager: Buffer Cache Hit Ratio - This is easily one of the most recognized counters in the world of SQL performance tuning. This counter indicates the percentage of data pages found in the buffer cache as opposed to disk. A value of 95% indicates that pages were found in memory 95% of the time. The other 5% required physical disk access. A consistent value below 90% indicates that more physical memory is needed on the server. If you are using SQL 2005 and above then you can also capture Buffer Cache Hit Ratio using the DMVs. This could be useful if you want to store values in a table and then graph... [More]