THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

Dev Advice: Make a Tiny Dev Database Act Like a HUGE Prod Database

Here's an evergreen question. It's a question that never completely goes away. But lately, I've been getting it a few times per week. So I thought it's time to readdress the question, which usually takes some form of the following:

I can't really do effective development on my little dev laptop because our production SQL Server database is 15 gazillionbytes, way too big for my workstation. What's a uber-nerd to do?

Well, maybe they didn't use the word "uber-nerd". But you get my drift, right? The production database is really, really big - unmanageably big for keeping a local copy. So that means the dev either has to create a metadata-only version of the database, which won't produce realistic query plans, or somehow crush their laptop under 15 gazillionbytes of MDF and LDF files.

Actually, you have a better alternative - a clone database, sometimes called a shell database. Here's how I described a cloned database a few years ago here in my Tool Time column for SQL Server Pro Magazine:

In effect, a cloned database includes all of the schema objects of the database (e.g., tables, views, stored procedures), as well as the statistics and histograms (the so-called "statistics blob"). This metadata is quite small by volume but can tell you what estimated query plans look like outside of a large production environment and how those estimated query plans might change when SQL Server is upgraded. Cloned databases are especially useful when the data is confidential, classified, or subject to privacy laws.

The article gives you all the detail you need to effectively and quickly create a small version of a big, ol' production database that produces the same query execution plans as you'd get on the prod server.

If you're struggling with doing development on a big SQL Server database, learn the ropes on cloned databases asap! You'll be glad you did.

Enjoy!

-Kev

-Follow me on Twitter

Published Friday, March 16, 2012 10:30 AM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

GrumpyOldDBA said:

In reality I don't think there is a perfect solution. I've too often had issues where development is done with desktop o/s and tiny databases - something which as you rightly say bears no resemblence to the production environment.

What I believe is that stress/performance testing should occur seperate to the actual development. The clone idea is ok but it won't really contain a subset of data or allow large data manipulations .. for example a number of times I have had to work "upgrades" which have been tested ( by the thrid party ) on their test database. "It'll take no more than an hour" - we run it and 3 days later it's still only 30% through. The conversation goes something like this " So what type of server did you use?" - " My laptop " - " How many rows in your xxx table? " - " 50,000" - "oh - we have 450 million rows "   and yes this is the abridged conversation I had with the company whose previous "fix" to a business critical system had introduced a bug which could be fixed with a simple update - their words not mine.

You're right, the question never goes away.

March 19, 2012 9:05 AM
 

KKline said:

Right you are, Grumpy.  It's definitely NOT the same thing and can't be used for stress/performance testing.  (At least not with the expectation that you'll get realistic results).  But, at least you'll now get realistic execution plans for your SQL - and that's a big step forward.

March 19, 2012 10:22 AM
 

Ben Thul said:

My take on this is: the easier you can make doing this, the more likely it is to be done.  So, here you go: http://www.spartansql.com/2012/03/scripting-out-statistics.html.

March 23, 2012 11:23 AM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement