Replication Trigger Problem (SQL 2005): Assistance Please

Our company is wanting to replicate a bunch of data down from our central server to tablet PC's. We really do not have anyone on staff with solid experience in replication, and have hit an error that we cannot seem to find any information whatsoever on.

Here is the problem:

When setting up a publication to replicate certain tables, we stop being able to do any updates on the tables. After some research, we were able to narrow the problem into the msrepl triggers the publication places on the table. In the update trigger of the table, the following line is being placed by SQL Server:

execute sp_mapdown_bitmap 0x0000000040, @.bm output

The sp_mapdown_bitmap call, passes through to a xp_mapdown_bitmap call (limited documentation I have found is it is suppose to be an extended stored procedure), which if I try to manually execute, does not exist on the server (Logged in as SA the entire time).

Commenting out that line in the replication stored procedures corrects the problem. However obviously we do not want to manually do that everytime we create a publication, and we have no idea what negative impacts may occur because of it.

We are getting this behavior on several SQL Server 2005's...

Additional notes on the environment:

-CLR Triggers written in VB, on most tables performing application functions

-SQL Server 2005 SP1 is on the servers, and SQL Server 2005 Express Edition is on the tablets.

-Again this problem only exists on some tables in the database.

Any assistance would be greatly appreciated.

Can you give us some more detail about the problem? When you say you cannot do any updates, do you mean the updates are taking a long time to return, or do you suspect the SPID is hung? How many rows are in the table?

|||

No the updates completely fail with error message, because the Update triggers for the replication have the line:

execute sp_mapdown_bitmap 0x0000000040, @.bm output

...placed in them, that makes a call to xp_mapdown_bitmap, which as far as I can tell does not exist on the server (If I try and execute xp_mapdown_bitmap from a query tool, I get the does not exist error).

This problem existed before SP1 and after. We have completely rebuilt the publication, made the publication only do one of the problem tables, nothing we can do seems to correct the error, except commenting the line out of the Triggers.

|||Without knowing the error, it's hard to pinpoint the problem. Are you getting an error message?|||

xp_mapdown_bitmap is not public, that is why you will get the does not exist error. Please give the specific error message when update fails so we can investigate further.

Thanks

Wanwen

|||

Ok, after some further investigation the problem seems to be occuring when one CLR Trigger, executes an update on anothre table that has the replication trigger on it. Commenting out the line sp_mapdown_bitmap from the replication trigger fixes the problem (on the table being updated). The error we get from inside the CLR trigger on the UPDATE command is the following:

Msg 6549, Level 16, State 1, Procedure AR_Invoice_InvoiceNumber, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'AR_Invoice_InvoiceNumber':
System.Exception: <Message>The user transaction that has been started in user defined routine, trigger or aggregate "AR_Invoice_Audit" is not ended upon exiting from it. This is not allowed and the transaction will be rolled back. Change application logic to enforce strict transaction nesting.</Message> > System.Data.SqlClient.SqlException: The user transaction that has been started in user defined routine, trigger or aggregate "AR_Invoice_Audit" is not ended upon exiting from it. This is not allowed and the transaction will be rolled back. Change application logic to enforce strict transaction nesting.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at SQLHVMS.Administration.Common.SetNumberColumn(SqlString TableName, SqlString ColumnName, SqlConnection Connection, String& Number)
System.Exception:
at SQLHVMS.Administration.Common.SetNumberColumn(SqlString TableName, SqlString ColumnName, SqlConnection Connection, String& Number)
at SQLHVMS.Receivables.Triggers.AR_Invoice_InvoiceNumber()
. User transaction, if any, will be rolled back.
The statement has been terminated.

--

Initially we had no transaction logic in the CLR trigger, since then we have tried placing a transaction around the contents of our CLR trigger (using sqlconnection.begintran, commit, etc) that seemed to have no effect.

Again what confuses me is commenting out that mapdown call in the replication update trigger immediately fixes the problem. So is there some issue with executing that statement when a CLR trigger is the source of the call of the update?

Jason

|||

Hi Jason,

This is actually a known issue with respect to the execution of extended procedure within SQLCLR. Unfortunately, the fix hasn't been fully approved for SP2 yet so I will attach your posting to the bug which will hopefully expedite the approval process. But since there really is no good workaround for your scenario (triggers are always executed in transaction context), you may want to push for a QFE by logging a bug @. http://lab.msdn.microsoft.com/productfeedback/, our internal reference number for this bug is SQLBU450991.

Thank you for reporting the issue.

-Raymond

|||

Thanks for the response. Can you please enlighten me on what the possible negative side effects of just commenting out the sp_mapdown_bitmap would be in one of the Replication triggers?

I sort of need a workaround for the mean time if possible...

|||

Hi Jason,

Since I am no merge replication expert, I can only imagine that commenting the call out will break merge replication. Ok, I should probably let the merge experts chime in on that. I do have this rather far-fetched idea of committing\starting transactions around the sp_mapdown_bitmap call in your trigger in an attempt to trick the server into thinking that the transaction balances out. How about making most of your trigger T-SQL and call CLR procs to do the things that you absolutely have to do in CLR?

-Raymond

|||

Jason,

sp_mapdown_bitmap is necessary to adjust the bitmap for updated columns when column id in the table is not continuous, e.g. there has been columns dropped from the table. Commenting out sp_mapdown_bitmap could lead to data non-convergence. It is not a valid workaround.

Will workaround mentioned by Raymond work for you?

Wanwen

|||

The transaction idea didnt seem to have any effect. And switching away from CLR would be a multi-week resolution to the problem, which would be hard to swallow...

If we are 100% sure there will be no added/removed columns, as a temporary workaround is commenting out that line not an option?

|||

The fact your update trigger is calling sp_mapdown_bitmap shows there is a need to do the map down adjustment. It could be because there has been dropped column, or table contains computed or timestamp column. You could run a simple query to find out:

select column_id from sys.columns where object_id = OBJECT_ID(<your_source_table>)

to see if column_id is continous from 1 to max column id

select * from sys.columns where object_id = OBJECT_ID(<your_source_table>) and (is_computed = 1 or system_type_id = type_id('timestamp'))

