Pointing multiple SQL Servers at a single MDF (on a SAN) with Read Only access

Question

Pointing multiple SQL Servers at a single MDF (on a SAN) with Read Only access

Michael.Anderson

SSC Enthusiast

Points: 129
More actions
July 14, 2008 at 7:56 pm

#190682

Hi everyone,
I've been searching to see if what I'm trying to do is possible, and I can't seem to find any posts where someone's mentioned what I'm thinking (usually not a good sign :)).
We've just implemented a SAN (HP MSA 2000i), and we're doing some testing. One of the scenarios we wanted to test out was pointing multiple SQL Servers at a central .MDF stored on the SAN to spread the load of Read-only traffic across multiple servers (for CPU and Memory reasons) but sharing the same database data files.
In the iSCSI configuration, you can set connections up as either read+write or read only, so the thought would be to set up the primary SQL Server with read+write, and the secondary servers with read only.
Thanks in advance for any help/feedback.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Roust_m SSCoach Points: 17410 More actions · Answer 1

I don't think this is supported. Even if you will be able to trick SQL Server into such configuration this is not acceptable for production environment. Besides, you bottleneck will likely to be the hard drives for such configuration. You should consider log shipping or replication to accomodate you load balancing.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Not possible in SQL 2000. SQL requires exclusive access to its data files

In SQL 2005, if all the databases are readonly (all of them) it's possible to do, though it's a non-trivial operation. It's called Scalable Shared Databases.

Books Online
The scalable shared database feature allows you to scale out a read-only database built exclusively for reporting purposes (a reporting database). The reporting database must reside on a set of dedicated, read-only volumes whose primary purpose is hosting the database. Using commodity hardware for servers and volumes, you can scale out a reporting database that provides an identical view of the reporting data on multiple reporting servers. This feature also permits a smooth update path for the reporting database.
After the reporting database is built on a set of reporting volumes, the volumes are marked as read-only and mounted to multiple reporting servers. On each reporting server, the reporting database is then attached to an instance of Microsoft SQL Server 2005 and becomes available as a shared scalable database. Once established as a scalable shared database, a reporting database can be shared by clients using different reporting servers. To query the database, a user or application can connect to any server instance to which the database is attached. For a given version of a reporting database, clients on different servers obtain an identical view of the reporting data, making query results consistent across servers.

See more here

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Roust_m SSCoach Points: 17410 More actions · Answer 3

GilaMonster (7/15/2008)
Not possible in SQL 2000. SQL requires exclusive access to its data files
In SQL 2005, if all the databases are readonly (all of them) it's possible to do, though it's a non-trivial operation. It's called Scalable Shared Databases.
Books Online
The scalable shared database feature allows you to scale out a read-only database built exclusively for reporting purposes (a reporting database). The reporting database must reside on a set of dedicated, read-only volumes whose primary purpose is hosting the database. Using commodity hardware for servers and volumes, you can scale out a reporting database that provides an identical view of the reporting data on multiple reporting servers. This feature also permits a smooth update path for the reporting database.
After the reporting database is built on a set of reporting volumes, the volumes are marked as read-only and mounted to multiple reporting servers. On each reporting server, the reporting database is then attached to an instance of Microsoft SQL Server 2005 and becomes available as a shared scalable database. Once established as a scalable shared database, a reporting database can be shared by clients using different reporting servers. To query the database, a user or application can connect to any server instance to which the database is attached. For a given version of a reporting database, clients on different servers obtain an identical view of the reporting data, making query results consistent across servers.
See more here

But apparantly it does not allow you to share a production database (read/write) with reporting servers in read only mode.

This is what it says about setting up and refreshing the reporting database, which looks like a bit of pain:

SQL Server Integration Services (SSIS)

You can create or copy a database by running SSIS packages and using the Execute SQL task or the Transfer Database task:

Backup and restore

You can restore a backup of a production database onto the reporting volume. This involves restoring and recovering a full database backup on to the reporting volume.

Copying the production database

Before you can copy a database manually or use the Detach and Attach Method of the Copy Database Wizard, you must take the database offline, and after copying it, bring it back online. The Copy Database Wizard, however, offers an alternative method, the SMO Transfer method, that copies the database while it remains online. Though slower than the Detach and Attach Method, the SMO Transfer method has the advantage of preserving active connections to the database.

Note:

Before you can refresh a reporting database, its set of reporting volumes must have been dismounted from all of the reporting servers. For more information, see Detaching a Scalable Shared Database.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Roust_m (7/15/2008)
But apparantly it does not allow you to share a production database (read/write) with reporting servers in read only mode.

You are correct, it does not. All of the databases involved in scalable shared must be read only, in fact, if you check the MSDN page, the drive that the data files are on must be read only.

There is no way to have several instances of SQL reading the same data files if any one of them is able to make changes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 5

Again, in SQL 2005, so unless you are planning to upgrade this isn't much help, but I am thinking a database snapshot might suffice. The initial snapshot is based on only the existing database (so the data is not duplicated). All changes are stored in the snapshot database, allowing you to go back to a point in time, but you should be able to select from the snapshot database as though it was the production database.

I don't think you will get any performance benefit from this; infact, I could see it hindering performance, but it would allow you to use the primary mdf from your production server. Database Mirroring (2k5), log shipping, or standard transactional replication seem like a better fit (though my knowledge of all of this, I will admit, is severely limited)