What is the best way to move Sims & FMS SQL databases to a new server.

[edutech4schools]

Morning all. I was going to move the SQL (Sims & FMS) databeses from a Windows 2008r2 server running SQL 2012 Express, to a new Windows 2012r2 server also running SQL 2012 Express, simply by restoring the backed up database files but @matt40k mentioned something about a migration tool in another post, although it was not mentioned in the context I am using.

 

I will be leaving the Sims, FMS and Document Server files on the old server for the moment and will only move the databases, but will use a re-director.

Currently the Sims and FMS databases are backed up using a script.

 

So I am wondering, what is the correct way of moving the databases to a new server?

[matt40k]

Migration tool was for upgrading from SQL 2008 > 2012 > 2014. Not server moves.

[edutech4schools]

OK Thanks. So am I OK simply importing the backed up database or should I use Sims Database Management Utility (dbAttach) to detach the database and then re-attach.

 

I would prefer using the first option as it leaves the original databases in place should something go sideways.

[cullingsh]

Contact Capita for guidance docs - they will be able to send them for server-server migration. Depending on your setup there will also be other considerations, eg clients being able to find the new database server.

[matt40k]

I would prefer using the first option as it leaves the original databases in place should something go sideways.

 

Just be careful, if you don't update all the ini files you could end up with some people still using the old database. So if you do, do that, stop disable the SQL service.

[edutech4schools]

Contact Capita for guidance docs - they will be able to send them for server-server migration. Depending on your setup there will also be other considerations, eg clients being able to find the new database server.

 

Thanks. we already use redirect script so should only need to update the Sims / FMS connect.ini on the server once for all clients to pick up the new SQL server.

 

Just be careful, if you don't update all the ini files you could end up with some people still using the old database. So if you do, do that, stop disable the SQL service.

 

Spot on. Was going to disable SQL on the old server asap.

[steveg]

Use the dbattach utility to detach from old and re-attach to new. Lots of things don't go well if you just restore a backed up database (user accounts for example)

 

I guess you could use the upgrade utility and use the opportunity to move to sql 2014 at the same time? Could potentially save you another job in the future.

 

Steve

[matt40k]

Use the dbattach utility to detach from old and re-attach to new. Lots of things don't go well if you just restore a backed up database (user accounts for example

 

Seriously? They haven't fixed that in dbattach? People are still either doing that or manually running the SQL command to transfer the logins? Next you'll be telling me you still can't us dbattach to restore SOLUS3 databases?! Seriously @PhilNeal, one of your devs could (code) fix it in 1hr.

[edutech4schools]

I guess you could use the upgrade utility and use the opportunity to move to sql 2014 at the same time?

I have been asked by LA if we can stay on same version as them.

 

Use the dbattach utility to detach from old and re-attach to new. Lots of things don't go well if you just restore a backed up database (user accounts for example)

Now you have muddied things. What can happen with the user account? What if I use the dbattach and it borks. I am left with no working databases.

[matt40k]

If you use SIMS SQL logins the passwords are stored in the MASTER database. DBAttach should transfer\backup the logins with passwords from MASTER into the SIMS\FMS database when it creates a backup and\or detaches the database, then when you reattach\restore it should restore\transfer the passwords from the database back into the MASTER database.

 

If you use the more Windows Trusted, the password is stored in AD and your golden... so long as you don't change domain

[steveg]

 

Now you have muddied things. What can happen with the user account? What if I use the dbattach and it borks. I am left with no working databases.

 

I've never known it go wrong using the db attach utility. To clarify what I said earlier, if you use sql management studio to do a backup, and then the restore on the new server there are often problems with user accounts etc. Using the db attach utility at both ends of the process and it all goes swimmingly! You can always re-attach the database back to the server you took it from after you have copied it if things go wrong on the new server.

 

Steve

[vikpaw]

A restore should work fine. It's the other related items and installs that may need looking at , such as the doc storage, b2b links / software, anything else installed on the old server or elsewhere which points to it. The more third party apps you have linking in, the more you need to consider.

 

There are a few threads here on the topic, so you can gather a bunch of caveats.

 

The best thing about doing a restore, is you can do that and test it works. The other links, depend on how they work, but you can set up a new server somewhere and if you're careful setup a client to point to it, and at least prove to yourself the basic idea works. This is how i run our test server for testing updates. If you virtualise the whole lot too, it's even easier to play around.

[edutech4schools]

I was going to use the DB attach utility not SQL studio manager. I was not going to us dbAttach to detach and attach a database but further down on the page you can backup or restore a database.

 

We use a script to backup the databases which uses dbattach to run the backup.

$sims_path = "C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn\dbattach.exe"

 

Does this mean we should be good to use the backup and the user accounts should always be in our backups? or am I running in the wrong direction?

[vikpaw]

I don't believe that you can rely on a dbattach backup to copy the logins. A detach, reattach cycle will. However, as i said, you can do a restore of that backup and test to see.

 

There is also a thread around here that describes the SQL code to manually push the logins into the database. I did have a change request to make the code available as a commandline parameter but it disappeared.

[edutech4schools]

Thanks for all the info so far.

 

The reason I am worried about doing a detach is last year I was at a local get together of techies and someone mentioned they did a detach, but when they came to do the re attach on the new server it gave an error. It also would not re attach to the old sever. The LA could not help and it got escalated to Capita.

[edutech4schools]

Just so I can understand how Sims works a bit better can you explain how long the database will keep the users merged in its database? The reason I ask is last year I did run the script that @vikpaw mentioned and then a few months back I also migrated from SQL2008r2 to SQL2012 which should have merged the users. With this being a small primary and not many Sims users being added would it still have those uses or does it lose them from it's database after an amount of time?

[matt40k]

It's the passwords which is the issue, if you haven't added anyone and no-one has changed their passwords it'll be fine. If they have, you'll need to reset their password. Unless you run that command again

[vikpaw]

Sounds like a restore will be fine. Anyone that can't login you can reset.