One Week Full of SQL Server DTS Development
Posted on May 6, 2006
One Comment
Wow! What a long week it was, almost 70 hours on one project. Figure I’ll jot some of it down just to relive the glory…
We have this in-house built process at work that helps us keep our development and production databases in sync. The wrinkle is that our apps are almost entirely database driven, but we can’t just copy every row of every table. There has to be logic applied on almost every table to get the proper stuff for the proper business channel. So, quite some time ago one of the guys built an export program that emits five XML files and then 5 import programs that takes those XML files and updates or inserts records. The import stuff also does some timestamp magic to spot deleted records. This works well in a situation where the dev and prod db’s aren’t connected.
I then took his processes and wrapped a bunch of batch files around them and, with a nifty little/free job scheduler, automated the process as part of our nightly job scheduling. Run the export on dev, use some dependency checking to figure out when to zip the 5 xml files. Then, from the prod box, check for that zipped file and, if it exists, pull it over and extract the xml files and initiate the 5 import processes.
Over time our databases have grown and so have the xml files. As they grow, the processing time has grown as well. And there… we have problems. We’re up around 8 hours from the start of the export to the end of the last import. If something goes wrong along the way, restarting is very much not convenient. The programs rely on Microsoft’s xml implementations and we’ve become a bit nervous about them as we ramp up in size. Some nights the export job takes 3 hours. Yet other nights it will double that — and we don’t know why. I’d even kill all SQL Server sessions to ensure no locks. When the export would run long, we’d miss the automated window to get the Import going. For quite some time now I get up stupidly early each morning to verify the Import either ran or to get it running.
Long story short, we needed to do something different and we needed to capitalize on the fact that the dev and prod databases can see each other. The process we have been using is technically very sound, but just wasn’t scaling gracefully. Thus, it was time to delve into the world of SQL Server’s Data Transformation Services (DTS). And, I’m pleased to say that after 6 days of mostly heads down learnin’ and buildin’, that same import / export process runs in about 5 minutes. All of the same logic involved on chosing which data to sync. The performance is just simply stunning. And rather gratifying I must say.
Some sites I found useful on my journey this week:
I still have some things to do to make my jobs a bit more portable. In particular, I need to get the global variables working better. Ran into some snags initially and went a different direction. Based on some things I’ve since learned, I suspect I can crack that nut next time I have a chance. With that working better, my packages and logic will be much more portable. Right now it takes me about 30 minutes to port these from one dev/prod pair of databases to another. Yuck.
Oh, two more links to include. My original DTS Packages were working great on db’s hosted on Win2k Server. However, they weren’t running well at all with Win2k3 server. Turns out that was related to my use of transaction management and defaults on 2k3 for the Distributed Transaction Coordinator (DTC). These two knowledge base articles were quite helpful in resolving that before I lost too much more hair.
Final thoughts: We’ve been telling the suits at work that this is a nut we could crack if we just had a week to devote to it. Guess we found that week somehow! Wouldn’t have happened if we had not just hired a couple new developers. And, the icing on the cake, is that one of those new guys was the inspiration to delve into the DTS world to make this work — Thanks Jordan!
Tags: database, microsoft, sql-server
Possibly Related Posts
Comments
One Response to “One Week Full of SQL Server DTS Development”
Leave a Reply
It always rocks my world when I find a solution that works like that, making the life of a company much better and easier…that’s why I do consulting and not work for a company…I’m focused on finding fixes for people, and can avoid (happily
the grudge work that goes with the maintainance crap that I hate 