to see if you have computed column or timestamp column.

As I said before, commenting out calling sp_mapdown_bitmap in this case will lead to data non-convergence. We already brought up the issue and hopefully the bug could be fixed in SP2

|||

After running the queries, a "hole" did show up in the column id's in one of the tables causing the problem (there is no computed or timestamp columns). Is there anyway to close the gap or renumber the columns?

As much as I want to wait for SP2, we all know it could be quite awhile and we were really hoping to go live with the solution shortly...

|||

Jason,

You could

1.drop that article from your publication

2.run snapshot and merge agent if necessary

3.drop the physical tables from publisher and subscriber

4.recreate the table at publisher, make sure there are no holes in column id this time

5.add that article back to your publication

6.rerun snapshot and merge agent.

Or you could clean up your environment and start from beginning if that is more convenient for you.

Please let me know if you have any further questions.

Wanwen

|||Dear Raymond,

Please also see my post
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=865299&SiteID=1&mode=1
since I am also running into a problem with CLR trigger nesting which seems related to the bug. Thanks in advance.

Replication Trigger Problem (SQL 2005): Assistance Please

Our company is wanting to replicate a bunch of data down from our central server to tablet PC's. We really do not have anyone on staff with solid experience in replication, and have hit an error that we cannot seem to find any information whatsoever on.

Here is the problem:

When setting up a publication to replicate certain tables, we stop being able to do any updates on the tables. After some research, we were able to narrow the problem into the msrepl triggers the publication places on the table. In the update trigger of the table, the following line is being placed by SQL Server:

execute sp_mapdown_bitmap 0x0000000040, @.bm output

The sp_mapdown_bitmap call, passes through to a xp_mapdown_bitmap call (limited documentation I have found is it is suppose to be an extended stored procedure), which if I try to manually execute, does not exist on the server (Logged in as SA the entire time).

Commenting out that line in the replication stored procedures corrects the problem. However obviously we do not want to manually do that everytime we create a publication, and we have no idea what negative impacts may occur because of it.

We are getting this behavior on several SQL Server 2005's...

Additional notes on the environment:

-CLR Triggers written in VB, on most tables performing application functions

-SQL Server 2005 SP1 is on the servers, and SQL Server 2005 Express Edition is on the tablets.

-Again this problem only exists on some tables in the database.

Any assistance would be greatly appreciated.

Can you give us some more detail about the problem? When you say you cannot do any updates, do you mean the updates are taking a long time to return, or do you suspect the SPID is hung? How many rows are in the table?

|||

No the updates completely fail with error message, because the Update triggers for the replication have the line:

execute sp_mapdown_bitmap 0x0000000040, @.bm output

...placed in them, that makes a call to xp_mapdown_bitmap, which as far as I can tell does not exist on the server (If I try and execute xp_mapdown_bitmap from a query tool, I get the does not exist error).

This problem existed before SP1 and after. We have completely rebuilt the publication, made the publication only do one of the problem tables, nothing we can do seems to correct the error, except commenting the line out of the Triggers.

|||Without knowing the error, it's hard to pinpoint the problem. Are you getting an error message?|||

xp_mapdown_bitmap is not public, that is why you will get the does not exist error. Please give the specific error message when update fails so we can investigate further.

Thanks

Wanwen

|||

Ok, after some further investigation the problem seems to be occuring when one CLR Trigger, executes an update on anothre table that has the replication trigger on it. Commenting out the line sp_mapdown_bitmap from the replication trigger fixes the problem (on the table being updated). The error we get from inside the CLR trigger on the UPDATE command is the following:

Msg 6549, Level 16, State 1, Procedure AR_Invoice_InvoiceNumber, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'AR_Invoice_InvoiceNumber':
System.Exception: <Message>The user transaction that has been started in user defined routine, trigger or aggregate "AR_Invoice_Audit" is not ended upon exiting from it. This is not allowed and the transaction will be rolled back. Change application logic to enforce strict transaction nesting.</Message> > System.Data.SqlClient.SqlException: The user transaction that has been started in user defined routine, trigger or aggregate "AR_Invoice_Audit" is not ended upon exiting from it. This is not allowed and the transaction will be rolled back. Change application logic to enforce strict transaction nesting.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at SQLHVMS.Administration.Common.SetNumberColumn(SqlString TableName, SqlString ColumnName, SqlConnection Connection, String& Number)
System.Exception:
at SQLHVMS.Administration.Common.SetNumberColumn(SqlString TableName, SqlString ColumnName, SqlConnection Connection, String& Number)
at SQLHVMS.Receivables.Triggers.AR_Invoice_InvoiceNumber()
. User transaction, if any, will be rolled back.
The statement has been terminated.

--

Initially we had no transaction logic in the CLR trigger, since then we have tried placing a transaction around the contents of our CLR trigger (using sqlconnection.begintran, commit, etc) that seemed to have no effect.

Again what confuses me is commenting out that mapdown call in the replication update trigger immediately fixes the problem. So is there some issue with executing that statement when a CLR trigger is the source of the call of the update?

Jason

|||

Hi Jason,

This is actually a known issue with respect to the execution of extended procedure within SQLCLR. Unfortunately, the fix hasn't been fully approved for SP2 yet so I will attach your posting to the bug which will hopefully expedite the approval process. But since there really is no good workaround for your scenario (triggers are always executed in transaction context), you may want to push for a QFE by logging a bug @. http://lab.msdn.microsoft.com/productfeedback/, our internal reference number for this bug is SQLBU450991.

Thank you for reporting the issue.

-Raymond

|||

Thanks for the response. Can you please enlighten me on what the possible negative side effects of just commenting out the sp_mapdown_bitmap would be in one of the Replication triggers?

I sort of need a workaround for the mean time if possible...

|||

Hi Jason,

Since I am no merge replication expert, I can only imagine that commenting the call out will break merge replication. Ok, I should probably let the merge experts chime in on that. I do have this rather far-fetched idea of committing\starting transactions around the sp_mapdown_bitmap call in your trigger in an attempt to trick the server into thinking that the transaction balances out. How about making most of your trigger T-SQL and call CLR procs to do the things that you absolutely have to do in CLR?

-Raymond

|||

Jason,

sp_mapdown_bitmap is necessary to adjust the bitmap for updated columns when column id in the table is not continuous, e.g. there has been columns dropped from the table. Commenting out sp_mapdown_bitmap could lead to data non-convergence. It is not a valid workaround.

Will workaround mentioned by Raymond work for you?

Wanwen

|||

The transaction idea didnt seem to have any effect. And switching away from CLR would be a multi-week resolution to the problem, which would be hard to swallow...

If we are 100% sure there will be no added/removed columns, as a temporary workaround is commenting out that line not an option?

|||

The fact your update trigger is calling sp_mapdown_bitmap shows there is a need to do the map down adjustment. It could be because there has been dropped column, or table contains computed or timestamp column. You could run a simple query to find out:

select column_id from sys.columns where object_id = OBJECT_ID(<your_source_table>)

to see if column_id is continous from 1 to max column id

select * from sys.columns where object_id = OBJECT_ID(<your_source_table>) and (is_computed = 1 or system_type_id = type_id('timestamp'))

to see if you have computed column or timestamp column.

As I said before, commenting out calling sp_mapdown_bitmap in this case will lead to data non-convergence. We already brought up the issue and hopefully the bug could be fixed in SP2

|||

After running the queries, a "hole" did show up in the column id's in one of the tables causing the problem (there is no computed or timestamp columns). Is there anyway to close the gap or renumber the columns?

As much as I want to wait for SP2, we all know it could be quite awhile and we were really hoping to go live with the solution shortly...

|||

Jason,

You could

1.drop that article from your publication

2.run snapshot and merge agent if necessary

3.drop the physical tables from publisher and subscriber

4.recreate the table at publisher, make sure there are no holes in column id this time

5.add that article back to your publication

6.rerun snapshot and merge agent.

Or you could clean up your environment and start from beginning if that is more convenient for you.

Please let me know if you have any further questions.

Wanwen

|||Dear Raymond,

Please also see my post
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=865299&SiteID=1&mode=1
since I am also running into a problem with CLR trigger nesting which seems related to the bug. Thanks in advance.

replication to sql server by activeX

Hello
i have a an application developed in vb6.0 and back end sql server 2000.
i have a batch file that i created to replicate the data from the sql server
2000 on my pc(local) to replicate on another sql server 2000 which is found
on another machine and the batch file is working fine.
i would like to implement it in my VB application.That is the user will
have a button on the program and on click this will do the replication that
is being done by the batch file for the moment.
i can call the batch file but we would like to do it by activeX.
Is there any activeX that already exist?
how can i do this by using activeX in VB6.0
If you need additional info please let me know..
Thx a lot
Nazeedah
try this:
http://www.indexserverfaq.com/replic...excontrols.zip
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
"Nazeedah" <Nazeedah@.discussions.microsoft.com> wrote in message
news:53D7A4A9-EBEB-4F76-AF9B-5EF670EBC707@.microsoft.com...
> Hello
> i have a an application developed in vb6.0 and back end sql server 2000.
> i have a batch file that i created to replicate the data from the sql
> server
> 2000 on my pc(local) to replicate on another sql server 2000 which is
> found
> on another machine and the batch file is working fine.
> i would like to implement it in my VB application.That is the user will
> have a button on the program and on click this will do the replication
> that
> is being done by the batch file for the moment.
> i can call the batch file but we would like to do it by activeX.
> Is there any activeX that already exist?
> how can i do this by using activeX in VB6.0
> If you need additional info please let me know..
> Thx a lot
> Nazeedah
>
|||hello
Thank you for the link. its very interesting but i am bit lost in all the
code. could you please tell me which one is more specific in my case
Thank you a lot
"Nazeedah" wrote:

> Hello
> i have a an application developed in vb6.0 and back end sql server 2000.
> i have a batch file that i created to replicate the data from the sql server
> 2000 on my pc(local) to replicate on another sql server 2000 which is found
> on another machine and the batch file is working fine.
> i would like to implement it in my VB application.That is the user will
> have a button on the program and on click this will do the replication that
> is being done by the batch file for the moment.
> i can call the batch file but we would like to do it by activeX.
> Is there any activeX that already exist?
> how can i do this by using activeX in VB6.0
> If you need additional info please let me know..
> Thx a lot
> Nazeedah
>
|||its code sample 5
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
"Nazeedah" <Nazeedah@.discussions.microsoft.com> wrote in message
news:BEC8AF2F-7EEB-4AF4-B269-7BDF5917BEA7@.microsoft.com...[vbcol=seagreen]
> hello
> Thank you for the link. its very interesting but i am bit lost in all the
> code. could you please tell me which one is more specific in my case
> Thank you a lot
> "Nazeedah" wrote:
sql

Replication to DMZ...something's missing....HELP!!

I've followed the steps in an attempt to replicate to & from the DMZ sql server.
It's a one-way trust.
Replication, Trans. Repl w/ Immediate Update is setup
My domain sql server can replicate to the dmz, but when I try to change something at the subscriber it tells me
"SQL Server not found or access denied"
I have attempted everything from the article on Replication Answers.
Server is in hosts file
I setup an alias in Client Network utilities using the IP Address that I input in the hosts file
What else can I do to replicate back from the DMZ? What am I missing?
JLS,
is the client alias the same name as the servername?
Can you try establishing client connectivity using query analyser on the subscriber to the sql server?
Can you ping the publishing sql server from the subscriber?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanx for answering. It seems the problem was with the firewall port & that has been fixed, but now I get a Login failed for user SA.
I am not using SA for this publication / subscription. I am using Sql Authentication & have setup a new account on each box called dmzrepl.
I also tried to do a linked server query & receive the exact same message about sa login failing.
What in the world am I missing?
jls
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:e5tvXzH7FHA.1864@.TK2MSFTNGP12.phx.gbl...
JLS,
is the client alias the same name as the servername?
Can you try establishing client connectivity using query analyser on the subscriber to the sql server?
Can you ping the publishing sql server from the subscriber?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||JLS,
I think you're almost there. Have a look at this article for the next stage: http://support.microsoft.com/default...b;en-us;320773
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||YIKES! I was afraid you were going to point me in that direction....
I found this in my searching & tried it, to no avail. I am still receiving sa login failed even after following the workaround.
Any other ideas?
jls
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:eAA0tpI7FHA.3136@.TK2MSFTNGP09.phx.gbl...
JLS,
I think you're almost there. Have a look at this article for the next stage: http://support.microsoft.com/default...b;en-us;320773
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I don't believe this will work. MSDTC needs RPC ports open for inbound
traffic on both sides of your DMZ.
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
"JLS" <jlshoop@.hotmail.com> wrote in message
news:ue1ctaF7FHA.3880@.TK2MSFTNGP12.phx.gbl...
I've followed the steps in an attempt to replicate to & from the DMZ sql
server.
It's a one-way trust.
Replication, Trans. Repl w/ Immediate Update is setup
My domain sql server can replicate to the dmz, but when I try to change
something at the subscriber it tells me
"SQL Server not found or access denied"
I have attempted everything from the article on Replication Answers.
Server is in hosts file
I setup an alias in Client Network utilities using the IP Address that I
input in the hosts file
What else can I do to replicate back from the DMZ? What am I missing?
|||JLS,
please see Hilary's post. Looks like you need to open up RPC over TCP\IP and add another rule to the firewall. These links will hopefully help:
http://support.microsoft.com/default.aspx?kbid=841251
http://www.windowsitpro.com/Article/...412/13412.html
Paul Ibison
|||Paul / Hilary,
Thanx for the answers, I appreciate it. With the answer being to open RPC ports, I think we will rethink this project, as that will open a security hole we don't want open.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:uhvxlPM7FHA.444@.TK2MSFTNGP11.phx.gbl...
I don't believe this will work. MSDTC needs RPC ports open for inbound
traffic on both sides of your DMZ.
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
"JLS" <jlshoop@.hotmail.com> wrote in message
news:ue1ctaF7FHA.3880@.TK2MSFTNGP12.phx.gbl...
I've followed the steps in an attempt to replicate to & from the DMZ sql
server.
It's a one-way trust.
Replication, Trans. Repl w/ Immediate Update is setup
My domain sql server can replicate to the dmz, but when I try to change
something at the subscriber it tells me
"SQL Server not found or access denied"
I have attempted everything from the article on Replication Answers.
Server is in hosts file
I setup an alias in Client Network utilities using the IP Address that I
input in the hosts file
What else can I do to replicate back from the DMZ? What am I missing?

Replication target table shrinks to zero

I want to replicate a database to a subscriber that will be used as a read
only copy. The data has to be replicated as close to instantly as possible.
To do this I set up a database export of objects and data to populate the
subscriber, then I set up transactional replication. To verify that
replication is working successfully, I count the rows in each table, there
are 3 tables in total. For one of the tables, the replication completes but
almost immediately afterward, the table starts to shrink, and after several
hours the record count is zero. This isn't happening to the other two
tables, and I can't figure out why.

If you have no idea what might be causing this, perhaps you can suggest
some places to start looking. This is Win2k SP4 with SQL 2000 SP3.

Thanks much."someguy" <nospam@.thanks.com> wrote in message
news:Xns945451BFDB728nospamthankscom@.207.35.177.13 5...
> I want to replicate a database to a subscriber that will be used as a read
> only copy. The data has to be replicated as close to instantly as
possible.
> To do this I set up a database export of objects and data to populate the
> subscriber, then I set up transactional replication. To verify that
> replication is working successfully, I count the rows in each table, there
> are 3 tables in total. For one of the tables, the replication completes
but
> almost immediately afterward, the table starts to shrink, and after
several
> hours the record count is zero. This isn't happening to the other two
> tables, and I can't figure out why.

Sounds like there's a trigger or another user operating on that table.

Replication itself should not cause a table to shrink (unless of course the
original source table is shrinking.)

> If you have no idea what might be causing this, perhaps you can suggest
> some places to start looking. This is Win2k SP4 with SQL 2000 SP3.
> Thanks much.

replication tables with forign key

Hi all

i have one central site and 7 remote sites, there are one mssqlserver 2k in each sites.
i have to replicate 4 table in one DB(my DB have about 20 tables) in my 8 sites.
this 4 table have forignkey between themselves and other15 tables
i planed to transactional replication but i cant becuse forign keys occure erros
if needed i can send my DB digram to you.

can anyone help me? :confused:

Thanx
M.J.Daneshhttp://www.microsoft.com/technet/prodtechnol/sql/2000/books/c09ppcsq.mspx for reference on configuring Merge/Transactional/snapshot replication types.

Replication Table Permissions

Is there a way to replicate the table permissions from publisher to
subscriber? I noticed that when replication takes place, the permissions
that were set up on tables on the subscriber are wiped out. I need the permissions to be send to the subscriber automatically.I believe that is not part of the snapshot. However, you can script it and make it the post snapshot script.|||Can yoy give me the script or recommend a side with the script because that exactly what i am looking for and can't figure it out as is not discussed on microsoft help|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replmon_9pwh.asp for reference.|||Satya, i don't see anything to do with permissions on the website you send me|||As per the BOL :
How to script replication (Enterprise Manager)
At the Publisher, open SQL Server Enterprise Manager, expand a server group, right-click the Replication folder, and then click Generate SQL Script.

Select the replication component to script (Distributor properties, publications and push subscriptions, or pull subscriptions) and whether you want the script to enable or create the components or disable or drop the components. .

HTH|||I am sorry about this Satya, but i can see only the logins granted for my publicatiom on my Publisher/Distributor, my question is how do you take those permissions automatically to the subsciber?

replication sqlserver2000 to sqlserver2005

Hi,
I want to replicate all Updates, Inserts and Deletes (possibly indexed
view changes) to a whole database from sql server2000 (live server) to
sql server2005 (reporting server), but I only want to do it every
night to at an interval that I choose. (Note I don't want to replicate
the whole database each time, just initially).
Is it easy to use replication between sql Server 2000
(publisher/distributor) and sql server 2005 (Subscriber)?
I want to use Merge replication, mainly because I can set it to only be
scheduled once a night (as opposed to Transactional where changes would
be sent for every complete transaction)... not interested in merging
any data, just sending all the changes across.
Are there any issues with this idea?
If this is the right way to do it? Or should I be using Data
Transformation Services?
If I am to use Data Transformation services, is there a way to
schedule/automate this task on both sides?
- The Bermused<tech101@.gmail.com> wrote in message
news:1149812382.243609.307830@.i40g2000cwc.googlegroups.com...
> Hi,
> I want to replicate all Updates, Inserts and Deletes (possibly indexed
> view changes) to a whole database from sql server2000 (live server) to
> sql server2005 (reporting server), but I only want to do it every
> night to at an interval that I choose. (Note I don't want to replicate
> the whole database each time, just initially).
> Is it easy to use replication between sql Server 2000
> (publisher/distributor) and sql server 2005 (Subscriber)?
I don't think you'll have issues here.
I wouldn't use Merge. I'd use transactional, but set it up to run
non-continously.
Another option btw, is to log-ship. This may ni fact work even better in
this case if you're not doing reports at night.

> I want to use Merge replication, mainly because I can set it to only be
> scheduled once a night (as opposed to Transactional where changes would
> be sent for every complete transaction)... not interested in merging
> any data, just sending all the changes across.
> Are there any issues with this idea?
> If this is the right way to do it? Or should I be using Data
> Transformation Services?
> If I am to use Data Transformation services, is there a way to
> schedule/automate this task on both sides?
> - The Bermused
>|||Thanks Greg.
I think that the transacitonal solution is a good way to go. With the
distributor being on the same box as the publisher initially, and when
we have more resources then move the distributor to a box in the same
network/domain as the publisher.
Have tried setting this replication up, but run into authentication
problems.
I think the problem is that the Publishing/Distributor sqlserver2000
(Prod/Live) machine is in a different domain to the subscribing
sqlserver2005 (Reporting) machine. Also there is no trust set up
between the domains, so an AD account will not work.
I have heard that impersonation may work in that:
* an agent that SQL Server2005(Subscriber) uses (one of many found in
Services) can run under a specified system account
* I supply the credentials of this account during the (transactional)
replication wizard on the SQL Server2000(Publisher)
This may work but, I am not sure which agent/service to apply the
account to - and I don't want to break things!
By default, services run under a local system account... not sure which
account is default though...
I can set up an SqlServer service to run under a particular account,
however, as mentioned, I am not sure which service to use.
Does this style of solution sound like it is the right way to go?
Also, would log shipping be a less technical/problematic solution for
this? Would it consume less resources on the live machine? And would
it be an incremental type solution that would not clog up the network?
Cheers,
Adam
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> <tech101@.gmail.com> wrote in message
> news:1149812382.243609.307830@.i40g2000cwc.googlegroups.com...
> I don't think you'll have issues here.
> I wouldn't use Merge. I'd use transactional, but set it up to run
> non-continously.
> Another option btw, is to log-ship. This may ni fact work even better in
> this case if you're not doing reports at night.
>|||Thanks Greg.
I think that the transacitonal solution is a good way to go. With the
distributor being on the same box as the publisher initially, and when
we have more resources then move the distributor to a box in the same
network/domain as the publisher.
Have tried setting this replication up, but run into authentication
problems.
I think the problem is that the Publishing/Distributor sqlserver2000
(Prod/Live) machine is in a different domain to the subscribing
sqlserver2005 (Reporting) machine. Also there is no trust set up
between the domains, so an AD account will not work.
I have heard that impersonation may work in that:
* an agent that SQL Server2005(Subscriber) uses (one of many found in
Services) can run under a specified system account
* I supply the credentials of this account during the (transactional)
replication wizard on the SQL Server2000(Publisher)
This may work but, I am not sure which agent/service to apply the
account to - and I don't want to break things!
By default, services run under a local system account... not sure which
account is default though...
I can set up an SqlServer service to run under a particular account,
however, as mentioned, I am not sure which service to use.
Does this style of solution sound like it is the right way to go?
Also, would log shipping be a less technical/problematic solution for
this? Would it consume less resources on the live machine? And would
it be an incremental type solution that would not clog up the network?
Cheers,
Adam
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> <tech101@.gmail.com> wrote in message
> news:1149812382.243609.307830@.i40g2000cwc.googlegroups.com...
> I don't think you'll have issues here.
> I wouldn't use Merge. I'd use transactional, but set it up to run
> non-continously.
> Another option btw, is to log-ship. This may ni fact work even better in
> this case if you're not doing reports at night.
>sql

replication sqlserver2000 to sqlserver2005

Hi,
I want to replicate all Updates, Inserts and Deletes (possibly indexed
view changes) to a whole database from sql server2000 (live server) to
sql server2005 (reporting server), but I only want to do it every
night to at an interval that I choose. (Note I don't want to replicate
the whole database each time, just initially).
Is it easy to use replication between sql Server 2000
(publisher/distributor) and sql server 2005 (Subscriber)?
I want to use Merge replication, mainly because I can set it to only be
scheduled once a night (as opposed to Transactional where changes would
be sent for every complete transaction)... not interested in merging
any data, just sending all the changes across.
Are there any issues with this idea?
If this is the right way to do it? Or should I be using Data
Transformation Services?
If I am to use Data Transformation services, is there a way to
schedule/automate this task on both sides?
- The Bermused<tech101@.gmail.com> wrote in message
news:1149812382.243609.307830@.i40g2000cwc.googlegroups.com...
> Hi,
> I want to replicate all Updates, Inserts and Deletes (possibly indexed
> view changes) to a whole database from sql server2000 (live server) to
> sql server2005 (reporting server), but I only want to do it every
> night to at an interval that I choose. (Note I don't want to replicate
> the whole database each time, just initially).
> Is it easy to use replication between sql Server 2000
> (publisher/distributor) and sql server 2005 (Subscriber)?
I don't think you'll have issues here.
I wouldn't use Merge. I'd use transactional, but set it up to run
non-continously.
Another option btw, is to log-ship. This may ni fact work even better in
this case if you're not doing reports at night.
> I want to use Merge replication, mainly because I can set it to only be
> scheduled once a night (as opposed to Transactional where changes would
> be sent for every complete transaction)... not interested in merging
> any data, just sending all the changes across.
> Are there any issues with this idea?
> If this is the right way to do it? Or should I be using Data
> Transformation Services?
> If I am to use Data Transformation services, is there a way to
> schedule/automate this task on both sides?
> - The Bermused
>|||Thanks Greg.
I think that the transacitonal solution is a good way to go. With the
distributor being on the same box as the publisher initially, and when
we have more resources then move the distributor to a box in the same
network/domain as the publisher.
Have tried setting this replication up, but run into authentication
problems.
I think the problem is that the Publishing/Distributor sqlserver2000
(Prod/Live) machine is in a different domain to the subscribing
sqlserver2005 (Reporting) machine. Also there is no trust set up
between the domains, so an AD account will not work.
I have heard that impersonation may work in that:
* an agent that SQL Server2005(Subscriber) uses (one of many found in
Services) can run under a specified system account
* I supply the credentials of this account during the (transactional)
replication wizard on the SQL Server2000(Publisher)
This may work but, I am not sure which agent/service to apply the
account to - and I don't want to break things!
By default, services run under a local system account... not sure which
account is default though...
I can set up an SqlServer service to run under a particular account,
however, as mentioned, I am not sure which service to use.
Does this style of solution sound like it is the right way to go?
Also, would log shipping be a less technical/problematic solution for
this? Would it consume less resources on the live machine? And would
it be an incremental type solution that would not clog up the network?
Cheers,
Adam
Greg D. Moore (Strider) wrote:
> <tech101@.gmail.com> wrote in message
> news:1149812382.243609.307830@.i40g2000cwc.googlegroups.com...
> > Hi,
> > I want to replicate all Updates, Inserts and Deletes (possibly indexed
> > view changes) to a whole database from sql server2000 (live server) to
> > sql server2005 (reporting server), but I only want to do it every
> > night to at an interval that I choose. (Note I don't want to replicate
> > the whole database each time, just initially).
> > Is it easy to use replication between sql Server 2000
> > (publisher/distributor) and sql server 2005 (Subscriber)?
> I don't think you'll have issues here.
> I wouldn't use Merge. I'd use transactional, but set it up to run
> non-continously.
> Another option btw, is to log-ship. This may ni fact work even better in
> this case if you're not doing reports at night.
>
> > I want to use Merge replication, mainly because I can set it to only be
> > scheduled once a night (as opposed to Transactional where changes would
> > be sent for every complete transaction)... not interested in merging
> > any data, just sending all the changes across.
> >
> > Are there any issues with this idea?
> > If this is the right way to do it? Or should I be using Data
> > Transformation Services?
> >
> > If I am to use Data Transformation services, is there a way to
> > schedule/automate this task on both sides?
> >
> > - The Bermused
> >

replication SQLServer 2-way

We have 2 SQLServer-databases with the same table in it. In both databases the table can be updated by users.
Is is possible to replicate the updates in this table from the one database to the other and vice versa? We want to use transactional-replication.You can setup an updatable transactional replication. To do that, specify 'true' to the paramenter @.allow_sync_tran when you execute sp_addpublication. And @.update_mode = 'sync tran' when you execute sp_addsubscription.|||Just out of curiousity, why can't he use merge replication instead?
I'm still learning from this replication thing...is there any reasons for chosing transactional against merge for this purpose?|||My personal experience in both merge and updatable transactional is that merge is slower, and it take a lot more to setup and manage. You can treat updatable transactional just like the regular transactional replication with a couple of more parameters to setup. There are other minor reasons but the main thing is the simplicity and better performance of transactional replication.

Hope this helps.|||joejcheng,

We tried the solution you provided and it works fine now. Thank you for your information.

replication setup

Hi,
I am using SQL 2005 server. If I want to replicate a table from A
server to B server, using transactional publication, do I setup a
publication in A server with article of the table and then setup a
subscription in B server? Am I over-simplifying or there is some thing I
need to set up? Cause now it is not changing the record in B server when I
change the record in A server.
Please help. Thanks.
Michael - have you set this up already? If the table is on Server B then I
assume that the initialization has worked and therefore the snapshot and
distribution agent have worked. Can you see if the distribution agent is on
a schedule and if it is, start the relevant job to send the recent
transactions over. Assuming this is the case, you might want to set the
agent to run continuously to reduce latency.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Replication setup

How many articles can take part in merge replication?
I need to replicate about 200 tables. From them 25 are common for all my
subscribers and do not need to be filtered. Other I plan to filter with
static filters.
Is there benefit to allocate this 25 common tables in separate publication
or it will be better to include them in all publications where tables with
filters are placed?
Ideally you would have a single publication. There is no performance impact
by having multiple publications. If you are going to have multiple
publications (perhaps a publication for each article), you should group
articles which are related (PK-FK) relationships into the same publication.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"massa" <massa@.discussions.microsoft.com> wrote in message
news:460AB955-4C93-4EE0-BC24-3B479E7807CF@.microsoft.com...
> How many articles can take part in merge replication?
> I need to replicate about 200 tables. From them 25 are common for all my
> subscribers and do not need to be filtered. Other I plan to filter with
> static filters.
> Is there benefit to allocate this 25 common tables in separate publication
> or it will be better to include them in all publications where tables with
> filters are placed?
>

Replication schedule

How can I check if the transactional replication is set up in continuous
mode or on a set time such as replicate once every hour ? Thanks
Have a look at the distribution agent's job schedule.
Rgds,
Paul Ibison

Replication says success, but tables not showing in EM?

Hello All,
I am trying to replicate data from a SQL Server (7.0) to another SQL
Server using a one-way immediate push subscription. After executing,
there are no errors in the Snapshot or Push agents, but two of the
tables are missing in the subscription database. Where did they go?
I can find the tables listed in the publication->articles tab and in
the snapshot logs on the publisher. The tables are also listed on the
subscriber database under 'Database Roles Properties' --> Permissions.
Thank You For Your Time And Help,
Nate
Hi Nate,
You may want to check whether subscriptions for the two missing tables were
really created by calling sp_helpsubscription at the publisher. If not,
manually add them by calling sp_addsubscriptions with explicit article names
and rerun snapshot + distribution agents.
-Raymond
"Nate" <nathandeneau@.braintrade.biz> wrote in message
news:1142037215.646558.84890@.i40g2000cwc.googlegro ups.com...
> Hello All,
> I am trying to replicate data from a SQL Server (7.0) to another SQL
> Server using a one-way immediate push subscription. After executing,
> there are no errors in the Snapshot or Push agents, but two of the
> tables are missing in the subscription database. Where did they go?
>
> I can find the tables listed in the publication->articles tab and in
> the snapshot logs on the publisher. The tables are also listed on the
> subscriber database under 'Database Roles Properties' --> Permissions.
>
> Thank You For Your Time And Help,
> Nate
>
|||Everything looks as it should after calling sp_helpsubscription - the
two tables are listed.
|||This looks really strange. If you check the history messages of the snapshot
agent, do you see files for the two missing tables generated? And if you
check the distribution agent history, do you see that the files for the two
missing tables applied? If the tables are relatively small, you may be able
to fix things up by reinitializing the subscription, regenerate the snapshot
and reapply it. You may also want to watch out for processes outside of
replication that may have dropped the tables at the subscriber. What are the
sync_type values of subscriptions to the missing tables?
-Raymond
sql

Replication Recommendation

Hi,
We have 4 different international sites that will need to replicate changes
between 4 tables. Each site will need to make changes to the tables locally
and have those changes propogated out to the other 3 sites. Data volume
will be about 25 changes per site per minute. Latency should be under 10
minitues. The connections between the sites is pretty reliable and fast
(max not available - twice a month - fix in 1 hour).
Which replication type would work best here? i.e, Merge, Transactional with
Immediate Updating Subscribers. Which would be the easiest to recovery from
a failure using a backup if a disaster occurs?
Thanks.
Jerry
Also,
Each server is in a seperate domain. I'm not sure of the setup but I can
create a linked server to each. Should I be using linked servers in this
replication setup as well?
Thanks again
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OMjgWXCeFHA.2688@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have 4 different international sites that will need to replicate
> changes between 4 tables. Each site will need to make changes to the
> tables locally and have those changes propogated out to the other 3 sites.
> Data volume will be about 25 changes per site per minute. Latency should
> be under 10 minitues. The connections between the sites is pretty
> reliable and fast (max not available - twice a month - fix in 1 hour).
> Which replication type would work best here? i.e, Merge, Transactional
> with Immediate Updating Subscribers. Which would be the easiest to
> recovery from a failure using a backup if a disaster occurs?
> Thanks.
> Jerry
>
sql

replication question

My situation:
I have a DB hosted by a 3rd party ISP. I want to replicate that DB locally
with in my office on our local server, mainly for backup purposes. I then
want to replicate that backup DB to a development DB for testing purposes.
Basically I'll have a LIVE DB hosted at the ISP. A backup version of the
LIVE
DB on our local server (peace of mind really) Then a development DB for
testing
purposes.
The Publisher will be the DB at the ISP.
Question 1. Is this a doable or a reasonable solution. I figured
transaction replication
from LIVE to BACKUP. Then Snapshot from BACKUP to
DEVELOPMENT.
It isn't absolutely necessary to have the most recent
data on the development db
since I will only be using it for testing purposes. But
would be nice to draw from
real data, almost live situations.
Question 2. Can a DB or server be both a Publisher and a Subscriber in this
case the BACKUP DB
would be both?
Thanks,
George
George,
this is possible - it is known as republishing. In your case my preference
would be to use merge between the live system and the Backup system, as it
is ideal for this sort of high latency situation. This would allow you to
have the Backup system as the publisher (with merge, the
publisher/subscriber metaphor doesn't especially hold), as you can then
control the configuration settings locally, and I'd have the live system as
a push subscriber. The other system is used as a testing system and the data
could become incompatible with replication from the Backup system, so I
wouldn't use replication to transfer to this box. Instead, I'd use a backup
and restore of the whole database - Backup to Test.
HTH,
Paul Ibison
|||I'd use transactional as
1) it offers better performance
2) it doesn't add a GUID key to every table.
The con of transactional replication is that you need a PK on every table you wish to replicate, and with republishing transactional is more sensitive to schema changes. By more sensitive I mean you have to drop your publications and subscriptions, make c
hanges and re-build again.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
sql

Replication Problems

We are thinking to replicate a 'READ ONLY' database to an
off-site location by using a third party utility (We will
try to replicate from the warm backup server where we
restore transaction logs every hour from the original prod
server). Here are the two facts:
1- We don't want any performance decrease from the
original production server to replicate directly from the
prod server.
2- The third party utility is a file replication utility
which can replicate the whole database, differences only
e.t.c.
The problem seems to be, when the transaction log restore
starts, it kicks out all other users (SINGLE_USER,
RESTRICTED_USER).
Is there a way to restore the Transaction log without
cutting all the connections (Especially replication) to
the read-only database '
Thanks for any feedback....> Is there a way to restore the Transaction log without
> cutting all the connections (Especially replication) to
> the read-only database '
I'm afraid not.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:b48d01c3ecfc$63946850$a101280a@.phx.gbl...
> We are thinking to replicate a 'READ ONLY' database to an
> off-site location by using a third party utility (We will
> try to replicate from the warm backup server where we
> restore transaction logs every hour from the original prod
> server). Here are the two facts:
> 1- We don't want any performance decrease from the
> original production server to replicate directly from the
> prod server.
> 2- The third party utility is a file replication utility
> which can replicate the whole database, differences only
> e.t.c.
> The problem seems to be, when the transaction log restore
> starts, it kicks out all other users (SINGLE_USER,
> RESTRICTED_USER).
> Is there a way to restore the Transaction log without
> cutting all the connections (Especially replication) to
> the read-only database '
> Thanks for any feedback....|||Change the schedule for the restore portion of transaction logshipping so th
at it doesn't conflict with the replication. Why are you using a third-part
y replication tool?sql

