March 15, 2016 at 9:09 am
Hi all
We're upgrading on of our servers from SQL2005 to SQL2012.
On the 2005 box, we have mirroring set up to another server.
We're wanting to use replicas to multiple servers once the upgrade has been done to save a lot of cross-server/network traffic.
We'd like to add indexes to the original tables but the 3rd-party vendor won't allow that as it might break any updates to the table structure they do.
As we could really use some indexes (the tables already have primary keys), can we add indexes to the replicas?
Also, can you use a replica to go from a 2012 box to a 2008 box?
Thanks in advance.
March 15, 2016 at 10:50 am
replica? Do you mean as part of an availability group? Or a secondary for mirroring?
You typically can't go from higher to lower version in anything except replication.
March 15, 2016 at 10:53 am
Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups)
Indexing
To optimize read-only workloads on the readable secondary replicas, you may want to create indexes on the tables in the secondary databases. Because you cannot make schema or data changes on the secondary databases, create indexes in the primary databases and allow the changes to transfer to the secondary database through the redo process.
Availability Groups were new in 2012, so you cannot set one up in 2008.
March 16, 2016 at 3:13 am
Hi both
I've realised I've made a mistake in my original post and all the servers will be on SQL2012 (just different editions).
What we're wanting to do is this:-
Server A will be the basis for the SQL databases from the 3rd-party application and will not be accessed for reporting purposes.
Servers B,C and D will have replicas on them and will be used for reporting.
We're wanting to add indexes just to the replicas on servers B,C and D to speed up reads.
Hopefully this clarifies what we're wanting to do.
March 16, 2016 at 3:41 am
What exactly do you mean by 'replicas'?
Availability Groups secondary replicas?
Transactional replication subscribers?
Database mirroring secondary?
Something else?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2016 at 4:02 am
Hi Gail
We currently use mirroring from Server A to server B and then take a snapshot so we can report from it.
We're wanting something we can read from directly rather taking a snapshot.
It was my understanding that replicas were the new mirrors.
March 16, 2016 at 4:44 am
Ok, so you are talking about availability group replicas. Yes you can read from them. They are read-only and are identical to the primary (read-write) replica.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2016 at 5:10 am
So that would mean we can't add indexes without adding to the "original".
Oh well, back to the drawing board......unless we use another method.
Any suggestions?
March 16, 2016 at 5:13 am
Add the indexes to the primary and keep a 'revert index changes.sql' around for when the vendor wants to make an update.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 16, 2016 at 9:05 am
GilaMonster (3/16/2016)
Add the indexes to the primary and keep a 'revert index changes.sql' around for when the vendor wants to make an update.
+1. I've done this.
I will send suggestions to vendors, saying I've noticed improvement in "testing" with indexes. Quite a few have added these changes to patches or (more likely) upgrades (that cost $$)
March 16, 2016 at 9:39 am
You can add indexes to the readable side if you use replication.
March 16, 2016 at 10:48 am
I was going to note this. If you want transactional replication (for example), then you can move over data regularly, with minimal delays, but have different indexes on the subscriber, read-only, side.
March 16, 2016 at 11:00 am
Apologies if this is a stupid question, but would be similar to transaction log shipping?
I did float that idea (about trans log shipping) (some of our transaction logs are ridiculously large) but got shouted down.
How would transactional replication work if the tables change on the principal?
March 16, 2016 at 1:03 pm
Log shipping works by taking a log backup, copying to the secondary, and applying it as a restore (with norecovery/standby). These restores occur at discrete intervals (every 5 minutes, every hour, whatever you configure). While the secondary is readable most of the time, it is unavailable during the restore. Since this is a restore operation, no changes can be made on the secondary (indexes).
Replication works by reading the log, but sending the actual SQL changes to the secondary. Again, this can be by each transaction (continuous) or at intervals. The changes are made on the secondary just like any other client connecting to the database and running code. Since this is a normal SQL database, you can make changes. You can add indexes, and users (if they have rights) can change data.
Replication is more flexible, but it can be brittle. If it breaks, it can be a pain to reset up. Not hard, but painful
March 17, 2016 at 2:52 am
Thanks Steve. That's given me something to think about then.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply