Sql-server – Move database from windows server 2003 to windows 7/8

sql-server-expressvirtualisationwindows-server

I am working with this company currently running their SQL Express database in a virtualized Windows Server 2003.

What would you experts say about replacing that for a virtualized slimmed-down windows 7 or 8.1?

I am aware this is not ideal but they are not willing to pay for a new server licence. (Please understand that this cost can be a higher concern depending on where you are).

This is a small network with only 8 local users and some RDP users (2 regular users, 2 very casual users).

If I understand correctly, Windows 7 has raised TPC connections limits to 20, but I am not sure how this or other limits will affect an SQL instance on top of it.

The databse is purged at the beginin of the year and then its size is ~1.5 GB, by the end of the year it grows (*) to ~10 GB.

() It grows unnecesarily as there is absolutely not that much valuable data in it, it is moslty logs…*

What would you suggest?
Thank you.

Best Answer

It looks like your question revolves around 3 parts to me:

1-Licensing compliance/support/(security compliance?).

2-Best Practices to serve this.

3-Test it. It's a uncommon scenario.

For #1 I contacted MS Support and they said:

"MS REP: Hello, how may I assist you today?
Ali: at 11:49:20
Hello MS REP, may I use SQL Express in a corporate environment for commercial purposes and will it be supported by MS in that scenario?
MS REP: at 11:50:41
I am glad to assist, as long as you don't go over a 10 gigabyte database, only have a 1 processor/CPU computer running it, and don't need any of the additional features of the full editions, then yes it will be fine.
Ali: at 11:50:55
Perfect.
Ali: at 11:51:02
Let's see what they do when it hits 10gb lol. TY"
You're welcome, how large of a database are you using now?  The server will actually start crashing and failing a bit before you hit 10 GB, so if you are already at 9 GB, it would be a good idea to invest in a paid edition.
Ali: at 11:52:24
that's a great idea, they should do that even before 9GB IMO. do you know if the transaction log also counts as part of the DB Size or just the data files?
Danforth T: at 11:53:13
MS REP: data will count towards the total for as long as it is in the system.  If it gets stored permanently, then yes, transactional logs, applications, other servers, and data files will all contribute to the total.
Ali: at 11:54:22
thank you. thats good to know!"

So according to the rep not only do your data files count towards it but so does the log and temp data as well. You're probably aware of the limitations of express though if you're already using it.

CPU: As far as best practices go, can you give it a really good CPU in your hypervisor of choice since yo're limited on that. Perhaps setup your own log shipping using powershell or another option to have some sort of 'HA' if needed. This is more to ensure you don't have data loss since it seems with so few users and such small database size restraints that restore time might not be an issue.

RAM: You are limited to 1GB of RAM so make sure your execution plan cache isn't full and you work around that, otherwise or also make sure you have fast disks. 10GB disk with 1GB ram means we're back to the 32bit days of optimizing disks. Now we have SSDs so it's a nice cheat.

Disks/IOPS: They don't want to pay licencing? Fine, throw down $200 on a nice Samsung 850 which has excellent life span. Put your tlog and data onto there if they want to be really cheap and lose the tail of the log backups. You'll still get the IOPS and you are doing frequent log backups off disk and preferably off VM, correct? You are because if you aren't, you now will lose everything. Backup on to a disk on another VM all together if possible or a NAS device just in case the actual VM get's corrupted and the virtual harddrive file for some reason isn't accessible. Remember to test restore.

Monitoring: Monitor your data size and disk sec/read and write. Windows Server has optimization in many areas in the kernal that Windows 7 just doesn't have. It could creep up in all places. Grab a good baseline of performance from the old system and compare the performance to the new system.

Test Test Test: Can you replay some of the load on the new VM while you migrate? Replay the production load on the new machine. Put up a new parallel instance and restore last nights backup to then document the security and configuration steps needed so you can script them and repeat it if the VM goes down or you need to redo. Once it's scripted well restore the EOD backup and test with the end users. Have them all go on your new test instance and hammer at it hard. Make load, collect metrics. Have them do the same thing on the current prod server and collect metrics. How's it doing? Good? You're closer to implementing this properly, or as 'properly' as one can get using sql express on windows 7 in prod...

Cover Your Butt: Let the business know this is Windows 7 and might actually cost them more in support over the long term. You might want to collect metrics to show them in 6 months. Let them know their SLAs such as time to recovery, acceptable data loss, and make sure to test your backups/log shipping also preferred. Homebrew log shipping on express also tests tlog backups. If you put tlogs on 1 disk let them know there is a maximum x minutes of potential data loss due to no tail of the log backups.

Related Question