Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

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.

share|improve this question

4 Answers

up vote 4 down vote accepted

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.

share|improve this answer
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 '11 at 15:57
3  
I can count on one hand the number of times a SQL Service has crashed, in 13 years. – Mark Storey-Smith Oct 31 '11 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 '11 at 16:17
1  
A more eloquently put explanation of my point Marian :) – Mark Storey-Smith Oct 31 '11 at 16:21
@Mark: I was just completing your answer, not correcting. Hope you didn't mind :-). – Marian Oct 31 '11 at 16:23
up vote 5 down vote

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.

share|improve this answer
up vote 3 down vote

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.

share|improve this answer
+1 In a virtualised environment you can achieve shared storage relatively cheaply without a SAN using drbd. – Jack Douglas♦ Oct 31 '11 at 19:10
up vote 3 down vote

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

share|improve this answer
1  
Very valid point. Even more so with Denali around the corner. – Mark Storey-Smith Oct 31 '11 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. – StanleyJohns Nov 1 '11 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 '11 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 '11 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. – StanleyJohns Nov 2 '11 at 16:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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