DBA Test/Development Server Best Practices

These are things I learned from a hardware failure of a test server (you might also call it a staging area). So what’s the big deal? Developers should have all their code checked into source control and nothing critical should be on there right?

Sadly no. These are things you should do on your test server to limit your exposure as a DBA.

1. Treat the server as a production server. This includes transaction log backups, backups of user accounts, SSIS, SSRS, SQL Agent jobs, and Windows Tasks.

2. Trust, but verify. Trust the developers to be following best practices, after all your job is to make the company money and so is theirs. Periodically issue correspondence with the developers to verify that nothing is running in a production manner.

3. Get a good snapshot/system image from time to time. If someone or something destroys your server, you have something to fall back upon. This is especially true when developers are creating lots of dependencies in their code to OS level libraries.

Monitoring SQL Server with Profiler ~ 5 Things to Avoid

SQL Profiler

Everyone loves Profiler and for good reason, it provides you with good data to help you make your SQL Server run even better. To quote FDR, “great power involves great responsibility.” Here is a brief list of things to avoid.

Run Profiler on the same server as SQL Server.

This adds too much overhead to the server, please don’t do it, EVER! Ideally, run it on a test server that has some good system resources to be able to capture and analyse all that data.

Select too many events to trace.

For example, selecting Batch Begin and Batch Completed. Choose only what you need.

Don’t filter the results.

Filtering saves you time crawling through the results and saves SQL Server time. This falls back on number 3, choose only what you need.

Don’t join the Performance Monitoring data with Profiler data.

Why chase down a problem, that is not the real cause of your slow server? While you may see a small performance boost on the server, you may be missing the elephant in the room.

SQL Server String PadLeft

I think this comes up a lot in day to day operations. Needing to pad the left hand side of a char/varchar field. T0 address this I made a handy dandy function that works similar to the padleft string function in .Net. There is a caveat to this code, if string length also sets the max length of string being returned. Below is the code, please test it for your application before putting it into production to make sure it has the behaviour that you need.

CREATE FUNCTION [dbo].[PadLeft](
    @StringLength INT,
@String VARCHAR(1000),
@PadChar CHAR(1)
    DECLARE @Result VARCHAR(1000)
    DECLARE @PadLength INT;
    SET @PadLength = @StringLength LEN(@String)
    IF @PadLength <= 0
        SET @Result =LEFT(@string,@StringLength)
        SET @Result =REPLICATE(@PadChar,@PadLength)+ @String
    RETURN @Result

SQL Server Coalesce

Coalesce is one of those old functions that has been around for a very long time and lost some of it’s lime light. It is good for a number of things but it’s VERY important to know how it behaves.


So coalesce takes any two or more parameters and takes the first parameter which IS NOT NULL and returns that value. For example:

SELECT COALESCE(null,’  ‘, ‘value’);

This would return the second argument, ‘ ‘, not the value ‘value’. You need to be very careful with this! Sometimes programmers don’t recognize the difference between null and empty values. If that is you, I have a very good article discussing null values in SQL Server, listed below:

SQL Server Null Values ~ Into the Void (pun intended)

SQL Server CTE ~ Hooray for Common Table Expressions

What is a SQL Server CTE?

CTE stands for Common Table Expression.

A CTE is basically a subquery. So why is it special? This is why they are good:

  • Separate a subquery from the body of the main query.
  • Cleaner code – CTEs enable you to separate your logically congruent code segments.
  • Can be used for improving the execution plan of your query.
  • Can be used for recursive, hierarchical queries.

When aren’t they good? Almost never. Basically, if you don’t need a subquery don’t use a CTE. Also don’t bother using a CTE when you are performing a query with resources outside of SQL Server, there are negative performance issues in SQL Server 2005, but is fixed in SQL Server 2008. Here is everything you will need to know about CTEs.

CTE Basics

Books Online

Recursive CTE

SQL Server Reporting Services ~ Cascading/Dependant Parameters

This is my first screencast, enjoy!

A couple things to ALWAYS enable on your SQL Server

Trace flag 1222 – This little bugger will record deadlock information for you. A deadlock can occur when two transactions are trying to use the others’ resources and SQL Server can’t decide which transaction to run first. Enable it by either issuing the command DBCC TRACEON (1222,-1) or inside SQL Server Configuration Manager, select the SQL Server instance and add -T1222 to the startup parameters. 

Torn page detection – Torn page detection will allow you to see if any pages in your databases are corrupt. This is an indication of a physical hardware issue either on your hard drives or your disk controllers.


SQL Server Reporting Services ~ Ordering Parameters

Did you get the error when previewing: FORWARD DEPENDENCIES ARE NOT VALID

In SSRS 2005 it was relatively easy to change the order of the parameters, just right mouse click on the report and click on Report Parameters. But they “moved the cheese”. Once you know where it is, it is fast to change. It is now in the Report Data tab. Click on the parameter to move. Up/Down arrows then become highlighted. You can then use those arrows to change the parameter order.


Canceled By User ~ Debugging CLR Objects in SQL Server and Visual Studio

So you enabled CLR debugging on your TEST SQL Server (don’t ever do this on production!)

You are trying to debug but you get a message “Canceled by user” in the Output window in Visual Studio. Here is the answer, you have to enable the ports on BOTH your workstation and the SQL Server machine.

Add the following inbound exceptions to both machines:
TCP: 135
UDP: 400,500

Add the program devenv.exe to your exceptions list in your firewall.

This will worked for me. Let me know if you had a different solution.

SQL Server ~ Creating a CLR User Defined Function Error 181

Cannot use the OUTPUT option in DECLARE or CREATE FUNCTION statement

So this may or may not trip you up. If you are like me, you want thing to run fast. One of those ways is to pass a reference to a variable as opposed to passing a copy of the value to the function. In .Net this works well, however SQL Server translates the byref to it’s equivalent which is OUTPUT option. Simply bite the bullet and change the function to have all of your parameters to byval and you are good to go.

Hopefully I saved you some time and agrivation.