[sims] move sims to new server

[ful56_uk]

hello

 

what's the best way to move a sims server that runs on sql 2008 32bit to a new server thats running 2008 64bit

 

the new 64bit server only has sql 2008 installed, the server will be used for fms and sims.

[GREED]

Should be no problem if you are using same versions of SQL, just install new server from scratch (using new installs) and copy the database over. Did it fine (twice) last week in fact.

 

My recommendation as it has always been, is to use SQL Server Management Studio to backup the db, then restore using same tools to the new server. If needed, then use dbattach tools (although this is just what SQL does anyway).

[vikpaw]

Should be no problem if you are using same versions of SQL, just install new server from scratch (using new installs) and copy the database over. Did it fine (twice) last week in fact.

 

My recommendation as it has always been, is to use SQL Server Management Studio to backup the db, then restore using same tools to the new server. If needed, then use dbattach tools (although this is just what SQL does anyway).

 

Ahem, correct me if i'm wrong, but my understanding is that dbattach does something special, in that it moves the sims2sql mapped logins into a db table and then on attach pushes them back into the sql instance (master table i think).

 

Just doing an SQL backup wont do this will it? However there are those stored procedures that can be run to transfer logins, i don't know if this will work on a file that's just been backed up and moved. Thankfully, never needed to do this, but when i migrate to Windows 2008 R2, i'll be wanting to do this, so interested to hear back on experiences from others.

[ful56_uk]

yeah all i did in the end was us dbattach for sims and fms after installing the spring 2010 setups and updating them to current from old server

[Janit]

@vikpaw - Personally, I would always use DBAttach in the process of transferring the sims database to a new instance but as people seem to be getting away with not using DBAttach, could it be because the user info was transferred to the db when the migration to sql2008 took place? If so, maybe people won't have a problem with this at the moment unless they've had any new sims users or reset passwords since the migration. Just a thought.

[vikpaw]

hmm, indeed an interesting thought. @janit.

i don't know whether the stored procs, take the pre-stored user logins and push them to the instance, or whether it can just take any backup even if it's user lists is totally mashed up and recreate new logins. I would presume the former only, otherwise there wouldn't be such a big palava since time began.

perhaps a Gin&Tonic would help... might need more than one...

[splattthecat]

My understanding is that the stored procedures copy the login details from the master database where they are maintained to a table in the SIMS database. When a database is restored using dbAttach this data is then extracted from the table in SIMS and used to recreate the details in the Master database if they don't already exist.

We run the stored procedure to copy login details as part of our backup process each night to ensure any login changes are also backed up.

[GREED]

@Vik I have done the above procedure several (well, many) times in the past with new server migrations, has never been an issue. In exactly the same way that I create a redundant/backup server which utilises backups created directly from SQL Maintenance Plans.

 

It should be noted that this is my way, not Capita recommended!!

 

I always understood dbattach to do just the same, but I am happy to be corrected if I am in error. Would very much like to know others experiences (or someone from Capita!)

[vikpaw]

This related thread is of particular relevance, and the attachment has a very good explanation of the process, though it doesn't mention the stored procs as it wants you to use the interface.

 

http://www.edugeek.net/forums/mis-systems/71629-sims-backup.html

 

It's a shame a dbattach backup doesn't do this transfer logins procedure, perhaps we should log a change request...?

 

@Greed - i really don't know how you're getting your installs to work with existing logins, as it shouldn't work after a restore. I guess the logins that are stored in the db are the ones from a previous detach, and not the most up to date copy and then doing a detach reattach pushes the old ones into the new master. Unless, you are running the stored proc, just before the backup or something like that.

[GREED]

@Vik: I very much appreciate your input on this, as I want to know if I am right, lucky or living dangerously with this, and I especially do not want to be giving bad or incorrect advice! You may well be right in that the passwords are from an old dbdetach procedure, difficult to know.

 

Interestingly, including this procedure in a maintenance plan to backup the database (mentioned elsewhere) would do the trick.

[vikpaw]

@Vik: I very much appreciate your input on this, as I want to know if I am right, lucky or living dangerously with this, and I especially do not want to be giving bad or incorrect advice! You may well be right in that the passwords are from an old dbdetach procedure, difficult to know.

 

Interestingly, including this procedure in a maintenance plan to backup the database (mentioned elsewhere) would do the trick.

 

You're most welcome, especially after it was you that helped me do my first initial test setup, with your guide notes. Back in those days we just dropped logins, and started afresh from the sysman account.

 

Now with the help of that document in the other thread I think we are pretty clued up on the processes. Despite all the reading up, the best thing for us will be to test it out in some virtual scenarios.

 

As i'm sure running the stored procedure is going to be against general Capita advice, i think getting dbattach backup to run it first would solve everyone's issues. Or maybe even asking for that procedure to be a runnable option on dbattach would be good. As an extra parameter, without having to physically detach and reattach.

 

I would suggest you add a new user, and change a few passwords, then take a SQL backup and restore that. I don't think those new passwords will work even though all the others will. Now thinking back when i played with lots of virtual setups and snapshotting, i did often get username/password unknown, or other errors and just thought it was me forgetting the passwords, because i'd change them and reset them all the time for test accounts but in fact it was most likely that i was doing a restore from a file that didn't have the logins stored in it.

[GREED]

Sounds comprehensive enough for me buddy! Of course always the answer is to follow the instructions from Capita (or any supplier), but there are ways to make things somewhat easier.

 

I would like to apologise for any confusion on here or other threads my advice may have caused, and am grateful that a more accurate answer (even using the same principles) has been found/mentioned!

 

G