Sql-server – Set a filegroup to readonly

optimizationread-only-databasesql serversql-server-2012

I have a set of dimensions which are updated only during specific times.

I have moved these tables to a specific filegroup named Dimensions. I wanna keep this filegroup as readonly. When ETL runs, it will set the filegroup to read-write, run, then set it back to readonly.

But when I run ALTER DATABASE MYDB MODIFY FILEGROUP Dimensions READ_ONLY, I get the message Database state cannot be changed while other users are using the database 'MYDB'.

What's the least intrusive way of avoiding new transactions, waiting current ones to finish, then run this command? Is there a way to make a DB single user for some time, or make it offline, whatever?

UPDATE: I'm basing myself on the article The Read Uncommitted Isolation Level:

For scenarios that demand the very highest levels of consistency guarantees, serializable remains the only safe choice. For performance-critical operations on read-only data (for example, large databases that are effectively read-only between ETL windows), explicitly setting the database to READ_ONLY can be a good choice as well (shared locks are not taken when the database is read only, and there is no risk of inconsistency).

Best Answer

Usually people just want to kick all users out right away, so they use:

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

In your case, if you want all existing activity to continue until you get your turn, this will wait indefinitely:

ALTER DATABASE MyDB SET SINGLE_USER;
-- alter filegroup code here
ALTER DATABASE MyDB SET MULTI_USER;

If you want to give some time but not wait forever, you can say something like:

ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK AFTER 300 SECONDS;

Which will wait 5 minutes and then roll back any remaining transactions. RESTRICTED_USER might be an option for you as well, in case SINGLE_USER allows agent or other background threads from taking your connection.

I don't know of any way to set the state of the database so that existing transactions are allowed to finish and no new connections (or new transactions from existing connections) are possible.

Some information here:

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

There are several steps to configuring a server to accept ReadOnly traffic. The following link walks you through it, http://msdn.microsoft.com/en-us/library/hh710054.aspx ,but basically you need to configure each server in the AG and then set up the routing for each.

Here's the T-SQL involved:

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER01' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER02' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));
GO

Sounds like you may be missing the configuration and/or routing information for the primary.

Sql-server – Hold exclusive lock on a whole DB

Setting the filegroup to read only doesn't actually improve performance. It might appear that way if there are users/jobs that update the data while most users are just reading data (blocking) but that's a misinterpretation of your observation.

A common misconception is that it saves on locking however that would require the database to be read only not just the specific file group (Source1, Source2).

From your description, it looks like nobody else apart from the ETL job should have write access to the table. That means, all users are read only by definition. So what makes you think further making the table read only will help performance? Have you observed other users writing to the database hence causing blocking? If so why not just secure the table and allow only the account used for ETL to write data while all other accounts can only read? Much easier to do, far less intrusive plus it enforces your rule of only ETL can write, all others can only read.

Best Answer

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

Sql-server – Hold exclusive lock on a whole DB

Related Question