October 14, 2015 at 9:55 pm
Hi,
we would like our SQL Server 2012 (production server) clone, to have a test server. It must be transferred about 5 databases (Schema and Data) and server-/ Database credentials. Is there a guide to implement this (min. cost, fast realization :Whistling: )
Thank you
Regards
Nicole
October 14, 2015 at 11:26 pm
Are you doing this on premises? Or someplace like Azure?
Honestly a clone makes me nervous. Transferring the databases themselves is easy, backup and restore. The credentials is more difficult, however, it is possible to transfer the credentials with SIDs intact so that the exact same credentials exist in both places, BUT I still recommend changing the non-prod environment passwords. You don't want to accidentally connect to prod.
I would add that rebuilding it for dev/test is actually a good test for disaster recovery, you should know ALL the extra "bits" that go into building the server. As well as any special SQL settings.
CEWII
October 15, 2015 at 8:56 am
When you say Clone what do you really mean?
If you are simply wanting to take a prod environment and create a test environment that looks just like it.. then.
Install SQL Server to the same level as the prod box.
use the SP_HELP_REVLOGIN procedure to script out all userids and rights.
Go to the test server and create the databases named exactly as they are in prod
Run the output from SP_HELP_REVLOGIN into the test system.
Backup/restore the databases.
Script out any jobs
recreate any linked servers
recreate any maintenance plans via the wizard.
April 26, 2017 at 7:56 am
Both Red Gate and Windocks are now supporting the ability to clone SQL Server databases. The workflow is to restore a production database into a full byte copy image, that is subsequently available to be cloned in seconds. Under the covers, the full byte copy is actually restored to a virtual disk, which is used as a read-only source for differencing disks (ie., clones). Differencing disks are provisioned in seconds, and only require about 40 MB in storage, and are writable (capture any changes). This is the emerging best practice for delivering production data environments for Dev and Test.
Red Gate's SQL Clone is a cliet server solution, with the server running on Windows Server 2008r2, 2012, or 2016. Windocks has the cloning outlined above built into the SQL Serer containers (Windocks is a port of Docker to Windows). SQL Server containers are named instances that are installed and available on a shared host in about 10 seconds.
April 26, 2017 at 10:26 am
SQL Clone from Redgate, Windocks, plenty of ways to help from third parties. Disclosure: I work for Redgate.
If you just need to move databases often, is backup/restore not good enough? If that's hard, look at dbatools.io and use PoSh to copy databases.
April 26, 2017 at 10:37 am
If you have the right hardware, you can use the disk system to create snapshots of the system and it's fast. Check with your hardware vendor to see if they support that.
Otherwise, you're looking at backup & restore as was already stated, or, you could look to a tool like SQL Clone from Redgate (DISCLOSURE: I work for Redgate). These tools work well. A backup and restore takes however long it takes to perform those functions. SQL Clone works much faster because after you create an image (effecitively a backup, and takes just as long), creating a cloned database takes only seconds (10-15 for up to 2tb). That can make creating a test system much faster than traditional backup/restore operations.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply