December 19, 2016 at 2:08 pm
I have a mirror in place. since i don't have enterprise edition i need to do maintenance(index rebuild offline, Gather Stats etc.)
Two databases A = Active B = Passive.
I broke Mirror. ( no more transnational log sent to B. ). I open B to do maintenance but A is still receiving online transactions. (So i have Two DBs open : one receiving transactions(A) and other (B) doing maintenance to be used as the new Active.
I have a set of tables that receives inserts and updates and on A I don't want to lose those updates. Then I create Replication between A and B.
The replication works On Demand with no filter in the replicated tables.
Since mirror Database (B) has all rows from (A) until the broken time in that set of tables.
Each table has a Key Value and that is unique. But there is another column that can get updated on that same row.
example :
I have the key value of : 255. and also a column that can be updated with Value of False. that value exists on A and B.
If i update A in that same Key Value of 255 to True which already exist on B, Will the replication capture it and replicated to to B.?
Take in consideration the key value cannot be duplicated since already exist on both DBs.
December 20, 2016 at 6:54 am
If the update touches columns with a unique constraint and you try to insert a duplicate value into that column then it will be rejected.
That said, if you have merge replication in place and you are not inserting a new record with duplicate values then the merge should be successful.
December 20, 2016 at 9:50 am
What is the maintenance you do on B?
does it affect the data?
And what type of replication have you configured?
December 22, 2016 at 2:10 pm
The Merge replication creates a new column and that is making the new inserts to fail.
What i wanted is to create transactional replication.
The steps are as follow
Break Mirroring. Open Mirror database : after opened The A database will keep receiving new transactions(that are not in B) updates on A (on certain rows that are in B).
Then I'm planning on doing some maintenance on B. But i will need any changes done on A on B to put B as active after receiving the new transactions and the updates.
Can that be accomplished with transactional replication. ?
December 23, 2016 at 3:23 am
Is replication here the best solution?
I would personally be happier with mirroring (or Always On Availability Groups if that is an Option) because all changes are mirrored at the partner site. In that way you wouldn't have Problems with DDL changes breaking the replication model.
December 27, 2016 at 6:58 am
I already have the mirroring in place.
But in order for me to do maintenance i need to break mirroring and keep both databases open.
One database will be live receiving all transactions and the other will be under maintenance (index Rebuild,Gather Stats,etc). I cannot lose the transactions running on the live database and that is why i need to replicate the changes from live to database under maintenance. But now I find out that replication won't copy an updated row already existing unless I do a merge replication which will add a new column to my table. all this stuff just because i have the standard edition running. i can't do nothing online (maintenance wide).
December 27, 2016 at 11:41 am
paul.s.vidal (12/27/2016)
I already have the mirroring in place.But in order for me to do maintenance i need to break mirroring and keep both databases open.
One database will be live receiving all transactions and the other will be under maintenance (index Rebuild,Gather Stats,etc). I cannot lose the transactions running on the live database and that is why i need to replicate the changes from live to database under maintenance. But now I find out that replication won't copy an updated row already existing unless I do a merge replication which will add a new column to my table. all this stuff just because i have the standard edition running. i can't do nothing online (maintenance wide).
I think you have misunderstood something.....
You don't need to break the mirror to do maintenance on a mirror. With scripts like those for example for Ola Hallengren you can run them on the principal during an appropriate time and the changes are reflected on the mirror side.
If you still want to do it on both sides then you have the option of performing a switchover and doing the same on the other side. As long as you have the MIRROR_PARTNER parameter in the connection string for your application then there is nothing further to do to maintain connectivity. That said, you don't acheive anything by doing this.
Breaking the mirror achieves nothing because the whole process of rebuilding the mirror has to be done again from the beginning.
I am a Little confused about what you understand under mirroring. With a mirror in place, all changes within the database are reflected in the partner, DDL and DML, so your statement about some changes not been carried over doesn't make much sense.
Can you send a screenshot of your mirroring configuration please?
December 27, 2016 at 11:55 am
I understand your point and I already have the Ola Hallegren scripts in place. Just undertand this.
I do have Sql Server Standard Edition and I can't do index maintenance online.
This is from Ola Scripts.
@FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
I can't do online or offline. why ? simple. Online requires Enterprise edition and Offline will lock the table that needs to be available 24x7x365.
If I break mirroring and has two open databases I can do maintenance on the recently opened DB. But the thing is to replicate the newest transactions that are coming from the live database on few tables. Then when everything is synchronized the application will be pointed to the database were maintenance just ran and the old db will be dropped.
I just take a backup of the new DB and then establish mirror again. All this stuff just because I do have the standard edition.
December 27, 2016 at 12:28 pm
Now I understand a Little more now that I know that the database Needs to be online 24x7x365.
What your employer is asking for is unrealistic and unreasonable under the circumstances. Your hands are tied under almost all scenarios.
When you don't have the option to add hardware or upgrade the SQL Server version then the database owner has to create a maintenance window within which you can work unhindered.
There is no other option.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply