Master database in Single_User mode
-
Everything I read and even a test tells me that you can't put master into Single_User mode. So can someone please explain this to me:
It's a live environment and apparently working, but I'm not free to try change anything at the moment.
On my test environment I've tried to get to this state by first restarting SQL with -m, and doing an ALTER database master set Single_User with rollback immediate (from SQLCMD). I even tested what SQL does if I try set multi_user, but SQL tells me you cant set either of these, at least not on my 2019 test SQL.
So how and why would someone do this?
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated. -
As a guess (no access to or history of your system) - I would say that in some old old version of SQL Server, single user mode on system databases was allowed. So someone thought it would be "smart" to put it into single user mode to reduce the chances of multiple admins mucking around in master and then you did in-place upgrades. Years go by and nobody bats an eye as thing are "just working" but then you notice something like that and it's an odd scenario.
Another possible how would be to restore master from a backup and call it something like "master_2" (not a good name) and have it as a user database. Then put that DB into single user mode. Next, stop the instance, rename the master mdf and ldf files on disk to something like "master_backup" and then rename the master_2 mdf and ldf files on disk to "master". Now your user database is a system database called master.
So that explains two possible "how"'s and a possible "why", but (my opinion) the real "why" is because they didn't know what they were doing OR they were trying to be clever. I also imagine if you did some monkeying around with a hex editor you could probably set a value in the database to force it to start up in single user mode.
Now, to fix it becomes a fun thing too as I expect you would need to go with the second scenario I listed above to get it back to multi user mode OR use a hex editor, but that has some extra steps (need to turn off page verify which I do not recommend) and has some risk (if you change the wrong thing, you could corrupt the whole DB and corrupting a system DB means the instance will not start).
My opinion, if things are working and nobody is complaining, I would document the issue and ignore it for now. When you go to upgrade to the next major version of SQL (I see you are on 2012 in that screenshot, so when you upgrade to 2014, 2016, 2019, or 2022), I would recommend doing a migration install rather than an in-place upgrade. Has pros and cons. Pros are that you would fix that problem and you would be able to clean up any legacy settings that are not applicable and I personally find it more fun setting it up fresh and going through all the options to configure for the system and all the new features in newer SQL versions that I can play with. PLUS it gives you a chance to do good login cleanup. I know my IT dept doesn't always notify me when users leave the company, so my SQL logins have a lot of accounts that are no longer with the company. When I go to migrate them, it fails to migrate users who aren't in AD, so it helps clean up the user logins. Some cons are that it can (and usually does) muck up your connection strings because you can't have 2 servers with the same name on the same network AND you can't have 2 SQL instances with the same name on the same machine. So if your old SQL instance is named "CompanyData" (horrid name, but just an example), and your old server is named "SQLHost1", and the new ones are "NewCompanyData" and "SQLHost2", all connections strings that used "SQLHost1\CompanyData" would need to be updated to use "SQLHost2\NewCompanyData". If you use SQL aliases, this is less of a problem, but maintaining the SQL aliases can be a pain in the butt too. An alternate way to do that last step is to have the new SQL instance on "SQLHost2" but with the name "CompanyData", then have your IT dept set up a DNS alias so SQLHost1 points to SQLHost2's IP. That way SQLHost1\CompanyData would point to the new system and no app changes. The last (and most important in my opinion) pro for a migration upgrade is that you can get things up and running and tested against it WHILE live is running and then schedule downtime to transfer the user db's once it is running well and end users are happy. AND if go live fails for any reason, you just turn the old system back on. Cons are that all apps will need to be reconfigured and if anyone hard-coded stuff into their app and lost the source (as an extreme example), then you may not be able to do this approach. And it can impact licensing costs as you would have a second server hosting SQL. Plus it needs IT to get involved to help with the upgrade. An in-place upgrade is nice as it is a single-user (DBA) that needs to do the upgrade, but if things fail with the upgrade, it can quickly turn into panic while you try to get it back up during your outage window.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
This was removed by the editor as SPAM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply