Can I use different index on main DB and mirror Database?

Question

Post reply

Can I use different index on main DB and mirror Database?

ram1447

SSC-Addicted

Points: 499
More actions
October 31, 2015 at 4:32 am

#309360

Hi all,

We use SQL server always on feather on my database and we distribute statement on main database server and mirror database server for raise performance.

My police for split statement is DML (insert, update and delete) statement go to main DB and Read Data (select) statement go to mirror DB.

I want know can I use different index on main DB and mirror Database?

Because some index are used in mirror DB not used in main database.

Thanks.

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

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

andrew.leef SSC Veteran Points: 240 More actions · Answer 1

Just to clarify the question ...

You have a DB-1 and a DB-2 which are mirror images of each other, but when someone executes code with insert update or delete, you want it to use the DB-1 - and you want read to hit DB-2?

We just named the second DB XYZ_Reporting in our environment - and anything that isn't live business processes hits the _Reporting DB. For instance, any time we run a SSRS or Crystal Report.

I don't know that there is a way to direct based on the code call - what if you have a function or proc that reads and insert/updates data?

Ant-Green SSC Guru Points: 112965 More actions · Answer 2

ram1447 (10/31/2015)

Hi all,

We use SQL server always on feather on my database and we distribute statement on main database server and mirror database server for raise performance.

My police for split statement is DML (insert, update and delete) statement go to main DB and Read Data (select) statement go to mirror DB.

I want know can I use different index on main DB and mirror Database?

Because some index are used in mirror DB not used in main database.

Thanks.

No, you can't create an index on your read secondary and not have it appear in your write primary. The technology works on the same principle as mirroring, anything done on one gets replicated to the rest.

Perry Whittle SSC Guru Points: 233792 More actions · Answer 3

ram1447 (10/31/2015)

Hi all,

We use SQL server always on feather on my database and we distribute statement on main database server and mirror database server for raise performance.

My police for split statement is DML (insert, update and delete) statement go to main DB and Read Data (select) statement go to mirror DB.

I want know can I use different index on main DB and mirror Database?

Because some index are used in mirror DB not used in main database.

Thanks.

Are there using database mirroring or AlwaysOn availability groups?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