[sims] SIMS SQL Migration failing

[synaesthesia]

Posting here before I attempt tearing my hair out on MyAccount.

 

So just upgraded SIMS server to windows 2016 and trying to do the same for SQL Server. However the SQL migration utility keeps failing on "Detach SIMS database", one of the last steps.

-----------------STEPS---------------------------
05/04/2018 12:19:39	Info 		START: Backup SIMS Database
05/04/2018 12:20:00	Info 		SUCCEED: Backup SIMS Database
05/04/2018 12:20:00	Info 		START: Copy Database Backup File
05/04/2018 12:20:00	Info 		SUCCEED: Copy Database Backup File
05/04/2018 12:20:00	Info 		START: Install Server Applications
05/04/2018 12:20:31	Info 		SUCCEED: Install Server Applications
05/04/2018 12:20:31	Info 		START: Restore SIMS Database
05/04/2018 12:21:01	Info 		SUCCEED: Restore SIMS Database
05/04/2018 12:21:01	Info 		START: Configure SIMS Database
05/04/2018 12:22:47	Info 		SUCCEED: Configure SIMS Database
05/04/2018 12:22:47	Info 		START: Detach SIMS Database
05/04/2018 12:22:48	 ***STOP*** 	FAIL: Detach SIMS Database
-----------------ERRORS-------------
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
------------------------------------
05/04/2018 12:22:48	Info 		START: RollBack System
05/04/2018 12:22:50	Info 		SUCCEED: RollBack System
05/04/2018 12:22:50	 ***STOP*** 	Detach SIMS Database Failed: Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
05/04/2018 12:22:50	Info 		SIMS migration failed
05/04/2018 12:22:50	Info 		Migration Finished

 

SQL service set as local system, when it does this it leaves the database in single user mode and I have to fudge getting it back (stop the service, start it and then really quickly putting it back to multi-user).

 

Annoyingly I tested all these migrations prior to doing it live on a clone of the machine and all went through absolutely fine with exactly the same steps being followed.

Anyone have any cunning plan for moving forward please?

[pete]

Investigate what's causing the deadlock - there's reports (xml_deadlock_report) in SQL Management Studio that'll give you info on what (or who) is causing it:

 

https://social.technet.microsoft.com/wiki/contents/articles/31280.finding-and-extracting-deadlock-information-using-extended-events.aspx

 

Assuming there's N+1 humans in the building, are they trying to have adventures in SIMS even though you've scheduled an outage?

Do you have any other SIMS components (Discover sync task, SIMS Service Manager) or third-party software (Groupcall sync, cashless catering software) trying to access the database?

[synaesthesia]

Already been on that. Unfortunately I'm not fluent in SQL or XML so all that is mumbo jumbo. One recurring theme from googling around is "This is typical with poorly indexed databases". I wonder what on earth that could mean....

But yes, trying again just now with all services ceased, impero forcibly stopping any new connection attempts on SIMS (there haven't been any however sql management studio is reporting about 10 random users who dont actually have any possible means of doing so)

 

Edit: Scratch the above. It's deadlocking whilst it's in single user mode >

Edited April 5, 2018 by synaesthesia

[Knil92]

have you tried restarting the sql service and trying again?

[synaesthesia]

obviously.

[Meldrew]

Edit: Scratch the above. It's deadlocking whilst it's in single user mode >

 

Is it still in single mode now? I have a vague recollection of this failing as it was already in single user mode by a different user...

 

Meldrew.

[psydii]

So doing this is on my long list (probably for the Summer). Are you following a guide or did you have to plan it out step by step yourself. Either way.. a link to a document on how to do this would be very much appreciated!

[synaesthesia]

It leaves it in single user mode yeah, i have to fudge it back into multi user by stopping the sql service, issuing the multi user command and restarting quickly so nothing else chops in. Capita is now looking into it too, they suspect permissions may be in play. Some of the users logged in seem odd as they should have no means to do so unless they're old/cached connections. Dropping the db offline so they disappear, after a moment or so they come back too.

Crossing fingers!

 

@psydii - I did a migration on a clone of our sims server first, just using the guide on Capita's knowledgebase (search for sql 2016 migration tool). I used it previous for 2014 at another site with no issues, our test last week ran with no issues either. The only thing that really isn't documented is the install of sql 2016 if you';re using full, but that's not really anything out of the ordinary. Capita's guys say use SA authentication rather than windows auth when using the migration tool.

Edited April 5, 2018 by synaesthesia

[Knil92]

if there's a sql server brower service running, try stopping that as that can connect to the sql server and take up the one connection.

[synaesthesia]

All sorted; Capita remoted on in the end and had to basically resort to doing it manually as nothing seemed to clear the deadlock. Strange one, as nothing else should have caused it as it goes into single user mode, effectively meaning only one user (the migrating user) should have the ability to manipulate the database. Clearly something was attempting connection regardless, possibly some other service that was forgotten despite everything else being disabled. Still, can't complain, really couldn't fault their support