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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am quite new in terms of SQL Server. I tried to search but can't really find any topic related on what I asked, or probably I didn't phrase my search term correctly. Here is what I am trying to figure out:

in SQL Server 2012, Is it possible to have two different database instance but stored in different Harddrive? For example, I have SQL Server "MSSQLSERVER".

I will have 2 database that are mirror of each other. The database will be database A and database B.

I want to have database A to be stored in D: drive, and database B to be stored in E: drive, but when I write into database A, it will automatically synch into database B as well.

I want this to be in 1 server and I am not planning to use RAID for the HDD. I only want to have database B as my backup in case one HDD failed.

Any advice?

share|improve this question

closed as unclear what you're asking by swasheck, Kin, Mark Storey-Smith, ConcernedOfTunbridgeWells, Paul White Jul 26 '13 at 16:54

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

What are you ultimately trying to achieve with this? If you want backups, create backups and get them off the machine to somewhere safe. Mirroring to the same machine is not a good plan. – Mark Storey-Smith Jul 26 '13 at 12:16
Does it not seem simpler and more reliable to use RAID 1 instead of Database Mirroring? If you're inexperienced in this area (which is totally fine), why are you taking particular technical solutions off the table immediately? Please describe for us what your end goal is, and we'll try to help you get there. – Jon Seigel Jul 26 '13 at 13:19
I'd like to second Mark and Jon and say that your solution seems like a wrong solution to an unknown (yet) problem. On a db server not only a disk can fail, but also a network card, a memory chip, a raid controller, even a driver crash etc. So, what exactly is the problem that you're facing? – Marian Jul 26 '13 at 21:26
1. Backup is only per day, while I want to have backup as per transaction. I am looking for the simplest way without trying to write procedure since I don't have any experience in this area. 2. I am not using RAID since I want to save only the data and not the whole OS. Alternatively of course I can RAID only the SQL Server database, but it waste a lot of HDD for only small data. Anyway the answer given has helped to achieve my goal. My apology if this question seems unusual. – blurryMVC Jul 27 '13 at 10:24
10 times out of 10, given the same constraints, I'd be RAID1 for 2 drives + 1 spare. – Mark Storey-Smith Jul 27 '13 at 18:46
up vote -3 down vote accepted

While creating your database, you can specify the location of mdf/ldf files.

So in your case, You can put the mdf/ldf files of databaseA on the D: drive and the same set of files of databaseB on the E: drive.

New Database

You can use Database Mirroring for the sync you are looking for:

Database Mirroring (SQL Server)

Setting Up Database Mirroring (SQL Server)

SQL Server Replication (MSDN)

Types of Replication

share|improve this answer
Just to confirm, it is in the path for the rows and log? Can you help me on how to ensure both will be in synch? – blurryMVC Jul 26 '13 at 10:55
I have updated my answer, check the Path column, you have to update it for both rows: MDF and LDF files. – Adel Khayata Jul 26 '13 at 11:03
I got it about the location. But is it possible to synch 2 database real-time from SQL Server? – blurryMVC Jul 26 '13 at 11:32
I have updated my answer again. – Adel Khayata Jul 26 '13 at 11:37
Hi, thank you for the link. But if I read correctly, mirroring only works for different sql server instance. I am trying to create 2 synchronized database within 1 sql server instance. Is this possible? – blurryMVC Jul 26 '13 at 11:54

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