+ Post New Thread
Results 1 to 12 of 12
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 ...
  1. #1
    ful56_uk's Avatar
    Join Date
    Mar 2008
    Location
    Essex
    Posts
    562
    Thank Post
    105
    Thanked 25 Times in 22 Posts
    Rep Power
    19

    move sims to new server

    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.

  2. #2

    GREED's Avatar
    Join Date
    Mar 2008
    Location
    Portsmouth
    Posts
    3,062
    Thank Post
    375
    Thanked 376 Times in 307 Posts
    Blog Entries
    8
    Rep Power
    177
    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).

  3. #3

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    6,008
    Thank Post
    680
    Thanked 1,397 Times in 1,157 Posts
    Rep Power
    353
    Quote Originally Posted by GREED View Post
    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.

  4. #4
    ful56_uk's Avatar
    Join Date
    Mar 2008
    Location
    Essex
    Posts
    562
    Thank Post
    105
    Thanked 25 Times in 22 Posts
    Rep Power
    19
    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

  5. #5

    Join Date
    Apr 2010
    Location
    Cheshire
    Posts
    17
    Thank Post
    1
    Thanked 2 Times in 2 Posts
    Rep Power
    10
    @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.

  6. #6

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    6,008
    Thank Post
    680
    Thanked 1,397 Times in 1,157 Posts
    Rep Power
    353
    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...

  7. #7

    Join Date
    Sep 2007
    Location
    Somerset
    Posts
    62
    Thank Post
    5
    Thanked 15 Times in 12 Posts
    Rep Power
    17
    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.

  8. Thanks to splattthecat from:

    jinnantonnix (1st March 2011)

  9. #8

    GREED's Avatar
    Join Date
    Mar 2008
    Location
    Portsmouth
    Posts
    3,062
    Thank Post
    375
    Thanked 376 Times in 307 Posts
    Blog Entries
    8
    Rep Power
    177
    @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!)

  10. #9

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    6,008
    Thank Post
    680
    Thanked 1,397 Times in 1,157 Posts
    Rep Power
    353
    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.

    Sims Backup

    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.

  11. #10

    GREED's Avatar
    Join Date
    Mar 2008
    Location
    Portsmouth
    Posts
    3,062
    Thank Post
    375
    Thanked 376 Times in 307 Posts
    Blog Entries
    8
    Rep Power
    177
    @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.

  12. #11

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    6,008
    Thank Post
    680
    Thanked 1,397 Times in 1,157 Posts
    Rep Power
    353
    Quote Originally Posted by GREED View Post
    @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.

  13. Thanks to vikpaw from:

    GREED (2nd March 2011)

  14. #12

    GREED's Avatar
    Join Date
    Mar 2008
    Location
    Portsmouth
    Posts
    3,062
    Thank Post
    375
    Thanked 376 Times in 307 Posts
    Blog Entries
    8
    Rep Power
    177
    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

SHARE:
+ Post New Thread

Similar Threads

  1. Potential move from SIMS to Isams - have you done it?
    By Max_Power in forum MIS Systems
    Replies: 30
    Last Post: 14th April 2011, 03:19 PM
  2. move sims sql database to new partition
    By marekbrad in forum MIS Systems
    Replies: 4
    Last Post: 6th July 2010, 07:45 PM
  3. Move sims server to new domain
    By techie08 in forum MIS Systems
    Replies: 1
    Last Post: 12th June 2010, 06:33 AM
  4. Replies: 2
    Last Post: 8th March 2008, 02:18 PM
  5. sims server move
    By Oops_my_bad in forum MIS Systems
    Replies: 8
    Last Post: 4th June 2007, 12:49 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •