Importing a Production Database to SQL Server Data Tools (SSDT)

First of all as a Gooner (Arsenal FC Fan) BIG CONGRATS to The Arsenal for going 2-0 over Bayern Munich! And best of luck against Swansea this weekend! COME ON YOU GUNNERS!

On to work stuff… Well it’s been about 2 weeks in my first DBA position and so far I’ve had to make many requested updates to current production databases at the request of developers. It’s been difficult because we have a few Oracle RDBs which are typically filled through replication from SQL Server. Right now I’ve been focusing on setting up a better development structure. Our company has never had a “dedicated” DBA, just the VP of IT managing it. So we’ve been doing all of our development against the production database. When we were small it was probably not a big deal but we’ve grown, just this year, from 3 developers to 6 full time + 4 contractors! So my first and main task as the Junior DBA is to create a better development and testing environment.

The plan is to have 3 databases, Production, Test, and Development. We want them all to start off in sync, so Test and Dev are being recreated in Production’s image. I’m in the process of creating a larger project using SQL Server Data Tools (SSDT) in Visual Studio 2012. It’s been a long process of importing a database (6+ hours in some cases), build failing to due to unknown references, scouring Google for a solution, implementing solution, build failing due to unknown references… Finally I think I’m working towards the final resolution, with the help of a friendly Senior Application Developer from Microsoft. It seems that we have views in Database1 and Database2 that point to Database3, We also have views in Database 3 that point back to 1 and 2. So I’ve gone back and forth creating the reference in 1 and 2 to 3, and from 3 to 1 and 2, but you can only have 1 reference between two databases. Or to be a bit more clear you cannot reference 2 from 3 and 3 from 2 or you get a circular reference issue which SSDT doesn’t like.

So my working solution is to pull all of the views out of the projects, effectively splitting the database projects into Database1_Tables & Database1_Views (which actually holds all views, functions, and anything else that points outward). I’m doing this for each one. Right now I can get the Database1_Tables to build which creates a .dacpac file used to create references. So I think I’m closing in on this thing. I’ve just installed SQL Server 2012 onto my machine, hopefully by the end of the day I’ll be able to publish all of the projects to my machine and hopefully it will actually mirror production!

This has been a two week trial, and I can only hope I am nearing the end!