SQLTeam.com | Weblogs | Forums

Dev and UAT servers


I do not know much about sql server DBA tasks but my next project will be to setup a Dev, UAT environment.
Currently there is a production server.
There will be allocated one server for Dev and another server for the UAT environment.

As far as I know, I should install sql server on each of the Dev and the UAT server, then get a latest backup from production and restore to the Dev and the UAT servers.

1- Is my assumption above correct?
2- What should I consider and do to have a successful setup from start to finish for these environment?
3- Any thing you can add to this for me to learn?
4- is there a link y ou can guide me for this kind of setup for me to study before I start t his project?

kind regards

  1. Your basic assumption is correct: Make the servers, install SQL Server and restore the production Database(s)
  2. Consider Security: First, you'll want to migrate the Logins from the Production environment to the new servers and make sure that the Logins and Users are using the same SIDs. Also, are any rights being granted at the server Role level? Second, define what rights the developers and testers should be granted on their respective servers.
    3a) SQL Jobs: Any Jobs that are part of your solution? Backups: What do you want to do with backups on these new servers? Do they need to be backed up under the same schedule as Production? Perhaps the need is less stringent for these servers since their schema objects can be recreated from scripts if necessary but perhaps the developers can't be trusted to have their scripts in source control.
    3b) Other Server level objects: Any replication to consider? Any Credentials that would need to be migrated? Do you need to migrate any linked servers? If so, what needs to be created and do you want to use the same server as the target? Synonyms: If you're using these, do they need to be changed for the new environment?
    3c) Any SSIS, SSAS or SSRS that is part of your solution?
    3d) Any configuration files that would need to be migrated and potentially modified? There is often something that tells the application where the database is and how to connect.
    3e) Any hard coded references to the Production server name in the code?

I'm sure that there are others but, hopefully, this gets the engine primed...