Moving SQL databases from one server to another

We have a poorly performing SQL 2005 standard server.  So, I created a new server and want to move the databases from the old server to the new one.  Please answer both of the following questions:

1.  How should I proceed and what are the risks?
2.  What settings should I change from the default for best operation?

Please keep in mind that I know next to nothing about SQL.
tscdAsked:
Who is Participating?
 
Spot_The_CatConnect With a Mentor Commented:
By the looks of things the easiest and safest method for you to migrate the database would be to get a SQL database administrator/contractor to come and do it for you. At the same time you could watch what they do and then you may be able to understand what it is we're getting at. As it is I think Angel's right it's probably best that you don't attempt this on your own.

1.  Can you outline the steps to take the database offline, back it up, and move it please>
to take a database off-line - right click the database in Enterprise Manager and then choose All Tasks> Take Offline.

2. Explain "load permissions by running SQL scripts and check permissions are correct."
In a similar way you can generate SQL scripts for permissions and then you can run those scripts in Query Analyser.

3.  I want to move the transaction logs to a seperate set of disks.  how?
When you create a database you can set where the database files and the transaction log files are kept. You can also access this information if you right-click the database and choose Properties. As long as you've set your new server up with seperate spindles (physical disks) then it'll speed things up by spliting the data and logs.  However I very much doubt that you've got anything other than a flat raid 5 configuration. These days you loose so much space by utilising a single drive for transaction logs and also you'll loose some resilience by not running raid 5. In which case this won't make any difference so probably not worth worrying about at this point.


As I said earlier 'First are you certain that the new server will resolve the performance problems.' and Angel has also said if you move a database that's slow to another server it may well remain slow.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>1.  How should I proceed and what are the risks?
transfer the logins as first step.
then, backup the database(s), copy the backup file(s), and restore on the new server.
once that is done, use the sp_change_users_login procedure to fix mismatches between the logins/users on the new server.

>2.  What settings should I change from the default for best operation?
* ensure the allowed memory is NOT set to the full RAM available on the server
* ensure you have proper indexes in place
* ensure you have proper backup in place
* ensure you have up-to-date statistics on the tables
* ensure you have enough memory
* ensure you have fast disks
* ensure you spead the disk/io among as many (physical) disks as possible (check out filegroups)
* disable the parallel query execution by default

0
 
Spot_The_CatCommented:
It all depends upon how big the database is, how long you can have it offline for, how busy (transactions per second) the database is and whether you'll need to re-configure clients.

First are you certain that the new server will resolve the performance problems.

Myself I'd probably:
- backup the database and restore it to the new server.
- you'll need to make sure that you've got the permissions correct on the new database/server. This can be done by generating SQL scripts on the old server for the server, databases and object-level permissions.
- test the applications and assess client configuration needs with the new server.
- Once this is done you should have a good idea how long it'll take to do it live.

- Live migration:
      - take the database offline backup and restore to new server
      - load permissions by running SQL scripts and check permissions are correct.
      - reconfigure clients

That should just about do it. First thing to do is test the new server though - can't stress that enough.

2. Settings in most cases for SQL Server are ok out of the box. If you can have the transaction logs on a seperate disk to the database. There are many other settings to optimise SQL Server and in many cases it depends what you are using it for but it's best to get a baseline before you start changing things.

Hope that helps
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
tscdAuthor Commented:
Ok, thanks for the replies but again I'm very new to SQL, so you guys have to "dumb it down" for me.  Talking about "load permissions by running sql scripts" means zero to me.

Please take another shot at it, and just dial it down a few notches.  Thanks so much.
0
 
tscdAuthor Commented:
Let's start with angellll's post:

>1.  How should I proceed and what are the risks?
transfer the logins as first step.  NO IDEA HOW TO DO THIS
then, backup the database(s), copy the backup file(s), and restore on the new server.  BACKUP WON'T COMPLETE, I'LL HAVE TO TROUBLESHOOT WITH SYMANTEC
once that is done, use the sp_change_users_login procedure to fix mismatches between the logins/users on the new server.  NO CLUE WHAT YOU ARE EVEN TALKING ABOUT

>2.  What settings should I change from the default for best operation?
* ensure the allowed memory is NOT set to the full RAM available on the server - CHANGE THIS ON THE MEMORY PAGE, UNDER SERVER MEMORY OPTIONS.  SERVER HAS 2GB SO I SET IT TO 1.5G
* ensure you have proper indexes in place NO IDEA
* ensure you have proper backup in place NO IDEA
* ensure you have up-to-date statistics on the tables NO IDEA
* ensure you have enough memory I BELIEVE I DO, ONLY 3-4 DATABASES, <100 PERSON COMPANY, 2GB MEMORY
* ensure you have fast disks I DO
* ensure you spead the disk/io among as many (physical) disks as possible (check out filegroups) WILL DO
* disable the parallel query execution by default NO IDEA

