Development environment for SQL Server databases, gotchas!
Do you have any criteria for your Development Environment within the SQL Server platform?
Do you perform end-to-end testing whenever a new hotfix or service pack needs to be applied on the SQL Server?
So think about having seperate Database Development environment where it is like to like with Production environment, in other words call it UAT - User Acceptance Testing platform. When it is a development platform the users will have a light weight concern to perform any changes, this is wrong in terms of business continuity and you should keep it relatively simple and out from problems.
Say having recommended hardware is better to ensure the performance and load testing is not compromised, so using Virutal based servers is quite common now a days in special using VMWare & Microsoft Virtual Server methods. Before that you should consider the available techniques and should not allow all the users with higher privileges in terms of security & access. So going deeper down I understand that this is a broad question with various answers. Having a development environment is always a good idea. It allows you a place to play and not disrupt production. So on SQL Server perspective the developers (always) generate change script (T-SQL) in development, and then the DBA's implement that changes, by running the generate T-SQL scripts in Quality Assurance and Production - this is what I see a common approach of implementation. By default you should not allow Developers to access in making changes to the database schema in QA and Production platforms.
Not to mention about the strict controls you need to keep in place in order to save/keep consistent on the code versions. As for software to manage both environment, that is really not my expertise. Although I know some developers are placing the T-SQL change scripts into Visual SourceSafe or PVCS. So going down this route, keeping the code in Development in sync with production is normally done manually. But periodically we copy our production database back over to development. In an ideal world you would keep a create script that drops and recreates all the objects in a database. Then you would have a script to populate your tables with clean test data. We also have had issues with keeping dev clean and QA and production in sync. I did come accross tools from Redgate that not only keep the schema for the database in sync but you can select tables and their data to keep in sync too(this is great for static config data).
We will keep update of this blog, as time comes by lot of useful tools are coming up to help in this regard.