June 2, 2011 at 12:57 pm
Hi all,
I have setup replication following the guide on this site and it is working ok,
But - I have noticed that there are specific permissions on each table in the main database and when I look at the permissions on the replicated database they don't exist.
So it appears that the table and data are all replicating over ok but for some reason the permissions that are set on each table do not.
I am only a beginner on SQL and it is probably something simple but I am unsure what.
So anyone have any idea's on why this would happen and how to fix it.
Basically what I wanted to achive was to have the main database on the server replicated to the 2nd server and the databases should be the same.
June 2, 2011 at 1:04 pm
It appears that Copy Permissions is an article property that may default to false. You can find it under Publication Properties > Articles > Article Properties.
June 2, 2011 at 1:47 pm
Great, thanks for your reply.
Another question if I may,
What I want to achive is basically to have a standby sql server that is updated all the time - so it is a complete copy of the original for the purpose of disaster recovery.
If the main server falls over, I want to be able to just point the users at the replicated database and carry on.
The standby server is not always onsite so I decided to use merge replication to achieve this.
Is merge replication the best way to achive what I want ?
June 3, 2011 at 4:57 am
icemannz (6/2/2011)
What I want to achive is basically to have a standby sql server that is updated all the time - so it is a complete copy of the original for the purpose of disaster recovery.
Replication is not ment for DR. It is ment for data distribution.
For DR, check whether Database Mirroring or Log shipping is suitable to your requirement.
June 3, 2011 at 5:37 am
Hi,
I would look into mirroring if I were you it sounds like it meets your requirements better. Also, if you're using a .NET application to connect to the DB you can specify both the primary and secondary server in the connection string.
If you configure your mirroring to use a witness server then everything will be taken care of for you and your users will see little or no difference in their app. Mirroring is meant more for High Availability more than DR. Logshipping is better suited to DR but it all depends on your requirements.
From what you've said, mirroring is the best option i think. If the Secondary server is unavailable then the transactions will just queue up until it is available again and then they will sync over. You need to take into account the fact that your TempDB will grow to store the un-synced transactions.
Thanks,
Simon
June 3, 2011 at 1:11 pm
Suresh B. (6/3/2011)
icemannz (6/2/2011)
What I want to achive is basically to have a standby sql server that is updated all the time - so it is a complete copy of the original for the purpose of disaster recovery.Replication is not ment for DR. It is ment for data distribution.
For DR, check whether Database Mirroring or Log shipping is suitable to your requirement.
It is a form of "high availability" in Microsoft speak which includes mirroring, logshipping, replication and clustering. It could work as a DR in some circumstances but the admin overhead would be massive as would the headache of recovery.
Mirroring is fine if you only want a single database backup. If you've got a lot of databases I'd recommend looking at clustering as well.
June 3, 2011 at 1:57 pm
s_osborne2 (6/3/2011)
Hi,I would look into mirroring if I were you it sounds like it meets your requirements better. Also, if you're using a .NET application to connect to the DB you can specify both the primary and secondary server in the connection string.
If you configure your mirroring to use a witness server then everything will be taken care of for you and your users will see little or no difference in their app. Mirroring is meant more for High Availability more than DR. Logshipping is better suited to DR but it all depends on your requirements.
From what you've said, mirroring is the best option i think. If the Secondary server is unavailable then the transactions will just queue up until it is available again and then they will sync over. You need to take into account the fact that your TempDB will grow to store the un-synced transactions.
Thanks,
Simon
Simon,
I think you have some mistaken statements. The transactions don't just queue up in mirroring and the asynchronous mode that most closely mimics that behavior is only permitted in enterprise edition as high performance mode. From BOL: "asynchronous database mirroring (high-performance mode) is supported only by Enterprise Edition".
Otherwise mirroring is in many ways similar two phase commit, the change has to be completed on both the primary and the mirror before the statement is considered complete. I tend to recommend log-shipping if a slight delay is permitted and clustering if you have the $$. The overhead of mirroring is a bit high for my taste.
CEWII
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply