[sims] De-attaching/Re-attaching SIMS database SQL2012/2014 to SQL2016 - resetting passwords

[DawnL]

Hi

 

I've read quite a few posts now surrounding my query but edugeek posting dates are 2013 and things may have changed.

 

Really appreciate any answers.

 

1. Trying to de-attach a database and it says users are still logged in. How can I safely de-attach their logins through SQL, can I use the kill command? I am aware of the exec sp_who2 to identify these SIMS users.

2. How can I identify active and stop non-SIMS users (ie. third party app connections) - by viewing processes in Task Manager and ending these?

 

These non-SIMS users were Ended and the server was rebooted. I was then able to do a DBattach on SQL2014. I'm not sure how.

 

However, I had the famous Invalid username/password after re-attaching to SQL2016.

 

3. I've seen the command osql -S%COMPUTERNAME%\%NEW_INSTANCE_NAME% -Usa -P%NEW_SA_PASSWORD% -n -Q"exec %SIMSDB%.sims.db_p_transfer_login", floating around. When/if/how should this be used? From my understanding the SIMS logins are stored in the master database (old server) which are not then transferred to the new server SIMS database.

 

4. Are these sort of queries answered in any Capita technical training or do you folks just know it from a standard SQL basis?

 

Thanks for your help.

Dawn

Edited February 6, 2019 by DawnL

[bobsmith]

Hi Dawn

 

The logins are indeed stored in the old master database. The transfer logins needs to be done before the dettach as it transfers the logins into the sims db (from what i remember). There's some really good threads on it here on edugeek.

 

As to item 1, you can block incoming connections then reboot the server, that'll kick em out and keep em out.

 

4. it's 50/50, not every bit of SQL training helps with the way SIMS is set up and the oddities like the transfer logins bit are learnt from bitter experience.

[matt40k]

Why are you deattaching the database? If you are moving server, do a backup\restore. Stop\disable the SQL service. Goes wrong, enable, start, try again later *

 

I'd hope Capita technical training covers, you know, the technical things!! LOL

 

And more to the point, why hasn't added db_p_transfer_login stored procedure as a pre-step to the backup?

 

 

* assuming no one has started entering data into the database you're about to rollback.

[DawnL]

Thanks bobsmith and matt40K, didn't mean to open a can of worms. Will look into their technical training?

Dawn