Switching between database servers

Question

We have 2 database servers with the same database (in both servers) for a web application, (only 1 was there when the application was deployed to production).Now the current database in the server connected to the web application is made read only and the second database in the second server is active. We are supposed to switch between the servers as the first primary one turns readonly. The web application is in asp.net ,C# with sql back end. How do we check the read only for a db and connect to the next server ?

Did you see [How to detect a SQL Server database's read only](http://stackoverflow.com/questions/2930251/how-to-detect-a-sql-server-databases-read-only-status-using-tsql) — huMpty duMpty, Sep 06 '13 at 16:43

score 1 · Answer 1 · answered Sep 06 '13 at 16:38

1

Create a second connection string in the Web.config and use whichever you need to establish a connection in the app.

answered Sep 06 '13 at 16:38

Metaphor

6,157
10
54
77

I think it would be better to keep them in **app.config** instead of **web.config** – Rahul Tripathi Sep 06 '13 at 16:43

score 1 · Answer 2 · answered Sep 06 '13 at 16:40

You should be able to query the database to check if it is read-only , then change your connection string based on the result of the query.

In MSSQL I believe this information is held in the is_read_only column of the sys.databases table

http://technet.microsoft.com/en-us/library/ms178534.aspx

score 0 · Answer 3 · answered Sep 06 '13 at 16:39

0

How do we check the read only for a db and connect to the next server ?

using T-SQL

SELECT name, is_read_only 
FROM sys.databases 
WHERE name = 'databaseName'
GO

This will return 1 in is_read_only column when database is set to read-only mode.

answered Sep 06 '13 at 16:39

Kin Shah

612
6
25

score 0 · Answer 4 · answered Sep 06 '13 at 16:46

You could have both connections strings in your app.config and create a table in your 'non read only' database that will contain one row and one column. Make this row a boolean and once you make the change, swap this value to true. When your application loads, it will check this table's value and if it's true then it will use the connection string to the non read only DB. If it's false, it'll change to the 'soon to be read only' database.

Eventually you will want to change it so that you only have one connection string, unless you will need to have access to both. Typically I've only seen this setup for reporting, and generally there is only one application that connects to the read only/archive database.

Switching between database servers

4 Answers4