And for Spot:

1.  Can you outline the steps to take the database offline, back it up, and move it please>
2. Explain "load permissions by running SQL scripts and check permissions are correct."
3.  I want to move the transaction logs to a seperate set of disks.  how?

Thank you both so very much.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
honestly, with the number of "NO IDEA" responses you gave, with all due respect, I doubt that it is a good idea that you have to handle this server migration...


anyhow, here we go, step by step:

>1.  How should I proceed and what are the risks?
>transfer the logins as first step.  NO IDEA HOW TO DO THIS
support.microsoft.com/kb/246133

>then, backup the database(s), copy the backup file(s), and restore on the new server.  >BACKUP WON'T COMPLETE, I'LL HAVE TO TROUBLESHOOT WITH SYMANTEC
don't use third party tools for this, but straight TSQL BACKUP statement:
tutorial: http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1052507,00.html


>once that is done, use the sp_change_users_login procedure to fix mismatches between the logins/users on the new server.  NO CLUE WHAT YOU ARE EVEN TALKING ABOUT
msdn2.microsoft.com/en-us/library/aa259633(SQL.80).aspx


>2.  What settings should I change from the default for best operation?
* ensure the allowed memory is NOT set to the full RAM available on the server - CHANGE THIS ON THE MEMORY PAGE, UNDER SERVER MEMORY OPTIONS.  SERVER HAS 2GB SO I SET IT TO 1.5G
ok

* ensure you have proper indexes in place NO IDEA
a good tutorial (free login):
www.sql-server-performance.com/articles/per/query_tuning_p1.aspx
another one
www.sqlteam.com/tag/sql-server-query-tuning

* ensure you have proper backup in place NO IDEA
see above with the backup tutorial

* ensure you have up-to-date statistics on the tables NO IDEA
another tutorial (free login, same as above):
www.sql-server-performance.com/tips/update_statistics_p1.aspx
docs:
msdn2.microsoft.com/en-us/library/ms187348.aspx

* ensure you have enough memory I BELIEVE I DO, ONLY 3-4 DATABASES, <100 PERSON COMPANY, 2GB MEMORY
OK
* ensure you have fast disks I DO
OK

* ensure you spead the disk/io among as many (physical) disks as possible (check out filegroups) WILL DO
OK

* disable the parallel query execution by default NO IDEA
take this screenshot:
http://www.microsoft.com/library/media/1033/technet/images/prodtechnol/sql/2000/maintain/ppc0207_big.gif
change from "use all available processors" to "use 1 CPU"
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
PS: you might want to do all this on the source server, eventually it will run much faster so you don't need to move anything (yet).
0
 
tscdAuthor Commented:
All I had to do was detach the database, move the files, and reattach.  Alternatively, I could have taken the DB offline, moved the files, deleted it in the database list, and reattached.  A lot easier than you guys made it out to be, but w/e I got the problem solved.  Thanks everyone.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
huh?

>All I had to do was detach the database, move the files, and reattach.
that is a quick and dirty way of doing, and you need to be "prepared" for getting some side effects later...
if this went smoothly, you probably don't have any special logins, ie only the sa, ie using that in your applications, which is NOT recommended... (security issues).

>Thanks everyone.
interesting that you only put 1 comment in the accepted answer list, you might not have been aware that you can accept multiple solutions?



0
 
tscdAuthor Commented:
He laid it out exactly as I had asked, for a newbie.  Your solution was too complicated.  Keep your audience in mind, esp. when they are telling you they are novices.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>He laid it out exactly as I had asked, for a newbie.  
but only answering q1, and not q2.

also, I claim that my method is not more complicated, although I agree that I did not post directly:
* right-click the database, choose backup database, choose backup to backup device, specify file to backup to, run backup
* copy backup file
* on destination server, right-click the databases folder, choose restore database, choose restore from device, choose the backup file, specify the database name, on the advanced tab,make sure you specify existing folders for the db files, and run the restore.
etc ...

I know that you specified "beginner on this subject", but, if the first suggestion has to be "complete" on the details, entire books would have to be posted. I post the main lines, and when you have detailed questions, I post the clarifications.
see my second post, where I posted the link to full tutorials / support notes of all the questions you asked for.

I don't know what more you would like to ask for.

So, in short, I will leave it there, but please understand that with that attitude, if you postulated at my company, you would have no chance...





0
All Courses

From novice to tech pro — start learning today.