Assistance with new nework design

Question

Assistance with new nework design

ThrashCore

Newbie

Points: 3
More actions
July 14, 2016 at 11:46 am

#311713

We currently have one large VM production server with 15+ databases. We are currently in the process of designing a new server infrastructure using SQL 2012, and we are looking to seperate the databases into two VM's on two different hosts to maximize utilization and I/O.
Is there a design which will have both VM's active and access one database in a shared storage? For more clarity, i have a database Adventure.mdf in shared storage. Can Adventure.mdf be attached to VM1 and VM2 at the same time?

Viewing 4 posts - 1 through 3 (of 3 total)

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

jasona.work SSC Guru Points: 50083 More actions · Answer 1

ThrashCore (7/14/2016)
We currently have one large VM production server with 15+ databases. We are currently in the process of designing a new server infrastructure using SQL 2012, and we are looking to seperate the databases into two VM's on two different hosts to maximize utilization and I/O.
Is there a design which will have both VM's active and access one database in a shared storage? For more clarity, i have a database Adventure.mdf in shared storage. Can Adventure.mdf be attached to VM1 and VM2 at the same time?

Short answer: no.

Longer answer: No, SQL acquires and requires an exclusive lock on the database files themselves (this has nothing to do with locking records, this is at the OS level and is to some extent a limitation of the OS,) so there's no way for two instances of SQL to have the same physical database files open at the same time. It's one of the reasons why you should never backup the MDF / NDF / LDFs directly with backup software, but instead should run a backup in SQL itself, to a file on disk, which then gets backed up by your backup software.

Eric M Russell SSC Guru Points: 125522 More actions · Answer 2

Short answer: Yes

Longer answer:

https://technet.microsoft.com/en-us/library/ms345392(v=sql.105).aspx

Scalable Shared Databases Overview
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.
...

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

jasona.work SSC Guru Points: 50083 More actions · Answer 3

Eric M Russell (7/14/2016)
Short answer: Yes
Longer answer:
https://technet.microsoft.com/en-us/library/ms345392(v=sql.105).aspx
Scalable Shared Databases Overview
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.
...

The big caveat there is, that's only for a "reporting" database (note, not an SSRS database,) and it can only be read-only.

Admittedly, we're both working from the rather limited information in the original post, so it's possible that this will work for what they need.