April 11, 2019 at 4:19 pm
While setting up the transactional replication I was not replicating non clustered index, however, now the user wants non clustered index as well and match the index same as publisher, if I enable the non- clustered to true from properties that would replicate to subscriber right? Would that cause any problems or create any locks? What is the best practice to do or any other better option?
April 11, 2019 at 4:59 pm
This will replicate the index, but the creation of the index should take place on the subscriber. This will cause some workload as the indexes are built and a small increase in workload as things update.
If they want the same indexes, this is the easiest way. If they might just want certain ones, then just create those on the subscriber(s)
April 11, 2019 at 5:11 pm
If you generate a new snapshot yes the indexes would be replicated. But like everything else, it comes with some costs. There will be additional locks, you'll need to address index maintenance on the subscriber, in can slow down bulk loading/snapshots, you'd have some additional overhead on the subscriber. One of the considerations is that you often don't have the exact same workload on the subscriber as you do on the publisher so the exact same indexes are often not needed. Does the user really know enough about their queries, the query plans, the indexing to say that the exact same indexes should exist on the subscriber?
Sue
April 15, 2019 at 4:05 pm
Since i have some of the huge tables on publisher, so i didn't enable snapshot to happen once every day. I think it make sense otherwise the copy would take long time if i enable the snapshot every day. So in this situation the better option would be create index on subscriber right? Would there be any problem? I would think that the good points you mentioned that everything comes with some costs, so when i create indexes on subscriber then replication can run slow as well right? I would also check with user why they need the exact same indexes?
April 15, 2019 at 4:50 pm
Snapshot moves everything, so if you include NCIs, they have to xfer. If you wait and build them on the subscriber, they have to be built. Not sure which is better.
April 16, 2019 at 10:26 pm
You wouldn't need to enable the snapshot daily, you just use that when you have updated the publication and need to send a new snapshot to the subscribers. You could just create the indexes on the subscriber. You will be sending the non-clustered indexes when you reinitialize with a new snapshot. If the indexes are different between the two, the indexes would be replaced on the subscriber with indexes on the publisher.
Very often, you end up with different indexes needed on a subscriber. One way to address that and keep it correct when you send a new snapshot is to not replicate the non-clustered indexes and have your won index script to run after the snapshot is run on the subscriber. If you look at the properties for the publication, then go to Snapshot, you'll see where you can include script to run before or after the snapshot. I've usually put the index script in the section to run after the snapshot.
Sue
April 17, 2019 at 5:14 pm
Thanks sue and steve. This is very good information, i really appreciate it.
I have clustered indexes on subscriber and the user has some reports running directly against subscriber database and then there is a ETL Process runs against the subscriber as well which loads into other system. So i was thinking to create non clustered index on subscriber database for reports running directly against subscriber to improve performance. Does this also help for ETL load process as well? However, another concern is that creating too many non clustered index on subscriber would take extra disk space, needs additional maintenance and the insertion and deletion would be slower using replication right? What other problems you think i might run into?
April 18, 2019 at 2:50 pm
What indexes will help or what would be too much is the same thing you would ask yourself with any database, replication or not. You often have to weigh those types of things out when doing any tuning. Whether it helps the ETL process or not depends on the indexes. You probably want to spend some time evaluating the workload on the subscriber and come up with an indexing strategy. Taking into account the space needed, the maintenance, any extra overhead from locking/blocking would be things you would take into consideration.
Overall, you seem to have a good understanding on what the issues are and what you might run into so now you just need to test things out and come up with a plan for your situation.
Sue
April 19, 2019 at 10:48 pm
Thanks! I have few more questions , with replication there are limitation that tables with primary key are qualified to replicate and configure. So what if the tables does not have a primary key and date column, how can we address those tables if i need to replication. Is there any other option other than Always on Availability. Will there be any issues if i have 100's of tables with larger database for replication? If yes, what issues i could expect and then resolve?
April 22, 2019 at 1:48 am
If you don't have a primary key on a table, you can't publish it with transactional replication. You could use merge or snapshot replication which don't require a primary key.
The size of the tables may or may not matter. Same for size. It really depends on too many different things, the activity, the network, the distributor, number of publications and on and on.
Sue
April 22, 2019 at 5:11 pm
Thanks! There is a ETL Process which connects to my SQL server replicated database and the ETL jobs started failing and the ETL team thinks this could be connection issue from the source system(which is my SQL Server database getting replicated). I am not seeing any maintenance jobs running during this time on the replication database. Not seeing any errors, how could this be a connection issue?
April 22, 2019 at 5:22 pm
It would depend on why they think its a connectivity issue. Sometimes people just say things like that for no reason really other than they are just guessing. You could check the dm_os_ring_buffers to see if you can find any connectivity, login issues. Check this link - it has a good list of different queries against that DMV to find some of those issues:
https://mssqlwiki.com/tag/ring_buffer_connectivity/
Sue
April 22, 2019 at 5:57 pm
Thank you. I see the record type as Login timers and record time using the link you sent. Where would i see if there is any connectivity issue? I will keep looking but curious if you know anything top of your head?
April 22, 2019 at 6:08 pm
Keep looking 🙂 There are actually six different queries in there for different connectivity or login issues. I'd look at all of them just with it being such a vague "connectivity" type of issue they said they are having.
Sue
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply