MIS Systems Thread, move sims to new server in Technical; hello
what's the best way to move a sims server that runs on sql 2008 32bit to a new server ...
22nd February 2011, 10:13 AM #1
move sims to new server
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.
22nd February 2011, 10:08 PM #2
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).
23rd February 2011, 07:10 AM #3
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).
Originally Posted by GREED
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.
23rd February 2011, 08:30 AM #4
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
23rd February 2011, 05:32 PM #5
- Rep Power
@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.
23rd February 2011, 06:18 PM #6
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...
25th February 2011, 12:38 PM #7
- Rep Power
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.
Thanks to splattthecat from:
jinnantonnix (1st March 2011)
25th February 2011, 01:19 PM #8
@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!)
1st March 2011, 09:11 PM #9
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.
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.
2nd March 2011, 03:27 PM #10
@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.
2nd March 2011, 04:25 PM #11
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.
Originally Posted by GREED
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.
2nd March 2011, 10:03 PM #12
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!
By Max_Power in forum MIS Systems
Last Post: 14th April 2011, 04:19 PM
By marekbrad in forum MIS Systems
Last Post: 6th July 2010, 08:45 PM
By techie08 in forum MIS Systems
Last Post: 12th June 2010, 07:33 AM
By Sunshine in forum Scripts
Last Post: 8th March 2008, 03:18 PM
By Oops_my_bad in forum MIS Systems
Last Post: 4th June 2007, 01:49 PM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)