hi,
our app works in a multi tier model to provide inet connectivity
now a customer wants to be able to keep working even if the inet is down or
the main server is down
so the obvious solution is to use replication
so:
1) what is the best place to start understanding what is involved
2) all relations are now by the use of integer PK's, some of them are auto
generated, does this has to change?
3) can i have only selected tables replicated?
4) can i have only a subset of rows of a table replicated?
tia!
Marc
1. BOL, www.replicationanswers.com, go for merge replication
2. N
3. Y
4. Y
Hope this will help
-Ravi Lobo
"Marc Antheunis" wrote:
> hi,
> our app works in a multi tier model to provide inet connectivity
> now a customer wants to be able to keep working even if the inet is down or
> the main server is down
> so the obvious solution is to use replication
> so:
> 1) what is the best place to start understanding what is involved
> 2) all relations are now by the use of integer PK's, some of them are auto
> generated, does this has to change?
> 3) can i have only selected tables replicated?
> 4) can i have only a subset of rows of a table replicated?
> tia!
> Marc
>
>
|||1) Well there's my book. Query Replication Tutorials on the web
2) No, not for one way transactional replication. With merge replication DRI
is maintained but another tracking column is added - a guid column.
3) Yes, for transactional replication all tables must have a pk, with merge
they don't require a pk.
4) Yes, as long as there is a criteria to filter it by you can replicate a
subset of rows - this is called horizontal partitioning or horizontal
filtering.
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
"Marc Antheunis" <ask_me@.ask.me> wrote in message
news:uCKSLCKJFHA.2956@.TK2MSFTNGP12.phx.gbl...
> hi,
> our app works in a multi tier model to provide inet connectivity
> now a customer wants to be able to keep working even if the inet is down
or
> the main server is down
> so the obvious solution is to use replication
> so:
> 1) what is the best place to start understanding what is involved
> 2) all relations are now by the use of integer PK's, some of them are auto
> generated, does this has to change?
> 3) can i have only selected tables replicated?
> 4) can i have only a subset of rows of a table replicated?
> tia!
> Marc
>
|||Hi Hilary!
i just phoned the biggest IT related book store in belgium but they don't
sell your book
awaiting an answer from them if it is availabel in belgium
i'll try to collect some ideas and post some to the point questions
thx
marc
"Hilary Cotter" <hilary.cotter@.gmail.com> schreef in bericht
news:eXyQEfLJFHA.3928@.TK2MSFTNGP09.phx.gbl...
> 1) Well there's my book. Query Replication Tutorials on the web
> 2) No, not for one way transactional replication. With merge replication
> DRI
> is maintained but another tracking column is added - a guid column.
> 3) Yes, for transactional replication all tables must have a pk, with
> merge
> they don't require a pk.
> 4) Yes, as long as there is a criteria to filter it by you can replicate a
> subset of rows - this is called horizontal partitioning or horizontal
> filtering.
> --
> 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
> "Marc Antheunis" <ask_me@.ask.me> wrote in message
> news:uCKSLCKJFHA.2956@.TK2MSFTNGP12.phx.gbl...
> or
>
|||You can get it from Amazon.com or you can order it by following this link:
http://www.nwsu.com/0974973602.html
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
"Marc Antheunis" <ask_me@.ask.me> wrote in message
news:uCCa01LJFHA.1860@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Hi Hilary!
> i just phoned the biggest IT related book store in belgium but they don't
> sell your book
> awaiting an answer from them if it is availabel in belgium
> i'll try to collect some ideas and post some to the point questions
> thx
> marc
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> schreef in bericht
> news:eXyQEfLJFHA.3928@.TK2MSFTNGP09.phx.gbl...
a[vbcol=seagreen]
down
>
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Friday, March 30, 2012
New to replication please help
Hi
I am new to this topic.i am trying to create a publisher in sql server 2005
here is what i do..
sql server studio->replication->local publisher->New publication
I get this error mesage
"Microsoft sql server management studio is unable to access replication components because replication is not installed on this instance of sql server
Additional information:Execute permission denied on object sp_MS_replication_installed database:master,owner:dbo"
I have installed sql server again but it show me the same error.
please anyone let me know.
To install here are the steps which i followed.
To install replication components, including Replication Management Objects (RMO), select SQL Server Database Services on the Components to Install page of the Installation Wizard.
To install replication components:
On the Components to Install page of the SQL Server Installation Wizard, click Advanced.
On the Feature Selection page, expand Database Services.
Click Replication, and then click Entire feature will be installed on local hard drive.
After this a screen pops up saying Database Services on the Components installed.
there is a button at the bottom called details when clicked shows up with the message below.
Name: Microsoft SQL Server 2000
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Service pack requirement check:
Your upgrade is blocked because of service pack requirements. To proceed, apply the required service pack and then rerun SQL Server Setup. For more information about upgrade support, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Edition check:
Your upgrade is blocked because of edition upgrade rules. For more information about edition upgrades, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
I have no idea what does this message means.
please let me know
|||It means you're trying to upgrade, but cannot due to requriements that you haven't met. Read the books online topics for more information.New to replication please help
Hi
I am new to this topic.i am trying to create a publisher in sql server 2005
here is what i do..
sql server studio->replication->local publisher->New publication
I get this error mesage
"Microsoft sql server management studio is unable to access replication components because replication is not installed on this instance of sql server
Additional information:Execute permission denied on object sp_MS_replication_installed database:master,owner:dbo"
I have installed sql server again but it show me the same error.
please anyone let me know.
To install here are the steps which i followed.
To install replication components, including Replication Management Objects (RMO), select SQL Server Database Services on the Components to Install page of the Installation Wizard.
To install replication components:
On the Components to Install page of the SQL Server Installation Wizard, click Advanced.
On the Feature Selection page, expand Database Services.
Click Replication, and then click Entire feature will be installed on local hard drive.
After this a screen pops up saying Database Services on the Components installed.
there is a button at the bottom called details when clicked shows up with the message below.
Name: Microsoft SQL Server 2000
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Service pack requirement check:
Your upgrade is blocked because of service pack requirements. To proceed, apply the required service pack and then rerun SQL Server Setup. For more information about upgrade support, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Edition check:
Your upgrade is blocked because of edition upgrade rules. For more information about edition upgrades, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
I have no idea what does this message means.
please let me know
|||It means you're trying to upgrade, but cannot due to requriements that you haven't met. Read the books online topics for more information.New to Replication
I am new to replication. Please forgive me if this sounds silly.
I have one publisher and several subscriber, all in different locations. In
case of emergency and I have to shut down the publisher, I want to use the
one of the subscribers to process our business, and I want to turn this
subscriber to a new publisher. Is this possible? What kind of replication
should I use?
Its not really possible in SQL 2000, but it looks like the peer-to-peer
replication model in 2005.
You have to look at how the data flows. If it flows only from the publisher
to the subscribers it might be possible to convert a subscriber to a
publisher by applying the publication creation script on one of the
subscribers you wish to promote the new publisher. Then make all other
subscribers subscribers of this new publisher.
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
"Wind" <Wind@.discussions.microsoft.com> wrote in message
news:4AF7A0BF-DB5F-43E8-BD2C-D7F1EE5E6098@.microsoft.com...
> I am new to replication. Please forgive me if this sounds silly.
> I have one publisher and several subscriber, all in different locations.
In
> case of emergency and I have to shut down the publisher, I want to use the
> one of the subscribers to process our business, and I want to turn this
> subscriber to a new publisher. Is this possible? What kind of replication
> should I use?
|||Thanks a lot for your reply.
Do you mean that if I am in SQL 2000, and the subscribers only read data,
not write, I should be able to turn one subscriber to a publisher? Actually,
what I want to do is, when the publisher has to be shut down (or damaged), I
will still have the full database for business continuity in another
location. This can be a kind of emergency backup system.
"Hilary Cotter" wrote:
> Its not really possible in SQL 2000, but it looks like the peer-to-peer
> replication model in 2005.
> You have to look at how the data flows. If it flows only from the publisher
> to the subscribers it might be possible to convert a subscriber to a
> publisher by applying the publication creation script on one of the
> subscribers you wish to promote the new publisher. Then make all other
> subscribers subscribers of this new publisher.
> --
> 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
> "Wind" <Wind@.discussions.microsoft.com> wrote in message
> news:4AF7A0BF-DB5F-43E8-BD2C-D7F1EE5E6098@.microsoft.com...
> In
>
>
|||Yes you can do this, but its difficult. Basically you have to prep your
database on the subscriber.
IIRC you have to do the following
1) backup the publisher
2) restore it on the subscriber
3) change all identity columns to NFR
4) change all triggers to NFR
5) change all constraints to NFR
You may need to run DBCC checkident ('tableName', reseed,X) on some of the
subscriber tables to fix the identity property on the restored databases,
don't use the set identity_insert 'tableName', off in the insert procs
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
"Wind" <Wind@.discussions.microsoft.com> wrote in message
news:346FA462-5CD1-4156-BC06-E6B0662E4BFF@.microsoft.com...[vbcol=seagreen]
> Thanks a lot for your reply.
> Do you mean that if I am in SQL 2000, and the subscribers only read data,
> not write, I should be able to turn one subscriber to a publisher?
> Actually,
> what I want to do is, when the publisher has to be shut down (or damaged),
> I
> will still have the full database for business continuity in another
> location. This can be a kind of emergency backup system.
> "Hilary Cotter" wrote:
|||What about the alternate synchronization partner?
Jos.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23zmkjND0FHA.2960@.tk2msftngp13.phx.gbl...
> Its not really possible in SQL 2000, but it looks like the peer-to-peer
> replication model in 2005.
> You have to look at how the data flows. If it flows only from the
publisher[vbcol=seagreen]
> to the subscribers it might be possible to convert a subscriber to a
> publisher by applying the publication creation script on one of the
> subscribers you wish to promote the new publisher. Then make all other
> subscribers subscribers of this new publisher.
> --
> 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
> "Wind" <Wind@.discussions.microsoft.com> wrote in message
> news:4AF7A0BF-DB5F-43E8-BD2C-D7F1EE5E6098@.microsoft.com...
> In
the[vbcol=seagreen]
replication
>
|||Plain merge and queued updating subscribers would also come into this
section, although none of these techniques would really allow the subscriber
to easily become the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I have one publisher and several subscriber, all in different locations. In
case of emergency and I have to shut down the publisher, I want to use the
one of the subscribers to process our business, and I want to turn this
subscriber to a new publisher. Is this possible? What kind of replication
should I use?
Its not really possible in SQL 2000, but it looks like the peer-to-peer
replication model in 2005.
You have to look at how the data flows. If it flows only from the publisher
to the subscribers it might be possible to convert a subscriber to a
publisher by applying the publication creation script on one of the
subscribers you wish to promote the new publisher. Then make all other
subscribers subscribers of this new publisher.
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
"Wind" <Wind@.discussions.microsoft.com> wrote in message
news:4AF7A0BF-DB5F-43E8-BD2C-D7F1EE5E6098@.microsoft.com...
> I am new to replication. Please forgive me if this sounds silly.
> I have one publisher and several subscriber, all in different locations.
In
> case of emergency and I have to shut down the publisher, I want to use the
> one of the subscribers to process our business, and I want to turn this
> subscriber to a new publisher. Is this possible? What kind of replication
> should I use?
|||Thanks a lot for your reply.
Do you mean that if I am in SQL 2000, and the subscribers only read data,
not write, I should be able to turn one subscriber to a publisher? Actually,
what I want to do is, when the publisher has to be shut down (or damaged), I
will still have the full database for business continuity in another
location. This can be a kind of emergency backup system.
"Hilary Cotter" wrote:
> Its not really possible in SQL 2000, but it looks like the peer-to-peer
> replication model in 2005.
> You have to look at how the data flows. If it flows only from the publisher
> to the subscribers it might be possible to convert a subscriber to a
> publisher by applying the publication creation script on one of the
> subscribers you wish to promote the new publisher. Then make all other
> subscribers subscribers of this new publisher.
> --
> 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
> "Wind" <Wind@.discussions.microsoft.com> wrote in message
> news:4AF7A0BF-DB5F-43E8-BD2C-D7F1EE5E6098@.microsoft.com...
> In
>
>
|||Yes you can do this, but its difficult. Basically you have to prep your
database on the subscriber.
IIRC you have to do the following
1) backup the publisher
2) restore it on the subscriber
3) change all identity columns to NFR
4) change all triggers to NFR
5) change all constraints to NFR
You may need to run DBCC checkident ('tableName', reseed,X) on some of the
subscriber tables to fix the identity property on the restored databases,
don't use the set identity_insert 'tableName', off in the insert procs
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
"Wind" <Wind@.discussions.microsoft.com> wrote in message
news:346FA462-5CD1-4156-BC06-E6B0662E4BFF@.microsoft.com...[vbcol=seagreen]
> Thanks a lot for your reply.
> Do you mean that if I am in SQL 2000, and the subscribers only read data,
> not write, I should be able to turn one subscriber to a publisher?
> Actually,
> what I want to do is, when the publisher has to be shut down (or damaged),
> I
> will still have the full database for business continuity in another
> location. This can be a kind of emergency backup system.
> "Hilary Cotter" wrote:
|||What about the alternate synchronization partner?
Jos.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23zmkjND0FHA.2960@.tk2msftngp13.phx.gbl...
> Its not really possible in SQL 2000, but it looks like the peer-to-peer
> replication model in 2005.
> You have to look at how the data flows. If it flows only from the
publisher[vbcol=seagreen]
> to the subscribers it might be possible to convert a subscriber to a
> publisher by applying the publication creation script on one of the
> subscribers you wish to promote the new publisher. Then make all other
> subscribers subscribers of this new publisher.
> --
> 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
> "Wind" <Wind@.discussions.microsoft.com> wrote in message
> news:4AF7A0BF-DB5F-43E8-BD2C-D7F1EE5E6098@.microsoft.com...
> In
the[vbcol=seagreen]
replication
>
|||Plain merge and queued updating subscribers would also come into this
section, although none of these techniques would really allow the subscriber
to easily become the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
New to Replication
I have a SQL 2000 database used in a data entry / transactional mode. We
have few users, but they do occassionally run reports which are server
intensive. It is a small business. We would like to begin using the data in
the database on the website. I'm wondering if I should replicate the DB on
the same server. Does that really provide any performance protection for the
data entry folks? We don't think the traffic on the Internet will be intense
to begin with because it will only be for members. Does a snapshot
replication make sense (we only need a refresh each night) or should I just
let the web hit the data entry db? Thanks
Hi,
I understand that some of your users occasionally ran reports on your
server. The operations impacted your server performance. You want to know
if you can replicate the DB on the same server to improve the performance.
If I have misunderstood, please let me know.
Generally producing report is CPU, memory and I/O intensive, so I recommend
that you replicate your database to a different server (dedicated for
report server) and have your web application use this report server for
producing report. Since reports are for history data, snapshot replication
is enough.
For implementing replication, please refer to:
Implementing Replication (Enterprise Manager)
http://msdn2.microsoft.com/en-us/library/aa216618(sql.80).aspx
Implementing Master-Slave Snapshot Replication Using SQL Server
http://msdn2.microsoft.com/en-us/library/ms998561.aspx
Planning for Snapshot Replication
http://msdn2.microsoft.com/en-us/library/aa237481(SQL.80).aspx
If you have any other questions or concerns, please let me know. Have a
nice day!
Best regards,
Charles Wang
Microsoft Online Community 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.
================================================== ====
|||That answers my question. Thanks Charles!
"Charles Wang[MSFT]" wrote:
> Hi,
> I understand that some of your users occasionally ran reports on your
> server. The operations impacted your server performance. You want to know
> if you can replicate the DB on the same server to improve the performance.
> If I have misunderstood, please let me know.
> Generally producing report is CPU, memory and I/O intensive, so I recommend
> that you replicate your database to a different server (dedicated for
> report server) and have your web application use this report server for
> producing report. Since reports are for history data, snapshot replication
> is enough.
> For implementing replication, please refer to:
> Implementing Replication (Enterprise Manager)
> http://msdn2.microsoft.com/en-us/library/aa216618(sql.80).aspx
> Implementing Master-Slave Snapshot Replication Using SQL Server
> http://msdn2.microsoft.com/en-us/library/ms998561.aspx
> Planning for Snapshot Replication
> http://msdn2.microsoft.com/en-us/library/aa237481(SQL.80).aspx
> If you have any other questions or concerns, please let me know. Have a
> nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community 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.
> ================================================== ====
>
>
>
>
>
>
>
sql
have few users, but they do occassionally run reports which are server
intensive. It is a small business. We would like to begin using the data in
the database on the website. I'm wondering if I should replicate the DB on
the same server. Does that really provide any performance protection for the
data entry folks? We don't think the traffic on the Internet will be intense
to begin with because it will only be for members. Does a snapshot
replication make sense (we only need a refresh each night) or should I just
let the web hit the data entry db? Thanks
Hi,
I understand that some of your users occasionally ran reports on your
server. The operations impacted your server performance. You want to know
if you can replicate the DB on the same server to improve the performance.
If I have misunderstood, please let me know.
Generally producing report is CPU, memory and I/O intensive, so I recommend
that you replicate your database to a different server (dedicated for
report server) and have your web application use this report server for
producing report. Since reports are for history data, snapshot replication
is enough.
For implementing replication, please refer to:
Implementing Replication (Enterprise Manager)
http://msdn2.microsoft.com/en-us/library/aa216618(sql.80).aspx
Implementing Master-Slave Snapshot Replication Using SQL Server
http://msdn2.microsoft.com/en-us/library/ms998561.aspx
Planning for Snapshot Replication
http://msdn2.microsoft.com/en-us/library/aa237481(SQL.80).aspx
If you have any other questions or concerns, please let me know. Have a
nice day!
Best regards,
Charles Wang
Microsoft Online Community 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.
================================================== ====
|||That answers my question. Thanks Charles!
"Charles Wang[MSFT]" wrote:
> Hi,
> I understand that some of your users occasionally ran reports on your
> server. The operations impacted your server performance. You want to know
> if you can replicate the DB on the same server to improve the performance.
> If I have misunderstood, please let me know.
> Generally producing report is CPU, memory and I/O intensive, so I recommend
> that you replicate your database to a different server (dedicated for
> report server) and have your web application use this report server for
> producing report. Since reports are for history data, snapshot replication
> is enough.
> For implementing replication, please refer to:
> Implementing Replication (Enterprise Manager)
> http://msdn2.microsoft.com/en-us/library/aa216618(sql.80).aspx
> Implementing Master-Slave Snapshot Replication Using SQL Server
> http://msdn2.microsoft.com/en-us/library/ms998561.aspx
> Planning for Snapshot Replication
> http://msdn2.microsoft.com/en-us/library/aa237481(SQL.80).aspx
> If you have any other questions or concerns, please let me know. Have a
> nice day!
> Best regards,
> Charles Wang
> Microsoft Online Community 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.
> ================================================== ====
>
>
>
>
>
>
>
sql
Wednesday, March 28, 2012
New to Replication
Anand,
here are some sites which have step-by-step examples:
http://www.mssqlcity.com/Articles/Replic/Replic.htm
http://www.sqlservercentral.com/arti...ticleList.asp?
categoryid=26
BOL is currently the best resource 'bookwise' but keep an
eye out for Hilary's forthcoming book.
As for tables without PK - yes they can be replicated
using snapshot or merge, but not using transactional.
HTH,
Paul Ibison
actually in most cases I have seen you can extend tables without pk's to add
PK's to them. There are two approaches
1) in every instance I have run across there will be the functional
equivalent of a PK, in other words a candidate key. This is because the app
that uses these tables must be able to uniquely identify a record in order
to know what record to update or delete. So, by carefully studying these
tables and the data in them, as well as how the app intereacts with the
tables and data you can determine the candidate keys and convert them into
PK's, which you can replicate.
2) see this link for the other approach
http://groups.google.com/groups?selm...&output=gplain
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:29c801c470a1$57be6e80$a401280a@.phx.gbl...
> Anand,
> here are some sites which have step-by-step examples:
> http://www.mssqlcity.com/Articles/Replic/Replic.htm
> http://www.sqlservercentral.com/arti...ticleList.asp?
> categoryid=26
> BOL is currently the best resource 'bookwise' but keep an
> eye out for Hilary's forthcoming book.
> As for tables without PK - yes they can be replicated
> using snapshot or merge, but not using transactional.
> HTH,
> Paul Ibison
here are some sites which have step-by-step examples:
http://www.mssqlcity.com/Articles/Replic/Replic.htm
http://www.sqlservercentral.com/arti...ticleList.asp?
categoryid=26
BOL is currently the best resource 'bookwise' but keep an
eye out for Hilary's forthcoming book.
As for tables without PK - yes they can be replicated
using snapshot or merge, but not using transactional.
HTH,
Paul Ibison
actually in most cases I have seen you can extend tables without pk's to add
PK's to them. There are two approaches
1) in every instance I have run across there will be the functional
equivalent of a PK, in other words a candidate key. This is because the app
that uses these tables must be able to uniquely identify a record in order
to know what record to update or delete. So, by carefully studying these
tables and the data in them, as well as how the app intereacts with the
tables and data you can determine the candidate keys and convert them into
PK's, which you can replicate.
2) see this link for the other approach
http://groups.google.com/groups?selm...&output=gplain
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:29c801c470a1$57be6e80$a401280a@.phx.gbl...
> Anand,
> here are some sites which have step-by-step examples:
> http://www.mssqlcity.com/Articles/Replic/Replic.htm
> http://www.sqlservercentral.com/arti...ticleList.asp?
> categoryid=26
> BOL is currently the best resource 'bookwise' but keep an
> eye out for Hilary's forthcoming book.
> As for tables without PK - yes they can be replicated
> using snapshot or merge, but not using transactional.
> HTH,
> Paul Ibison
New To Replication
I am a software developer who has been tasked with setting up replication for one of our clients. I have about 1 week of experience working with replication, so any help would be appreciated.
My situation:
5 databases:
Production - All sales transactions go into here
Corporate - Used to report on everything in the Production DB
Site A - Filtered for Site A data
Site B - Filtered for Site B data
Site C - Filtered for Site C data
All connections must be two-way (Data changed at Site A most propagate back through Corporate to Production, data entered at Production -> Corporate -> Site A\B\C)
I want the Corporate DB to be the publisher and Production, Site A\B\C to be subscribers to keep the load off Production.
The issues I have are:
Filtering the data to Sites A\B\C
Default values are being stripped off columns during replication
Data is not being propagated accuratly (Data entered at production replicated to Corporate, then the data is deleted at Corporate and at Production).
I have tried several combinations of Merge and Transactional Replication and nothing works as needed.
Thanks,
David
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
David,
one topology that should solve your issues:
Production publishes everything to Corporate using merge.
Corporate publishes to SitesA-C (3 subscribers) using merge with dynamic
filters.
This setup is known as republishing, as Corporate is both a publisher and a
subscriber.
HTH,
Paul Ibison
My situation:
5 databases:
Production - All sales transactions go into here
Corporate - Used to report on everything in the Production DB
Site A - Filtered for Site A data
Site B - Filtered for Site B data
Site C - Filtered for Site C data
All connections must be two-way (Data changed at Site A most propagate back through Corporate to Production, data entered at Production -> Corporate -> Site A\B\C)
I want the Corporate DB to be the publisher and Production, Site A\B\C to be subscribers to keep the load off Production.
The issues I have are:
Filtering the data to Sites A\B\C
Default values are being stripped off columns during replication
Data is not being propagated accuratly (Data entered at production replicated to Corporate, then the data is deleted at Corporate and at Production).
I have tried several combinations of Merge and Transactional Replication and nothing works as needed.
Thanks,
David
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
David,
one topology that should solve your issues:
Production publishes everything to Corporate using merge.
Corporate publishes to SitesA-C (3 subscribers) using merge with dynamic
filters.
This setup is known as republishing, as Corporate is both a publisher and a
subscriber.
HTH,
Paul Ibison
New to merge replication - simple scenario......
I have a product that sits on a main server and wish to implement
functionality to allow salesmen to come along, pick up a snapshot of the
database, go away and maybe modify/add to it and then come back and
"synchronise" their data. I'm reading up on Merge Replication for this
purpose. But anyway, I created a publisher on my server and it went away
and generated a "rowguid" column on all of my tables (my tables all have an
Identity column key field). Now of course my "Inserts" no longer work, as
they expect a GUID. I would have expected SQL Server to automatically
generate a guid for new inserts (in a similar way to it's TIMESTAMP), but it
appears it doesn't, despite the fact I have "(newid())" as the default for
the column. It always inserts the same value:
{00000000-0000-0000-0000-000000000000}.
functionality to allow salesmen to come along, pick up a snapshot of the
database, go away and maybe modify/add to it and then come back and
"synchronise" their data. I'm reading up on Merge Replication for this
purpose. But anyway, I created a publisher on my server and it went away
and generated a "rowguid" column on all of my tables (my tables all have an
Identity column key field). Now of course my "Inserts" no longer work, as
they expect a GUID. I would have expected SQL Server to automatically
generate a guid for new inserts (in a similar way to it's TIMESTAMP), but it
appears it doesn't, despite the fact I have "(newid())" as the default for
the column. It always inserts the same value:
{00000000-0000-0000-0000-000000000000}.
So, back to basics, now I have a guid field for each record, how do I manage
inserts?
Thanks."Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:d7n9tc$1uu$1$8302bc10@.news.demon.co.uk...
> I have a product that sits on a main server and wish to implement
> functionality to allow salesmen to come along, pick up a snapshot of the
> database, go away and maybe modify/add to it and then come back and
> "synchronise" their data. I'm reading up on Merge Replication for this
> purpose. But anyway, I created a publisher on my server and it went away
> and generated a "rowguid" column on all of my tables (my tables all have
> an Identity column key field). Now of course my "Inserts" no longer work,
> as they expect a GUID. I would have expected SQL Server to automatically
> generate a guid for new inserts (in a similar way to it's TIMESTAMP), but
> it appears it doesn't, despite the fact I have "(newid())" as the default
> for the column. It always inserts the same value:
> {00000000-0000-0000-0000-000000000000}.
> So, back to basics, now I have a guid field for each record, how do I
> manage inserts?
> Thanks.
Good practice is to explicitly name the target columns in all your INSERTs,
eg:
insert into dbo.MyTable (col1, col2)
select col1, col2
from dbo.MyOtherTable
If you do this, then there's no problem - your INSERT works as usual, and
you can ignore the uniqueidentifier column completely. Since there's a
default on it, there's no need to include it in the INSERT.
Simon|||So am I right in thinking that the unique GUID will be created on the field
when data is pushed or pulled - as I say, the newid() seems to produce a
zero'd GUID. I was thinking I am responsible for maintaining the uniqueness
of this ID, but this can't be right.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:429f41fa$1_3@.news.bluewin.ch...
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:d7n9tc$1uu$1$8302bc10@.news.demon.co.uk...
>>
>> I have a product that sits on a main server and wish to implement
>> functionality to allow salesmen to come along, pick up a snapshot of the
>> database, go away and maybe modify/add to it and then come back and
>> "synchronise" their data. I'm reading up on Merge Replication for this
>> purpose. But anyway, I created a publisher on my server and it went
>> away and generated a "rowguid" column on all of my tables (my tables all
>> have an Identity column key field). Now of course my "Inserts" no longer
>> work, as they expect a GUID. I would have expected SQL Server to
>> automatically generate a guid for new inserts (in a similar way to it's
>> TIMESTAMP), but it appears it doesn't, despite the fact I have
>> "(newid())" as the default for the column. It always inserts the same
>> value: {00000000-0000-0000-0000-000000000000}.
>>
>> So, back to basics, now I have a guid field for each record, how do I
>> manage inserts?
>>
>> Thanks.
>>
>>
>>
> Good practice is to explicitly name the target columns in all your
> INSERTs, eg:
> insert into dbo.MyTable (col1, col2)
> select col1, col2
> from dbo.MyOtherTable
> If you do this, then there's no problem - your INSERT works as usual, and
> you can ignore the uniqueidentifier column completely. Since there's a
> default on it, there's no need to include it in the INSERT.
> Simon|||"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:d7nge1$1p7$1$8300dec7@.news.demon.co.uk...
> So am I right in thinking that the unique GUID will be created on the
> field when data is pushed or pulled - as I say, the newid() seems to
> produce a zero'd GUID. I was thinking I am responsible for maintaining
> the uniqueness of this ID, but this can't be right.
<snip
As far as I'm aware, in the case of uniqueidentifier columns added to
support merge replication, it's the replication process that maintains these
values. But I'm not at all an expert in replication, so you might want to
consider posting to microsoft.public.sqlserver.replication if you need more
details.
Simon
Monday, March 26, 2012
New table added to p-t-p transactional publication does not replicate - why?
Hello,
We are new to replication and are testing it in our development environment. We have a peer-to-peer transactional publication on our three servers. The single table in the original publication replicated fine to the two subscribing servers. We next added a new table (article) to the publication. Adding it to the original publication worked fine but the table did not replicate to the other servers. (We previously had changed the schema of the original table and the schema changes replicated properly.) We attempted to recreate the snapsnot using the "View Snapshot Agent Status" option. Clicking the Start button resulted in the display of this message: "[0%] A snapshot was not generated because no subscriptions needed initialization."
This seems odd because a new table was added to the publication and Microsoft help states that the snapshot must be rebuilt. I have read other topics that refer to a @.immediate_sync property that must be set to zero. I'm not sure if this is our problem or even how to set this value. Meanwhile, the other servers, as viewed through the Replication Monitor, are complaining that their snapshots do not match the publication snapshot.
Can someone point me in the right direction?
Thanks,
BCB
Hi,
P2P replication should not be initialized using snapshot agent. The subscription must be initialized either manually or using backup/restore.
To add a new article into an existing P2P topology, following the instruction in this link (http://msdn2.microsoft.com/en-us/library/ms146867.aspx). Basically you need to ensure all nodes have the same schema/data for the table you would llike to add. And then call sp_addarticle at each node. P2P topology should be quiesced before it.
Thanks,
Peng
New subscription wizard never completes or even errors (SQL 2005 Trans repl with Queued Up
I am testing replication (Transactional with Queued Updating) between SQL
Server 2005 Developer Edition (which is on a domain) and SQL Express (which
is on a machine on same LAN but in a workgroup). I have configured the
publisher as its own distributor and created a publication consisting of 2
views (1 indexed, 1 regular). My problem is that when I run the New
Subscription Wizard, after clicking Finish the process hangs forever on the
first step ("Creating Subscription For ....."). No errors appear, nothing.
Clicking Stop has no effect.
Both views are tiny, only returning half a dozen rows. I have also tried
with a publication containing only one small table, and the same thing
occurs.
The publisher is clearly able to connect to the subscriber because the
linked server is created on the subscriber successfully. There is no sign of
a subscription however on the subscriber.
Here is the configuration for the subscription (following steps in New
Subscription Wizard):
Push subscription
Subscription database is new blank database
Distribution Agent Security: Run under SQL Agent service account, Connects
to distributor by impersonation, Connects to subscriber using a SysAdmin SQL
login
Schedule: runs continuously
Queued updating
Create new linked server using same SysAdmin SQL login as above
Initialize subscription immediately
What could be wrong?
Can you script out your publication creation steps and then run it manually
through Query Analyzer or the SSMS and see where it hangs. This should give
you some indication of where the problem is.
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
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I am testing replication (Transactional with Queued Updating) between SQL
>Server 2005 Developer Edition (which is on a domain) and SQL Express (which
>is on a machine on same LAN but in a workgroup). I have configured the
>publisher as its own distributor and created a publication consisting of 2
>views (1 indexed, 1 regular). My problem is that when I run the New
>Subscription Wizard, after clicking Finish the process hangs forever on the
>first step ("Creating Subscription For ....."). No errors appear, nothing.
>Clicking Stop has no effect.
> Both views are tiny, only returning half a dozen rows. I have also tried
> with a publication containing only one small table, and the same thing
> occurs.
> The publisher is clearly able to connect to the subscriber because the
> linked server is created on the subscriber successfully. There is no sign
> of a subscription however on the subscriber.
> Here is the configuration for the subscription (following steps in New
> Subscription Wizard):
> Push subscription
> Subscription database is new blank database
> Distribution Agent Security: Run under SQL Agent service account, Connects
> to distributor by impersonation, Connects to subscriber using a SysAdmin
> SQL login
> Schedule: runs continuously
> Queued updating
> Create new linked server using same SysAdmin SQL login as above
> Initialize subscription immediately
> What could be wrong?
>
|||The step that hangs is the last step - the execution of sp_link_publication
on the subscriber:
exec sp_link_publication @.publisher = N'LAURENCEN\SQL2005', @.publisher_db =
N'RMIS_QA', @.publication = N'test views', @.distributor =
N'LAURENCEN\SQL2005', @.security_mode = 0, @.login = N'xxxxx', @.password =
N'xxxx'
In the Application Event log the following error appears:
MS DTC is unable to communicate with MS DTC on a remote system. No common
RPC protocol is supported between the two systems. Please ensure that one
or more of the following RPC protocols are common to both systems: TCP/IP,
SPX, or NetBEUI. Error Specifics: .\iomgrclt.cpp:204, Pid: 904, CmdLine:
C:\WINNT\System32\msdtc.exe
I have checked that the MSDTC service is running on publisher and
subscriber. The publisher server is Windows XP SP2 and from googling it
looks like DTC is not given network access by default, however I have
enabled the required settings and the error persists:
In Admin tools.Component Services or via Component Services MSC Snap-in
- right-click the appropriate computer node and select properties
- on the MSDTC tab click Security Configuration button
- in addition to the Network DTC Access being ticked, tick Allow Remote
Clients, Allow Inbound, Allow Outbound
- restart MSDTC
The subscriber server is Windows 2000 Workstation, and it doesn't look like
there are any DTC settings to configure.
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I am testing replication (Transactional with Queued Updating) between SQL
>Server 2005 Developer Edition (which is on a domain) and SQL Express (which
>is on a machine on same LAN but in a workgroup). I have configured the
>publisher as its own distributor and created a publication consisting of 2
>views (1 indexed, 1 regular). My problem is that when I run the New
>Subscription Wizard, after clicking Finish the process hangs forever on the
>first step ("Creating Subscription For ....."). No errors appear, nothing.
>Clicking Stop has no effect.
> Both views are tiny, only returning half a dozen rows. I have also tried
> with a publication containing only one small table, and the same thing
> occurs.
> The publisher is clearly able to connect to the subscriber because the
> linked server is created on the subscriber successfully. There is no sign
> of a subscription however on the subscriber.
> Here is the configuration for the subscription (following steps in New
> Subscription Wizard):
> Push subscription
> Subscription database is new blank database
> Distribution Agent Security: Run under SQL Agent service account, Connects
> to distributor by impersonation, Connects to subscriber using a SysAdmin
> SQL login
> Schedule: runs continuously
> Queued updating
> Create new linked server using same SysAdmin SQL login as above
> Initialize subscription immediately
> What could be wrong?
>
|||Is this an upgrade from SQL 2000? There are some issues with a remote
distributor in an upgrade. Basically the repl_distributor remote server is
modified to a linked server and there have been cases where you have to
update the passwords to get this to work. AFAIK - MSDTC is not involved in
queued replication.
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
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:O%237HO7oAHHA.5068@.TK2MSFTNGP02.phx.gbl...
> The step that hangs is the last step - the execution of
> sp_link_publication on the subscriber:
> exec sp_link_publication @.publisher = N'LAURENCEN\SQL2005', @.publisher_db
> = N'RMIS_QA', @.publication = N'test views', @.distributor =
> N'LAURENCEN\SQL2005', @.security_mode = 0, @.login = N'xxxxx', @.password =
> N'xxxx'
> In the Application Event log the following error appears:
> MS DTC is unable to communicate with MS DTC on a remote system. No common
> RPC protocol is supported between the two systems. Please ensure that one
> or more of the following RPC protocols are common to both systems:
> TCP/IP, SPX, or NetBEUI. Error Specifics: .\iomgrclt.cpp:204, Pid: 904,
> CmdLine: C:\WINNT\System32\msdtc.exe
> I have checked that the MSDTC service is running on publisher and
> subscriber. The publisher server is Windows XP SP2 and from googling it
> looks like DTC is not given network access by default, however I have
> enabled the required settings and the error persists:
> In Admin tools.Component Services or via Component Services MSC Snap-in
> - right-click the appropriate computer node and select properties
> - on the MSDTC tab click Security Configuration button
> - in addition to the Network DTC Access being ticked, tick Allow Remote
> Clients, Allow Inbound, Allow Outbound
> - restart MSDTC
> The subscriber server is Windows 2000 Workstation, and it doesn't look
> like there are any DTC settings to configure.
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>
|||I figured it out after a long search.
MSDTC is involved, at least to set up the subscription. MSDTC in Windows XP
(and Win 2003 also I think) has some network access settings that are set to
prevent any access from outside by default. I mentioned these before.
However, in addition to setting these settings to allow network access,
there is a security setting for RPC, which I missed. This is the registry
key:
HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Win dows
NT\RPC\RestrictRemoteClients
This needs to exist and have a value of 0 (DWORD) if the subscriber is
Windows 2000.
For more info read:
http://blogs.msdn.com/florinlazar/archive/2004/06/18/159127.aspx
http://www.microsoft.com/technet/prodtechnol/winxppro/maintain/sp2netwk.mspx#XSLTsection128121120120
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23tmOUdqAHHA.3536@.TK2MSFTNGP03.phx.gbl...
> Is this an upgrade from SQL 2000? There are some issues with a remote
> distributor in an upgrade. Basically the repl_distributor remote server is
> modified to a linked server and there have been cases where you have to
> update the passwords to get this to work. AFAIK - MSDTC is not involved in
> queued replication.
> --
> 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
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:O%237HO7oAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>
Server 2005 Developer Edition (which is on a domain) and SQL Express (which
is on a machine on same LAN but in a workgroup). I have configured the
publisher as its own distributor and created a publication consisting of 2
views (1 indexed, 1 regular). My problem is that when I run the New
Subscription Wizard, after clicking Finish the process hangs forever on the
first step ("Creating Subscription For ....."). No errors appear, nothing.
Clicking Stop has no effect.
Both views are tiny, only returning half a dozen rows. I have also tried
with a publication containing only one small table, and the same thing
occurs.
The publisher is clearly able to connect to the subscriber because the
linked server is created on the subscriber successfully. There is no sign of
a subscription however on the subscriber.
Here is the configuration for the subscription (following steps in New
Subscription Wizard):
Push subscription
Subscription database is new blank database
Distribution Agent Security: Run under SQL Agent service account, Connects
to distributor by impersonation, Connects to subscriber using a SysAdmin SQL
login
Schedule: runs continuously
Queued updating
Create new linked server using same SysAdmin SQL login as above
Initialize subscription immediately
What could be wrong?
Can you script out your publication creation steps and then run it manually
through Query Analyzer or the SSMS and see where it hangs. This should give
you some indication of where the problem is.
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
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I am testing replication (Transactional with Queued Updating) between SQL
>Server 2005 Developer Edition (which is on a domain) and SQL Express (which
>is on a machine on same LAN but in a workgroup). I have configured the
>publisher as its own distributor and created a publication consisting of 2
>views (1 indexed, 1 regular). My problem is that when I run the New
>Subscription Wizard, after clicking Finish the process hangs forever on the
>first step ("Creating Subscription For ....."). No errors appear, nothing.
>Clicking Stop has no effect.
> Both views are tiny, only returning half a dozen rows. I have also tried
> with a publication containing only one small table, and the same thing
> occurs.
> The publisher is clearly able to connect to the subscriber because the
> linked server is created on the subscriber successfully. There is no sign
> of a subscription however on the subscriber.
> Here is the configuration for the subscription (following steps in New
> Subscription Wizard):
> Push subscription
> Subscription database is new blank database
> Distribution Agent Security: Run under SQL Agent service account, Connects
> to distributor by impersonation, Connects to subscriber using a SysAdmin
> SQL login
> Schedule: runs continuously
> Queued updating
> Create new linked server using same SysAdmin SQL login as above
> Initialize subscription immediately
> What could be wrong?
>
|||The step that hangs is the last step - the execution of sp_link_publication
on the subscriber:
exec sp_link_publication @.publisher = N'LAURENCEN\SQL2005', @.publisher_db =
N'RMIS_QA', @.publication = N'test views', @.distributor =
N'LAURENCEN\SQL2005', @.security_mode = 0, @.login = N'xxxxx', @.password =
N'xxxx'
In the Application Event log the following error appears:
MS DTC is unable to communicate with MS DTC on a remote system. No common
RPC protocol is supported between the two systems. Please ensure that one
or more of the following RPC protocols are common to both systems: TCP/IP,
SPX, or NetBEUI. Error Specifics: .\iomgrclt.cpp:204, Pid: 904, CmdLine:
C:\WINNT\System32\msdtc.exe
I have checked that the MSDTC service is running on publisher and
subscriber. The publisher server is Windows XP SP2 and from googling it
looks like DTC is not given network access by default, however I have
enabled the required settings and the error persists:
In Admin tools.Component Services or via Component Services MSC Snap-in
- right-click the appropriate computer node and select properties
- on the MSDTC tab click Security Configuration button
- in addition to the Network DTC Access being ticked, tick Allow Remote
Clients, Allow Inbound, Allow Outbound
- restart MSDTC
The subscriber server is Windows 2000 Workstation, and it doesn't look like
there are any DTC settings to configure.
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I am testing replication (Transactional with Queued Updating) between SQL
>Server 2005 Developer Edition (which is on a domain) and SQL Express (which
>is on a machine on same LAN but in a workgroup). I have configured the
>publisher as its own distributor and created a publication consisting of 2
>views (1 indexed, 1 regular). My problem is that when I run the New
>Subscription Wizard, after clicking Finish the process hangs forever on the
>first step ("Creating Subscription For ....."). No errors appear, nothing.
>Clicking Stop has no effect.
> Both views are tiny, only returning half a dozen rows. I have also tried
> with a publication containing only one small table, and the same thing
> occurs.
> The publisher is clearly able to connect to the subscriber because the
> linked server is created on the subscriber successfully. There is no sign
> of a subscription however on the subscriber.
> Here is the configuration for the subscription (following steps in New
> Subscription Wizard):
> Push subscription
> Subscription database is new blank database
> Distribution Agent Security: Run under SQL Agent service account, Connects
> to distributor by impersonation, Connects to subscriber using a SysAdmin
> SQL login
> Schedule: runs continuously
> Queued updating
> Create new linked server using same SysAdmin SQL login as above
> Initialize subscription immediately
> What could be wrong?
>
|||Is this an upgrade from SQL 2000? There are some issues with a remote
distributor in an upgrade. Basically the repl_distributor remote server is
modified to a linked server and there have been cases where you have to
update the passwords to get this to work. AFAIK - MSDTC is not involved in
queued replication.
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
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:O%237HO7oAHHA.5068@.TK2MSFTNGP02.phx.gbl...
> The step that hangs is the last step - the execution of
> sp_link_publication on the subscriber:
> exec sp_link_publication @.publisher = N'LAURENCEN\SQL2005', @.publisher_db
> = N'RMIS_QA', @.publication = N'test views', @.distributor =
> N'LAURENCEN\SQL2005', @.security_mode = 0, @.login = N'xxxxx', @.password =
> N'xxxx'
> In the Application Event log the following error appears:
> MS DTC is unable to communicate with MS DTC on a remote system. No common
> RPC protocol is supported between the two systems. Please ensure that one
> or more of the following RPC protocols are common to both systems:
> TCP/IP, SPX, or NetBEUI. Error Specifics: .\iomgrclt.cpp:204, Pid: 904,
> CmdLine: C:\WINNT\System32\msdtc.exe
> I have checked that the MSDTC service is running on publisher and
> subscriber. The publisher server is Windows XP SP2 and from googling it
> looks like DTC is not given network access by default, however I have
> enabled the required settings and the error persists:
> In Admin tools.Component Services or via Component Services MSC Snap-in
> - right-click the appropriate computer node and select properties
> - on the MSDTC tab click Security Configuration button
> - in addition to the Network DTC Access being ticked, tick Allow Remote
> Clients, Allow Inbound, Allow Outbound
> - restart MSDTC
> The subscriber server is Windows 2000 Workstation, and it doesn't look
> like there are any DTC settings to configure.
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>
|||I figured it out after a long search.
MSDTC is involved, at least to set up the subscription. MSDTC in Windows XP
(and Win 2003 also I think) has some network access settings that are set to
prevent any access from outside by default. I mentioned these before.
However, in addition to setting these settings to allow network access,
there is a security setting for RPC, which I missed. This is the registry
key:
HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Win dows
NT\RPC\RestrictRemoteClients
This needs to exist and have a value of 0 (DWORD) if the subscriber is
Windows 2000.
For more info read:
http://blogs.msdn.com/florinlazar/archive/2004/06/18/159127.aspx
http://www.microsoft.com/technet/prodtechnol/winxppro/maintain/sp2netwk.mspx#XSLTsection128121120120
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23tmOUdqAHHA.3536@.TK2MSFTNGP03.phx.gbl...
> Is this an upgrade from SQL 2000? There are some issues with a remote
> distributor in an upgrade. Basically the repl_distributor remote server is
> modified to a linked server and there have been cases where you have to
> update the passwords to get this to work. AFAIK - MSDTC is not involved in
> queued replication.
> --
> 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
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:O%237HO7oAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>
Wednesday, March 21, 2012
New SQL Replication implementation
Hello,
I'm just beginning my R&D on SQL Server Replication for a project and had some questions about what paths to pursue
We have multiple locations which we want to all work off the same set of data essentially, but the kicker is that many of the sites become disconnected for minutes or either days at a time due to their remote locations, so we cannot simply deploy a web application with one SQL backend because remote sites will be unable to work during service disruptions.
Ideally, what I'd like to be able to do is have multiple instances of the SQL server/web applications(one at each site..they all have their own internal network), but which replicates with the main site/all other sites whenever it can(i.e. the internet is available). So one site goes without internet for a few days, they have up to date data from the last replication and they can work off their own sql server/web application, and any updates they've made/or any updates from other sites they've missed during the disconnected period would be replicated when the opportunity arises(ie. the web connectivity comes back)
Is this scenario possible? and I am struggling to find the right strategy path for implementing something like this. Any guidance would be greatly appreciated.
Thanks,
Chris
Yes this is possible. What you want is Merge replication. That will allow updates on the subscribers and they will sync when they are online.
I would recommend making the client a click once .net application rather than a web app as the click once will also update when online however web app will work too on the local servers. You will just need a mechanism to update the pages.
Martin
sql
New SQL Replication implementation
Hello,
I'm just beginning my R&D on SQL Server Replication for a project and had some questions about what paths to pursue
We have multiple locations which we want to all work off the same set of data essentially, but the kicker is that many of the sites become disconnected for minutes or either days at a time due to their remote locations, so we cannot simply deploy a web application with one SQL backend because remote sites will be unable to work during service disruptions.
Ideally, what I'd like to be able to do is have multiple instances of the SQL server/web applications(one at each site..they all have their own internal network), but which replicates with the main site/all other sites whenever it can(i.e. the internet is available). So one site goes without internet for a few days, they have up to date data from the last replication and they can work off their own sql server/web application, and any updates they've made/or any updates from other sites they've missed during the disconnected period would be replicated when the opportunity arises(ie. the web connectivity comes back)
Is this scenario possible? and I am struggling to find the right strategy path for implementing something like this. Any guidance would be greatly appreciated.
Thanks,
Chris
Yes this is possible. What you want is Merge replication. That will allow updates on the subscribers and they will sync when they are online.
I would recommend making the client a click once .net application rather than a web app as the click once will also update when online however web app will work too on the local servers. You will just need a mechanism to update the pages.
Martin
Monday, March 19, 2012
New Server and replication
Have a look at 'replication, backup and restore
operations' in BOL. Provided the new server and the old
server have the same name, this is possible. You'll need
to take copies of all the publisher/distributor databases
involved - the publishing database, MSDB, distribution
and master. BTW, prevent anyone from accessing the
publishing database and stop all replication jobs inc the
cleanup agent before starting the backup procedures.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks for your reply. The new server has the same name as of old. But
eventually I'll have to change the name of the old server since two machines
with same name cannot belong to the same domain.
How do i set up the subscribers to replicate the data on the old server that
will sit in the branch and then replicate the data on the new server that
has the publisher?
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:046901c53435$c318f170$a601280a@.phx.gbl...
> Have a look at 'replication, backup and restore
> operations' in BOL. Provided the new server and the old
> server have the same name, this is possible. You'll need
> to take copies of all the publisher/distributor databases
> involved - the publishing database, MSDB, distribution
> and master. BTW, prevent anyone from accessing the
> publishing database and stop all replication jobs inc the
> cleanup agent before starting the backup procedures.
> HTH,
> Paul Ibison SQL Server MVP,
> www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I'm not too sure I follow the last bit. As I understand,
you have an existing publisher that will be replaced by a
new publisher server. The new server will have the same
name and the old server taken offline. Form the point of
view of the subscribers, they won't know any difference,
unless they are using IP addresses for registration of
the publisher.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I am sorry If I confused you. You are absolutly correct till now. What I
intend to do now is put our old server in one of our branches. I'll call it
Server1. Server1 is going to be a member of the domain if it makes sense.
Earlier managers used to replicate directly on to the publisher server but
now I want a bunch of managers to replicate to Server1 and rest of them to
Server0 i.e. my new server (Domain Controller). And I want Server1 to
replicate everything onto Server0. Server1 should pickup any relevant
changes from Server0 and pass onto subscribers replicating on Server1.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:108301c53502$00b05510$a501280a@.phx.gbl...
> I'm not too sure I follow the last bit. As I understand,
> you have an existing publisher that will be replaced by a
> new publisher server. The new server will have the same
> name and the old server taken offline. Form the point of
> view of the subscribers, they won't know any difference,
> unless they are using IP addresses for registration of
> the publisher.
> HTH,
> Paul Ibison SQL Server MVP,
> www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Thanks for the clarification. What you are referring to
is 'republishing'. There isn't a huge amount of
information in BOL but there is some. If you are using
merge replication, the same effect could be achieved by
using alternative synchronization partners.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
operations' in BOL. Provided the new server and the old
server have the same name, this is possible. You'll need
to take copies of all the publisher/distributor databases
involved - the publishing database, MSDB, distribution
and master. BTW, prevent anyone from accessing the
publishing database and stop all replication jobs inc the
cleanup agent before starting the backup procedures.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks for your reply. The new server has the same name as of old. But
eventually I'll have to change the name of the old server since two machines
with same name cannot belong to the same domain.
How do i set up the subscribers to replicate the data on the old server that
will sit in the branch and then replicate the data on the new server that
has the publisher?
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:046901c53435$c318f170$a601280a@.phx.gbl...
> Have a look at 'replication, backup and restore
> operations' in BOL. Provided the new server and the old
> server have the same name, this is possible. You'll need
> to take copies of all the publisher/distributor databases
> involved - the publishing database, MSDB, distribution
> and master. BTW, prevent anyone from accessing the
> publishing database and stop all replication jobs inc the
> cleanup agent before starting the backup procedures.
> HTH,
> Paul Ibison SQL Server MVP,
> www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I'm not too sure I follow the last bit. As I understand,
you have an existing publisher that will be replaced by a
new publisher server. The new server will have the same
name and the old server taken offline. Form the point of
view of the subscribers, they won't know any difference,
unless they are using IP addresses for registration of
the publisher.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I am sorry If I confused you. You are absolutly correct till now. What I
intend to do now is put our old server in one of our branches. I'll call it
Server1. Server1 is going to be a member of the domain if it makes sense.
Earlier managers used to replicate directly on to the publisher server but
now I want a bunch of managers to replicate to Server1 and rest of them to
Server0 i.e. my new server (Domain Controller). And I want Server1 to
replicate everything onto Server0. Server1 should pickup any relevant
changes from Server0 and pass onto subscribers replicating on Server1.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:108301c53502$00b05510$a501280a@.phx.gbl...
> I'm not too sure I follow the last bit. As I understand,
> you have an existing publisher that will be replaced by a
> new publisher server. The new server will have the same
> name and the old server taken offline. Form the point of
> view of the subscribers, they won't know any difference,
> unless they are using IP addresses for registration of
> the publisher.
> HTH,
> Paul Ibison SQL Server MVP,
> www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Thanks for the clarification. What you are referring to
is 'republishing'. There isn't a huge amount of
information in BOL but there is some. If you are using
merge replication, the same effect could be achieved by
using alternative synchronization partners.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Monday, March 12, 2012
New PULL setup problems
I have read & read & read and cannot seem to find the answer to my issue.
I have several publications on SQL-01 with Transactional Replication from one db to another, running well.
Now I have a new reporting Sql Server - SQL-02.
I set the Distributor up, and created the reporting db to be replicated to.
I want to PULL the db from SQL-01 to the db on SQL-02. I want the Distributor on the new box, SQL-02 to be used. Since the publication already exists on SQL-01, isn't it just a matter of PULLING the subscription to SQL-02?
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
What am I missing?
To use the distribution database on the subscriber you will have to
1) enable the subscriber for replication
2) go to tools, replication, configure publishers, subscribers, distributor on the subscriber and click on the publisher tab. And then click on the publisher tab, and enable the remote publisher to use this distributor. Click on the three ellipses to the right of your publisher and disable the administrative link password.
3) connect to your publisher and disable replication. Then recreate it and configure it to use the remote distributor on the subscriber.
4) recreate your publication and pull subscription.
Your error sounds like your pull subscription does not have rights to access the snapshot share. Either add the account that the SQL Agent account runs under on the subscriber to the local admin group on the publisher, or change the snapshot share to a folder which the SQL Agent account on the subscriber can access.
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23a3jTrxnEHA.648@.tk2msftngp13.phx.gbl...
I have read & read & read and cannot seem to find the answer to my issue.
I have several publications on SQL-01 with Transactional Replication from one db to another, running well.
Now I have a new reporting Sql Server - SQL-02.
I set the Distributor up, and created the reporting db to be replicated to.
I want to PULL the db from SQL-01 to the db on SQL-02. I want the Distributor on the new box, SQL-02 to be used. Since the publication already exists on SQL-01, isn't it just a matter of PULLING the subscription to SQL-02?
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
What am I missing?
|||# 3 - connect to your publisher and disable replication.
What if I have multiple publications of 1 table going here & 1 table going there and want these publications to continue to use the distributor on the publishing server?
Is that ok?
For instance; 1 publication publishes only 1 table from database a to database b on the same server and uses this server as the distributor.
Then the publication I am now trying to Pull & have the distributor be remote, will pull multiple tables from database a to the database on the remote server.
Any problems with that?
The disable replication concerned me, I don't want to affect any of the other publications by setting up the new publication to run at the remote distributor & pull subscription.
I sure hope I am making sense....
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:e%23ah21xnEHA.3680@.TK2MSFTNGP10.phx.gbl...
To use the distribution database on the subscriber you will have to
1) enable the subscriber for replication
2) go to tools, replication, configure publishers, subscribers, distributor on the subscriber and click on the publisher tab. And then click on the publisher tab, and enable the remote publisher to use this distributor. Click on the three ellipses to the right of your publisher and disable the administrative link password.
3) connect to your publisher and disable replication. Then recreate it and configure it to use the remote distributor on the subscriber.
4) recreate your publication and pull subscription.
Your error sounds like your pull subscription does not have rights to access the snapshot share. Either add the account that the SQL Agent account runs under on the subscriber to the local admin group on the publisher, or change the snapshot share to a folder which the SQL Agent account on the subscriber can access.
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23a3jTrxnEHA.648@.tk2msftngp13.phx.gbl...
I have read & read & read and cannot seem to find the answer to my issue.
I have several publications on SQL-01 with Transactional Replication from one db to another, running well.
Now I have a new reporting Sql Server - SQL-02.
I set the Distributor up, and created the reporting db to be replicated to.
I want to PULL the db from SQL-01 to the db on SQL-02. I want the Distributor on the new box, SQL-02 to be used. Since the publication already exists on SQL-01, isn't it just a matter of PULLING the subscription to SQL-02?
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
What am I missing?
|||Basically you are hosed. Existing publications on the publisher will have to be migrated to the new distributor.
Why do you want to use a remote distributor? If it is to offload the extra processing from the new publication subscription you might want to consider remote activation.
For plain pull subscriptions that distributor on the subscriber will only contain historical information. It won't contain the transactions and commands to be replicated.
Perhaps at this point you could tell us exactly what you are trying to accomplish so we can consider all the options available to you.
"JLS" <jlshoop@.hotmail.com> wrote in message news:O%23kH290nEHA.2300@.TK2MSFTNGP10.phx.gbl...
# 3 - connect to your publisher and disable replication.
What if I have multiple publications of 1 table going here & 1 table going there and want these publications to continue to use the distributor on the publishing server?
Is that ok?
For instance; 1 publication publishes only 1 table from database a to database b on the same server and uses this server as the distributor.
Then the publication I am now trying to Pull & have the distributor be remote, will pull multiple tables from database a to the database on the remote server.
Any problems with that?
The disable replication concerned me, I don't want to affect any of the other publications by setting up the new publication to run at the remote distributor & pull subscription.
I sure hope I am making sense....
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:e%23ah21xnEHA.3680@.TK2MSFTNGP10.phx.gbl...
To use the distribution database on the subscriber you will have to
1) enable the subscriber for replication
2) go to tools, replication, configure publishers, subscribers, distributor on the subscriber and click on the publisher tab. And then click on the publisher tab, and enable the remote publisher to use this distributor. Click on the three ellipses to the right of your publisher and disable the administrative link password.
3) connect to your publisher and disable replication. Then recreate it and configure it to use the remote distributor on the subscriber.
4) recreate your publication and pull subscription.
Your error sounds like your pull subscription does not have rights to access the snapshot share. Either add the account that the SQL Agent account runs under on the subscriber to the local admin group on the publisher, or change the snapshot share to a folder which the SQL Agent account on the subscriber can access.
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23a3jTrxnEHA.648@.tk2msftngp13.phx.gbl...
I have read & read & read and cannot seem to find the answer to my issue.
I have several publications on SQL-01 with Transactional Replication from one db to another, running well.
Now I have a new reporting Sql Server - SQL-02.
I set the Distributor up, and created the reporting db to be replicated to.
I want to PULL the db from SQL-01 to the db on SQL-02. I want the Distributor on the new box, SQL-02 to be used. Since the publication already exists on SQL-01, isn't it just a matter of PULLING the subscription to SQL-02?
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
What am I missing?
|||I have a new server which will serve as the reporting server. I want to replicate all the transactions from my ERP database to a database on the reporting server to offload this activity on the production server.
I don't have to use a remote distributor. I am new to this and basically feeling my way around to find the absolute best solution. (I, for one, am anxiously awaiting the book release)
From what I have read thus far, it was suggested that running the Distributor & Pulling the subscription would result in better performance for both boxes.
Overall my goal is to enhance performance on the production server, the OLTP system. I thought replicating to a reporting server would be a significant step in accomplishing this task.
If you had 2 servers, 1 for production transactions and 1 for reporting, what would be your choices?
The other thing is that I am still experiencing the 8198 error message.
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
I added the accounts on both boxes to the local admins group, therefore, they have access to everything on that box. I'm really stuck on this one!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:%23Rt9Vx3nEHA.1296@.TK2MSFTNGP09.phx.gbl...
Basically you are hosed. Existing publications on the publisher will have to be migrated to the new distributor.
Why do you want to use a remote distributor? If it is to offload the extra processing from the new publication subscription you might want to consider remote activation.
For plain pull subscriptions that distributor on the subscriber will only contain historical information. It won't contain the transactions and commands to be replicated.
Perhaps at this point you could tell us exactly what you are trying to accomplish so we can consider all the options available to you.
"JLS" <jlshoop@.hotmail.com> wrote in message news:O%23kH290nEHA.2300@.TK2MSFTNGP10.phx.gbl...
# 3 - connect to your publisher and disable replication.
What if I have multiple publications of 1 table going here & 1 table going there and want these publications to continue to use the distributor on the publishing server?
Is that ok?
For instance; 1 publication publishes only 1 table from database a to database b on the same server and uses this server as the distributor.
Then the publication I am now trying to Pull & have the distributor be remote, will pull multiple tables from database a to the database on the remote server.
Any problems with that?
The disable replication concerned me, I don't want to affect any of the other publications by setting up the new publication to run at the remote distributor & pull subscription.
I sure hope I am making sense....
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:e%23ah21xnEHA.3680@.TK2MSFTNGP10.phx.gbl...
To use the distribution database on the subscriber you will have to
1) enable the subscriber for replication
2) go to tools, replication, configure publishers, subscribers, distributor on the subscriber and click on the publisher tab. And then click on the publisher tab, and enable the remote publisher to use this distributor. Click on the three ellipses to the right of your publisher and disable the administrative link password.
3) connect to your publisher and disable replication. Then recreate it and configure it to use the remote distributor on the subscriber.
4) recreate your publication and pull subscription.
Your error sounds like your pull subscription does not have rights to access the snapshot share. Either add the account that the SQL Agent account runs under on the subscriber to the local admin group on the publisher, or change the snapshot share to a folder which the SQL Agent account on the subscriber can access.
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23a3jTrxnEHA.648@.tk2msftngp13.phx.gbl...
I have read & read & read and cannot seem to find the answer to my issue.
I have several publications on SQL-01 with Transactional Replication from one db to another, running well.
Now I have a new reporting Sql Server - SQL-02.
I set the Distributor up, and created the reporting db to be replicated to.
I want to PULL the db from SQL-01 to the db on SQL-02. I want the Distributor on the new box, SQL-02 to be used. Since the publication already exists on SQL-01, isn't it just a matter of PULLING the subscription to SQL-02?
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
What am I missing?
I have several publications on SQL-01 with Transactional Replication from one db to another, running well.
Now I have a new reporting Sql Server - SQL-02.
I set the Distributor up, and created the reporting db to be replicated to.
I want to PULL the db from SQL-01 to the db on SQL-02. I want the Distributor on the new box, SQL-02 to be used. Since the publication already exists on SQL-01, isn't it just a matter of PULLING the subscription to SQL-02?
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
What am I missing?
To use the distribution database on the subscriber you will have to
1) enable the subscriber for replication
2) go to tools, replication, configure publishers, subscribers, distributor on the subscriber and click on the publisher tab. And then click on the publisher tab, and enable the remote publisher to use this distributor. Click on the three ellipses to the right of your publisher and disable the administrative link password.
3) connect to your publisher and disable replication. Then recreate it and configure it to use the remote distributor on the subscriber.
4) recreate your publication and pull subscription.
Your error sounds like your pull subscription does not have rights to access the snapshot share. Either add the account that the SQL Agent account runs under on the subscriber to the local admin group on the publisher, or change the snapshot share to a folder which the SQL Agent account on the subscriber can access.
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23a3jTrxnEHA.648@.tk2msftngp13.phx.gbl...
I have read & read & read and cannot seem to find the answer to my issue.
I have several publications on SQL-01 with Transactional Replication from one db to another, running well.
Now I have a new reporting Sql Server - SQL-02.
I set the Distributor up, and created the reporting db to be replicated to.
I want to PULL the db from SQL-01 to the db on SQL-02. I want the Distributor on the new box, SQL-02 to be used. Since the publication already exists on SQL-01, isn't it just a matter of PULLING the subscription to SQL-02?
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
What am I missing?
|||# 3 - connect to your publisher and disable replication.
What if I have multiple publications of 1 table going here & 1 table going there and want these publications to continue to use the distributor on the publishing server?
Is that ok?
For instance; 1 publication publishes only 1 table from database a to database b on the same server and uses this server as the distributor.
Then the publication I am now trying to Pull & have the distributor be remote, will pull multiple tables from database a to the database on the remote server.
Any problems with that?
The disable replication concerned me, I don't want to affect any of the other publications by setting up the new publication to run at the remote distributor & pull subscription.
I sure hope I am making sense....
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:e%23ah21xnEHA.3680@.TK2MSFTNGP10.phx.gbl...
To use the distribution database on the subscriber you will have to
1) enable the subscriber for replication
2) go to tools, replication, configure publishers, subscribers, distributor on the subscriber and click on the publisher tab. And then click on the publisher tab, and enable the remote publisher to use this distributor. Click on the three ellipses to the right of your publisher and disable the administrative link password.
3) connect to your publisher and disable replication. Then recreate it and configure it to use the remote distributor on the subscriber.
4) recreate your publication and pull subscription.
Your error sounds like your pull subscription does not have rights to access the snapshot share. Either add the account that the SQL Agent account runs under on the subscriber to the local admin group on the publisher, or change the snapshot share to a folder which the SQL Agent account on the subscriber can access.
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23a3jTrxnEHA.648@.tk2msftngp13.phx.gbl...
I have read & read & read and cannot seem to find the answer to my issue.
I have several publications on SQL-01 with Transactional Replication from one db to another, running well.
Now I have a new reporting Sql Server - SQL-02.
I set the Distributor up, and created the reporting db to be replicated to.
I want to PULL the db from SQL-01 to the db on SQL-02. I want the Distributor on the new box, SQL-02 to be used. Since the publication already exists on SQL-01, isn't it just a matter of PULLING the subscription to SQL-02?
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
What am I missing?
|||Basically you are hosed. Existing publications on the publisher will have to be migrated to the new distributor.
Why do you want to use a remote distributor? If it is to offload the extra processing from the new publication subscription you might want to consider remote activation.
For plain pull subscriptions that distributor on the subscriber will only contain historical information. It won't contain the transactions and commands to be replicated.
Perhaps at this point you could tell us exactly what you are trying to accomplish so we can consider all the options available to you.
"JLS" <jlshoop@.hotmail.com> wrote in message news:O%23kH290nEHA.2300@.TK2MSFTNGP10.phx.gbl...
# 3 - connect to your publisher and disable replication.
What if I have multiple publications of 1 table going here & 1 table going there and want these publications to continue to use the distributor on the publishing server?
Is that ok?
For instance; 1 publication publishes only 1 table from database a to database b on the same server and uses this server as the distributor.
Then the publication I am now trying to Pull & have the distributor be remote, will pull multiple tables from database a to the database on the remote server.
Any problems with that?
The disable replication concerned me, I don't want to affect any of the other publications by setting up the new publication to run at the remote distributor & pull subscription.
I sure hope I am making sense....
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:e%23ah21xnEHA.3680@.TK2MSFTNGP10.phx.gbl...
To use the distribution database on the subscriber you will have to
1) enable the subscriber for replication
2) go to tools, replication, configure publishers, subscribers, distributor on the subscriber and click on the publisher tab. And then click on the publisher tab, and enable the remote publisher to use this distributor. Click on the three ellipses to the right of your publisher and disable the administrative link password.
3) connect to your publisher and disable replication. Then recreate it and configure it to use the remote distributor on the subscriber.
4) recreate your publication and pull subscription.
Your error sounds like your pull subscription does not have rights to access the snapshot share. Either add the account that the SQL Agent account runs under on the subscriber to the local admin group on the publisher, or change the snapshot share to a folder which the SQL Agent account on the subscriber can access.
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23a3jTrxnEHA.648@.tk2msftngp13.phx.gbl...
I have read & read & read and cannot seem to find the answer to my issue.
I have several publications on SQL-01 with Transactional Replication from one db to another, running well.
Now I have a new reporting Sql Server - SQL-02.
I set the Distributor up, and created the reporting db to be replicated to.
I want to PULL the db from SQL-01 to the db on SQL-02. I want the Distributor on the new box, SQL-02 to be used. Since the publication already exists on SQL-01, isn't it just a matter of PULLING the subscription to SQL-02?
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
What am I missing?
|||I have a new server which will serve as the reporting server. I want to replicate all the transactions from my ERP database to a database on the reporting server to offload this activity on the production server.
I don't have to use a remote distributor. I am new to this and basically feeling my way around to find the absolute best solution. (I, for one, am anxiously awaiting the book release)
From what I have read thus far, it was suggested that running the Distributor & Pulling the subscription would result in better performance for both boxes.
Overall my goal is to enhance performance on the production server, the OLTP system. I thought replicating to a reporting server would be a significant step in accomplishing this task.
If you had 2 servers, 1 for production transactions and 1 for reporting, what would be your choices?
The other thing is that I am still experiencing the 8198 error message.
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
I added the accounts on both boxes to the local admins group, therefore, they have access to everything on that box. I'm really stuck on this one!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:%23Rt9Vx3nEHA.1296@.TK2MSFTNGP09.phx.gbl...
Basically you are hosed. Existing publications on the publisher will have to be migrated to the new distributor.
Why do you want to use a remote distributor? If it is to offload the extra processing from the new publication subscription you might want to consider remote activation.
For plain pull subscriptions that distributor on the subscriber will only contain historical information. It won't contain the transactions and commands to be replicated.
Perhaps at this point you could tell us exactly what you are trying to accomplish so we can consider all the options available to you.
"JLS" <jlshoop@.hotmail.com> wrote in message news:O%23kH290nEHA.2300@.TK2MSFTNGP10.phx.gbl...
# 3 - connect to your publisher and disable replication.
What if I have multiple publications of 1 table going here & 1 table going there and want these publications to continue to use the distributor on the publishing server?
Is that ok?
For instance; 1 publication publishes only 1 table from database a to database b on the same server and uses this server as the distributor.
Then the publication I am now trying to Pull & have the distributor be remote, will pull multiple tables from database a to the database on the remote server.
Any problems with that?
The disable replication concerned me, I don't want to affect any of the other publications by setting up the new publication to run at the remote distributor & pull subscription.
I sure hope I am making sense....
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:e%23ah21xnEHA.3680@.TK2MSFTNGP10.phx.gbl...
To use the distribution database on the subscriber you will have to
1) enable the subscriber for replication
2) go to tools, replication, configure publishers, subscribers, distributor on the subscriber and click on the publisher tab. And then click on the publisher tab, and enable the remote publisher to use this distributor. Click on the three ellipses to the right of your publisher and disable the administrative link password.
3) connect to your publisher and disable replication. Then recreate it and configure it to use the remote distributor on the subscriber.
4) recreate your publication and pull subscription.
Your error sounds like your pull subscription does not have rights to access the snapshot share. Either add the account that the SQL Agent account runs under on the subscriber to the local admin group on the publisher, or change the snapshot share to a folder which the SQL Agent account on the subscriber can access.
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23a3jTrxnEHA.648@.tk2msftngp13.phx.gbl...
I have read & read & read and cannot seem to find the answer to my issue.
I have several publications on SQL-01 with Transactional Replication from one db to another, running well.
Now I have a new reporting Sql Server - SQL-02.
I set the Distributor up, and created the reporting db to be replicated to.
I want to PULL the db from SQL-01 to the db on SQL-02. I want the Distributor on the new box, SQL-02 to be used. Since the publication already exists on SQL-01, isn't it just a matter of PULLING the subscription to SQL-02?
The publication job is setup to use SA, but I continually get
"Error 8198: Could not obtain information about Windows NT group Domain\SQL02Notif'
What am I missing?
New Publication Error
Hi All,
A couple of months ago, I setup a transactional replication between 2
servers. In the last week, the publisher stopped replicating to the
subscriber for some unknown reason. When I was troubleshooting the problem,
I tried to add a new publication and I received the error attached in the
screenshot.
I ran the DBCC CHECKDB on that DB and the results were ok.
Any help would be greatly appreciated.
Regards,
JN
Can't see the screenshot using the web interface to the newsgroup. Can you
post up the error message for us...
Cheers,
Paul Ibison
|||Sure thing...Error as follows:
"New Publication Wizard encountered one or more errors while retrieving the
list of databases on the 'servername'. The list of databases may not be
complete.
Additional Information:
An exception occured while executing a T-SQL statement or batch
(Microsoft.SqlServer.ConnectionInfo)
The operating system returned error 21 (The device is not ready) to SQL
server during a read at offset 0x000000614d2000 in
file F:\TST\TSTDAT.mdf (not sure what this DB is?). Addtional messages
in the SQL Server error log and system event log my provide more detail.
This is a severe system level error condition that threatens database
integrity and must be corrected
immediatly. Complete a full database consistency check (DBCC CHECKDB).
This error can be caused by many factors; for
more information, SQL Server Books Online (MS SQL Error: 823)"
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:D538F184-0AC3-4CD2-AC4D-0029B4E0ED7E@.microsoft.com...
> Can't see the screenshot using the web interface to the newsgroup. Can you
> post up the error message for us...
> Cheers,
> Paul Ibison
>
|||I'd look to see what database the file F:\TST\TSTDAT.mdf corresponds to, then
run DBCC CHECKDB on this database. The info should be in sysaltfiles.
HTH,
Paul Ibison
A couple of months ago, I setup a transactional replication between 2
servers. In the last week, the publisher stopped replicating to the
subscriber for some unknown reason. When I was troubleshooting the problem,
I tried to add a new publication and I received the error attached in the
screenshot.
I ran the DBCC CHECKDB on that DB and the results were ok.
Any help would be greatly appreciated.
Regards,
JN
Can't see the screenshot using the web interface to the newsgroup. Can you
post up the error message for us...
Cheers,
Paul Ibison
|||Sure thing...Error as follows:
"New Publication Wizard encountered one or more errors while retrieving the
list of databases on the 'servername'. The list of databases may not be
complete.
Additional Information:
An exception occured while executing a T-SQL statement or batch
(Microsoft.SqlServer.ConnectionInfo)
The operating system returned error 21 (The device is not ready) to SQL
server during a read at offset 0x000000614d2000 in
file F:\TST\TSTDAT.mdf (not sure what this DB is?). Addtional messages
in the SQL Server error log and system event log my provide more detail.
This is a severe system level error condition that threatens database
integrity and must be corrected
immediatly. Complete a full database consistency check (DBCC CHECKDB).
This error can be caused by many factors; for
more information, SQL Server Books Online (MS SQL Error: 823)"
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:D538F184-0AC3-4CD2-AC4D-0029B4E0ED7E@.microsoft.com...
> Can't see the screenshot using the web interface to the newsgroup. Can you
> post up the error message for us...
> Cheers,
> Paul Ibison
>
|||I'd look to see what database the file F:\TST\TSTDAT.mdf corresponds to, then
run DBCC CHECKDB on this database. The info should be in sysaltfiles.
HTH,
Paul Ibison
New Publication - Snapshot Error
Help!
Some genius cancelled a replication job that was very close to completing the Initial Snapshot of a database being replicated (Transactional) from one server to another.
Is the only thing necessary to do a 'Reinitialize' of this publication?
Is there any way around having the tables locked when the Snapshot is being generated?
(This is why the doofus killed the job)
Any help is greatly appreciated!
You might be able to get away with a concurrent snapshot if you are using transactional replication. On your publication properties, clcik the snapshot tab, and select concurrent access during snapshot generation.
Another Snapshot will be generated but there will be far less locking.
If you can kick all your users off the database you can DTS the data over. This is faster than a snapshot.
To get this to work you should
1) create a new database.
2) script out the tables you are replicating, and any other objects you are replicating, and run this script in the new database.
3) publish this database and objects to a second new database on the same server.
4) script out the objects and the stored procedures and then run this in the subscriber.
This way your objects will be the way replication wants them and you will have the necessary replication stored procedures. Then do your DTS and then do a no-sync subscription.
DTS can be twice as fast as a snapshot generation and distribution.
It is critical that you be able to prevent any updates from happening to your publication database while you do this.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JLS" wrote:
> Help!
> Some genius cancelled a replication job that was very close to completing the Initial Snapshot of a database being replicated (Transactional) from one server to another.
> Is the only thing necessary to do a 'Reinitialize' of this publication?
> Is there any way around having the tables locked when the Snapshot is being generated?
> (This is why the doofus killed the job)
> Any help is greatly appreciated!
|||Thanx!
Any way to buy a copy of your book before August 15?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ED801206-889D-42F6-A590-663BC63F3BBA@.microsoft.com...
> You might be able to get away with a concurrent snapshot if you are using
transactional replication. On your publication properties, clcik the
snapshot tab, and select concurrent access during snapshot generation.
> Another Snapshot will be generated but there will be far less locking.
> If you can kick all your users off the database you can DTS the data over.
This is faster than a snapshot.
> To get this to work you should
> 1) create a new database.
> 2) script out the tables you are replicating, and any other objects you
are replicating, and run this script in the new database.
> 3) publish this database and objects to a second new database on the same
server.
> 4) script out the objects and the stored procedures and then run this in
the subscriber.
> This way your objects will be the way replication wants them and you will
have the necessary replication stored procedures. Then do your DTS and then
do a no-sync subscription.
> DTS can be twice as fast as a snapshot generation and distribution.
> It is critical that you be able to prevent any updates from happening to
your publication database while you do this.[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "JLS" wrote:
completing the Initial Snapshot of a database being replicated
(Transactional) from one server to another.[vbcol=seagreen]
being generated?[vbcol=seagreen]
|||no. There may be some proofs out in a month.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message
news:%23qw9qmIWEHA.1380@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanx!
> Any way to buy a copy of your book before August 15?
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:ED801206-889D-42F6-A590-663BC63F3BBA@.microsoft.com...
using[vbcol=seagreen]
> transactional replication. On your publication properties, clcik the
> snapshot tab, and select concurrent access during snapshot generation.
over.[vbcol=seagreen]
> This is faster than a snapshot.
> are replicating, and run this script in the new database.
same[vbcol=seagreen]
> server.
> the subscriber.
will
> have the necessary replication stored procedures. Then do your DTS and
then[vbcol=seagreen]
> do a no-sync subscription.
> your publication database while you do this.
> completing the Initial Snapshot of a database being replicated
> (Transactional) from one server to another.
publication?
> being generated?
>
Some genius cancelled a replication job that was very close to completing the Initial Snapshot of a database being replicated (Transactional) from one server to another.
Is the only thing necessary to do a 'Reinitialize' of this publication?
Is there any way around having the tables locked when the Snapshot is being generated?
(This is why the doofus killed the job)
Any help is greatly appreciated!
You might be able to get away with a concurrent snapshot if you are using transactional replication. On your publication properties, clcik the snapshot tab, and select concurrent access during snapshot generation.
Another Snapshot will be generated but there will be far less locking.
If you can kick all your users off the database you can DTS the data over. This is faster than a snapshot.
To get this to work you should
1) create a new database.
2) script out the tables you are replicating, and any other objects you are replicating, and run this script in the new database.
3) publish this database and objects to a second new database on the same server.
4) script out the objects and the stored procedures and then run this in the subscriber.
This way your objects will be the way replication wants them and you will have the necessary replication stored procedures. Then do your DTS and then do a no-sync subscription.
DTS can be twice as fast as a snapshot generation and distribution.
It is critical that you be able to prevent any updates from happening to your publication database while you do this.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JLS" wrote:
> Help!
> Some genius cancelled a replication job that was very close to completing the Initial Snapshot of a database being replicated (Transactional) from one server to another.
> Is the only thing necessary to do a 'Reinitialize' of this publication?
> Is there any way around having the tables locked when the Snapshot is being generated?
> (This is why the doofus killed the job)
> Any help is greatly appreciated!
|||Thanx!
Any way to buy a copy of your book before August 15?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ED801206-889D-42F6-A590-663BC63F3BBA@.microsoft.com...
> You might be able to get away with a concurrent snapshot if you are using
transactional replication. On your publication properties, clcik the
snapshot tab, and select concurrent access during snapshot generation.
> Another Snapshot will be generated but there will be far less locking.
> If you can kick all your users off the database you can DTS the data over.
This is faster than a snapshot.
> To get this to work you should
> 1) create a new database.
> 2) script out the tables you are replicating, and any other objects you
are replicating, and run this script in the new database.
> 3) publish this database and objects to a second new database on the same
server.
> 4) script out the objects and the stored procedures and then run this in
the subscriber.
> This way your objects will be the way replication wants them and you will
have the necessary replication stored procedures. Then do your DTS and then
do a no-sync subscription.
> DTS can be twice as fast as a snapshot generation and distribution.
> It is critical that you be able to prevent any updates from happening to
your publication database while you do this.[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "JLS" wrote:
completing the Initial Snapshot of a database being replicated
(Transactional) from one server to another.[vbcol=seagreen]
being generated?[vbcol=seagreen]
|||no. There may be some proofs out in a month.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message
news:%23qw9qmIWEHA.1380@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanx!
> Any way to buy a copy of your book before August 15?
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:ED801206-889D-42F6-A590-663BC63F3BBA@.microsoft.com...
using[vbcol=seagreen]
> transactional replication. On your publication properties, clcik the
> snapshot tab, and select concurrent access during snapshot generation.
over.[vbcol=seagreen]
> This is faster than a snapshot.
> are replicating, and run this script in the new database.
same[vbcol=seagreen]
> server.
> the subscriber.
will
> have the necessary replication stored procedures. Then do your DTS and
then[vbcol=seagreen]
> do a no-sync subscription.
> your publication database while you do this.
> completing the Initial Snapshot of a database being replicated
> (Transactional) from one server to another.
publication?
> being generated?
>
New on Replication
I just started to use SQL Server replication. When I configue the Publisher
and Distributor (they are on same server), the snapshot folder is always
invalid. I was using administrator ID, the folder is located on the local
machine, it's shared. What else I need to change? Please help. Thanks.
Are you connected to a network? You need to be to be able to map a drive to
the snapshot share. I am pretty sure this is your problem. Note you can
install the loopback adapter and get it working that way.
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
"WindWind" <WindWind@.discussions.microsoft.com> wrote in message
news:8CD07DA4-9620-48C8-963C-0183601719D0@.microsoft.com...
>I just started to use SQL Server replication. When I configue the Publisher
> and Distributor (they are on same server), the snapshot folder is always
> invalid. I was using administrator ID, the folder is located on the local
> machine, it's shared. What else I need to change? Please help. Thanks.
|||Thanks for your response.
This server is connected to the network and I am able to map a drive to this
share folder, but when I chose the folder, it just wouldn't allow me to use
this folder as the snapshot share.
"Hilary Cotter" wrote:
> Are you connected to a network? You need to be to be able to map a drive to
> the snapshot share. I am pretty sure this is your problem. Note you can
> install the loopback adapter and get it working that way.
> --
> 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
> "WindWind" <WindWind@.discussions.microsoft.com> wrote in message
> news:8CD07DA4-9620-48C8-963C-0183601719D0@.microsoft.com...
>
>
|||Check to make sure that the SQL Server agent account on the publisher has
rights to write to the share, and full control of the directory and
subdirectory of the folder underlying this share. By default it will be read
only.
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
"WindWind" <WindWind@.discussions.microsoft.com> wrote in message
news:1C880CED-A2D2-4A73-B3EE-C49A50AFF5A0@.microsoft.com...[vbcol=seagreen]
> Thanks for your response.
> This server is connected to the network and I am able to map a drive to
> this
> share folder, but when I chose the folder, it just wouldn't allow me to
> use
> this folder as the snapshot share.
> "Hilary Cotter" wrote:
and Distributor (they are on same server), the snapshot folder is always
invalid. I was using administrator ID, the folder is located on the local
machine, it's shared. What else I need to change? Please help. Thanks.
Are you connected to a network? You need to be to be able to map a drive to
the snapshot share. I am pretty sure this is your problem. Note you can
install the loopback adapter and get it working that way.
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
"WindWind" <WindWind@.discussions.microsoft.com> wrote in message
news:8CD07DA4-9620-48C8-963C-0183601719D0@.microsoft.com...
>I just started to use SQL Server replication. When I configue the Publisher
> and Distributor (they are on same server), the snapshot folder is always
> invalid. I was using administrator ID, the folder is located on the local
> machine, it's shared. What else I need to change? Please help. Thanks.
|||Thanks for your response.
This server is connected to the network and I am able to map a drive to this
share folder, but when I chose the folder, it just wouldn't allow me to use
this folder as the snapshot share.
"Hilary Cotter" wrote:
> Are you connected to a network? You need to be to be able to map a drive to
> the snapshot share. I am pretty sure this is your problem. Note you can
> install the loopback adapter and get it working that way.
> --
> 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
> "WindWind" <WindWind@.discussions.microsoft.com> wrote in message
> news:8CD07DA4-9620-48C8-963C-0183601719D0@.microsoft.com...
>
>
|||Check to make sure that the SQL Server agent account on the publisher has
rights to write to the share, and full control of the directory and
subdirectory of the folder underlying this share. By default it will be read
only.
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
"WindWind" <WindWind@.discussions.microsoft.com> wrote in message
news:1C880CED-A2D2-4A73-B3EE-C49A50AFF5A0@.microsoft.com...[vbcol=seagreen]
> Thanks for your response.
> This server is connected to the network and I am able to map a drive to
> this
> share folder, but when I chose the folder, it just wouldn't allow me to
> use
> this folder as the snapshot share.
> "Hilary Cotter" wrote:
Wednesday, March 7, 2012
New install - replication test takes 14 seconds
SQL 2000 Installation - Clustered
6 GB of RAM
Dual Core with hyperthreading (shows 4 procs)
Created the simplest of tests, created 2 DB's (1 for publication, 1 for subscription), 1 table with 2 columns, a PK int and a varchar(50).
Setup distribution
Setup the publication on that 1 table
Setup a pull subscription on that publication
inserted a record, takes on average 12 seconds to replicate to the subscription DB, tested it 7 times, and every time it takes 12 seconds, tried updating a column, still takes 12 seconds.
It must be something very simple - anyone have an idea what could be going on here that would make it take so long? Any type of trace I can do to see what the bottleneck is? Going to start a SQL Profiler trace and see what I see, brb.Here are the trace results...
--14:58:53
UPDATE MY_TEST
SET MY_DATA = '__NEW__2'
WHERE MY_ID = 7
--14:58:54
exec sp_MSadd_logreader_history 1, 3, N'1 transaction(s) with 1 command(s) were delivered.', 0x000000050000025E0004, 0, 8, 8, 0, 0x00, 1, 0x01
--14:58:54
SELECT GETDATE(),* FROM DELETE_ME..MY_TEST
--14:58:55
SELECT GETDATE(),* FROM DELETE_ME..MY_TEST
--14:58:56
SELECT GETDATE(),* FROM DELETE_ME..MY_TEST
--14:58:57
SELECT GETDATE(),* FROM DELETE_ME..MY_TEST
--14:58:58
SELECT GETDATE(),* FROM DELETE_ME..MY_TEST
--14:58:59
SELECT GETDATE(),* FROM DELETE_ME..MY_TEST
--14:59:00
SELECT GETDATE(),* FROM DELETE_ME..MY_TEST
--14:59:00
exec sp_MSget_subscription_guid 1
--14:59:00
exec sp_MSget_repl_commands 1, 0x000000050000025C000400000000, 0, 7500000
--14:59:00
exec sp_MSupd_MY_TEST NULL, '__NEW__2', 7, 0x02
6 GB of RAM
Dual Core with hyperthreading (shows 4 procs)
Created the simplest of tests, created 2 DB's (1 for publication, 1 for subscription), 1 table with 2 columns, a PK int and a varchar(50).
Setup distribution
Setup the publication on that 1 table
Setup a pull subscription on that publication
inserted a record, takes on average 12 seconds to replicate to the subscription DB, tested it 7 times, and every time it takes 12 seconds, tried updating a column, still takes 12 seconds.
It must be something very simple - anyone have an idea what could be going on here that would make it take so long? Any type of trace I can do to see what the bottleneck is? Going to start a SQL Profiler trace and see what I see, brb.Here are the trace results...
--14:58:53
UPDATE MY_TEST
SET MY_DATA = '__NEW__2'
WHERE MY_ID = 7
--14:58:54
exec sp_MSadd_logreader_history 1, 3, N'1 transaction(s) with 1 command(s) were delivered.', 0x000000050000025E0004, 0, 8, 8, 0, 0x00, 1, 0x01
--14:58:54
SELECT GETDATE(),* FROM DELETE_ME..MY_TEST
--14:58:55
SELECT GETDATE(),* FROM DELETE_ME..MY_TEST
--14:58:56
SELECT GETDATE(),* FROM DELETE_ME..MY_TEST
--14:58:57
SELECT GETDATE(),* FROM DELETE_ME..MY_TEST
--14:58:58
SELECT GETDATE(),* FROM DELETE_ME..MY_TEST
--14:58:59
SELECT GETDATE(),* FROM DELETE_ME..MY_TEST
--14:59:00
SELECT GETDATE(),* FROM DELETE_ME..MY_TEST
--14:59:00
exec sp_MSget_subscription_guid 1
--14:59:00
exec sp_MSget_repl_commands 1, 0x000000050000025C000400000000, 0, 7500000
--14:59:00
exec sp_MSupd_MY_TEST NULL, '__NEW__2', 7, 0x02
New in Replication
There is now a decent book on replication by Hilary
Cotter who posts regularly here. Have a look at
http://www.nwsu.com/0974973602p.html. Apart from that,
there is books on line, which although it doesn't have
screenshots, is certainly enough to get started. There is
a Microsoft 3-day course run at many CTECs, and there are
a few resources on the web - have a look at the links on
www.replicationanswers.com.
Once you've got a grounding in the fundamentals, I'd
recommend setting up as many different scenarios as
possible on a test environment which will bring up many
further questions, and the newsgroup is ideal for this.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks
Amish M Shah
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:168201c4db97$432fb080$a301280a@.phx.gbl...
> There is now a decent book on replication by Hilary
> Cotter who posts regularly here. Have a look at
> http://www.nwsu.com/0974973602p.html. Apart from that,
> there is books on line, which although it doesn't have
> screenshots, is certainly enough to get started. There is
> a Microsoft 3-day course run at many CTECs, and there are
> a few resources on the web - have a look at the links on
> www.replicationanswers.com.
> Once you've got a grounding in the fundamentals, I'd
> recommend setting up as many different scenarios as
> possible on a test environment which will bring up many
> further questions, and the newsgroup is ideal for this.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
Cotter who posts regularly here. Have a look at
http://www.nwsu.com/0974973602p.html. Apart from that,
there is books on line, which although it doesn't have
screenshots, is certainly enough to get started. There is
a Microsoft 3-day course run at many CTECs, and there are
a few resources on the web - have a look at the links on
www.replicationanswers.com.
Once you've got a grounding in the fundamentals, I'd
recommend setting up as many different scenarios as
possible on a test environment which will bring up many
further questions, and the newsgroup is ideal for this.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks
Amish M Shah
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:168201c4db97$432fb080$a301280a@.phx.gbl...
> There is now a decent book on replication by Hilary
> Cotter who posts regularly here. Have a look at
> http://www.nwsu.com/0974973602p.html. Apart from that,
> there is books on line, which although it doesn't have
> screenshots, is certainly enough to get started. There is
> a Microsoft 3-day course run at many CTECs, and there are
> a few resources on the web - have a look at the links on
> www.replicationanswers.com.
> Once you've got a grounding in the fundamentals, I'd
> recommend setting up as many different scenarios as
> possible on a test environment which will bring up many
> further questions, and the newsgroup is ideal for this.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
New icon appears after restarting Distribution/Merge agents - mean
Hello,
All of a sudden, Sql Server 2000 Replication has started failing for
Transaction and Merge Replication on our system. I have been restarting the
Distribution and Merge Agents and after Refresh, the Red X goes away (for a
little while). Something obviously is out of synch. But on the last restart
of the agents (which is actually the first time I restarted the Merge agent -
as previously only the Distribution Agent had the red x), now there is this
blue twisting Arrow inside of a small circle icon attached to the Replication
Monitor Icon, Publishers Icon, Agents Icon, and Distribution Icon. When I
click on the distribution icon, in the Right pane of Enterprise Manager, the
<Multiple Publications> icon also has the little circle with the blue
twisting arrow icon attached to it.
Is there any significance to this icon? What does it mean? It is not red
and is not an X, so it does not look that scary, but I am sure it means
something I need to worry about. Any suggestions greatly appreciated.
Thanks,
Rich
I just did a Refesh on the Replication Monitor and the strange new icon has
disappeard, and there is no Red X icon (what a relief). But I am still
curious what is the meaning of this icon of a little circle with the twisting
blue arrow attached to the Replication Monitor Icon and the others. This
strange icon was attached to the lower right corner of the main icon.
"Rich" wrote:
> Hello,
> All of a sudden, Sql Server 2000 Replication has started failing for
> Transaction and Merge Replication on our system. I have been restarting the
> Distribution and Merge Agents and after Refresh, the Red X goes away (for a
> little while). Something obviously is out of synch. But on the last restart
> of the agents (which is actually the first time I restarted the Merge agent -
> as previously only the Distribution Agent had the red x), now there is this
> blue twisting Arrow inside of a small circle icon attached to the Replication
> Monitor Icon, Publishers Icon, Agents Icon, and Distribution Icon. When I
> click on the distribution icon, in the Right pane of Enterprise Manager, the
> <Multiple Publications> icon also has the little circle with the blue
> twisting arrow icon attached to it.
> Is there any significance to this icon? What does it mean? It is not red
> and is not an X, so it does not look that scary, but I am sure it means
> something I need to worry about. Any suggestions greatly appreciated.
> Thanks,
> Rich
>
|||That blue arrow means retrying.
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
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:040C384F-4B30-4904-AEB2-ADA2E6F7B2FD@.microsoft.com...[vbcol=seagreen]
>I just did a Refesh on the Replication Monitor and the strange new icon has
> disappeard, and there is no Red X icon (what a relief). But I am still
> curious what is the meaning of this icon of a little circle with the
> twisting
> blue arrow attached to the Replication Monitor Icon and the others. This
> strange icon was attached to the lower right corner of the main icon.
>
> "Rich" wrote:
|||Thank you.
"Hilary Cotter" wrote:
> That blue arrow means retrying.
> --
> 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
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:040C384F-4B30-4904-AEB2-ADA2E6F7B2FD@.microsoft.com...
>
>
All of a sudden, Sql Server 2000 Replication has started failing for
Transaction and Merge Replication on our system. I have been restarting the
Distribution and Merge Agents and after Refresh, the Red X goes away (for a
little while). Something obviously is out of synch. But on the last restart
of the agents (which is actually the first time I restarted the Merge agent -
as previously only the Distribution Agent had the red x), now there is this
blue twisting Arrow inside of a small circle icon attached to the Replication
Monitor Icon, Publishers Icon, Agents Icon, and Distribution Icon. When I
click on the distribution icon, in the Right pane of Enterprise Manager, the
<Multiple Publications> icon also has the little circle with the blue
twisting arrow icon attached to it.
Is there any significance to this icon? What does it mean? It is not red
and is not an X, so it does not look that scary, but I am sure it means
something I need to worry about. Any suggestions greatly appreciated.
Thanks,
Rich
I just did a Refesh on the Replication Monitor and the strange new icon has
disappeard, and there is no Red X icon (what a relief). But I am still
curious what is the meaning of this icon of a little circle with the twisting
blue arrow attached to the Replication Monitor Icon and the others. This
strange icon was attached to the lower right corner of the main icon.
"Rich" wrote:
> Hello,
> All of a sudden, Sql Server 2000 Replication has started failing for
> Transaction and Merge Replication on our system. I have been restarting the
> Distribution and Merge Agents and after Refresh, the Red X goes away (for a
> little while). Something obviously is out of synch. But on the last restart
> of the agents (which is actually the first time I restarted the Merge agent -
> as previously only the Distribution Agent had the red x), now there is this
> blue twisting Arrow inside of a small circle icon attached to the Replication
> Monitor Icon, Publishers Icon, Agents Icon, and Distribution Icon. When I
> click on the distribution icon, in the Right pane of Enterprise Manager, the
> <Multiple Publications> icon also has the little circle with the blue
> twisting arrow icon attached to it.
> Is there any significance to this icon? What does it mean? It is not red
> and is not an X, so it does not look that scary, but I am sure it means
> something I need to worry about. Any suggestions greatly appreciated.
> Thanks,
> Rich
>
|||That blue arrow means retrying.
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
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:040C384F-4B30-4904-AEB2-ADA2E6F7B2FD@.microsoft.com...[vbcol=seagreen]
>I just did a Refesh on the Replication Monitor and the strange new icon has
> disappeard, and there is no Red X icon (what a relief). But I am still
> curious what is the meaning of this icon of a little circle with the
> twisting
> blue arrow attached to the Replication Monitor Icon and the others. This
> strange icon was attached to the lower right corner of the main icon.
>
> "Rich" wrote:
|||Thank you.
"Hilary Cotter" wrote:
> That blue arrow means retrying.
> --
> 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
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:040C384F-4B30-4904-AEB2-ADA2E6F7B2FD@.microsoft.com...
>
>
Subscribe to:
Posts (Atom)