There are many reasons you can have different results in your production environment than in your development environment. In database applications, two major causes are differences in database schemas and differences in the data. Application code and user differences can also causes differences, but these are out of scope for this discussion. This discussion is limited to differences in the data and the database schema.
Using nursing as a paradigm, before diagnosis comes assessment. A thorough assessment must be done before an accurate diagnosis can be made. A proper assessment requires the proper tools. There are tools for comparing database schemas and tools for comparing data. Today I’m focusing primarily on database schema comparisons. Schema comparisons are usually easier to assess quickly and thoroughly than data comparisons. Because of that, I recommend starting with a schema comparison before performing a data comparison.
Ideally your database schema is version controlled, you have the latest version of Visual Studio, TFS, and maybe Red Gate’s SQL Source Control. You may even have Red Gate’s SQL Compare, a tool I have used extensively with great benefit at multiple clients. As Peter Schott points out below, using SSDT is a really great idea, assuming you are allowed to download it, which isn’t a given in environments with extremely tight control of admin rights. If you have all of these tools, you probably already know how to compare database schemas. As a consultant, I know that most shops aren’t doing version control of their databases. Many small shops don’t have Visual Studio at all. They also may not have a budget for any software purchases, no matter what the ROI is. Rational thought does not always prevail in budgetary matters.
I’ve seen just about every workaround possible at low budget clients, not all of them good. You don’t have to have two instances of SSMS open side by side to look at the production machine’s version of a stored procedure and the development machine’s version. You can split SSMS into two vertical windows within a single instance of SSMS. Go to the SSMS menu bar and select the Window submenu to split the query window vertically. Most of you know this, but I’ve been asked “how did you do that?”. I respect anybody who asks a question in order to learn.
Figure 1. SSMS with a vertical split showing development code on the left and production code on the right.
The split window approach has limitations. You need to already know where you should be looking and which object(s) you should be comparing. If you are so lucky as to know that, then the amount of code needs to be very small. Visually scanning from left to right and back again ad nauseum is inefficient if the number of statements to compare is large. The risk of missing a difference is significant.
If you don’t have any first class tools to compare your schemas (Visual Studio has a good facility for comparing code versions), I recommend saving the results to files. In the following example, I save each of the stored procedures above to files. Use the free WinMerge differencing tool to compare the files. It is available as a portable application in case you are prevented from installing applications on your machine.
Figure 2. WinMerge comparison of the two script files from SSMS.
It’s much easier to see differences when using a differencing tool. Your time is valuable and you need to use the right tools to be efficient and effective. Scripting an individual object to a file only works well when you have a small number of objects and you know which objects you need to compare. There is a feature to script your entire database schema from SSMS into one file. You can compare one schema’s file to another schema’s file.
I have had people tell me that they tried to script their database schema to a file but it only scripted the database file creations statements and none of the objects inside the database. That’s not the option I’m talking about here. Scripting the objects that are in the database is a different menu option altogether.
Figure 3. This is NOT the option to script all of the objects in a database.
Figure 4. How to script all of the objects in a database.
By default when database objects are created using this wizard, a descriptive header appears before each object. The header is a SQL comment that includes the date and time the object was scripted. That’s a problem because you won’t be creating all of the development object scripts at the exact same time you create the production object scripts. You’ll have a false positive difference for each object, which is quite annoying. Fortunately you can used the Advanced button on the third screen of the scripting wizard to prevent this problem from occurring in the first place.
Figure 5. click the Advanced button to suppress the object creation comments.
Locate the Include Descriptive Headers and set it to False.
Figure 6. Set Include Descriptive Headers to False.
Once you have a script for the development schema and another for the production schema, use WinMerge to do an entire database comparison, assuming you don’t have one of the first class comparison tools mentioned earlier.
Just to clarify, you should use a proper database comparison tool to troubleshoot problems that may be caused by unintended database schema differences. But if you don’t have the proper tools or you’re not allowed to download and install SSDT (there really are companies that have such highly restrictive policies), scripting the schema to a file and using WinMerge is an acceptable alternative that works sufficiently well. I highly recommend that after deploying schema changes to a production database, you should use some sort of process to compare schemas to make certain that the changes made were exactly as intended. Your primary comparison should be to compare the production schema before the change to the production schema after the change. If you find unintended differences and can’t resolve the discrepancies quickly, you can obtain a resume template from here.