4

I'm designing an application and considering some options regarding the database system since I'm not familiar with Microsoft SQL Server. I would like to know if it's possible to have 2 servers sharing the same files, as shown below.

Diagram

The idea is that the data is in a raid system so it would grant us some safety. This way we would save effort in synchronizing both databases and save some money on storage. We only need one database at a time, the secondary is just for use in case of failure of the first.

Is this possible?

I'm open to different approaches. The main problem is the database redundancy, our application must guarantee that.

At the moment I'm using Windows 7 and SQL Server 2008R2.

4 Answers 4

4

Two distinct SQL Server machines can't share the same database files. Except when we're speaking about SQL Server 2008 Failover Clustering.

References here:

PS: while I use Windows 7 on the work and home machines I wouldn't advice having it as a Operating System for a database server. I think that MS Windows Server 2008 R2 should be the choice in this case.

5
  • is it possible to do it using only one machine? (i know its kinda stupid, but its just a little layer of protection in case 1 server stops functioning for some software error and the machine stays up with the other server)
    – RagnaRock
    Oct 31, 2011 at 15:57
  • 3
    I can count on one hand the number of times a SQL Service has crashed, in 13 years. Oct 31, 2011 at 16:09
  • 4
    On the other hand...I can use many limbs to count driver failures, network card failures (even admins failures :-D). So while SQL Server in itself seldom crashes, OS errors happen, driver failures happen, so clustering is a strong layer between the live databases and OS/hardware.
    – Marian
    Oct 31, 2011 at 16:17
  • 1
    A more eloquently put explanation of my point Marian :) Oct 31, 2011 at 16:21
  • @Mark: I was just completing your answer, not correcting. Hope you didn't mind :-).
    – Marian
    Oct 31, 2011 at 16:23
5

It is possible for two separate servers to simultaneously access a shared read-only database. The feature is known as Scalable Shared Databases.

From your description, it sounds more like you need a failover cluster, not a shared read-only copy.

3

What you are looking for is fail over clustering. This requires two servers, configured with shared storage such as a storage array (SAN). This isn't cheap to setup, but can be done pretty easily.

1
  • +1 In a virtualised environment you can achieve shared storage relatively cheaply without a SAN using drbd. Oct 31, 2011 at 19:10
3

I'm surprised everyone mentioned clustering.

Database mirroring avoids the shared nothing cluster disks. This is simpler than full-on clustering but still requires 2 SQL Server instances.

As for cost, either your data is worth something or nothing. If you lose the server, do you need it back ASAP? Or can you wait to rebuild a server and restore from a backup?

If "ASAP" then you have to spend money

5
  • 1
    Very valid point. Even more so with Denali around the corner. Oct 31, 2011 at 21:52
  • 2
    According to the diagram attached, it looks like a clustering scenario with shared drives. In mirroring, the drives are not shared between the instances. Nov 1, 2011 at 12:52
  • I'm able to do database mirroring ASAP (high-safety mode) the only thing is that they don't share the database itself.
    – RagnaRock
    Nov 2, 2011 at 11:27
  • @StanleyJohns : true, but shared disks means expensive SAN or cluster. Mirroring is the same *database" which is good enough, most likely
    – gbn
    Nov 2, 2011 at 11:31
  • @gbn I agree with you. Mirroring works just as fine and is much cheaper. Plus is has a one up on clustering in that the drives are not a single point of failure. Nov 2, 2011 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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