Dear All,
I'm just configured transactional replication.
I have 2 DB Servers, OLTP Server and Reporting Server.
I uses transactional replication to replicate the data from OLTP Server
to Reporting Server.
I configured the replcation to use Push Subcription.
I set the schedule every 3 minutes bcos I need the report to be avaiable
as soon as possible.
Unfortunately, my Reporting Server got deadlock bcos of it.
Did I configure incorrectly?
Pls I need some suggestion for it.
Thanks
Robert Lie
Robert,
perhaps you could use the NOLOCK hint on the reporting server queries.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Showing posts with label reporting. Show all posts
Showing posts with label reporting. Show all posts
Monday, March 26, 2012
Tuesday, March 20, 2012
Replication and Reports
Hi!
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.
In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.
In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.
Replication and Reports
Hi!
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.
Replication and Reports
Hi!
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.
I have transactional replication setup to a remote server. My
application is pointing to that server for reporting function.
Everytime I ran a report it locks the replication and replication
times out. In my reports I specified No Lock (not sure if this did
anything) but reporting still locking replication.
Any suggestions on how to implement reporting and replication so the
latter doesn't lock the former?
Thank you,
T.In your reports, perhaps you have missed some nolock hints. It's easier to
set the transaction isolation level to read_uncommitted for the reporting
connection. Once that is done, start the distribution agent and if there is
still a problem, run profiler for the blocked process report (assuming sql
server 2005).
Also, if you are on sql server 2005, you might also want to investigate the
read_committed_snapshot option instead of nolock.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||On Feb 21, 10:13 am, "tolcis" <nytolly...@.gmail.com> wrote:
> Hi!
> I have transactional replication setup to a remote server. My
> application is pointing to that server for reporting function.
> Everytime I ran a report it locks the replication and replication
> times out. In my reports I specified No Lock (not sure if this did
> anything) but reporting still locking replication.
> Any suggestions on how to implement reporting and replication so the
> latter doesn't lock the former?
> Thank you,
> T.
Start by reviewing the indexes that are on your tables, and the
execution plans of the queries that your reports are running.
Reporting functions usually require a totally different indexing
strategy than transactional processing does. Proper indexes and
efficient queries will help minimize locking.
Replication and read only
There is a need for a read only reporting server because the production
server seems to be hit real hard.
The app is bad i agree but it may take too long to make the needed
changes in the code ( Have little control over that)
I am looking at transactional replication and the question is how can
the users be prevented from updating on this server, less taking away
all the rights of the 500 or so users and granted them read only
permissions
Your input as usual will be highly appreciatedMassa,
presumably the users are in a rolw, or access via a windows froup login? If
so, I'd add them the the db_datareader role and that's all
(db_denydatawriter if you're really worried, but the first role as the only
permission should be enough).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Hopefully there might be a way to add every user in a database to deny
datawriter without having to manually go in to check all users
Paul Ibison wrote:
> Massa,
> presumably the users are in a rolw, or access via a windows froup login? If
> so, I'd add them the the db_datareader role and that's all
> (db_denydatawriter if you're really worried, but the first role as the only
> permission should be enough).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Transactional replication does not require the subscriber to be read only.
You can use the continue on data consistency errors profile to remove any
possibility of conflicts arising from pk collisions, or missing rows.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159184643.454594.323960@.i3g2000cwc.googlegroups.com...
> There is a need for a read only reporting server because the production
> server seems to be hit real hard.
> The app is bad i agree but it may take too long to make the needed
> changes in the code ( Have little control over that)
> I am looking at transactional replication and the question is how can
> the users be prevented from updating on this server, less taking away
> all the rights of the 500 or so users and granted them read only
> permissions
> Your input as usual will be highly appreciated
>|||The best way would be to add all the users to a custom database role then
give permissions to that role. To add all the users into the role, you could
just script them out from the sysusers table and run sp_addrolemember to the
output. Something like this should do it:
SELECT 'sp_addrolemember ''yournewrole'', ' + name AS YourCommand
FROM sysusers
WHERE (gid = 0) AND (isntgroup = 1) OR
(gid = 0) AND (isntuser = 1) OR
(gid = 0) AND (issqluser = 1)
Then:
sp_addrolemember 'db_datareader', 'yournewrole'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Massa Batheli wrote:
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
> > Massa,
> > presumably the users are in a rolw, or access via a windows froup login? If
> > so, I'd add them the the db_datareader role and that's all
> > (db_denydatawriter if you're really worried, but the first role as the only
> > permission should be enough).
> > Cheers,
> > Paul Ibison SQL Server MVP, www.replicationanswers.com .
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com|||Massa Batheli wrote:
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
> > Massa,
> > presumably the users are in a rolw, or access via a windows froup login? If
> > so, I'd add them the the db_datareader role and that's all
> > (db_denydatawriter if you're really worried, but the first role as the only
> > permission should be enough).
> > Cheers,
> > Paul Ibison SQL Server MVP, www.replicationanswers.com .
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com
server seems to be hit real hard.
The app is bad i agree but it may take too long to make the needed
changes in the code ( Have little control over that)
I am looking at transactional replication and the question is how can
the users be prevented from updating on this server, less taking away
all the rights of the 500 or so users and granted them read only
permissions
Your input as usual will be highly appreciatedMassa,
presumably the users are in a rolw, or access via a windows froup login? If
so, I'd add them the the db_datareader role and that's all
(db_denydatawriter if you're really worried, but the first role as the only
permission should be enough).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Hopefully there might be a way to add every user in a database to deny
datawriter without having to manually go in to check all users
Paul Ibison wrote:
> Massa,
> presumably the users are in a rolw, or access via a windows froup login? If
> so, I'd add them the the db_datareader role and that's all
> (db_denydatawriter if you're really worried, but the first role as the only
> permission should be enough).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Transactional replication does not require the subscriber to be read only.
You can use the continue on data consistency errors profile to remove any
possibility of conflicts arising from pk collisions, or missing rows.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159184643.454594.323960@.i3g2000cwc.googlegroups.com...
> There is a need for a read only reporting server because the production
> server seems to be hit real hard.
> The app is bad i agree but it may take too long to make the needed
> changes in the code ( Have little control over that)
> I am looking at transactional replication and the question is how can
> the users be prevented from updating on this server, less taking away
> all the rights of the 500 or so users and granted them read only
> permissions
> Your input as usual will be highly appreciated
>|||The best way would be to add all the users to a custom database role then
give permissions to that role. To add all the users into the role, you could
just script them out from the sysusers table and run sp_addrolemember to the
output. Something like this should do it:
SELECT 'sp_addrolemember ''yournewrole'', ' + name AS YourCommand
FROM sysusers
WHERE (gid = 0) AND (isntgroup = 1) OR
(gid = 0) AND (isntuser = 1) OR
(gid = 0) AND (issqluser = 1)
Then:
sp_addrolemember 'db_datareader', 'yournewrole'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Massa Batheli wrote:
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
> > Massa,
> > presumably the users are in a rolw, or access via a windows froup login? If
> > so, I'd add them the the db_datareader role and that's all
> > (db_denydatawriter if you're really worried, but the first role as the only
> > permission should be enough).
> > Cheers,
> > Paul Ibison SQL Server MVP, www.replicationanswers.com .
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com|||Massa Batheli wrote:
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
> > Massa,
> > presumably the users are in a rolw, or access via a windows froup login? If
> > so, I'd add them the the db_datareader role and that's all
> > (db_denydatawriter if you're really worried, but the first role as the only
> > permission should be enough).
> > Cheers,
> > Paul Ibison SQL Server MVP, www.replicationanswers.com .
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com
Replication and read only
There is a need for a read only reporting server because the production
server seems to be hit real hard.
The app is bad i agree but it may take too long to make the needed
changes in the code ( Have little control over that)
I am looking at transactional replication and the question is how can
the users be prevented from updating on this server, less taking away
all the rights of the 500 or so users and granted them read only
permissions
Your input as usual will be highly appreciated
Massa,
presumably the users are in a rolw, or access via a windows froup login? If
so, I'd add them the the db_datareader role and that's all
(db_denydatawriter if you're really worried, but the first role as the only
permission should be enough).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul
Hopefully there might be a way to add every user in a database to deny
datawriter without having to manually go in to check all users
Paul Ibison wrote:
> Massa,
> presumably the users are in a rolw, or access via a windows froup login? If
> so, I'd add them the the db_datareader role and that's all
> (db_denydatawriter if you're really worried, but the first role as the only
> permission should be enough).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Transactional replication does not require the subscriber to be read only.
You can use the continue on data consistency errors profile to remove any
possibility of conflicts arising from pk collisions, or missing rows.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159184643.454594.323960@.i3g2000cwc.googlegro ups.com...
> There is a need for a read only reporting server because the production
> server seems to be hit real hard.
> The app is bad i agree but it may take too long to make the needed
> changes in the code ( Have little control over that)
> I am looking at transactional replication and the question is how can
> the users be prevented from updating on this server, less taking away
> all the rights of the 500 or so users and granted them read only
> permissions
> Your input as usual will be highly appreciated
>
|||The best way would be to add all the users to a custom database role then
give permissions to that role. To add all the users into the role, you could
just script them out from the sysusers table and run sp_addrolemember to the
output. Something like this should do it:
SELECT 'sp_addrolemember ''yournewrole'', ' + name AS YourCommand
FROM sysusers
WHERE (gid = 0) AND (isntgroup = 1) OR
(gid = 0) AND (isntuser = 1) OR
(gid = 0) AND (issqluser = 1)
Then:
sp_addrolemember 'db_datareader', 'yournewrole'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Massa Batheli wrote:
[vbcol=seagreen]
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com
|||Massa Batheli wrote:
[vbcol=seagreen]
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com
server seems to be hit real hard.
The app is bad i agree but it may take too long to make the needed
changes in the code ( Have little control over that)
I am looking at transactional replication and the question is how can
the users be prevented from updating on this server, less taking away
all the rights of the 500 or so users and granted them read only
permissions
Your input as usual will be highly appreciated
Massa,
presumably the users are in a rolw, or access via a windows froup login? If
so, I'd add them the the db_datareader role and that's all
(db_denydatawriter if you're really worried, but the first role as the only
permission should be enough).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul
Hopefully there might be a way to add every user in a database to deny
datawriter without having to manually go in to check all users
Paul Ibison wrote:
> Massa,
> presumably the users are in a rolw, or access via a windows froup login? If
> so, I'd add them the the db_datareader role and that's all
> (db_denydatawriter if you're really worried, but the first role as the only
> permission should be enough).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Transactional replication does not require the subscriber to be read only.
You can use the continue on data consistency errors profile to remove any
possibility of conflicts arising from pk collisions, or missing rows.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159184643.454594.323960@.i3g2000cwc.googlegro ups.com...
> There is a need for a read only reporting server because the production
> server seems to be hit real hard.
> The app is bad i agree but it may take too long to make the needed
> changes in the code ( Have little control over that)
> I am looking at transactional replication and the question is how can
> the users be prevented from updating on this server, less taking away
> all the rights of the 500 or so users and granted them read only
> permissions
> Your input as usual will be highly appreciated
>
|||The best way would be to add all the users to a custom database role then
give permissions to that role. To add all the users into the role, you could
just script them out from the sysusers table and run sp_addrolemember to the
output. Something like this should do it:
SELECT 'sp_addrolemember ''yournewrole'', ' + name AS YourCommand
FROM sysusers
WHERE (gid = 0) AND (isntgroup = 1) OR
(gid = 0) AND (isntuser = 1) OR
(gid = 0) AND (issqluser = 1)
Then:
sp_addrolemember 'db_datareader', 'yournewrole'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Massa Batheli wrote:
[vbcol=seagreen]
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com
|||Massa Batheli wrote:
[vbcol=seagreen]
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com
Replication and read only
There is a need for a read only reporting server because the production
server seems to be hit real hard.
The app is bad i agree but it may take too long to make the needed
changes in the code ( Have little control over that)
I am looking at transactional replication and the question is how can
the users be prevented from updating on this server, less taking away
all the rights of the 500 or so users and granted them read only
permissions
Your input as usual will be highly appreciatedMassa,
presumably the users are in a rolw, or access via a windows froup login? If
so, I'd add them the the db_datareader role and that's all
(db_denydatawriter if you're really worried, but the first role as the only
permission should be enough).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Hopefully there might be a way to add every user in a database to deny
datawriter without having to manually go in to check all users
Paul Ibison wrote:
> Massa,
> presumably the users are in a rolw, or access via a windows froup login? I
f
> so, I'd add them the the db_datareader role and that's all
> (db_denydatawriter if you're really worried, but the first role as the onl
y
> permission should be enough).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Transactional replication does not require the subscriber to be read only.
You can use the continue on data consistency errors profile to remove any
possibility of conflicts arising from pk collisions, or missing rows.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159184643.454594.323960@.i3g2000cwc.googlegroups.com...
> There is a need for a read only reporting server because the production
> server seems to be hit real hard.
> The app is bad i agree but it may take too long to make the needed
> changes in the code ( Have little control over that)
> I am looking at transactional replication and the question is how can
> the users be prevented from updating on this server, less taking away
> all the rights of the 500 or so users and granted them read only
> permissions
> Your input as usual will be highly appreciated
>|||The best way would be to add all the users to a custom database role then
give permissions to that role. To add all the users into the role, you could
just script them out from the sysusers table and run sp_addrolemember to the
output. Something like this should do it:
SELECT 'sp_addrolemember ''yournewrole'', ' + name AS YourCommand
FROM sysusers
WHERE (gid = 0) AND (isntgroup = 1) OR
(gid = 0) AND (isntuser = 1) OR
(gid = 0) AND (issqluser = 1)
Then:
sp_addrolemember 'db_datareader', 'yournewrole'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Massa Batheli wrote:
[vbcol=seagreen]
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com|||Massa Batheli wrote:
[vbcol=seagreen]
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com
server seems to be hit real hard.
The app is bad i agree but it may take too long to make the needed
changes in the code ( Have little control over that)
I am looking at transactional replication and the question is how can
the users be prevented from updating on this server, less taking away
all the rights of the 500 or so users and granted them read only
permissions
Your input as usual will be highly appreciatedMassa,
presumably the users are in a rolw, or access via a windows froup login? If
so, I'd add them the the db_datareader role and that's all
(db_denydatawriter if you're really worried, but the first role as the only
permission should be enough).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Thanks Paul
Hopefully there might be a way to add every user in a database to deny
datawriter without having to manually go in to check all users
Paul Ibison wrote:
> Massa,
> presumably the users are in a rolw, or access via a windows froup login? I
f
> so, I'd add them the the db_datareader role and that's all
> (db_denydatawriter if you're really worried, but the first role as the onl
y
> permission should be enough).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Transactional replication does not require the subscriber to be read only.
You can use the continue on data consistency errors profile to remove any
possibility of conflicts arising from pk collisions, or missing rows.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Massa Batheli" <mngong@.gmail.com> wrote in message
news:1159184643.454594.323960@.i3g2000cwc.googlegroups.com...
> There is a need for a read only reporting server because the production
> server seems to be hit real hard.
> The app is bad i agree but it may take too long to make the needed
> changes in the code ( Have little control over that)
> I am looking at transactional replication and the question is how can
> the users be prevented from updating on this server, less taking away
> all the rights of the 500 or so users and granted them read only
> permissions
> Your input as usual will be highly appreciated
>|||The best way would be to add all the users to a custom database role then
give permissions to that role. To add all the users into the role, you could
just script them out from the sysusers table and run sp_addrolemember to the
output. Something like this should do it:
SELECT 'sp_addrolemember ''yournewrole'', ' + name AS YourCommand
FROM sysusers
WHERE (gid = 0) AND (isntgroup = 1) OR
(gid = 0) AND (isntuser = 1) OR
(gid = 0) AND (issqluser = 1)
Then:
sp_addrolemember 'db_datareader', 'yournewrole'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Massa Batheli wrote:
[vbcol=seagreen]
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com|||Massa Batheli wrote:
[vbcol=seagreen]
> Thanks Paul
> Hopefully there might be a way to add every user in a database to deny
> datawriter without having to manually go in to check all users
> Paul Ibison wrote:
Yes
Create a windows user group and all 500 users to that group. Grant that
group datareader permission .
Regards
Amish shah
http://shahamishm.tripod.com
Replication and Mirroring
I am setting up transactional replication on a database A with read only subscriber database B (for reporting purpose). I have also setup mirror on database A. I tried to manually failover to mirror...mirroring works fine but transactional replication is interrupted.
I have followed the steps as in article
http://msdn2.microsoft.com/en-us/library/ms151799.aspx
I have also used PublisherFailoverPartner parameter...but replication still complains that it cannot connect to publishing database.
Any ideas or direction is greatly appreciated.
I would trouble shoot the following way:
1) try to connect to the old mirror (which is the new principal after the failover) database from the distributor (for example using osql.exe) and see if connection can be made. This will eliminate the obvious connection issue.
2) check to see if the server that hosted the mirrored database was configured as publisher using the same distributor
Monday, March 12, 2012
Replication Active Connection
Using SQL Server 2000 transactional replication which is running continously
to populate our reporting server. We have a thrid party software which we
need to use a interface to take a cvs file and import it into their database.
The interface requires that it has exclusive access to the database. Since we
are running replication there is always a constant connection to the
published database. Is there away to stop that connection. I have tried
stopping the synchronization but it seems to me the only way to remove that
connection is to delete the publiction. Is there an easier way.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200610/1
If the third party software needs access to the subscriber database you
could stop the distribution agent, or have the third party application put
the database in read only or single user mode before it starts to do its
work.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"masmith via droptable.com" <u27472@.uwe> wrote in message
news:680d19bf42976@.uwe...
> Using SQL Server 2000 transactional replication which is running
> continously
> to populate our reporting server. We have a thrid party software which we
> need to use a interface to take a cvs file and import it into their
> database.
> The interface requires that it has exclusive access to the database. Since
> we
> are running replication there is always a constant connection to the
> published database. Is there away to stop that connection. I have tried
> stopping the synchronization but it seems to me the only way to remove
> that
> connection is to delete the publiction. Is there an easier way.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ation/200610/1
>
to populate our reporting server. We have a thrid party software which we
need to use a interface to take a cvs file and import it into their database.
The interface requires that it has exclusive access to the database. Since we
are running replication there is always a constant connection to the
published database. Is there away to stop that connection. I have tried
stopping the synchronization but it seems to me the only way to remove that
connection is to delete the publiction. Is there an easier way.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200610/1
If the third party software needs access to the subscriber database you
could stop the distribution agent, or have the third party application put
the database in read only or single user mode before it starts to do its
work.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"masmith via droptable.com" <u27472@.uwe> wrote in message
news:680d19bf42976@.uwe...
> Using SQL Server 2000 transactional replication which is running
> continously
> to populate our reporting server. We have a thrid party software which we
> need to use a interface to take a cvs file and import it into their
> database.
> The interface requires that it has exclusive access to the database. Since
> we
> are running replication there is always a constant connection to the
> published database. Is there away to stop that connection. I have tried
> stopping the synchronization but it seems to me the only way to remove
> that
> connection is to delete the publiction. Is there an easier way.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ation/200610/1
>
Friday, March 9, 2012
replication & log shipping
Has anyone tried to do replication or log shipping on the same server? The
reason being I need a replica copy to server the reporting service so that
heavy loaded queries won't impact the online processing.
Thanks for any advice.
Joseph,
I regularly set up replication on the same server for testing purposes and
have not had any problems. If you haven't yet seen it, have a look at the
article here which describes the pros and cons of log shipping and
transactional replication for setting up a reporting server:
http://www.replicationanswers.com/Standby.asp
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
reason being I need a replica copy to server the reporting service so that
heavy loaded queries won't impact the online processing.
Thanks for any advice.
Joseph,
I regularly set up replication on the same server for testing purposes and
have not had any problems. If you haven't yet seen it, have a look at the
article here which describes the pros and cons of log shipping and
transactional replication for setting up a reporting server:
http://www.replicationanswers.com/Standby.asp
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Replication - Replicating the contents from a view to a Table
I have a complex query I use to populate reports. I need to move the
reporting environment from actual production database that gets updated
frequently. I have converted the reporting query into a view that pulls
information from many tables with complex joins
In my reporting databases, i have a simple table with the exact fields
in my view.
So here is the situation.
Server A (Live production DB)
Many tables
View - V_abc (with complex joins and returns some columns)
Server B (Reporting Server)
View - V_abc (referencing T_abc with the same exact column on ServerA
view columns)
Table - T_abc (referenced by V-abc with the same column names)
I am trying to set up transactional replicatin (as I need live data) to
pull information from the Production Server A view and insert it into
the reporting Server B db view (V_abc) which will place data to the
underlaying table (T_abc).
I keep getting the error message when I start the replication
"Unable to replicate a view or function because the referenced objects
or columns are not present on the Subscriber. - (Source: AGENT (Agent);
Error number: 00000)"
Its also dropping the View in ServerB.
Is this even posible or am I just chasing ghost..?
I would appreciate some help with this problem...I would guess that the view is looking for the underlying tables on the
subscriber to create the view since there is no data stored with the view.
I wonder if you created an clustered index on the view then replicated the
view? Haven't tested it but something to consider if you are not going to
be replicating the underlying tables and SQL wants you to.
HTH
Jerry
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1148668560.818932.177910@.y43g2000cwc.googlegroups.com...
>I have a complex query I use to populate reports. I need to move the
> reporting environment from actual production database that gets updated
> frequently. I have converted the reporting query into a view that pulls
> information from many tables with complex joins
> In my reporting databases, i have a simple table with the exact fields
> in my view.
> So here is the situation.
> Server A (Live production DB)
> Many tables
> View - V_abc (with complex joins and returns some columns)
> Server B (Reporting Server)
> View - V_abc (referencing T_abc with the same exact column on ServerA
> view columns)
> Table - T_abc (referenced by V-abc with the same column names)
> I am trying to set up transactional replicatin (as I need live data) to
> pull information from the Production Server A view and insert it into
> the reporting Server B db view (V_abc) which will place data to the
> underlaying table (T_abc).
> I keep getting the error message when I start the replication
> "Unable to replicate a view or function because the referenced objects
> or columns are not present on the Subscriber. - (Source: AGENT (Agent);
> Error number: 00000)"
> Its also dropping the View in ServerB.
> Is this even posible or am I just chasing ghost..?
> I would appreciate some help with this problem...
>|||Thanks Jerry for your response...
I am not really clear where I need to have the index view.
Should I create this indexed view on Server A (My publisher) or Server
B (Subscriber). I am using Standard edition... Is there a limitation
to perform this kind of replication?
Thanks again...
reporting environment from actual production database that gets updated
frequently. I have converted the reporting query into a view that pulls
information from many tables with complex joins
In my reporting databases, i have a simple table with the exact fields
in my view.
So here is the situation.
Server A (Live production DB)
Many tables
View - V_abc (with complex joins and returns some columns)
Server B (Reporting Server)
View - V_abc (referencing T_abc with the same exact column on ServerA
view columns)
Table - T_abc (referenced by V-abc with the same column names)
I am trying to set up transactional replicatin (as I need live data) to
pull information from the Production Server A view and insert it into
the reporting Server B db view (V_abc) which will place data to the
underlaying table (T_abc).
I keep getting the error message when I start the replication
"Unable to replicate a view or function because the referenced objects
or columns are not present on the Subscriber. - (Source: AGENT (Agent);
Error number: 00000)"
Its also dropping the View in ServerB.
Is this even posible or am I just chasing ghost..?
I would appreciate some help with this problem...I would guess that the view is looking for the underlying tables on the
subscriber to create the view since there is no data stored with the view.
I wonder if you created an clustered index on the view then replicated the
view? Haven't tested it but something to consider if you are not going to
be replicating the underlying tables and SQL wants you to.
HTH
Jerry
"Query Builder" <querybuilder@.gmail.com> wrote in message
news:1148668560.818932.177910@.y43g2000cwc.googlegroups.com...
>I have a complex query I use to populate reports. I need to move the
> reporting environment from actual production database that gets updated
> frequently. I have converted the reporting query into a view that pulls
> information from many tables with complex joins
> In my reporting databases, i have a simple table with the exact fields
> in my view.
> So here is the situation.
> Server A (Live production DB)
> Many tables
> View - V_abc (with complex joins and returns some columns)
> Server B (Reporting Server)
> View - V_abc (referencing T_abc with the same exact column on ServerA
> view columns)
> Table - T_abc (referenced by V-abc with the same column names)
> I am trying to set up transactional replicatin (as I need live data) to
> pull information from the Production Server A view and insert it into
> the reporting Server B db view (V_abc) which will place data to the
> underlaying table (T_abc).
> I keep getting the error message when I start the replication
> "Unable to replicate a view or function because the referenced objects
> or columns are not present on the Subscriber. - (Source: AGENT (Agent);
> Error number: 00000)"
> Its also dropping the View in ServerB.
> Is this even posible or am I just chasing ghost..?
> I would appreciate some help with this problem...
>|||Thanks Jerry for your response...
I am not really clear where I need to have the index view.
Should I create this indexed view on Server A (My publisher) or Server
B (Subscriber). I am using Standard edition... Is there a limitation
to perform this kind of replication?
Thanks again...
Replication - filtering
Hi All,
Is it possible to replicate only INSERTS and UPDATES and NOT Deletes.
We have a production server and reporting server. The challenge is to keep
90days of data in the production system, and 180days of data on the
reporting system. Need to some how to ensure that the cleanups done on the
Production server are not propogated through to the Reporting server.
Really appreciate it if someone can point me in the right direction - I am
sure this problem has been solved before.
thanks
Mathew
If you're using transactional replication, on the article properties,
commands tab, enter NONE for the delete command. if it's merge, there isn't
a straightforward method, but provided you understand the ramifications, you
could disable the merge delete trigger.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Is it possible to replicate only INSERTS and UPDATES and NOT Deletes.
We have a production server and reporting server. The challenge is to keep
90days of data in the production system, and 180days of data on the
reporting system. Need to some how to ensure that the cleanups done on the
Production server are not propogated through to the Reporting server.
Really appreciate it if someone can point me in the right direction - I am
sure this problem has been solved before.
thanks
Mathew
If you're using transactional replication, on the article properties,
commands tab, enter NONE for the delete command. if it's merge, there isn't
a straightforward method, but provided you understand the ramifications, you
could disable the merge delete trigger.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Wednesday, March 7, 2012
Replication
Hi
We have 50 tables on our live Database instance. I need to replicate 10 of these tables for reporting and archving purposes. What is the best approach. Any good sites recommended for reference? Database is SQLServer 7.0.
ThanksI can tell you about our experience here. We are in the same boat that you are in, we have a production server and a reports server. I've implemented 2 types of replication Transaction and Snapshot.
Snapshot
Easiest to implenent, however if these tables have a lot of data could put a stress on the network and Disk IO on servers.
Transaction
Required us to create indexes using PRIMARY KEY constraints NOT CREATE INDEX. A little more complicated, but less data during the transmission, therefore less stress on system
It also depends on how up-to-date the data needs to be on the reporting server. For use it was 24 hrs behind, so it represented data as of yesterday. In this case I could setup transactional to be every 4 hrs. For Transactional should know your production servers daily activity, highly active server with a large window for replication (ie. 4hrs for use) could cause system stress and therefore the replication window may need to be decreased.
A lot to think about also you need to know were you will place the Distribution Service, we placed ours on the Reporting server.
Replication for SQL Server 7.0 (http://www.microsoft.com/SQL/techinfo/administration/70/replication.asp)
Here there are a few links at the bottom of the page on replication
SQL Server Performance (http://www.sql-server-performance.com/default.asp)
We have 50 tables on our live Database instance. I need to replicate 10 of these tables for reporting and archving purposes. What is the best approach. Any good sites recommended for reference? Database is SQLServer 7.0.
ThanksI can tell you about our experience here. We are in the same boat that you are in, we have a production server and a reports server. I've implemented 2 types of replication Transaction and Snapshot.
Snapshot
Easiest to implenent, however if these tables have a lot of data could put a stress on the network and Disk IO on servers.
Transaction
Required us to create indexes using PRIMARY KEY constraints NOT CREATE INDEX. A little more complicated, but less data during the transmission, therefore less stress on system
It also depends on how up-to-date the data needs to be on the reporting server. For use it was 24 hrs behind, so it represented data as of yesterday. In this case I could setup transactional to be every 4 hrs. For Transactional should know your production servers daily activity, highly active server with a large window for replication (ie. 4hrs for use) could cause system stress and therefore the replication window may need to be decreased.
A lot to think about also you need to know were you will place the Distribution Service, we placed ours on the Reporting server.
Replication for SQL Server 7.0 (http://www.microsoft.com/SQL/techinfo/administration/70/replication.asp)
Here there are a few links at the bottom of the page on replication
SQL Server Performance (http://www.sql-server-performance.com/default.asp)
Monday, February 20, 2012
Replicating mirrored dbs
Hello,
We're looking to replicate Database A to Database B. (transactional DB to a
reporting server).
Both database A and database B are mirrored. Can we setup replication from
database A to database B and have failover not interupt replication? I've
been playing around with a test setup and replication works fine until I
failover database A or database B. I've performed the steps outlined here as
best as I understand them:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/82796217-02e2-4bc5-9ab5-218bae11a2d6.htm
However once I failover either database, replication complains that it can't
connect to the publishing db or subscription db. (which makes sense since
they've failed over). Any direction or resources on setting this up would be
greatly appreciated.
Thanks in advance.I forgot to mention this is one way transactional replication with no
updating subscribers.
"sqlboy2000" wrote:
> Hello,
> We're looking to replicate Database A to Database B. (transactional DB to a
> reporting server).
> Both database A and database B are mirrored. Can we setup replication from
> database A to database B and have failover not interupt replication? I've
> been playing around with a test setup and replication works fine until I
> failover database A or database B. I've performed the steps outlined here as
> best as I understand them:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/82796217-02e2-4bc5-9ab5-218bae11a2d6.htm
> However once I failover either database, replication complains that it can't
> connect to the publishing db or subscription db. (which makes sense since
> they've failed over). Any direction or resources on setting this up would be
> greatly appreciated.
> Thanks in advance.|||No, but if you have a remote distributor you can use the alternate log
reader switch to have the standby come up and replicate to the remote
distributor and its subscribers. Have a look at the PublisherFailoverPartner
setting.
--
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
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:4BA4B447-CCB4-4402-B7DD-DAC7B03F31E2@.microsoft.com...
> Hello,
> We're looking to replicate Database A to Database B. (transactional DB to
> a
> reporting server).
> Both database A and database B are mirrored. Can we setup replication from
> database A to database B and have failover not interupt replication? I've
> been playing around with a test setup and replication works fine until I
> failover database A or database B. I've performed the steps outlined here
> as
> best as I understand them:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/82796217-02e2-4bc5-9ab5-218bae11a2d6.htm
> However once I failover either database, replication complains that it
> can't
> connect to the publishing db or subscription db. (which makes sense since
> they've failed over). Any direction or resources on setting this up would
> be
> greatly appreciated.
> Thanks in advance.
We're looking to replicate Database A to Database B. (transactional DB to a
reporting server).
Both database A and database B are mirrored. Can we setup replication from
database A to database B and have failover not interupt replication? I've
been playing around with a test setup and replication works fine until I
failover database A or database B. I've performed the steps outlined here as
best as I understand them:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/82796217-02e2-4bc5-9ab5-218bae11a2d6.htm
However once I failover either database, replication complains that it can't
connect to the publishing db or subscription db. (which makes sense since
they've failed over). Any direction or resources on setting this up would be
greatly appreciated.
Thanks in advance.I forgot to mention this is one way transactional replication with no
updating subscribers.
"sqlboy2000" wrote:
> Hello,
> We're looking to replicate Database A to Database B. (transactional DB to a
> reporting server).
> Both database A and database B are mirrored. Can we setup replication from
> database A to database B and have failover not interupt replication? I've
> been playing around with a test setup and replication works fine until I
> failover database A or database B. I've performed the steps outlined here as
> best as I understand them:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/82796217-02e2-4bc5-9ab5-218bae11a2d6.htm
> However once I failover either database, replication complains that it can't
> connect to the publishing db or subscription db. (which makes sense since
> they've failed over). Any direction or resources on setting this up would be
> greatly appreciated.
> Thanks in advance.|||No, but if you have a remote distributor you can use the alternate log
reader switch to have the standby come up and replicate to the remote
distributor and its subscribers. Have a look at the PublisherFailoverPartner
setting.
--
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
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:4BA4B447-CCB4-4402-B7DD-DAC7B03F31E2@.microsoft.com...
> Hello,
> We're looking to replicate Database A to Database B. (transactional DB to
> a
> reporting server).
> Both database A and database B are mirrored. Can we setup replication from
> database A to database B and have failover not interupt replication? I've
> been playing around with a test setup and replication works fine until I
> failover database A or database B. I've performed the steps outlined here
> as
> best as I understand them:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/82796217-02e2-4bc5-9ab5-218bae11a2d6.htm
> However once I failover either database, replication complains that it
> can't
> connect to the publishing db or subscription db. (which makes sense since
> they've failed over). Any direction or resources on setting this up would
> be
> greatly appreciated.
> Thanks in advance.
Subscribe to:
Posts (Atom)