We are preparing to migrate to a new SAN and I have been tasked with verifying/validating data after migrating a server.
Other than the DBCC commands that would tell me about data integrity, and comparing against a restored backup on a different server (on the old SAN), what would you recommend to do in order to V&V the migration?
Thanks
ps - I will have instances that range from MSSQL 2000-2008
I'd compare the data using, say, Red Gate SQL Data Compare in addition to DBCC. Comparing all data in all tables row by row means checking every populated page.
However, for larger databases this will a long time, so you can run aggregates over columns (say AVG or CHECKSUM_AGG). I use this to quickly compare results but it would scale and force data to be read.
It really depends on how you are going to perform migration. If you are planning to stop SQL services and copy data and log files from old LUNs to new ones, then there is no change in logical database structure and therefor no need for comparing data at that level.
I would recommend using a good copying program that calculates checksums and verifies that all files have been copied correctly.
In a similar project I used FastCopy http://ipmsg.org/tools/fastcopy.html.en which is very fast and reliable tool.
On the top of that, I would run old good CHKDSK on the new LUNs, just to make sure that there is no errors on NTFS level.
Good luck with your migration
Greg
