We have a situation where one of our database approx. 60 GB in size with transactions running every 2-3 mins 24*7 has to be moved, since the server it is on is going to be decommissioned.
Now, we cannot upgrade the above server to a different environment as its application is compatible with that particular env.
Now, we have 3 similar env, where we thought of migrating this database to. But the challenge is all these 3 exiting DB servers are heavily used. They also have process like transactional replication and log shipping set on them as well. Those servers are all on windows 2003 with SQL 2K5 running on them. I know this should have been upgraded , but for now we want to stick is application does not support the upgrade.
Please suggest from you're experience what can be done here? Should we think to migrate that DB on those heavy servers, using some analysis as moving may require additional storage and CPU or RAM. Or will it be better to cancel the decommission and let that DB reside there?
Edit: I am just looking for recommendations on how to calculate/make a process to meet those parameters say, X amount of RAM/CPU/Storage will be needed if I am going with option of moving the database on the heavy loaded servers
Best Answer
A couple of transactions running every 2-3 minutes and a 60GB database is pretty light IMHO and shouldn't add much load to an existing instance unless it's at the edge of it's capacity.
A couple of quick questions to take into account. You are just moving the database not creating a new instance on the "heavily used" machines correct? On the old instance is this the only database?
I would check RAM/CPU usage on each server. Pick the lowest usage instance of the three and compare that to the usage on the old instance. Remember that the CPU/RAM usage for an instance is more than just that for the database (even if it's the only user DB on the instance). Also that SQL is pretty good at managing multiple databases/workloads.
In the end your best bet is going to be to use a development machine, make a copy of the database there and load test. If everything goes well there then move to the lowest use old instance.
If you have the option to add CPU/RAM to the newer servers then that will be a nice safety net. Move the DB and monitor. If it needs more memory add it, more CPU add that.