Mirroring different versions

I am looking into Mirroring versus Replication. When going over the SQL2005
version features it list Mirroring (Safety Full Only). Does this mean the
database has to be in Full Recovery mode or that the mirror has to be
synchronous?
Thanks,
It means yes to both. With other editions of SQL Server 2005 (ie Enterprise
Edition) you have other options for mirroring, ie High Performance.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Cindy" <Cindy@.discussions.microsoft.com> wrote in message
news:9CC6F9E4-41F0-4F7F-AC52-09E74ED88B9A@.microsoft.com...
>I am looking into Mirroring versus Replication. When going over the
>SQL2005
> version features it list Mirroring (Safety Full Only). Does this mean the
> database has to be in Full Recovery mode or that the mirror has to be
> synchronous?
> Thanks,

Mirroring and Replication

I'm using replication and want to mirror my publisher to get high availability. As I understand the documentation the principal and the mirror must both use the same distributor instance. So if the distributor fails the replication fails. This seem like a big weakness. Maybe I'm missing some thing. Is there a way to set it up so that replication continues if a distributor fails -- some mechanism that will switch in a "back-up" distributor? I'm looking for a configuration that will continue to run in the event of any single failure. This means that both the publisher and the distributor must be covered by mechanisms that keeps them going in the event of a failure. Mirroring covers the failure of the publisher but I don't see a mechanism that will conver the failure of the distributor.

My application is developed in .net / c#. Data is accessed through .net sql server data provider etc.

The only solution right now is clustering, there's way too hard-coded server names at the publisher/distributor that makes it difficult to get mirroring to work. We're looking at developing other solutions, but they won't be available until after the next SQL Server release. But for now, clustering is your answer.

Mirroring and replication

Hi MSSQL friend,

We have a SQL2005 server which contains some replicated databases. We use merge replication and the publications are all local.

In order to make the server failsafe we need some kind of mirroring. Is this possible with replication?

Any ideas how to accomplish a failsafe scenario with replication?

Sincerely Edward

You can start by searching SQL Server Books Online, I searched for "replication mirroring" and found this:

Replication and Database Mirroring - http://msdn2.microsoft.com/en-us/library/ms151799.aspx

|||Thanks Greg!sql

mirror as a publisher

Hello,
Is it possible to setup replication on mirrored database? Using the naming
‘principal’ and ‘mirror’ for databases involved in mirroring and ‘publisher’
and ‘subscriber’ for replication, what I exactly need is to configure mirror
database as a publisher.
My motivation is as follows:
Currently, I have 2 applications (X and Y), each using its own database (Xdb
and Ydb). Both applications run in the same geographical location (Loc1),
both databases run in the same instance of SQL Server. Some tables are
replicated from Xdb to Ydb.
I need to move Y application to a different geographical location (Loc2) and
in the same time, I want to define some disaster recovery scenario in this
way:
Both applications will be installed in both locations. Y application is
sleeping in Loc1 and X is sleeping in Loc2. Xdb is mirrored from Loc1 to Loc2
and Ydb is mirrored from Loc2 to Loc1. As some data have to be replicated
from Xdb to Ydb I want to save the line between Loc1 and Loc2 and want to
have the replication locally on Loc2 (from mirror of Xdb into Ydb).
Thanks
eXavier
Hello eXavier,
Regarding your question, based on my research, it's not supported to
configure "mirrored" database as the publisher since it's a standby server.
You could only configure publisher in principle database. You may want to
refer to the following article for more details:
Replication and Database Mirroring
http://msdn2.microsoft.com/en-us/library/ms151799.aspx
You could configure snapshot of mirrored database but it's read only and
could not be configured as publisher as well.
Using Database Snapshots with Database Mirroring
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0bf1be90-7ce4-484c-aaa7-f8a7
82f57c5f.htm
Please rest assure that your feedback on this feature is routed to the
proper channel. Also, I encourage you submit via the link below and our
product team would like to hear your voice:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
If you have any further questions or comments, please feel free to let's
know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

mirror as a publisher

Hello,
Is it possible to setup replication on mirrored database? Using the naming
â'principalâ' and â'mirrorâ' for databases involved in mirroring and â'publisherâ'
and â'subscriberâ' for replication, what I exactly need is to configure mirror
database as a publisher.
My motivation is as follows:
Currently, I have 2 applications (X and Y), each using its own database (Xdb
and Ydb). Both applications run in the same geographical location (Loc1),
both databases run in the same instance of SQL Server. Some tables are
replicated from Xdb to Ydb.
I need to move Y application to a different geographical location (Loc2) and
in the same time, I want to define some disaster recovery scenario in this
way:
Both applications will be installed in both locations. Y application is
sleeping in Loc1 and X is sleeping in Loc2. Xdb is mirrored from Loc1 to Loc2
and Ydb is mirrored from Loc2 to Loc1. As some data have to be replicated
from Xdb to Ydb I want to save the line between Loc1 and Loc2 and want to
have the replication locally on Loc2 (from mirror of Xdb into Ydb).
Thanks
eXavierHello eXavier,
Regarding your question, based on my research, it's not supported to
configure "mirrored" database as the publisher since it's a standby server.
You could only configure publisher in principle database. You may want to
refer to the following article for more details:
Replication and Database Mirroring
http://msdn2.microsoft.com/en-us/library/ms151799.aspx
You could configure snapshot of mirrored database but it's read only and
could not be configured as publisher as well.
Using Database Snapshots with Database Mirroring
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0bf1be90-7ce4-484c-aaa7-f8a7
82f57c5f.htm
Please rest assure that your feedback on this feature is routed to the
proper channel. Also, I encourage you submit via the link below and our
product team would like to hear your voice:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
If you have any further questions or comments, please feel free to let's
know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Peter,
Thanks for your response.
eXavier
""Peter Yang[MSFT]"" wrote:
> Hello eXavier,
> Regarding your question, based on my research, it's not supported to
> configure "mirrored" database as the publisher since it's a standby server.
> You could only configure publisher in principle database. You may want to
> refer to the following article for more details:
> Replication and Database Mirroring
> http://msdn2.microsoft.com/en-us/library/ms151799.aspx
> You could configure snapshot of mirrored database but it's read only and
> could not be configured as publisher as well.
> Using Database Snapshots with Database Mirroring
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0bf1be90-7ce4-484c-aaa7-f8a7
> 82f57c5f.htm
>
> Please rest assure that your feedback on this feature is routed to the
> proper channel. Also, I encourage you submit via the link below and our
> product team would like to hear your voice:
> http://lab.msdn.microsoft.com/productfeedback/default.aspx
> If you have any further questions or comments, please feel free to let's
> know.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello eXavier,
My pleasure. Please feel free to let's know if you have any further
concerns. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
When responding to posts, please "Reply to Group" via your
newsreader so that others may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Minimum Setup Requirements for SQL 2005 Replication.

What are the minimum setup requirements to support the following.
2 Remote Laptops running SQL Server 2005 Express databases with the ability
to synchronize with each other. Also they should be able to synch with a
server hosting a SQL Database whci is accessible from 2 other Desktop PCs.
What are the SQL CAL Licensing issues that would apply to this scenario.
Regards
Unfortunately SQL Server Express can only be a subscriber to all types of
replication. All subscriptions must be created through RMO or replication
stored procedures.
Regarding your desktop pc's - If they are running workgroup edition they can
have up to 25 merge subscribers, but 5 transactional subscribers. In regards
to licensing anything which accesses the desktop must have a license, which
can be a CAL or a server per processor license (which will not make a whole
lot of sense).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Stuart Lowe" <StuartLowe@.discussions.microsoft.com> wrote in message
news:6C1471E6-61F1-4BF2-AAA8-16E67A29A967@.microsoft.com...
> What are the minimum setup requirements to support the following.
> 2 Remote Laptops running SQL Server 2005 Express databases with the
> ability
> to synchronize with each other. Also they should be able to synch with a
> server hosting a SQL Database whci is accessible from 2 other Desktop PCs.
> What are the SQL CAL Licensing issues that would apply to this scenario.
> Regards
sql

