Best Practices for Deploying SQL Server Projects (SSAS, SSIS, SSRS) across domains

Question

Scenario:

• Production servers are running SQL Server 2008 in the domain myDomain.com
• Dev/test/stage servers are running SQL Server 2008 in the domain dev-myDomain.com
• Actual dev work done on local running SQL Server 2008 source controlled using SourceSafe

First of all, does this setup/environment make sense? And, what are some good ways of deploying projects including SSAS, SSIS, SSRS from local to the dev servers and finally into live?

Is including the output (compiled) files in SourceSafe and deploying from there good practice? If it is any ideas?

Or, is going straight from Visual Studio without having the output in SoureSafe better?

Thanks.

Answer 1

Visual Studio has configurations you can set up in order to deploy objects to different servers.

You can also use utilities to copy some around:

DTUTIL for SSIS - delivered with SSIS

RSScripter for SSRS - third party free tool, which is very useful

I would think you would use the deploy tool in Visual Studio for SSAS, as it needs to populate the model. I am just getting started with SSAS, so there may be other tools.

Answer 2

We rely heavily on the use of variables for the items we want to change in differnt configurations and then config files (one each for Dev, QA, Staging, Prod). All SSIS packages and config files are in Source Save.