0

Our front end application uses\ pointed to a different database now in SQL Server 2008R2 in the same instance, so to prevent users from accidentally updating data in the old production database, is it simply just changing the Database Read Only State to "True"? As ADMIN users may still need to access it in case, to verify migrated data to the new DB matches the Old DB but not be able to modify anything.

Also, is it a good idea to change the recovery model for this old production database to Simple vs Full? and no need to run its Jobs or maintenance plans ?

In worse cases, I always can set it back to Read and Write at anytime with no issues correct? Thanks for any advice.

  • Readonly databases prior to 2012 can't auto create or auto update stats so you might want to ensure that stats are manually updated (and potentially any missing ones created) first to avoid sub optimal plans. – Martin Smith Mar 31 '15 at 18:59
  • Thanks Martin for your comment, I thought Statistics are unique to each database, since this database will be read only and eventually not in use, why I need to manage and update it's statistics any longer? Would you please clarify what you meant by "avoid sub optimal plans"? I'm expecting the optimizer to use plans created by the new database in use! Auto Create Stats and Auto Update Stats are already set to True for both databases. – Shayma Ahmad Mar 31 '15 at 21:02
  • Statistics are only auto created or auto updated when a query needs them. If you happen to be running queries against the read only version that are different from what it previously saw and for which no statistics are available or for which the stored statistics were already stale it won't be able to create/update them. – Martin Smith Mar 31 '15 at 21:05
0

is it simply just changing the Database Read Only State to "True"?

yes, you can use below t-sql

alter database db_name
set read_only with rollback immediate

Also, is it a good idea to change the recovery model for this old production database to "Simple" vs "Full"?

Yes, a read-only database wont be having any DML operations. So best practice is to change the recovery model to SIMPLE. Make sure that you take regular FULL backups (and most important is testing your restore) - just incase if the server goes down.

In worse cases, I always can set it back to Read and Write at anytime with no issues correct?

Yes, you can just run below tsql

ALTER DATABASE db_name SET READ_WRITE
with rollback immediate
GO
  • Thanks Kin for your replies, since I mainly use the database properties in SSMS instead of t-sql and never used -with rollback options, can I just set the DB State to True? I'm assuming this will rollback any transaction that it’s not complete yet, but since no one should be using this database as of today anyways, would this rollback command be necessary? I will educate myself more about why this command is used in general. – Shayma Ahmad Mar 31 '15 at 20:34
  • I would suggest you to use T-SQL, as it is much flexible and better than the clunky SSMS GUI. The WITH ROLLBACK IMMEDIATE will kill all the transactions and will ensure you don't end up in blocking or your command getting timedout. – Kin Shah Mar 31 '15 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.