Replication Problems

We are thinking to replicate a 'READ ONLY' database to an
off-site location by using a third party utility (We will
try to replicate from the warm backup server where we
restore transaction logs every hour from the original prod
server). Here are the two facts:
1- We don't want any performance decrease from the
original production server to replicate directly from the
prod server.
2- The third party utility is a file replication utility
which can replicate the whole database, differences only
e.t.c.
The problem seems to be, when the transaction log restore
starts, it kicks out all other users (SINGLE_USER,
RESTRICTED_USER).
Is there a way to restore the Transaction log without
cutting all the connections (Especially replication) to
the read-only database '
Thanks for any feedback....> Is there a way to restore the Transaction log without
> cutting all the connections (Especially replication) to
> the read-only database '
I'm afraid not.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:b48d01c3ecfc$63946850$a101280a@.phx.gbl...
> We are thinking to replicate a 'READ ONLY' database to an
> off-site location by using a third party utility (We will
> try to replicate from the warm backup server where we
> restore transaction logs every hour from the original prod
> server). Here are the two facts:
> 1- We don't want any performance decrease from the
> original production server to replicate directly from the
> prod server.
> 2- The third party utility is a file replication utility
> which can replicate the whole database, differences only
> e.t.c.
> The problem seems to be, when the transaction log restore
> starts, it kicks out all other users (SINGLE_USER,
> RESTRICTED_USER).
> Is there a way to restore the Transaction log without
> cutting all the connections (Especially replication) to
> the read-only database '
> Thanks for any feedback....|||Change the schedule for the restore portion of transaction logshipping so that it doesn't conflict with the replication. Why are you using a third-party replication tool?

Replication over VPN

I am new to both replication & VPN setup.
I've been asked to replicate a DB from WinXP SP2 to Server 2003 over a VPN.
Both machines are running SQL 2000 3a.
The VPN appears setup correctly (almost) - I can ping both ends and name
resolution is OK. However, I can't browse the server shares from the PC or
vice versa.
In Ent Man I can connect to the servers on both machines but browsing the DB
tables is very slow and most often results in general network failure errors.
Questions:
1. Will SQL Server operate over a VPN or does port 1433 have to be open at
the firewall?
2. I have tried setting up Snapshot Replication with Push Subscription
without success. What is the simplest way to achieve the goal of copying the
DB from XP to the server on a regular basis?
Thank you.
1 )Replication does work over a VPN, but as you have found out it does take
longer than if you were replicating locally over a LAN/WAN.
2) set LoginTimeout and QueryTimeout to large values. The fact that you
can't browse server shares to me indicates that the RPC ports aren't open.
In this case you will probably have to use FTP will a pull subscription.
"MikeH" <MikeH@.community.nospam> wrote in message
news:79A546ED-8473-4BED-9508-501EF601AE86@.microsoft.com...
>I am new to both replication & VPN setup.
> I've been asked to replicate a DB from WinXP SP2 to Server 2003 over a
> VPN.
> Both machines are running SQL 2000 3a.
> The VPN appears setup correctly (almost) - I can ping both ends and name
> resolution is OK. However, I can't browse the server shares from the PC or
> vice versa.
> In Ent Man I can connect to the servers on both machines but browsing the
> DB
> tables is very slow and most often results in general network failure
> errors.
> Questions:
> 1. Will SQL Server operate over a VPN or does port 1433 have to be open at
> the firewall?
> 2. I have tried setting up Snapshot Replication with Push Subscription
> without success. What is the simplest way to achieve the goal of copying
> the
> DB from XP to the server on a regular basis?
> Thank you.
>
|||Hilary
Thank you for the response. The recuperative power of a server reboot is
truly amazing. I can now browse shares and replicate servers.
"Hilary Cotter" wrote:

> 1 )Replication does work over a VPN, but as you have found out it does take
> longer than if you were replicating locally over a LAN/WAN.
> 2) set LoginTimeout and QueryTimeout to large values. The fact that you
> can't browse server shares to me indicates that the RPC ports aren't open.
> In this case you will probably have to use FTP will a pull subscription.
> "MikeH" <MikeH@.community.nospam> wrote in message
> news:79A546ED-8473-4BED-9508-501EF601AE86@.microsoft.com...
>
>

Replication over the internet

Hi All,
Can I replicate from one sql server to another over the internet?
Regards,
Mekim
sure can. By default SQL Server communicates using port 1433. You will have
to deploy your snapshots using ftp.
If you are using MSDE subscribers the best way to deploy them is by using
anonymous subscriptions and use the activeX objects. Fill in values for
PublisherAddress, DistributorAddress, and PublisherNetwork and
DistributorNetwork.
"mekim" <mekim@.discussions.microsoft.com> wrote in message
news:4004177D-6C38-4F78-A812-3CFCB2F69C7A@.microsoft.com...
> Hi All,
> Can I replicate from one sql server to another over the internet?
> Regards,
> Mekim
|||THX!
"Hilary Cotter" wrote:

> sure can. By default SQL Server communicates using port 1433. You will have
> to deploy your snapshots using ftp.
> If you are using MSDE subscribers the best way to deploy them is by using
> anonymous subscriptions and use the activeX objects. Fill in values for
> PublisherAddress, DistributorAddress, and PublisherNetwork and
> DistributorNetwork.
> "mekim" <mekim@.discussions.microsoft.com> wrote in message
> news:4004177D-6C38-4F78-A812-3CFCB2F69C7A@.microsoft.com...
>
>
|||These links should help:
http://support.microsoft.com/?id=321822
http://www.microsoft.com/technet/tre...odtechnol/sql/
reskit/sql7res/part5/sqc07.asp
http://support.microsoft.com/default...32&Product=sql
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||THANK YOU!!!!
"Paul Ibison" wrote:

> These links should help:
> http://support.microsoft.com/?id=321822
> http://www.microsoft.com/technet/tre...odtechnol/sql/
> reskit/sql7res/part5/sqc07.asp
> http://support.microsoft.com/default...32&Product=sql
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>