I was replying to an email when it hit me that this information would probably help a lot of other people as well as the person I was replying to, so how about posting it instead.
The question was how to keep multiple test servers synchronized with Production information.
Now, reasons for having test systems with a copy of your production information can range from anything like getting a valid idea how an upgrade to a new Service Pack or Version is going to go. To just letting users test out ideas about some of the gazillion options in Dynamics and how those options affect how you do specific processes at your company. Do all the reading you want sometimes it is just easier to have a test system where you can change some option and see what happens.
First, let us look at my general over all setup.
Production: Dynamics is running on a virtualized terminal server with a separate virtualized SQL Server with everything split out on its own array.
User Test: Dynamics is running on a physical machine that is also running SQL Server so that everything is on one box. However is has multiple drive arrays, so the OS in on one, the MDF and LDF files, and even the TEMPDB each have separate arrays. (Drives C, D, L, T)
Upgrade Test: Again, both Dynamics and SQL are on the same physical machine, just not the same machine as User Test. Additionally, it has fewer drive arrays, so the OS is on one, but the MDF and LDF files are on the same array. TEMPDB does have its own array. (Drives C, D, T)
Development: Once again, both Dynamics and SQL are on the same physical machine, but it only has two drives so the OS is on one and the MDF, LDF, and TEMPDB are on the other. (Drives C & E)
Demo: This is actually my LT6400 Laptop running Windows 7 x64. Again, both Dynamics and SQL (developer edition on this one) are both installed on the laptop. That way I do not have to have a network connection or anything to do demos at the various conventions and classes I teach. However, my laptop only has one Drive so everything is on Drive C.
Now, I will skip over the process of getting Dynamics installed on the various machines. It is just use the install DVD on the Production Server to create the client install program, and there are already multitudes of posts and KB's on how to do that. Moreover, I would like to concentrate more on how to keep the systems synchronized. However, I do want to note that I do not have the client install create the ODBC connections, I want to do that manually myself.
I will say that when setting up a new Test environment, one of the things that I personally do which seem a little backwards is I create the databases before running the client install. Now when I say create the databases, I actually do not restore Production Databases at this time I just create blank databases with the same names as my Production databases. Note: before doing this make sure you have gone into SSMS selected your server and set the Database default locations for "Data" and for "Log". Then all you have to do is right click databases, click new database, type in the name, and click OK. If you have many databases, you could use the following script to create the databases:
CREATE DATABASE DYNAMICS -- You have to have this one and if you any demo companies do those as well, check out the last two creates. CREATE DATABASE <company database name>CREATE DATABASE <company database name>CREATE DATABASE <company database name>CREATE DATABASE TWO -- It was The World Online years ago and I do not know if the database name changed for Fabrikam or not so, it might beCREATE DATABASE FABRIKAM
Another note on doing this it that the Initial settings like Recovery Model will be pulled from that servers MODEL database.
Now, with our blank databases created let us get to restoring actual Production Databases. Here is the code that I use on all of my TEST systems:
RESTORE DATABASE [DYNAMICS] FROM DISK = N'C:\MSSQL\Backup\DYNAMICS.BAK' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
RESTORE DATABASE [GPCAR] FROM DISK = N'C:\MSSQL\Backup\GPCAR.BAK' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
RESTORE DATABASE [GPGRE] FROM DISK = N'C:\MSSQL\Backup\GPGRE.BAK' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
RESTORE DATABASE [GPJAC] FROM DISK = N'C:\MSSQL\Backup\GPJAC.BAK' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
RESTORE DATABASE [GPNAT] FROM DISK = N'C:\MSSQL\Backup\GPNAT.BAK' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
RESTORE DATABASE [HMAIN] FROM DISK = N'C:\MSSQL\Backup\HMAIN.BAK' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
RESTORE DATABASE [TWO] FROM DISK = N'C:\MSSQL\Backup\TWO.BAK' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
Now note you will need to change this to your company database names and change the location of you database backups. However, notice no move command! Because the database already exists the MDF file and the LDF file from the backup that was on a different drive letter and path will be placed where the database that you are replacing has its MDF and LDF file. Now just create the ODBC connection manually and run the client install.
Wait a second, what about those annoying Dynamics logins that never work after a database restore from Production to you test system.
First, it this is a new test server, you will need to copy the logins, but there are many posts and KB's on how to do that part. The real question here is when you restore/refresh the test databases from Production later how to you easily change every user's password without taking forever. Moreover, it would really be great if it was possible to do so without having to login to Dynamics as SA. You can check out this post for more details, it even links to other post if you really want to dig into the problem. Nevertheless, briefly here is what I do.
First on each TEST server NOT THE PRODUCTION SERVER edit the Dex.ini file and either add the line SQLLoginCompatibilityMode=True or set the setting to TRUE.
Then after a restore run a script that looks somewhat like this:
ALTER LOGIN <User Name> WITH PASSWORD=N'<new password>
MSDB.DBO.SP_SEND_DBMAIL @recipients = <user email address>,
@subject = 'Your new password on the server <which ever server it is>,
@body = "You new password for the server <which ever server it is> is <new password>
When the user logins into that server with the password they just received in their email they will get a prompt to change their password.
Note: Again, I urge you to read all the links in my post about moving Dynamics GP Databases, and especially do not forget to check your Account Lockout Threshold.
The last part is just teaching your users that to go into Production select <production server name> in remote desktop and when they want to go into test select <User Test Server Name> in remote desktop. In addition, for some users it may be easier to just go to their workstation and create two different remote desktop icons with the server name already set and name the icons something like "TEST" and "PRODUCTION".