SQL Server Performance Forum – Threads Archive

SQL Server Standard to Enterprise – Take DB Down??

Question : How to Minimize downtime? Scenario: Curent Production system – 2 CPU server
Target Production system – 4 CPU server Current System Database – Sql Server 2000 Standard Edition
Target Production system – Sql Server 2000 Enterprise Edition Basically we want to move from Sql Standard to Sql Enterprise on a different machine. Is there a way to do the switching with minimum downtime? Steps: 1. Take the Production site down
2. Take a backup of the production db (2 hrs for 30 gb??)
3. restore the db on the new machine (2 hrs for 30 gb??)
4. Point the DNS to the new production DB
5. Perform full text catalog and indexing Q. is this the best approach?
Q. Does differential backup help in this case?
Q. Any other techniques to minimize downtime or do everything online?? thanks
Praveen

Please refer for Backup / Restore : http://www.sql-server-performance.com/backup_restore_tuning.asp</a> and If your company allows to do so its best approach or another thing you can do 1). set up Target Production System 2). down Current Production System 3). set replication between old and new production system 4). while data is been replicate to new system , point dns to new system [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards. hsGoswami [email protected]

http://support.microsoft.com/default.aspx?scid=kb;EN-US;314546
Ceteris paribus, I would prefer sp_detach, copy, sp_attach —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Agree with Frank, plus update full statistics.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Hemant, Frank and Luis – thank you for the posts and for the information. Frank, I have a question – what would be the approximate Downtime using this approach for a 30 gig database with the 2 servers on the same network? Hemant – I have question for your approach. Lets say I do an online backup first.
Restore on the new machine.
Take the production DB down and replicate it to the new DB.
Meanwhile I make a DNS switch. Will there be a risk of data loss? For eg: User xyz, registers as a new user while Production is the old one.
Then we make the switch.
User xyz now tries to make an operation like placing an order.
But since the new DB does not have his registration record replicated yet, he will be refused entry. What would be the average time the production needs to be down for replication operation to complete? I’m assuming the backup to take place for about 1-2 hours while the DB is still online and restore another 2 hrs. (We have a 30 gig database). So we have lost 4 hrs off-peak hours which could be say 10-100 records max.
Keeping it a to a max of 100 records how much time would it take to setup replication and move all these 100 records to the new production? If its less than an hour I can as well make the replication and then take the Production up as we can afford upto 1 hour. Thanks again for the valuable feedback! – Praveen Kulkarni

If you hold complete database backups before proceeding for upgrade and in any failure event you can retrieve the data. Previously during the upgrade of a 50GB took 40 minutes at my end. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thank you Satya. 40 minutes for 50 GB sounds great! What was the mechanism of upgrade? (Differential backups, transaction logs, replication etc) Did you use any tools like ‘Speedlite’ or it was directly from SQL Server for backups? Stupid question but need an answer:
Can I take a backup in SQL 2000 Standard and restore it in SQL 2000 Enterprise directly? Are there any issues that need to be considered? Is an application regression test required? (If it does not change the datatypes or formats or schema I wouldnt do a regression) – Praveen

You can upgrade from SE to EE by deploying setup disk on that server and before hand ensure to take full database backups for user and system databases. Refer to this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;268361 for further information. If I reckon correctly during upgrade for SE to EE it is really easier than what is referred in above link. You can go with by detach and attach method which is documented in books online, but this involves uninstall of SE and reinstall EE afresh. Yes you must test, test and test the upgrade on the test environment with similar setup in order to avoid any issues during production upgrade. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thank you! Thats an option worth looking at. I will soon do some research and post my comments. – Praveen

Can I detach the database from SQL Server 2000 Standard Edition on machine A and
attach to a SQL Server 2000 Enterprise Edition on machine B?

You can do that without any issues. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Excellent! Thank you Satya.

]]>