Million records table

Hi everybody,
I set up a merge replication on Sql2K with one publisher/distributor
and 6 anonymous subscribers that run msde.
The db is growing bigger and bigger and i can't understand why.
I noticed that some of the MS_xxxxx tables are very large: in
particular one is 16.7 millions and another over 770K. Is there a way
to reduce/shrink these tables?
Thanks
Lorenzo
How many rows are being modified (inserted/updated/deleted) in the replicated
articles? I would expect this to be a similar order of magnitude to the
msmerge-contents, msmerge_tombstone tables. These tables will be
automatically cleared down during synchronization as part of the meta data
cleanup naturally run in merge by sp_mergemetadataretentioncleanup so
normally you don't need to do anything - the rows will be removed as they
reach the retention period defined in the publication.
HTH,
Paul Ibison
|||On 17 Mag, 17:32, Paul Ibison <Paul.Ibi...@.Pygmalion.Com> wrote:
> How many rows are being modified (inserted/updated/deleted) in the replicated
> articles? I would expect this to be a similar order of magnitude to the
> msmerge-contents, msmerge_tombstone tables. These tables will be
> automatically cleared down during synchronization as part of the meta data
> cleanup naturally run in merge by sp_mergemetadataretentioncleanup so
> normally you don't need to do anything - the rows will be removed as they
> reach the retention period defined in the publication.
> HTH,
> Paul Ibison
Hi Paul,
i don't know exactly haw many rows are being modified, but i can tell
you that the largest replicated table counts less than 130000 records
and i'm sure only a small part of these are changed/added/deleted
the msmerge_tombstone now counts almost 7 millions rows
i'm having some timeout troubles when i synchronize and i often get
the "can't enumerate changes" error
now i'm checking all my tables and indexes to see if some are missing
(the publication is partitioned with dynamic filtering)
thanks a lot for your help
lorenzo
|||Paul Ibison:
> I'd do a join between the msmerge_tombstone table and sysmergearticles. Make
> it a group by on article name and count the records per article then compare
> this to the rowcounts. It may be that there are other articles/publications
> contributing that you haven't accounted for, but even if not, this should
> clarify the situation a bit.
> HTH,
> Paul Ibison
that was a great idea, Paul
i found that there are over 5 million rows relating to a view that
returns 4800!
now i know what to investigate ;-)

Migration replication jobs

Hi all,
I will be migrating a production database to a new box. I will be using
detach and attach db method to migrate, but I forsee that my replication job
will break. Can anyone advise me on how to migrate the replication jobs over
to the new db? Here is the sencario:
DB-1 in Box A detach and move over to Box B to be reattached,
DB-1 in Box A has some publications and subscriptions, and the distribution
is in Box C.
Let say Box B will resume the ip of Box A but different hostname.
You will be unable to detach replicated databases. You have to script out
the publications, edit them for the new server, remove all job names, and
then drop the publications, unpublish the databases and detach them. Attach
them on the new server, run the publication script and do a non sync.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"YK" <YK@.discussions.microsoft.com> wrote in message
news:25A6D0F1-6C2C-4B95-84F5-C96B963335D0@.microsoft.com...
> Hi all,
> I will be migrating a production database to a new box. I will be using
> detach and attach db method to migrate, but I forsee that my replication
job
> will break. Can anyone advise me on how to migrate the replication jobs
over
> to the new db? Here is the sencario:
> DB-1 in Box A detach and move over to Box B to be reattached,
> DB-1 in Box A has some publications and subscriptions, and the
distribution
> is in Box C.
> Let say Box B will resume the ip of Box A but different hostname.
>