March 8, 2011 at 7:51 am
Hi all,
Im currently moving a lot of DBs from 1 server to another, shortly after this for reasons out of my control, i will then have to move all the dbs to different set of LUNs on the SAN.
So my question to you is. Once you've moved a DB from one location to another, what checks or things do you run?
At present i have been doing:
Checking that autogrowth etc is what i want it to be
DBCC CheckDB ()
Full Rebuild of all Indexes
Anyone have any suggestions from passed experiences?
Thanks
S
March 8, 2011 at 7:56 am
Update stats.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 8, 2011 at 8:14 am
None unless you're moving one version to another.
Update stats needs to be done when upgrading from SQL 2000 to SQL 2005. If you're moving from one SQL 2008 server to another SQL 2008 server there's nothing you need to do. Indexes and stats are included in the database and go along with it, as are the autogrow settings.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 8, 2011 at 8:27 am
I would also keep an eye on disk IO - especially if your new SAN has inferior hardware or is shared with other systems.
Nathan
March 8, 2011 at 9:17 am
Beyond validating connectivity and running the DBCC checks, no, there's nothing to do at the database level.
As far as the server goes, you're going to want to migrate all your maintenance jobs and whatever else you've got running through SQL Agent.
"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
March 8, 2011 at 9:28 am
Hi all
thanks for the replies 🙂
Forgot stats thanks Jim, apart from that it seems im not missing anything to drastic, I will be creating new Maintenance plans etc so no need to bring them over, backups are done via DPM so no need to worry about adding those jobs.
Thanks all
S
March 8, 2011 at 9:36 am
Anthan has the best suggestion I've seen. I might do some metrics, maybe a large copy to the disks or something to test the current performance, and then another test later. You might not be able to do much, but documenting things at least will allow you to make a case.
March 8, 2011 at 10:46 am
For the IO testing mentioned by Nathan and Steve, i have done some testing of IO, Im really not sure how is best to do this and how to read the results.
I ran SQLIO using the below link as a guide
http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO
Ive compared the results of the current LUNs for the 2005 server and the new ones for 2008 and we have a nice performance boost from that (2005 was on a 12 disk RAID 5 but the disks where shared with our File Server, Now we have 3 RAID 10 for Data/Tempdb/Logs)
Someone else in the department has ran SQLIOSim and SQLStress. But i havent seen the results, Nor do i know how to read SQLIOSim results to be honest (If anyone can send me to a decent link on the subject id be very grateful!)
Ive also taken some perfmons of IO, queue length etc on the current SQL Server to compare to the SQLIO results and monitor the new SQL Server.
For the SQLIO results the only way i can see to read them is to compare them to something (Ie do the same test on old and new SANS see which has better results) is this right or should I be reading from the results than that?
Any other suggestions for IO testing would be greatly received, we have a lot of decisions at work at the moment about the performance of our SAN and no one really knows or is stepping up to get the answers.. Im willing to do it but not sure where to start!
March 8, 2011 at 12:45 pm
I am no wizard but thats how I use it. SQLIO can give you proof of a performance boost when you are messing around with different configurations. If you're in the mood to experiment this can help you "tweek" your SAN/Network/etc hardware for maximum performance.
Comparing the SQLIO results between the old and new SAN configurations is probably your best bet. Naturally if your new SAN is showing better throughput and lower latency then your new server is going to be happier.
Unfortunately its hard to simulate live-load in any shared resource environment. If your sql server is humming away during peek hours happily and the daily backup starts creating a 100 gigabyte file on your SAN from another system you might start seeing IO waits (pageiolatch* for example). The frustrating thing about that scenario is you probably will not see any significant IO in the SQL Activity Monitor since the resources are being consumed by a system other than the sql server. If you can monitor the IO usage on the SAN side you can get a pretty confident average of available IO bandwidth. It might also help you plan out the best time to run SQL jobs and maintenance schedules too.
Beware the "New SAN Fever". IT guys always like attaching new systems to new SANs. 🙂
Nathan
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply