June 11, 2020 at 8:20 pm
Would you see any problem leaving the old database to read only mode after the upgrade? It would be side by side upgrade. I don't think of any issues unless anyone can think off?
June 12, 2020 at 7:53 am
A connection string change that's not been made, opens a select to the database and works. How will you know of rouge connections which need to be changed if the database is still accessable.
If you migrate you migrate, the old should be switched off.
June 12, 2020 at 8:08 am
either switch it off or rename it (in case data is volatile and someone needs to look at the old content).
Either will prevent unwilling access to it as Anthony mentioned.
June 12, 2020 at 11:40 am
If you can afford to have it up with the server online, yeah, I'd leave it there. I mean, change the security so people can't accidently log into that instead of the new server. Last thing you want is mixed updates and new data in both places. Horror. However, having it right there, just in case, sure. Nice little safety net. Most of the time, you won't be able to do that for long. They're going to want to repurpose the server or stop paying for it or whatever. However, keep it while you can, just in case.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 12, 2020 at 2:57 pm
Thanks! I can't take it offline. Since users want database to be view only mode for a while. So change the db to ready only mode or rename and ensure the connection strings pointing to new server instead of old server. You mean disable all the accounts for security?
June 12, 2020 at 3:16 pm
changing to read only does not prevent access to it.
changing security may prevent those that need access to it to do what they need.
So if it needs to be online then rename it as that will prevent unwanted access to it
June 12, 2020 at 3:50 pm
Well, see, now I'm nervous. Making it read only could work. Should work. However, what level of access do people have? They could undo that depending (seen too many places where dbo or even sa is standard).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 18, 2020 at 3:10 pm
Thanks! Since no one has sa and db_owner access other than me. So I will change the db to read only mode first. Then change the connection string. Would that be suffice right?
June 18, 2020 at 3:39 pm
Should also be renaming the DB, as a select query on an old connection string could still access the database.
June 18, 2020 at 3:48 pm
Yeah, probably.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 18, 2020 at 3:51 pm
Thanks guys!
June 19, 2020 at 4:13 pm
When I change the DB to read only mode. Using the application i get sign on failed. Any idea?
June 19, 2020 at 9:17 pm
It could be that there's a proc that you need to execute. Maybe a table that logs the login. It's hard to know. You could monitor using Extended Events to see what queries get called as it attempts to connect.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 20, 2020 at 4:30 pm
Thanks! I can't take it offline. Since users want database to be view only mode for a while. So change the db to ready only mode or rename and ensure the connection strings pointing to new server instead of old server. You mean disable all the accounts for security?
This is going to be a problem - having an application available in view only mode does not mean the database can be set to read-only. To be able to set this up you need to be able to change the application user accounts to set them to view only permissions. That setup is from within the application - not within the database.
Second - you need to be able to setup and configure the application server(s) that will be utilized for this view only system. Which application servers will be utilized all depends on what functionality each application serves - and if those services are required for the view only system.
For example - let's say you have a web farm for application access, a set of servers for messaging (printing, faxing, electronic messages, etc...). In a view only system, will the users need printing/faxing functionality? If so - then the view only system will need at least 1 web server and 1 messaging server and a database server (licensed for production use).
Next - to ensure that this system is NOT accessed by the upgraded system, you either need to be able to validate and verify *every* application server, every connection setting (direct user links, linked servers (external connecting to your system), SSIS packages, interfaces, user favorites and any other external or saved links) do not connect to the 'old' system, or you need to rename and re-IP the old system so those old links will not connect to this system.
And finally - once you know for sure this view-only system is completely isolated, then you can allow the users access to the system.
Another way to look at this - a view only system is another copy of the environment the same as any DEV/QA/UAT environment. The difference is the data in this system is a COPY of the production data from a specific point in time - and access is limited within the application for view only access and the version of the application has not been upgraded.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply