Scenario: 3 servers - one Subscriber, one Distributor, one Publisher. Transactional replication; immediate updating subscribers disallowed. Each article in its own publication... independent agents.
Subscriber & Publisher = SQL 7.0
Distributor recently upgraded to 2000, SP3a.
Issue: new publications created at publisher fail to initialize snapshot agent properly. Error: "Another snapshot agent for the publication is running". Agent then fails after total retries (default is 10... does not matter what I set it to).
All existing publications work fine; all existing subscriptions work fine.
So what's the deal?
It appears that once you upgrade the Distributor(s), you have to upgrade the Publisher(s) immediately thereafter if you wish to create new publications. I have not seen that documented however... anyone know?
Thanks!
X
This is documented somewhat. Check out
http://msdn.microsoft.com/library/de...grade_45ir.asp
It doesn't say do this immediately though.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
news:D8EF5EEF-A0D7-4F69-B519-FD970A02F509@.microsoft.com...
> Scenario: 3 servers - one Subscriber, one Distributor, one Publisher.
Transactional replication; immediate updating subscribers disallowed. Each
article in its own publication... independent agents.
> Subscriber & Publisher = SQL 7.0
> Distributor recently upgraded to 2000, SP3a.
> Issue: new publications created at publisher fail to initialize snapshot
agent properly. Error: "Another snapshot agent for the publication is
running". Agent then fails after total retries (default is 10... does not
matter what I set it to).
> All existing publications work fine; all existing subscriptions work fine.
> So what's the deal?
> It appears that once you upgrade the Distributor(s), you have to upgrade
the Publisher(s) immediately thereafter if you wish to create new
publications. I have not seen that documented however... anyone know?
> Thanks!
> X
|||Thanks for the response Hilary.
I read that article; it doesn't specify that publishers MUST be upgraded to 2000 once the Distributor has been upgraded. That's the issue here: we have an upgraded Distributor, but we cannot upgrade the Publisher for some time. In the meantime, we need to
create new publications. We are unable to do so however, due to the error I outlined. It APPEARS that once you upgrade your Distributor, you have to upgrade your Publisher before you can create new publications. That doesn't seem right, but I have yet to
find evidence of its validity.
FWIW: one of the DBAs here reported the same error after applying SPs/hotfixes on the Distributor w/o applying same to Publisher.
"Hilary Cotter" wrote:
> This is documented somewhat. Check out
> http://msdn.microsoft.com/library/de...grade_45ir.asp
> It doesn't say do this immediately though.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
> news:D8EF5EEF-A0D7-4F69-B519-FD970A02F509@.microsoft.com...
> Transactional replication; immediate updating subscribers disallowed. Each
> article in its own publication... independent agents.
> agent properly. Error: "Another snapshot agent for the publication is
> running". Agent then fails after total retries (default is 10... does not
> matter what I set it to).
> the Publisher(s) immediately thereafter if you wish to create new
> publications. I have not seen that documented however... anyone know?
>
>
|||FYI: the following URL shows the configuration I outlined as a 'valid replication topology' http://msdn.microsoft.com/library/de...limpl_4joy.asp
However, it doe not indicate that new publications created on the 7.0 Publisher w/ 2k Distributor and 7.0 Subscriber(s) will fail. On the contrary - it leads one to believe that the new publications will be in 70 compatability mode, as the replication top
ology defaults to the lowest level in a mixed environment.
Odd then, that I get this error. (btw, we have recycled the servers)
"Hilary Cotter" wrote:
> This is documented somewhat. Check out
> http://msdn.microsoft.com/library/de...grade_45ir.asp
> It doesn't say do this immediately though.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
> news:D8EF5EEF-A0D7-4F69-B519-FD970A02F509@.microsoft.com...
> Transactional replication; immediate updating subscribers disallowed. Each
> article in its own publication... independent agents.
> agent properly. Error: "Another snapshot agent for the publication is
> running". Agent then fails after total retries (default is 10... does not
> matter what I set it to).
> the Publisher(s) immediately thereafter if you wish to create new
> publications. I have not seen that documented however... anyone know?
>
>
|||Let me try to repro this tonight.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
news:9034767C-3D75-4C7B-B975-510CC8AFDFDD@.microsoft.com...
> Thanks for the response Hilary.
> I read that article; it doesn't specify that publishers MUST be upgraded
to 2000 once the Distributor has been upgraded. That's the issue here: we
have an upgraded Distributor, but we cannot upgrade the Publisher for some
time. In the meantime, we need to create new publications. We are unable to
do so however, due to the error I outlined. It APPEARS that once you upgrade
your Distributor, you have to upgrade your Publisher before you can create
new publications. That doesn't seem right, but I have yet to find evidence
of its validity.
> FWIW: one of the DBAs here reported the same error after applying
SPs/hotfixes on the Distributor w/o applying same to Publisher.[vbcol=seagreen]
> "Hilary Cotter" wrote:
http://msdn.microsoft.com/library/de...grade_45ir.asp[vbcol=seagreen]
Each[vbcol=seagreen]
snapshot[vbcol=seagreen]
not[vbcol=seagreen]
fine.[vbcol=seagreen]
upgrade[vbcol=seagreen]
|||Were you able to reproduce the error?
Many thanks!!
X
"Hilary Cotter" wrote:
> Let me try to repro this tonight.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
> news:9034767C-3D75-4C7B-B975-510CC8AFDFDD@.microsoft.com...
> to 2000 once the Distributor has been upgraded. That's the issue here: we
> have an upgraded Distributor, but we cannot upgrade the Publisher for some
> time. In the meantime, we need to create new publications. We are unable to
> do so however, due to the error I outlined. It APPEARS that once you upgrade
> your Distributor, you have to upgrade your Publisher before you can create
> new publications. That doesn't seem right, but I have yet to find evidence
> of its validity.
> SPs/hotfixes on the Distributor w/o applying same to Publisher.
> http://msdn.microsoft.com/library/de...grade_45ir.asp
> Each
> snapshot
> not
> fine.
> upgrade
>
>
|||No, I was unable to get the error. What error did you get, and what service
packs were you running?
Where you using an Administrative Link password?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
news:C359EA3E-0ED4-417C-B1A9-F4F015253BD0@.microsoft.com...[vbcol=seagreen]
> Were you able to reproduce the error?
> Many thanks!!
> X
>
> "Hilary Cotter" wrote:
upgraded[vbcol=seagreen]
we[vbcol=seagreen]
some[vbcol=seagreen]
to[vbcol=seagreen]
upgrade[vbcol=seagreen]
create[vbcol=seagreen]
evidence[vbcol=seagreen]
http://msdn.microsoft.com/library/de...grade_45ir.asp[vbcol=seagreen]
Publisher.[vbcol=seagreen]
disallowed.[vbcol=seagreen]
is[vbcol=seagreen]
does[vbcol=seagreen]
work[vbcol=seagreen]
know?[vbcol=seagreen]
sql
Showing posts with label subscribers. Show all posts
Showing posts with label subscribers. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
Replication between SQL 2000 & SQL 2005
We currently replicate two databases. We would like to installed SQL 2005 o
n
the publisher of these replications. The subscribers will still be on
SQL2000. Will this work? What potential problems should I look for?
ThanksI built a couple of test servers to test upgrading from SQL Server 2000 to
2005. One of the components that I tested was replication.
I think that I upgraded the publisher first, but I cannot remember for sure.
I ended up upgrading both servers. Replication worked when I had one box on
2000 and one on 2005 and it worked after upgrading the other box to 2005.
I don't think that you are in for any surprises.
Keith Kratochvil
"Lynnie" <Lynnie@.discussions.microsoft.com> wrote in message
news:9ED90D1A-0EDF-4B3F-9E36-4792DD052F9B@.microsoft.com...
> We currently replicate two databases. We would like to installed SQL 2005
> on
> the publisher of these replications. The subscribers will still be on
> SQL2000. Will this work? What potential problems should I look for?
> Thankssql
n
the publisher of these replications. The subscribers will still be on
SQL2000. Will this work? What potential problems should I look for?
ThanksI built a couple of test servers to test upgrading from SQL Server 2000 to
2005. One of the components that I tested was replication.
I think that I upgraded the publisher first, but I cannot remember for sure.
I ended up upgrading both servers. Replication worked when I had one box on
2000 and one on 2005 and it worked after upgrading the other box to 2005.
I don't think that you are in for any surprises.
Keith Kratochvil
"Lynnie" <Lynnie@.discussions.microsoft.com> wrote in message
news:9ED90D1A-0EDF-4B3F-9E36-4792DD052F9B@.microsoft.com...
> We currently replicate two databases. We would like to installed SQL 2005
> on
> the publisher of these replications. The subscribers will still be on
> SQL2000. Will this work? What potential problems should I look for?
> Thankssql
Replication between SQL 2000 & SQL 2005
We currently replicate two databases. We would like to installed SQL 2005 on
the publisher of these replications. The subscribers will still be on
SQL2000. Will this work? What potential problems should I look for?
ThanksI built a couple of test servers to test upgrading from SQL Server 2000 to
2005. One of the components that I tested was replication.
I think that I upgraded the publisher first, but I cannot remember for sure.
I ended up upgrading both servers. Replication worked when I had one box on
2000 and one on 2005 and it worked after upgrading the other box to 2005.
I don't think that you are in for any surprises.
--
Keith Kratochvil
"Lynnie" <Lynnie@.discussions.microsoft.com> wrote in message
news:9ED90D1A-0EDF-4B3F-9E36-4792DD052F9B@.microsoft.com...
> We currently replicate two databases. We would like to installed SQL 2005
> on
> the publisher of these replications. The subscribers will still be on
> SQL2000. Will this work? What potential problems should I look for?
> Thanks
the publisher of these replications. The subscribers will still be on
SQL2000. Will this work? What potential problems should I look for?
ThanksI built a couple of test servers to test upgrading from SQL Server 2000 to
2005. One of the components that I tested was replication.
I think that I upgraded the publisher first, but I cannot remember for sure.
I ended up upgrading both servers. Replication worked when I had one box on
2000 and one on 2005 and it worked after upgrading the other box to 2005.
I don't think that you are in for any surprises.
--
Keith Kratochvil
"Lynnie" <Lynnie@.discussions.microsoft.com> wrote in message
news:9ED90D1A-0EDF-4B3F-9E36-4792DD052F9B@.microsoft.com...
> We currently replicate two databases. We would like to installed SQL 2005
> on
> the publisher of these replications. The subscribers will still be on
> SQL2000. Will this work? What potential problems should I look for?
> Thanks
Wednesday, March 21, 2012
Replication based on subscribers informations
Hello all,
I want to use replication between one main (publisher/distributor) SQL server 2000 on a Windows 2003 Server, and a group of SQL server 2000 over the internet.
1)
There is some tables on the main SQL server that need to be propagated to the other servers based on (Insert/Update/Delete) transactions. I called that kind static tables.
Example: there is a table called 'Events' wich have to propagate its data to ALL the subscribers.
2)
And there is another tables on the main SQL server that have data that must be propagated to clients (subscribers) based on their informations. I called that kind dynamic tables.
Example: there is table called 'Operations' wich have a column called 'Entreprise_ID', that have data about all operations concerning some entreprises. Those real entreprises have also SQL servers (wich shall be used as subscribers) to get data from the m
ain server, but Entreprise X must receive only data based on 'SELECT * from Operations WHERE Entreprise_ID=X'
I'm new into replication, i've searched over the web and understood it a little bit, so i thought that in my case i should use Transactional Replication if we suppose that data is a bit critical and must be refreshed on all the servers.
i configured the main SQL server with a subsriber to test transactional replication over the internet concerning what i called 'static tables' , it works very well.
But for applying filters on the 'dynamic tables' i don't know how to do it, i've seen that i can apply 'vertical filters' on those tables, but i don't know how to associate it with subscribers informations in order to send only appropriate data to each su
bscriber.
Thanx in advance for any comment.
a vertical filter is where you replicate a subset of the columns, a
horizontal filter is a where you replicate a subset of the rows in the
table.
I take it you want to do horizontal filtering or partitioning. To do this
right click on your publication and select publication properties, click on
the filter rows tab. locate the tables you wish to filter, and click on the
browse button to the right of the table/article. You will get the specify
filter dialog. Enter your filter there.
After your have made your changes, regenerate your snapshot, and distribute
it to your subscribers.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"The_Flanker" <The_Flanker@.discussions.microsoft.com> wrote in message
news:54A173EE-B094-46BA-B841-A57F95ADE772@.microsoft.com...
> Hello all,
> I want to use replication between one main (publisher/distributor) SQL
server 2000 on a Windows 2003 Server, and a group of SQL server 2000 over
the internet.
> 1)
> There is some tables on the main SQL server that need to be propagated to
the other servers based on (Insert/Update/Delete) transactions. I called
that kind static tables.
> Example: there is a table called 'Events' wich have to propagate its data
to ALL the subscribers.
> 2)
> And there is another tables on the main SQL server that have data that
must be propagated to clients (subscribers) based on their informations. I
called that kind dynamic tables.
> Example: there is table called 'Operations' wich have a column called
'Entreprise_ID', that have data about all operations concerning some
entreprises. Those real entreprises have also SQL servers (wich shall be
used as subscribers) to get data from the main server, but Entreprise X must
receive only data based on 'SELECT * from Operations WHERE Entreprise_ID=X'
>
> I'm new into replication, i've searched over the web and understood it a
little bit, so i thought that in my case i should use Transactional
Replication if we suppose that data is a bit critical and must be refreshed
on all the servers.
> i configured the main SQL server with a subsriber to test transactional
replication over the internet concerning what i called 'static tables' , it
works very well.
> But for applying filters on the 'dynamic tables' i don't know how to do
it, i've seen that i can apply 'vertical filters' on those tables, but i
don't know how to associate it with subscribers informations in order to
send only appropriate data to each subscriber.
> Thanx in advance for any comment.
|||Hello,
Yes Hilary, indeed, i made a mistake talking about 'vertical filtering', i must use 'horizontal filtering' but, the problem is not how to do it, by the wizard or something like that, i already know how to do it, my problem is a conception problem. How to
define an 'dynamic horizontal filter' for an article of a Transactional Replication, and this filter must propagate data dynamically based on subscribers informations.
For example:
Let's suppose that i have a table called 'Operations' that have 3 columns.
Op_ID--Date--Entreprise
1--01/05/2004--1
2--02/05/2004--2
3--15/05/2004--1
4--15/06/2004--3
5--19/06/2004--3
6--01/07/2004--2
so in this case let's suppose that we have 3 subscribers, hence 3 SQL servers each server in one Entreprise.
My need is, if a transaction happened in the table 'Operations' it have to be automatically propagated to the concerned entreprise. eg: there is one new row added '7--04/07/2004--2', this row have to be sent only to Subsriber number 2 because the o
ther subscribers don't need this data.
then, we'll have as a result, after synchronisation:
At Subscriber #1:
1--01/05/2004--1
3--15/05/2004--1
At Subscriber #2:
2--02/05/2004--2
6--01/07/2004--2
7--04/07/2004--2 (the new row)
And at Subscriber #3:
4--15/06/2004--3
5--19/06/2004--3
If anyone can tell me how to manage that problem, it will be a really Great help for me.
Sorry about my english level, it's my third language.
Thanks in advance for your comments.
"Hilary Cotter" wrote:
> a vertical filter is where you replicate a subset of the columns, a
> horizontal filter is a where you replicate a subset of the rows in the
> table.
> I take it you want to do horizontal filtering or partitioning. To do this
> right click on your publication and select publication properties, click on
> the filter rows tab. locate the tables you wish to filter, and click on the
> browse button to the right of the table/article. You will get the specify
> filter dialog. Enter your filter there.
> After your have made your changes, regenerate your snapshot, and distribute
> it to your subscribers.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "The_Flanker" <The_Flanker@.discussions.microsoft.com> wrote in message
> news:54A173EE-B094-46BA-B841-A57F95ADE772@.microsoft.com...
> server 2000 on a Windows 2003 Server, and a group of SQL server 2000 over
> the internet.
> the other servers based on (Insert/Update/Delete) transactions. I called
> that kind static tables.
> to ALL the subscribers.
> must be propagated to clients (subscribers) based on their informations. I
> called that kind dynamic tables.
> 'Entreprise_ID', that have data about all operations concerning some
> entreprises. Those real entreprises have also SQL servers (wich shall be
> used as subscribers) to get data from the main server, but Entreprise X must
> receive only data based on 'SELECT * from Operations WHERE Entreprise_ID=X'
> little bit, so i thought that in my case i should use Transactional
> Replication if we suppose that data is a bit critical and must be refreshed
> on all the servers.
> replication over the internet concerning what i called 'static tables' , it
> works very well.
> it, i've seen that i can apply 'vertical filters' on those tables, but i
> don't know how to associate it with subscribers informations in order to
> send only appropriate data to each subscriber.
>
>
I want to use replication between one main (publisher/distributor) SQL server 2000 on a Windows 2003 Server, and a group of SQL server 2000 over the internet.
1)
There is some tables on the main SQL server that need to be propagated to the other servers based on (Insert/Update/Delete) transactions. I called that kind static tables.
Example: there is a table called 'Events' wich have to propagate its data to ALL the subscribers.
2)
And there is another tables on the main SQL server that have data that must be propagated to clients (subscribers) based on their informations. I called that kind dynamic tables.
Example: there is table called 'Operations' wich have a column called 'Entreprise_ID', that have data about all operations concerning some entreprises. Those real entreprises have also SQL servers (wich shall be used as subscribers) to get data from the m
ain server, but Entreprise X must receive only data based on 'SELECT * from Operations WHERE Entreprise_ID=X'
I'm new into replication, i've searched over the web and understood it a little bit, so i thought that in my case i should use Transactional Replication if we suppose that data is a bit critical and must be refreshed on all the servers.
i configured the main SQL server with a subsriber to test transactional replication over the internet concerning what i called 'static tables' , it works very well.
But for applying filters on the 'dynamic tables' i don't know how to do it, i've seen that i can apply 'vertical filters' on those tables, but i don't know how to associate it with subscribers informations in order to send only appropriate data to each su
bscriber.
Thanx in advance for any comment.
a vertical filter is where you replicate a subset of the columns, a
horizontal filter is a where you replicate a subset of the rows in the
table.
I take it you want to do horizontal filtering or partitioning. To do this
right click on your publication and select publication properties, click on
the filter rows tab. locate the tables you wish to filter, and click on the
browse button to the right of the table/article. You will get the specify
filter dialog. Enter your filter there.
After your have made your changes, regenerate your snapshot, and distribute
it to your subscribers.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"The_Flanker" <The_Flanker@.discussions.microsoft.com> wrote in message
news:54A173EE-B094-46BA-B841-A57F95ADE772@.microsoft.com...
> Hello all,
> I want to use replication between one main (publisher/distributor) SQL
server 2000 on a Windows 2003 Server, and a group of SQL server 2000 over
the internet.
> 1)
> There is some tables on the main SQL server that need to be propagated to
the other servers based on (Insert/Update/Delete) transactions. I called
that kind static tables.
> Example: there is a table called 'Events' wich have to propagate its data
to ALL the subscribers.
> 2)
> And there is another tables on the main SQL server that have data that
must be propagated to clients (subscribers) based on their informations. I
called that kind dynamic tables.
> Example: there is table called 'Operations' wich have a column called
'Entreprise_ID', that have data about all operations concerning some
entreprises. Those real entreprises have also SQL servers (wich shall be
used as subscribers) to get data from the main server, but Entreprise X must
receive only data based on 'SELECT * from Operations WHERE Entreprise_ID=X'
>
> I'm new into replication, i've searched over the web and understood it a
little bit, so i thought that in my case i should use Transactional
Replication if we suppose that data is a bit critical and must be refreshed
on all the servers.
> i configured the main SQL server with a subsriber to test transactional
replication over the internet concerning what i called 'static tables' , it
works very well.
> But for applying filters on the 'dynamic tables' i don't know how to do
it, i've seen that i can apply 'vertical filters' on those tables, but i
don't know how to associate it with subscribers informations in order to
send only appropriate data to each subscriber.
> Thanx in advance for any comment.
|||Hello,
Yes Hilary, indeed, i made a mistake talking about 'vertical filtering', i must use 'horizontal filtering' but, the problem is not how to do it, by the wizard or something like that, i already know how to do it, my problem is a conception problem. How to
define an 'dynamic horizontal filter' for an article of a Transactional Replication, and this filter must propagate data dynamically based on subscribers informations.
For example:
Let's suppose that i have a table called 'Operations' that have 3 columns.
Op_ID--Date--Entreprise
1--01/05/2004--1
2--02/05/2004--2
3--15/05/2004--1
4--15/06/2004--3
5--19/06/2004--3
6--01/07/2004--2
so in this case let's suppose that we have 3 subscribers, hence 3 SQL servers each server in one Entreprise.
My need is, if a transaction happened in the table 'Operations' it have to be automatically propagated to the concerned entreprise. eg: there is one new row added '7--04/07/2004--2', this row have to be sent only to Subsriber number 2 because the o
ther subscribers don't need this data.
then, we'll have as a result, after synchronisation:
At Subscriber #1:
1--01/05/2004--1
3--15/05/2004--1
At Subscriber #2:
2--02/05/2004--2
6--01/07/2004--2
7--04/07/2004--2 (the new row)
And at Subscriber #3:
4--15/06/2004--3
5--19/06/2004--3
If anyone can tell me how to manage that problem, it will be a really Great help for me.
Sorry about my english level, it's my third language.
Thanks in advance for your comments.
"Hilary Cotter" wrote:
> a vertical filter is where you replicate a subset of the columns, a
> horizontal filter is a where you replicate a subset of the rows in the
> table.
> I take it you want to do horizontal filtering or partitioning. To do this
> right click on your publication and select publication properties, click on
> the filter rows tab. locate the tables you wish to filter, and click on the
> browse button to the right of the table/article. You will get the specify
> filter dialog. Enter your filter there.
> After your have made your changes, regenerate your snapshot, and distribute
> it to your subscribers.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "The_Flanker" <The_Flanker@.discussions.microsoft.com> wrote in message
> news:54A173EE-B094-46BA-B841-A57F95ADE772@.microsoft.com...
> server 2000 on a Windows 2003 Server, and a group of SQL server 2000 over
> the internet.
> the other servers based on (Insert/Update/Delete) transactions. I called
> that kind static tables.
> to ALL the subscribers.
> must be propagated to clients (subscribers) based on their informations. I
> called that kind dynamic tables.
> 'Entreprise_ID', that have data about all operations concerning some
> entreprises. Those real entreprises have also SQL servers (wich shall be
> used as subscribers) to get data from the main server, but Entreprise X must
> receive only data based on 'SELECT * from Operations WHERE Entreprise_ID=X'
> little bit, so i thought that in my case i should use Transactional
> Replication if we suppose that data is a bit critical and must be refreshed
> on all the servers.
> replication over the internet concerning what i called 'static tables' , it
> works very well.
> it, i've seen that i can apply 'vertical filters' on those tables, but i
> don't know how to associate it with subscribers informations in order to
> send only appropriate data to each subscriber.
>
>
Tuesday, March 20, 2012
Replication and security..is it really this UNSECURE!
Hi Guys
I need to create an anonymous pull subscription over the Internet.
I've got everything working but I don't want my subscribers to be able
to connect to the publisher/distributor via Enterprise Manager or
ISQLW or anything else for that matter!! Basically they should only
be able to talk to the Publishing SQL Server through replication
methods. How is this achieved?
I'm currently using SQL Security because I don't want an anonymous
subscriber to impersonate my publishers sqlserver agent account, the
agent on the publisher is used for other scheduled tasks that needs
Domain Admin privileges, am I missing something here!!'
Currently I've got it secure-ish. I've used a specific sql account
and given it access in the PAL and removed the guest account from
other DBs, however you cannot remove the guest account from Master or
Msdb and said account must also be a member of the db_owner role. The
most they can do is issue select statements against tables but I think
that is even too much, for instance they can query sysservers which
would allow them to see other subscribers that we do business with.
Even if I use a secure VPN between publisher and subscriber what's to
stop an inquistive subscriber, using the SQL account that I've told
them to use for their subscription, connecting using EM or ISQLW
through the VPN? Surely there must be a way to give ONLY replication
access but nothing else?
Please help!!What type of Replication are you implementing? Snapshot/Transactional or
Merge ?
If they have the data on the Subscriber, there would really be no need to
connect to the Publisher. So, if the users are connecting to the
Subscribing database, that's all they should need.
Are you attempting to restrict access to the Subscribed database?
There isn't a way to restrict access FROM a particular application. So, if
you allow the user to connect to the Server and grant them access to the
database, then they have access from *any* odbc or oledb application.
There isn't a way to restrict this.
Replication works using the accounts designated with the Agents. It
doesn't rely on what permissions the users have.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
I need to create an anonymous pull subscription over the Internet.
I've got everything working but I don't want my subscribers to be able
to connect to the publisher/distributor via Enterprise Manager or
ISQLW or anything else for that matter!! Basically they should only
be able to talk to the Publishing SQL Server through replication
methods. How is this achieved?
I'm currently using SQL Security because I don't want an anonymous
subscriber to impersonate my publishers sqlserver agent account, the
agent on the publisher is used for other scheduled tasks that needs
Domain Admin privileges, am I missing something here!!'
Currently I've got it secure-ish. I've used a specific sql account
and given it access in the PAL and removed the guest account from
other DBs, however you cannot remove the guest account from Master or
Msdb and said account must also be a member of the db_owner role. The
most they can do is issue select statements against tables but I think
that is even too much, for instance they can query sysservers which
would allow them to see other subscribers that we do business with.
Even if I use a secure VPN between publisher and subscriber what's to
stop an inquistive subscriber, using the SQL account that I've told
them to use for their subscription, connecting using EM or ISQLW
through the VPN? Surely there must be a way to give ONLY replication
access but nothing else?
Please help!!What type of Replication are you implementing? Snapshot/Transactional or
Merge ?
If they have the data on the Subscriber, there would really be no need to
connect to the Publisher. So, if the users are connecting to the
Subscribing database, that's all they should need.
Are you attempting to restrict access to the Subscribed database?
There isn't a way to restrict access FROM a particular application. So, if
you allow the user to connect to the Server and grant them access to the
database, then they have access from *any* odbc or oledb application.
There isn't a way to restrict this.
Replication works using the accounts designated with the Agents. It
doesn't rely on what permissions the users have.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Replication and security..is it really this UNSECURE!
Hi Guys
I need to create an anonymous pull subscription over the Internet.
I've got everything working but I don't want my subscribers to be able
to connect to the publisher/distributor via Enterprise Manager or
ISQLW or anything else for that matter!! Basically they should only
be able to talk to the Publishing SQL Server through replication
methods. How is this achieved?
I'm currently using SQL Security because I don't want an anonymous
subscriber to impersonate my publishers sqlserver agent account, the
agent on the publisher is used for other scheduled tasks that needs
Domain Admin privileges, am I missing something here!!?
Currently I've got it secure-ish. I've used a specific sql account
and given it access in the PAL and removed the guest account from
other DBs, however you cannot remove the guest account from Master or
Msdb and said account must also be a member of the db_owner role. The
most they can do is issue select statements against tables but I think
that is even too much, for instance they can query sysservers which
would allow them to see other subscribers that we do business with.
Even if I use a secure VPN between publisher and subscriber what's to
stop an inquistive subscriber, using the SQL account that I've told
them to use for their subscription, connecting using EM or ISQLW
through the VPN? Surely there must be a way to give ONLY replication
access but nothing else?
Please help!!
The problem is not with replication and security, but rather a larger one
with SQL Server security in general. Anytime you create an account on that
server, and give that account rights to only one user database, this account
can query system tables on the master database.
I suggest you post this question to the microsoft.public.sqlserver.security
newsgroup.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"greedo" <d8rrylf@.msn.com> wrote in message
news:f0badc7a.0408170010.47baef00@.posting.google.c om...
> Hi Guys
> I need to create an anonymous pull subscription over the Internet.
> I've got everything working but I don't want my subscribers to be able
> to connect to the publisher/distributor via Enterprise Manager or
> ISQLW or anything else for that matter!! Basically they should only
> be able to talk to the Publishing SQL Server through replication
> methods. How is this achieved?
> I'm currently using SQL Security because I don't want an anonymous
> subscriber to impersonate my publishers sqlserver agent account, the
> agent on the publisher is used for other scheduled tasks that needs
> Domain Admin privileges, am I missing something here!!?
> Currently I've got it secure-ish. I've used a specific sql account
> and given it access in the PAL and removed the guest account from
> other DBs, however you cannot remove the guest account from Master or
> Msdb and said account must also be a member of the db_owner role. The
> most they can do is issue select statements against tables but I think
> that is even too much, for instance they can query sysservers which
> would allow them to see other subscribers that we do business with.
> Even if I use a secure VPN between publisher and subscriber what's to
> stop an inquistive subscriber, using the SQL account that I've told
> them to use for their subscription, connecting using EM or ISQLW
> through the VPN? Surely there must be a way to give ONLY replication
> access but nothing else?
> Please help!!
I need to create an anonymous pull subscription over the Internet.
I've got everything working but I don't want my subscribers to be able
to connect to the publisher/distributor via Enterprise Manager or
ISQLW or anything else for that matter!! Basically they should only
be able to talk to the Publishing SQL Server through replication
methods. How is this achieved?
I'm currently using SQL Security because I don't want an anonymous
subscriber to impersonate my publishers sqlserver agent account, the
agent on the publisher is used for other scheduled tasks that needs
Domain Admin privileges, am I missing something here!!?
Currently I've got it secure-ish. I've used a specific sql account
and given it access in the PAL and removed the guest account from
other DBs, however you cannot remove the guest account from Master or
Msdb and said account must also be a member of the db_owner role. The
most they can do is issue select statements against tables but I think
that is even too much, for instance they can query sysservers which
would allow them to see other subscribers that we do business with.
Even if I use a secure VPN between publisher and subscriber what's to
stop an inquistive subscriber, using the SQL account that I've told
them to use for their subscription, connecting using EM or ISQLW
through the VPN? Surely there must be a way to give ONLY replication
access but nothing else?
Please help!!
The problem is not with replication and security, but rather a larger one
with SQL Server security in general. Anytime you create an account on that
server, and give that account rights to only one user database, this account
can query system tables on the master database.
I suggest you post this question to the microsoft.public.sqlserver.security
newsgroup.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"greedo" <d8rrylf@.msn.com> wrote in message
news:f0badc7a.0408170010.47baef00@.posting.google.c om...
> Hi Guys
> I need to create an anonymous pull subscription over the Internet.
> I've got everything working but I don't want my subscribers to be able
> to connect to the publisher/distributor via Enterprise Manager or
> ISQLW or anything else for that matter!! Basically they should only
> be able to talk to the Publishing SQL Server through replication
> methods. How is this achieved?
> I'm currently using SQL Security because I don't want an anonymous
> subscriber to impersonate my publishers sqlserver agent account, the
> agent on the publisher is used for other scheduled tasks that needs
> Domain Admin privileges, am I missing something here!!?
> Currently I've got it secure-ish. I've used a specific sql account
> and given it access in the PAL and removed the guest account from
> other DBs, however you cannot remove the guest account from Master or
> Msdb and said account must also be a member of the db_owner role. The
> most they can do is issue select statements against tables but I think
> that is even too much, for instance they can query sysservers which
> would allow them to see other subscribers that we do business with.
> Even if I use a secure VPN between publisher and subscriber what's to
> stop an inquistive subscriber, using the SQL account that I've told
> them to use for their subscription, connecting using EM or ISQLW
> through the VPN? Surely there must be a way to give ONLY replication
> access but nothing else?
> Please help!!
Monday, March 12, 2012
Replication and Different Domains
Hi guys,
We have a client who wants to set up the publisher (for merge
resplication) on a particular domain, but the subscribers will be on a
different domain. Is this even possible?
The main question was about the snapshot folder location. Is there a way
for the subscriber to get access to the folder being on a different domain?
Thanks,
Maer
You could have a look at
http://www.replicationanswers.com/InternetArticle.asp for details of
non-trusted setup. If there is a posibility of setting up a trust (or VPN)
then things are pretty straightforward.
HTH,
Paul Ibison
|||Hi Paul - Thank you so much for the article, it was extremely helpful!
Maer
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:7A661F57-4BB5-4D0C-9936-0A358E71683D@.microsoft.com...
> You could have a look at
> http://www.replicationanswers.com/InternetArticle.asp for details of
> non-trusted setup. If there is a posibility of setting up a trust (or VPN)
> then things are pretty straightforward.
> HTH,
> Paul Ibison
>
We have a client who wants to set up the publisher (for merge
resplication) on a particular domain, but the subscribers will be on a
different domain. Is this even possible?
The main question was about the snapshot folder location. Is there a way
for the subscriber to get access to the folder being on a different domain?
Thanks,
Maer
You could have a look at
http://www.replicationanswers.com/InternetArticle.asp for details of
non-trusted setup. If there is a posibility of setting up a trust (or VPN)
then things are pretty straightforward.
HTH,
Paul Ibison
|||Hi Paul - Thank you so much for the article, it was extremely helpful!
Maer
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:7A661F57-4BB5-4D0C-9936-0A358E71683D@.microsoft.com...
> You could have a look at
> http://www.replicationanswers.com/InternetArticle.asp for details of
> non-trusted setup. If there is a posibility of setting up a trust (or VPN)
> then things are pretty straightforward.
> HTH,
> Paul Ibison
>
Replication and Deleting Rows
Hello,
I have a large table with over 20 million rows.
It is replicated accross a 4 subscribers using Pull subscription. One of the
subscribers is also a Distributor.
We are running some processed to "clean up" the data in this table by
issuing a lot of DELETE commands (using Primary Key as the where clause)
against the large table. The DELETE commands are enclosed in BEGIN TRAN -
COMIT TRAN batches of 20 each.
The deletion works extremely fast on the Publisher.
However, it takes very very long time to apply the very same commands on all
of the subscribers. The subscribers also have the PRIMARY KEY index that is
on the Publisher.
We need to delete a few million rows from the large table using this
technique. However, it makes the Subscribers very slow and unresponsive.
Any suggestions?
Thanks,
ArsenConsider doing this in even smaller batches. If that is not possible, and
transactional consistency is not really of importance, then you could use a
parameter called MaxCmdsInTran for logreader (introduced in SQL Server 2000
SP1 I believe). This breaks huge transactions into smaller chunks of
specified size, while storing the commands in distribution database.
There is another approach, that I would recommend. Consider using 'stored
procedure execution' replication. With this method, you keep a copy of the
archiving stored procedure on all subscribers. When you run this procedure
on the publisher, the distribution agent will call this sp on all
subscribers, instead of sending millions of DELETEs over the network. See
SQL Server Books Online for more information on this.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Arsen V." <arsen@.community.nospam> wrote in message
news:%23hpi4tKoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a large table with over 20 million rows.
> It is replicated accross a 4 subscribers using Pull subscription. One of
the
> subscribers is also a Distributor.
> We are running some processed to "clean up" the data in this table by
> issuing a lot of DELETE commands (using Primary Key as the where clause)
> against the large table. The DELETE commands are enclosed in BEGIN TRAN -
> COMIT TRAN batches of 20 each.
> The deletion works extremely fast on the Publisher.
> However, it takes very very long time to apply the very same commands on
all
> of the subscribers. The subscribers also have the PRIMARY KEY index that
is
> on the Publisher.
> We need to delete a few million rows from the large table using this
> technique. However, it makes the Subscribers very slow and unresponsive.
> Any suggestions?
> Thanks,
> Arsen
>
I have a large table with over 20 million rows.
It is replicated accross a 4 subscribers using Pull subscription. One of the
subscribers is also a Distributor.
We are running some processed to "clean up" the data in this table by
issuing a lot of DELETE commands (using Primary Key as the where clause)
against the large table. The DELETE commands are enclosed in BEGIN TRAN -
COMIT TRAN batches of 20 each.
The deletion works extremely fast on the Publisher.
However, it takes very very long time to apply the very same commands on all
of the subscribers. The subscribers also have the PRIMARY KEY index that is
on the Publisher.
We need to delete a few million rows from the large table using this
technique. However, it makes the Subscribers very slow and unresponsive.
Any suggestions?
Thanks,
ArsenConsider doing this in even smaller batches. If that is not possible, and
transactional consistency is not really of importance, then you could use a
parameter called MaxCmdsInTran for logreader (introduced in SQL Server 2000
SP1 I believe). This breaks huge transactions into smaller chunks of
specified size, while storing the commands in distribution database.
There is another approach, that I would recommend. Consider using 'stored
procedure execution' replication. With this method, you keep a copy of the
archiving stored procedure on all subscribers. When you run this procedure
on the publisher, the distribution agent will call this sp on all
subscribers, instead of sending millions of DELETEs over the network. See
SQL Server Books Online for more information on this.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Arsen V." <arsen@.community.nospam> wrote in message
news:%23hpi4tKoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a large table with over 20 million rows.
> It is replicated accross a 4 subscribers using Pull subscription. One of
the
> subscribers is also a Distributor.
> We are running some processed to "clean up" the data in this table by
> issuing a lot of DELETE commands (using Primary Key as the where clause)
> against the large table. The DELETE commands are enclosed in BEGIN TRAN -
> COMIT TRAN batches of 20 each.
> The deletion works extremely fast on the Publisher.
> However, it takes very very long time to apply the very same commands on
all
> of the subscribers. The subscribers also have the PRIMARY KEY index that
is
> on the Publisher.
> We need to delete a few million rows from the large table using this
> technique. However, it makes the Subscribers very slow and unresponsive.
> Any suggestions?
> Thanks,
> Arsen
>
Replication and Deleting Rows
Hello,
I have a large table with over 20 million rows.
It is replicated accross a 4 subscribers using Pull subscription. One of the
subscribers is also a Distributor.
We are running some processed to "clean up" the data in this table by
issuing a lot of DELETE commands (using Primary Key as the where clause)
against the large table. The DELETE commands are enclosed in BEGIN TRAN -
COMIT TRAN batches of 20 each.
The deletion works extremely fast on the Publisher.
However, it takes very very long time to apply the very same commands on all
of the subscribers. The subscribers also have the PRIMARY KEY index that is
on the Publisher.
We need to delete a few million rows from the large table using this
technique. However, it makes the Subscribers very slow and unresponsive.
Any suggestions?
Thanks,
Arsen
Consider doing this in even smaller batches. If that is not possible, and
transactional consistency is not really of importance, then you could use a
parameter called MaxCmdsInTran for logreader (introduced in SQL Server 2000
SP1 I believe). This breaks huge transactions into smaller chunks of
specified size, while storing the commands in distribution database.
There is another approach, that I would recommend. Consider using 'stored
procedure execution' replication. With this method, you keep a copy of the
archiving stored procedure on all subscribers. When you run this procedure
on the publisher, the distribution agent will call this sp on all
subscribers, instead of sending millions of DELETEs over the network. See
SQL Server Books Online for more information on this.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Arsen V." <arsen@.community.nospam> wrote in message
news:%23hpi4tKoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a large table with over 20 million rows.
> It is replicated accross a 4 subscribers using Pull subscription. One of
the
> subscribers is also a Distributor.
> We are running some processed to "clean up" the data in this table by
> issuing a lot of DELETE commands (using Primary Key as the where clause)
> against the large table. The DELETE commands are enclosed in BEGIN TRAN -
> COMIT TRAN batches of 20 each.
> The deletion works extremely fast on the Publisher.
> However, it takes very very long time to apply the very same commands on
all
> of the subscribers. The subscribers also have the PRIMARY KEY index that
is
> on the Publisher.
> We need to delete a few million rows from the large table using this
> technique. However, it makes the Subscribers very slow and unresponsive.
> Any suggestions?
> Thanks,
> Arsen
>
I have a large table with over 20 million rows.
It is replicated accross a 4 subscribers using Pull subscription. One of the
subscribers is also a Distributor.
We are running some processed to "clean up" the data in this table by
issuing a lot of DELETE commands (using Primary Key as the where clause)
against the large table. The DELETE commands are enclosed in BEGIN TRAN -
COMIT TRAN batches of 20 each.
The deletion works extremely fast on the Publisher.
However, it takes very very long time to apply the very same commands on all
of the subscribers. The subscribers also have the PRIMARY KEY index that is
on the Publisher.
We need to delete a few million rows from the large table using this
technique. However, it makes the Subscribers very slow and unresponsive.
Any suggestions?
Thanks,
Arsen
Consider doing this in even smaller batches. If that is not possible, and
transactional consistency is not really of importance, then you could use a
parameter called MaxCmdsInTran for logreader (introduced in SQL Server 2000
SP1 I believe). This breaks huge transactions into smaller chunks of
specified size, while storing the commands in distribution database.
There is another approach, that I would recommend. Consider using 'stored
procedure execution' replication. With this method, you keep a copy of the
archiving stored procedure on all subscribers. When you run this procedure
on the publisher, the distribution agent will call this sp on all
subscribers, instead of sending millions of DELETEs over the network. See
SQL Server Books Online for more information on this.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Arsen V." <arsen@.community.nospam> wrote in message
news:%23hpi4tKoEHA.2612@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I have a large table with over 20 million rows.
> It is replicated accross a 4 subscribers using Pull subscription. One of
the
> subscribers is also a Distributor.
> We are running some processed to "clean up" the data in this table by
> issuing a lot of DELETE commands (using Primary Key as the where clause)
> against the large table. The DELETE commands are enclosed in BEGIN TRAN -
> COMIT TRAN batches of 20 each.
> The deletion works extremely fast on the Publisher.
> However, it takes very very long time to apply the very same commands on
all
> of the subscribers. The subscribers also have the PRIMARY KEY index that
is
> on the Publisher.
> We need to delete a few million rows from the large table using this
> technique. However, it makes the Subscribers very slow and unresponsive.
> Any suggestions?
> Thanks,
> Arsen
>
replication and datetime columns
I have an interesting server occuring. We have transactional replication running with 1 pub and 6 subscribers. On nearly all the tables we have some sort of date column that's a smalldatetime. If I look at the pub the data will appear like:
2006-11-13 09:59:00
However, on all the subs it appears like:
2006-11-13 09:59:00.000
It's the same column and record but for some reason it's become a datetime. I checked the column types and they are both smalldatetime's.
Why is this happening? What can be done?
Thanks,
Phil
I am unable to repro this on my SQL 2000 and SQL 2005 servers. What version of SQL Server are you running and what SPs?|||
Actually I found the problem. It looks as if when you use the four part name server.db.dbo.tablename from the publisher that the column "appears" to be a datetime. When I actually go to the server and select it.. then it's the normal smalldatetime. I don't know if this has something to do w/ ODBC connection accross linked servers or what...
Thanks,
Phil
Replication Advice
Hi all,
I am in need of some replication advice...
We have the following scenario:
Publisher running SQL Server 2005 with SP1
Multiple subscribers (at the minute, 10, but there will eventually be
around 30), running a mixture of SQL Server 2005 Express and SQL
Server 2005.
Now, I am fairly inexperienced with replication via the web service
(IIS), and have started looking at this particular project at work
using replication, and noticed something that does not seem right.
Whenever any schema changes are committed, we are forced to regenerate
the complete snapshot, and as such, the subscribers are forced to
download and re-initialise their snapshot.
Surely this is incorrect? Is there any way of configuring incremental
snapshot changes?
The big problem we have is that there database is around 200Mb, and we
have users who pay per MB...
Sorry for lack of information, as I say, I'm fairly new to Server to
Client replication - I normally only configure Server to Server!
Regards,
Andy Neillans
I too would be interested in this reply because my databasr is 1.6gb and to
generate a snapshot every time a schema changes is a real pain in the
butt....
Is it possible to create a snapshot on the publisher and then 'part' copy
over the new schema and tables to the subscriber (in my case).
Thanks
"Andrew Neillans" wrote:
> Hi all,
> I am in need of some replication advice...
> We have the following scenario:
> Publisher running SQL Server 2005 with SP1
> Multiple subscribers (at the minute, 10, but there will eventually be
> around 30), running a mixture of SQL Server 2005 Express and SQL
> Server 2005.
> Now, I am fairly inexperienced with replication via the web service
> (IIS), and have started looking at this particular project at work
> using replication, and noticed something that does not seem right.
> Whenever any schema changes are committed, we are forced to regenerate
> the complete snapshot, and as such, the subscribers are forced to
> download and re-initialise their snapshot.
> Surely this is incorrect? Is there any way of configuring incremental
> snapshot changes?
> The big problem we have is that there database is around 200Mb, and we
> have users who pay per MB...
> Sorry for lack of information, as I say, I'm fairly new to Server to
> Client replication - I normally only configure Server to Server!
> Regards,
> Andy Neillans
>
I am in need of some replication advice...
We have the following scenario:
Publisher running SQL Server 2005 with SP1
Multiple subscribers (at the minute, 10, but there will eventually be
around 30), running a mixture of SQL Server 2005 Express and SQL
Server 2005.
Now, I am fairly inexperienced with replication via the web service
(IIS), and have started looking at this particular project at work
using replication, and noticed something that does not seem right.
Whenever any schema changes are committed, we are forced to regenerate
the complete snapshot, and as such, the subscribers are forced to
download and re-initialise their snapshot.
Surely this is incorrect? Is there any way of configuring incremental
snapshot changes?
The big problem we have is that there database is around 200Mb, and we
have users who pay per MB...
Sorry for lack of information, as I say, I'm fairly new to Server to
Client replication - I normally only configure Server to Server!
Regards,
Andy Neillans
I too would be interested in this reply because my databasr is 1.6gb and to
generate a snapshot every time a schema changes is a real pain in the
butt....
Is it possible to create a snapshot on the publisher and then 'part' copy
over the new schema and tables to the subscriber (in my case).
Thanks
"Andrew Neillans" wrote:
> Hi all,
> I am in need of some replication advice...
> We have the following scenario:
> Publisher running SQL Server 2005 with SP1
> Multiple subscribers (at the minute, 10, but there will eventually be
> around 30), running a mixture of SQL Server 2005 Express and SQL
> Server 2005.
> Now, I am fairly inexperienced with replication via the web service
> (IIS), and have started looking at this particular project at work
> using replication, and noticed something that does not seem right.
> Whenever any schema changes are committed, we are forced to regenerate
> the complete snapshot, and as such, the subscribers are forced to
> download and re-initialise their snapshot.
> Surely this is incorrect? Is there any way of configuring incremental
> snapshot changes?
> The big problem we have is that there database is around 200Mb, and we
> have users who pay per MB...
> Sorry for lack of information, as I say, I'm fairly new to Server to
> Client replication - I normally only configure Server to Server!
> Regards,
> Andy Neillans
>
Friday, March 9, 2012
Replication - Row not correctly sync
Hi Everyone!
I have a strange issue in my replicated databases.
I've a 5 merge subscribers replication schema. Our client discover a row that isn't matching with another database.
After some research it seam that the correct row is only at one database and all other database, have a the same row in a incoherent status.
Anyone have a idea on how this could happen?
I'm clue less right now
Thanks!
To fix the issue I decide to bring a change to the correct row to let it propagate to every other subcriber. But event more strange, not all collumn are updated to other subscriber!!!
This is really really strange
Wednesday, March 7, 2012
Replication - changing a server
This is very important.
I have merge replication with publisher and distributor on the same server and about 20 Subscribers all over the country.
We are moving to another building, with new equipment, so Publisher will be on different server. How to make this move easily, without recreating new publication and pushing new subscribers and implementing initial snapshot, because it will destroy my network.
Is there any possibility to do something like attach/detach DB (distibution and source DB), do few steps more, and to have all like it was before.
Must be.
Thanks in advance.
If you are going to change the publisher server name, you will need to remove replication from the old server and reconfigure it on the new server.
There is another way which you can try:
1) Stop SQL Server on your publisher machine (Machine 1)
2) Rename the machine to something else
3) Copy all the database data files and log files from the old publisher machine to a second machine (Machine 2)
4) Install your new machine (Machine 3), give the old publisher server's name to this new machine
5) Install SQL Server on Machine 3 (which has the old publisher server's name). Make sure the installation directory is the same as the old machine. Bring SQL Server to the same service pack level.
6) Ensure any NT users and logins, AD settings on the new machine is the same as the old machine.
7) Stop SQL Server on Machine 3. Copy over the database data and log files from Machine 2.
8) Restart SQL Server on Machine 3.
Now you should be ready to sync the subscriptions.
replication
Hello,
I'm a developer and I've inherited a sql environment that has 1 publisher,
a distributor, and 12 subscribers. Several articles are published using
transactional replication. I need to make a constraint change on a table
that is published. Do I need to drop the publication and recreate it? What
scenario requires dropping of publication?
thanks
Unfortunately in SQL 2000 you need to. In sql 2005 you don't. Make sure you
script out the publications and subscriptions before making your change.
With luck you can do a no-sync subscription.
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
"Troy" <troy@.pushit.com> wrote in message
news:uarP51FHGHA.1088@.tk2msftngp13.phx.gbl...
> Hello,
> I'm a developer and I've inherited a sql environment that has 1
> publisher, a distributor, and 12 subscribers. Several articles are
> published using transactional replication. I need to make a constraint
> change on a table that is published. Do I need to drop the publication and
> recreate it? What scenario requires dropping of publication?
> thanks
>
>
|||Troy,
you don't necessarily need to drop the publication - it is possible to drop
the articles and keep the rest of the publication going:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
constraint change
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Start the snapshot then distribution agents.
Note that the validity of this method depends on the relationships between
these tables and the other articles, and if you can prevent changes to the
system while these changes are made.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I'm a developer and I've inherited a sql environment that has 1 publisher,
a distributor, and 12 subscribers. Several articles are published using
transactional replication. I need to make a constraint change on a table
that is published. Do I need to drop the publication and recreate it? What
scenario requires dropping of publication?
thanks
Unfortunately in SQL 2000 you need to. In sql 2005 you don't. Make sure you
script out the publications and subscriptions before making your change.
With luck you can do a no-sync subscription.
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
"Troy" <troy@.pushit.com> wrote in message
news:uarP51FHGHA.1088@.tk2msftngp13.phx.gbl...
> Hello,
> I'm a developer and I've inherited a sql environment that has 1
> publisher, a distributor, and 12 subscribers. Several articles are
> published using transactional replication. I need to make a constraint
> change on a table that is published. Do I need to drop the publication and
> recreate it? What scenario requires dropping of publication?
> thanks
>
>
|||Troy,
you don't necessarily need to drop the publication - it is possible to drop
the articles and keep the rest of the publication going:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
constraint change
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Start the snapshot then distribution agents.
Note that the validity of this method depends on the relationships between
these tables and the other articles, and if you can prevent changes to the
system while these changes are made.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Saturday, February 25, 2012
replicating UDT's to subscribers
I have a sql 2005 publisher and distributor and a sql 2000 subscribers. for some reason on one of the subscribers i'm getting errors that it can't replicate the UDT's. i tried a new snapshot and made sure it was set not to replicate UDT's but i'm still getting Create Type errors.
would anyone have any idea why it's trying to create UDT's at the subscriber when i specify not to replicate UDT's?
Are you trying to exclude the UDT column, so it doesn't replicate to subscriber? How did you specify, so it does not replicate UDT? Did you add a vertical filter? Or?
Gary
|||in the article properties i left it on false for replicate user defined data types. it's UDT on the publisher and the base types on the subscribers.|||
I am not sure if this will solve your problem but you can try re-initializing your subscription before regenerating a new snapshot.
-Raymond
|||how many dba's does it take to change a light bulb?stupid mistake. need to change it to true to convert data types like on all our other publications
Subscribe to:
Posts (Atom)