Sql-server – Any tips to watch out for when setting one of the databases to Read Only

sql-server-2008-r2

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.

Best Answer

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

Related Solutions

Sql-server – Work out date difference when there is only one date

With your sample data, there are some unmatched pairs - this solution only gives the interval for the matched pairs, and more than two entries for each event, which can cause an cross join between the start and two stops.

http://sqlfiddle.com/#!3/6b1f5/9

CREATE TABLE event (ID int, EventTime datetime, Action varchar(10));
INSERT INTO event VALUES
(1,       '2012-01-01 10:00',      'Stop')
,(1,       '2012-01-01 13:31',     'Start')
,(1,       '2012-01-03 09:45',      'Stop')
,(2,       '2012-01-01 11:12',      'Stop')
,(2,       '2012-01-10 15:35',     'Start')
,(2,       '2012-01-11 08:39',      'Stop');


WITH starts AS (
SELECT *
FROM event
WHERE Action = 'Start'
)
,stops AS (
SELECT *
FROM event
WHERE Action = 'Stop'
)
,intervals AS (
SELECT Starts.ID, Starts.EventTime AS StartTime
       ,(SELECT MIN(Stops.EventTime)
         FROM Stops
         WHERE Stops.EventTime > Starts.EventTime
         AND Stops.ID = Starts.ID) AS StopTime
FROM Starts
)
SELECT *, DATEDIFF(MINUTE, StartTime, StopTime) AS minutes FROM intervals;

SQL Server Database Synchronization – Methods and Best Practices

Modifying their structure is generally frowned upon

Replication is more than likely out and I'd throw Sync out before that. (from real life high transacitonal tests on Sync Framework)

If 3-4 hours is your data latency exceptance, log shipping will probably be your bests bet here on a read-only copy. But how much change is happening in the log? find that out be monitoring it to see how quickly and how much you need to push across.

If you can't go to Mirroring or upgrade to 2012 enterprise, that is out although that would be a sound strategy if you can go Enterprise if not on it.

SSIS isn't meant to just dump data over but it can do it. You're looking at far too much in the terms of lookup transformations though and the task would be expensive in time and resources. Although, like I said, it can do it.

Really, there will be a distinct narrowing of choices based on answering a few questions

Data latency acceptance
Amount of data change in a given minute,hour and day Connectivity to the secondary
Read requirements on the secondary instance
Up-time of the secondary instance
Alterations to existing schema and all objects
Security

Best Answer

Related Solutions

Sql-server – Work out date difference when there is only one date

SQL Server Database Synchronization – Methods and Best Practices

Related Question