Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DB Movement Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 12:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
DB movement activity on cluster server...

What are do's n dont's???


The Cluster environment is well set & operational..
So Only Moving DB's to this environment will do???








************************************
Every Dog has a Tail !!!!!
Post #1443448
Posted Wednesday, April 17, 2013 2:34 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:24 PM
Points: 302, Visits: 1,067
runal_jagtap (4/17/2013)
DB movement activity on cluster server...

What are do's n dont's???


The Cluster environment is well set & operational..
So Only Moving DB's to this environment will do???


What do you mean with "db move", exactly? Migrating a database from another SQL instance to the Cluster ?

If that's the case, there are several steps you need to take, plus lot of test for your application. These are not exhaustive , but some of them and what I usually do:

-Backup your database before the move (obvious, but some people don't do it or forget)
-Restore the database on the SQL failover instance
-Change compatibility mode (if old database is lower, like SQL2005, for example)
-Reset logins / SIDs
-Schedule new backups, for the new database
-Schedule maintenance plans: ReIndex, Integrity Checks.
-Leave old one on read only mode. Delete after a week or so
-Test connectivity, point your app to the new database

I would also recommend do a small test for your application. Some applications do not do well during a failover. So you may test that. But if the environment is live and you have database there already, that may be difficult to do.

I also refresh the statistics, but I think that's not needed if you are moving from SQL2005 to SQL2008.

-- What I usually DO NOT DO --

-Delete old database after the move
This is because if there are permissions issues, or a store procedure that does not run, it is easier to troubleshoot and see what's wrong if the old database is still on the other server.

-De-attach and attach
Some people also de-attach , move the mdf and ldf files, and attach on new server, I do not recommend that. We loss statistics I believe, and the database is no longer available on old server (see my previous point)
Post #1443493
Posted Wednesday, April 17, 2013 3:58 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
Yes, my task is to Move databses from one instance sql 2005 to other instance sql 2005..

So what things i have to do & take care of...

in terms of Login & password..
Application?
etc etc..


please help me & suggest..
co this is the first time i will be doing this..

While doing this activity.. their will be no processing on server..
Its a holiday tomorrow so the DATABSE will be untouched...








************************************
Every Dog has a Tail !!!!!
Post #1443522
Posted Wednesday, April 17, 2013 9:37 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
i have to migrate from Sql 2005 to Sql 2005..(Only 2 databases)

1) I will take a backup of two databases & then copy the backup file to new serevr
2) I will restore them to the new server.
3) Will Migrate Login using Login & Password Script.
4) Will schedule all maintenecae jobs which was existing on old server.
5) Will ask developer to point all application to the New database & will confirm it works.

Else what i have to do ???
Do i need to Change compatibility mode???


Please reply soon.. i need to impelment this in 1 hour









************************************
Every Dog has a Tail !!!!!
Post #1443584
Posted Wednesday, April 17, 2013 9:52 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:24 PM
Points: 302, Visits: 1,067
runal_jagtap (4/17/2013)
i have to migrate from Sql 2005 to Sql 2005..(Only 2 databases)

1) I will take a backup of two databases & then copy the backup file to new serevr
2) I will restore them to the new server.
3) Will Migrate Login using Login & Password Script.
4) Will schedule all maintenecae jobs which was existing on old server.
5) Will ask developer to point all application to the New database & will confirm it works.

Else what i have to do ???
Do i need to Change compatibility mode???


Please reply soon.. i need to impelment this in 1 hour



You have a DB migration in a few hours, and you have not planned it properly or in advance? Sorry, but that does not sound too smart.

Above steps, overall, is pretty much what you need. But If you just backup and restore, the only remaining step is fixing the SQL users via script, of course, assuming you create those logins on the new SQL instance too.

The most critical part is check connectivity, so you may ask your developer to run his application and check everything, after changing his SQL connection string in the code or ensure he is using the new SQL instance.

And no, if both versions of SQL are the same, the compatibility mode between both is also the same. You do not have to change it on the new one.
Post #1443586
Posted Wednesday, April 17, 2013 9:55 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
Actually this was very instant requirenment.. i thought they will give me a day or two to do this..
But they asked me to do it in an hour

Anyways thanks for suggestion.. will do as you have told... thanks..
Incase anything i will come back here








************************************
Every Dog has a Tail !!!!!
Post #1443588
Posted Thursday, April 18, 2013 6:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
All went well at Migration... on server (XXX)

One issue i have encountered..

While scheduling the jobs on server (XXX) the jobs are scheduled properly from a local server machine.. & its running fine at scheduled interval..

I just did a re,mote of the server (XXX) & when i opened the Job... its empty
all details like Job name, description, steps & scheduled time all is empty

But when i login on server (XXX) from local server using sa username & password.. i am able to see the details in job

Please suggest








************************************
Every Dog has a Tail !!!!!
Post #1443799
Posted Thursday, April 18, 2013 7:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:24 PM
Points: 302, Visits: 1,067
runal_jagtap (4/18/2013)
All went well at Migration... on server (XXX)

One issue i have encountered..

While scheduling the jobs on server (XXX) the jobs are scheduled properly from a local server machine.. & its running fine at scheduled interval..

I just did a re,mote of the server (XXX) & when i opened the Job... its empty
all details like Job name, description, steps & scheduled time all is empty

But when i login on server (XXX) from local server using sa username & password.. i am able to see the details in job

Please suggest


Ensure you have most recent version of management studio. If you are able to open the job and interact locally, using Management Studio there but not when using Management Studio from your own machine, it may be due Management Studio version or missing patches (for Management Studio, I mean)
Post #1443846
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse