replication error after database update

I have a system where I utilize merge replication between sqlserver2000 and
pocketpc sqlservece 2.0. The setup is very mature, and has been working fine
until I had my customer "refresh" the database with new data. We're still in
the testing phase, and the database I connect to is a copy of their live
one. After the database was restored from a backup of the live database, I
get the following error on the pocketpc when attempting to perform a merge.
Unique indexes and constraints cannot be created on columns that allow null
values
That's as descriptive as the error gets. I have a few questions:
Is there any place either on the server or the pocketpc that I can find a
log with more information. The publication has many articles with many
fields. I can remove one at a time and figure out which one is causing the
problem, but it will take lots of time. I was hoping to find some
information as to which table is causing the problem.
Also I'm having difficulty removing an article from a publication after
subscriptions have been pulled or attempted to be pulled. I've
re-initialized subscriptions, but the articles cannot be removed. What's the
procedure for doing this?
sorry about double post, I got an error message that the first one wasn't
sent!
"mgarner1980" <mgarner@.kbsi.com> wrote in message
news:%23aiSjpAeFHA.3028@.TK2MSFTNGP09.phx.gbl...
> I have a system where I utilize merge replication between sqlserver2000
and
> pocketpc sqlservece 2.0. The setup is very mature, and has been working
fine
> until I had my customer "refresh" the database with new data. We're still
in
> the testing phase, and the database I connect to is a copy of their live
> one. After the database was restored from a backup of the live database, I
> get the following error on the pocketpc when attempting to perform a
merge.
> Unique indexes and constraints cannot be created on columns that allow
null
> values
>
> That's as descriptive as the error gets. I have a few questions:
>
> Is there any place either on the server or the pocketpc that I can find a
> log with more information. The publication has many articles with many
> fields. I can remove one at a time and figure out which one is causing the
> problem, but it will take lots of time. I was hoping to find some
> information as to which table is causing the problem.
>
> Also I'm having difficulty removing an article from a publication after
> subscriptions have been pulled or attempted to be pulled. I've
> re-initialized subscriptions, but the articles cannot be removed. What's
the
> procedure for doing this?
>

replication error

i have a problem

when i select distributor and click next

"SQL server agent on 'PC-...' currently uses the system account, which causes replication between servers to fail. In te following dialog box, specify another account for the Service startup account"

and

"SQL server enterprise manager couldn not configure 'PC-...' as the distributor for 'PC-...'

Error 18483: Could not connect to server 'PC-...' because 'distributor admin' is not defined as a remote login at the server."

what can i do?I would start by setting up the sql server agent to use a domain user account. I am no replication expert. Not yet anyways. That project is coming soon.|||My suggestions are:

1. Create a domain user specifically for SQL replication, and no other purpose
2. Before you get a new machine involved with replication, make that domain user a local administrator on the new machine.
3. Start the SQL Agent on the machine that is the distributor using the domain accout from step 1.

Depending on how far you got with the replication installation, you may want to reinstall Replication. I know that I would!

-PatP

Replication design issue.

Hi, I need opinion for my system that I consider replication.
Our company has about 300 stores and each store has Sql-Server DB, called TimeDB.
Each store TimeDB contains their own data only.
And main office has Sql-server DB called TimeDBHQ which contains all stores' data that are posted from all the stores.
Most of time, store post their data to TimeDBHQ in daily base by application. And TimeDBHQ distribute policy data and modified data to stores corresponding the data.

All data transfer are handled by application.
But, I consider Replication for the periodical transfer and consider application as preserved way.

The issue I considered first time when I designed the system without using replication is that there are too many subscriber/distributros.
I use replication for several servers, about 3-5, which is very small number comparing to 300 or more.

Here is my question,
Is it good that I use replication for my system?
because replication made by MS is more stable and bug free than my application.
and My first hasitation is on whether replication works stable among more than 300 servers..

If anyone has experiance with this issue please post any idea,
It will be big help!!!

thank you..What compamy?

Even at 10k a licence, that's huge...

You sure we're talking sql server?|||we use sql server personal editon on store side.
anyway..
please consern the issue of the design.

Replication Cleanup in high volume database

We're having intermittent performance problems on a SQL 2005 SP1 system that
we suspect is related to transactional replication. There may be overlap
with Jack Griffith's thread "Replication system disk performance severly
degrades after 1 month", but I'm not sure.....
While troubleshooting the problem, we attempted to make things better by
clearing out any transactions that had already been replicated to the
subscriber (EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
@.max_distretention = 0). BAD, BAD, BAD!!!!! Setting @.max_distretention = 0
marked our subscription as inactive and we had to rebuild the subscriber
database to get it back in sync with the publisher.
I estimate add/update 2-4 million rows a day in our publication database.
The distribution database has grown to 9GB in < a week.
I have not altered any of the database cleanup jobs as of yet. The current
Distribution Cleanup job runs every 10 min and is taking 6-15 minutes to run.
We observe a tremendous number of disk reads on the Distribution database
disks. When we experience performance issues, we also see a HUGE number of
memory pages lasting ~ 15 seconds and occuring exactly every 2 minutes.
Restarting the SQLServer service seems to clear up the performance issues for
several hours.
* msrepl_commands rowcount approaches 35 million rows and
msrepl_transactions approaches 6 million rows. With a high volume in
transactional replication, should there be any indexes on the tables?
* While tracing through the MS SPs, I found that sp_MSdelete_dodelete only
deletes the top 5000 rows from MSrepl_transactions. In a high volume
transactional state, this might not keep up. Can this be changed safely?
* What other parameters can be changed to keep the transactions only as long
as they are needed but yet not mark the subscription as inactive?
Thanks!
If you have a few subscribers use raid 10, if you have a large number use
raid 5.
Set max_distretention to 3 days. This will only pool undistributed commands
for up to 3 days.
the setting for only deleting 5000 rows at a time is by design. If you
choose to modify it to something higher make sure you make the modification
to the end of
sp_MSdelete_publisherdb_trans as well.
The appropriate indexes are in place on msrepl_commands and
msrepl_transactions. You might want to defrag these indexes to see if that
helps or update statistics.
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
"RL" <RL@.discussions.microsoft.com> wrote in message
news:71F2956A-5B80-4918-993F-F8CF3AD2C0D5@.microsoft.com...
> We're having intermittent performance problems on a SQL 2005 SP1 system
> that
> we suspect is related to transactional replication. There may be overlap
> with Jack Griffith's thread "Replication system disk performance severly
> degrades after 1 month", but I'm not sure.....
> While troubleshooting the problem, we attempted to make things better by
> clearing out any transactions that had already been replicated to the
> subscriber (EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
> @.max_distretention = 0). BAD, BAD, BAD!!!!! Setting @.max_distretention =
> 0
> marked our subscription as inactive and we had to rebuild the subscriber
> database to get it back in sync with the publisher.
> I estimate add/update 2-4 million rows a day in our publication database.
> The distribution database has grown to 9GB in < a week.
> I have not altered any of the database cleanup jobs as of yet. The
> current
> Distribution Cleanup job runs every 10 min and is taking 6-15 minutes to
> run.
> We observe a tremendous number of disk reads on the Distribution database
> disks. When we experience performance issues, we also see a HUGE number
> of
> memory pages lasting ~ 15 seconds and occuring exactly every 2 minutes.
> Restarting the SQLServer service seems to clear up the performance issues
> for
> several hours.
>
> * msrepl_commands rowcount approaches 35 million rows and
> msrepl_transactions approaches 6 million rows. With a high volume in
> transactional replication, should there be any indexes on the tables?
> * While tracing through the MS SPs, I found that sp_MSdelete_dodelete only
> deletes the top 5000 rows from MSrepl_transactions. In a high volume
> transactional state, this might not keep up. Can this be changed safely?
> * What other parameters can be changed to keep the transactions only as
> long
> as they are needed but yet not mark the subscription as inactive?
> Thanks!
|||Thanks Hilary. We have one subscriber and are using RAID 10 as recommended.
The max_distretention is set to 72 hours already. The distribution database
was just rebuilt a week ago, so it only has 4 days of deletes in it, so i'm
not sure an index defrag will help.
Why do we need to keep 3 days worth of commands that have already been
replicated? Any suggestions on how to make the distribution cleanup job run
more efficiently? It's running at least 50% of the time now. Is it possible
we're just outpacing what replication was designed for? In my stress testing
before moving to SQL 2005 x64, replication was the weak performance link.
Thanks,
Rob
"Hilary Cotter" wrote:

> If you have a few subscribers use raid 10, if you have a large number use
> raid 5.
> Set max_distretention to 3 days. This will only pool undistributed commands
> for up to 3 days.
> the setting for only deleting 5000 rows at a time is by design. If you
> choose to modify it to something higher make sure you make the modification
> to the end of
> sp_MSdelete_publisherdb_trans as well.
> The appropriate indexes are in place on msrepl_commands and
> msrepl_transactions. You might want to defrag these indexes to see if that
> helps or update statistics.
>
> --
> 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
> "RL" <RL@.discussions.microsoft.com> wrote in message
> news:71F2956A-5B80-4918-993F-F8CF3AD2C0D5@.microsoft.com...
>
>
|||I like to keep 3 days around in case I leave on a Friday night of a long
weekend when I come back on Tuesday I can still restart failed distribution
agents without them going expired on me.
The problem is that if you delete too many rows at a time the locks will
escalate to table level locks and your log reader agents will fail.
I have a friend who was in a similar situation as yours (Wachovia bank IIRC
in NYC) and he manually deleted rows in large batches. Reverse engineer to
the procs to see what to delete and stop your log reader agents while doing
this.
I also worked on a similar problem in Philly where the backlog was something
similar and we were able to get through everything in 2 hours.
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
"RL" <RL@.discussions.microsoft.com> wrote in message
news:1F28BAE1-FD52-470F-9B35-E596A2544D46@.microsoft.com...[vbcol=seagreen]
> Thanks Hilary. We have one subscriber and are using RAID 10 as
> recommended.
> The max_distretention is set to 72 hours already. The distribution
> database
> was just rebuilt a week ago, so it only has 4 days of deletes in it, so
> i'm
> not sure an index defrag will help.
> Why do we need to keep 3 days worth of commands that have already been
> replicated? Any suggestions on how to make the distribution cleanup job
> run
> more efficiently? It's running at least 50% of the time now. Is it
> possible
> we're just outpacing what replication was designed for? In my stress
> testing
> before moving to SQL 2005 x64, replication was the weak performance link.
> Thanks,
> Rob
> "Hilary Cotter" wrote:
sql

Replication between SQL 2005 Express Edition and SQL 2005 Standard Edition

Hello everybody,

I got a problem in my development phase. I'm developing a web-application system that running merge database technology which is centralized database and distributed database. This is because my customer want a highly optimum system standby.

My centralized database running SQL Server 2005 Standard Edition and the distributed database running SQL Server 2005 Express Edition (because it is free). Each of these database, the users can change the data on its database. So, every database must have the same data. from my understanding, each server must act as Publisher and Subscriber. Can SQL Server 2005 Express Edition be a Publisher?

If not, what the solution you all may suggest, because cost is the first factor. If my architecture is not correct, so what you all suggest to me to resolve the problem.

Thanks so much. Any suggestion and opinion is highly appreciate and I'm highly thanks to you all.

You cannot republish with SQL Express, you need Standard Edition or higher. As for solutions, see if you can try to not republish and use the single publisher scenario, or pay for Standard Edition. If cost is a factor, then you have to weigh $$$ versus not being able to meet your business requirements.|||If your intent is that you want the Standard and the express editions to be in sync, and that you want the DML that happen at the Express to be sent to the Standard edition too, you dont need to make the Express as a publisher. By default when you setup merge replication between 2 nodes, they both are read-write. That is Express site and do DML and the merge agent can send across these DML to the Standard edition site.

replication and lots of questions

I keep going in circles on this...
We are trying to scale our system out. We want to have a
copy of our database available on each of our servers.
One server acts as a load machine, the rest answer IIS
requests hitting their local SQL database. Client
requests are forwarded based off network load balancing.
Updates are not frequently made to the database, but they
do happen on occasion. These updates would be made on one
of the servers servicing the requests from the client
(could be any of say 5 servers). I need this change to
be updated immediately on all other servers services
client requests.
So how do I do this? I can setup triggers. A trigger
fire whenever an update is made to the database, updating
the data in all the coresponding tables on the other
servers. Does this lead to an infinite loop, where this
trigger, triggers the same trigger on another machine,
triggering the same update back?
Transactional Replication? Can you have each server be a
publisher and subscriber. Will there be the same problem,
with one replication triggering the other to replicate.
I could use an instead of trigger to make the changes on
a 'Master' server, that will then replicate the changes to
the rest of the servers. Could be the same problem
though?
Merge Replication... I could do this, but less frequently,
and updates would not be replicated very often.
Has anyone ever done this. Are there any better options.
I am rather new to sql server.
-Jackjackb
What is a size of your database?
Have you tried snapshot replication?
<jackb@.qsi.xxx> wrote in message
news:0b0a01c344b7$57c81460$a001280a@.phx.gbl...
> I keep going in circles on this...
> We are trying to scale our system out. We want to have a
> copy of our database available on each of our servers.
> One server acts as a load machine, the rest answer IIS
> requests hitting their local SQL database. Client
> requests are forwarded based off network load balancing.
> Updates are not frequently made to the database, but they
> do happen on occasion. These updates would be made on one
> of the servers servicing the requests from the client
> (could be any of say 5 servers). I need this change to
> be updated immediately on all other servers services
> client requests.
> So how do I do this? I can setup triggers. A trigger
> fire whenever an update is made to the database, updating
> the data in all the coresponding tables on the other
> servers. Does this lead to an infinite loop, where this
> trigger, triggers the same trigger on another machine,
> triggering the same update back?
> Transactional Replication? Can you have each server be a
> publisher and subscriber. Will there be the same problem,
> with one replication triggering the other to replicate.
> I could use an instead of trigger to make the changes on
> a 'Master' server, that will then replicate the changes to
> the rest of the servers. Could be the same problem
> though?
> Merge Replication... I could do this, but less frequently,
> and updates would not be replicated very often.
> Has anyone ever done this. Are there any better options.
> I am rather new to sql server.
> -Jack|||Writing a distributed transaction in a trigger might be a problem, because
if one server is down, updates will NOT be allowed on any server...
You might try transactional replication with queued updating subscribers...
That will give you near real time updates, but if the publisher server is
down, NO replication will occur, so you need to make sure you have good
uptime on that particular box..
<jackb@.qsi.xxx> wrote in message
news:0b0a01c344b7$57c81460$a001280a@.phx.gbl...
> I keep going in circles on this...
> We are trying to scale our system out. We want to have a
> copy of our database available on each of our servers.
> One server acts as a load machine, the rest answer IIS
> requests hitting their local SQL database. Client
> requests are forwarded based off network load balancing.
> Updates are not frequently made to the database, but they
> do happen on occasion. These updates would be made on one
> of the servers servicing the requests from the client
> (could be any of say 5 servers). I need this change to
> be updated immediately on all other servers services
> client requests.
> So how do I do this? I can setup triggers. A trigger
> fire whenever an update is made to the database, updating
> the data in all the coresponding tables on the other
> servers. Does this lead to an infinite loop, where this
> trigger, triggers the same trigger on another machine,
> triggering the same update back?
> Transactional Replication? Can you have each server be a
> publisher and subscriber. Will there be the same problem,
> with one replication triggering the other to replicate.
> I could use an instead of trigger to make the changes on
> a 'Master' server, that will then replicate the changes to
> the rest of the servers. Could be the same problem
> though?
> Merge Replication... I could do this, but less frequently,
> and updates would not be replicated very often.
> Has anyone ever done this. Are there any better options.
> I am rather new to sql server.
> -Jack|||about 50GB, so snapshot will not work so well
>--Original Message--
>jackb
>What is a size of your database?
>Have you tried snapshot replication?
><jackb@.qsi.xxx> wrote in message
>news:0b0a01c344b7$57c81460$a001280a@.phx.gbl...
>> I keep going in circles on this...
>> We are trying to scale our system out. We want to have
a
>> copy of our database available on each of our servers.
>> One server acts as a load machine, the rest answer IIS
>> requests hitting their local SQL database. Client
>> requests are forwarded based off network load balancing.
>> Updates are not frequently made to the database, but
they
>> do happen on occasion. These updates would be made on
one
>> of the servers servicing the requests from the client
>> (could be any of say 5 servers). I need this change to
>> be updated immediately on all other servers services
>> client requests.
>> So how do I do this? I can setup triggers. A trigger
>> fire whenever an update is made to the database,
updating
>> the data in all the coresponding tables on the other
>> servers. Does this lead to an infinite loop, where this
>> trigger, triggers the same trigger on another machine,
>> triggering the same update back?
>> Transactional Replication? Can you have each server be
a
>> publisher and subscriber. Will there be the same
problem,
>> with one replication triggering the other to replicate.
>> I could use an instead of trigger to make the changes on
>> a 'Master' server, that will then replicate the changes
to
>> the rest of the servers. Could be the same problem
>> though?
>> Merge Replication... I could do this, but less
frequently,
>> and updates would not be replicated very often.
>> Has anyone ever done this. Are there any better
options.
>> I am rather new to sql server.
>> -Jack
>
>.
>

Replication and large transactions

Hello,
Is it true that merge agent is not replicating at all when a transaction is
going on?
In my system I use merge replication and I have set the merge agents on
continuous replication to make sure that all changes are replicated to all
databases as soon as possible. Now I notice that when a large transaction is
going on (about 10 minutes) the merge agent is not replicating at all. So if
concurrently a small transaction took place and was committed, these changes
are replicated after the large transaction was finished.
I guess this behaviour is caused by the merge agent that waits for the large
transaction to finish before it has access to the
msmerge_contents/msmerge_tombstone tables.
Is there any way to prevent this blocking?
Can I set the Isolation Level of the transaction of the merge agent?
thanks in advance,
Marco Broenink
Yes, the way it works is that triggers fire on the tables which are
published to and subscriber to for merge replication.
If you have a large transaction which commits at the end of the transaction,
the triggers will not fire until the commit happens.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:D0D14388-1660-49DC-9555-DAFD9B8E0C66@.microsoft.com...
> Hello,
> Is it true that merge agent is not replicating at all when a transaction
is
> going on?
> In my system I use merge replication and I have set the merge agents on
> continuous replication to make sure that all changes are replicated to all
> databases as soon as possible. Now I notice that when a large transaction
is
> going on (about 10 minutes) the merge agent is not replicating at all. So
if
> concurrently a small transaction took place and was committed, these
changes
> are replicated after the large transaction was finished.
> I guess this behaviour is caused by the merge agent that waits for the
large
> transaction to finish before it has access to the
> msmerge_contents/msmerge_tombstone tables.
> Is there any way to prevent this blocking?
> Can I set the Isolation Level of the transaction of the merge agent?
> thanks in advance,
> Marco Broenink

Replication & Report execution

I have an issue with some reports that are fired seamlessly from our ERP system, the problem is that they are attempting to run over the replicated database BEFORE the replication has time to get the records into the replicated database. I believe my longest latency time is 8400 (about 8 seconds if I am reading that right).
As soon as a shipment is processed the Bill Of Lading, Packing List & Certificate of Analysis Reports are fired, the ODBC driver is pointing to the replicated database, and in all my testing this did not occur. I couldn't get to the replicated database fast enough to not see the records present.
I know I could put a delay in the stored procedure, but doesn't that defeat my purpose a bit?
Does anyone have any suggestions on how I can speed things up? Any suggestions at all will be appreciated, I want my report database (Transactional replication) project to be a huge success.
8.4 s to be completely accurate
To get a consistent database state you should schedule your distribution agent. This way it will not run continuously but rather in a batch.
You might also want to split the tables into their own publication and then use the independent agent option. This should solve the synchronization problems.
The most significant thing you can do to improve your transactional performance is to try to minimize the indexes, triggers and indexed views hanging off the subscriber tables.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message news:e7ivuSSlEHA.3988@.TK2MSFTNGP14.phx.gbl...
I have an issue with some reports that are fired seamlessly from our ERP system, the problem is that they are attempting to run over the replicated database BEFORE the replication has time to get the records into the replicated database. I believe my longest latency time is 8400 (about 8 seconds if I am reading that right).
As soon as a shipment is processed the Bill Of Lading, Packing List & Certificate of Analysis Reports are fired, the ODBC driver is pointing to the replicated database, and in all my testing this did not occur. I couldn't get to the replicated database fast enough to not see the records present.
I know I could put a delay in the stored procedure, but doesn't that defeat my purpose a bit?
Does anyone have any suggestions on how I can speed things up? Any suggestions at all will be appreciated, I want my report database (Transactional replication) project to be a huge success.

Replication - will this work?

I have a system with two SQL 2K servers. The two servers are connected by
dial up 56K lines which will be used to update the system. The requirement
is that the first server S1 database match the second server S2 database on
a daily basis. S1 will be heavily updated but S2 will mostly have few
changes.
I was thinking replication via the SQL server would be the best way to
implement this but was wondering if it was possible.
One of my concerns is that since the two servers are not connected all the
time will they update correctly when they are connected.
I am also concerned about how the system will handle records that are
updated on both system and then merged.
Also can the replication occur while the server is receiving new data?
Any comments, suggestion, or links would be appreciated.
Regards,
John
John,
Merge replication should do what you need, it is designed for occasionally
disconnected sites. Check it in Books Online whether this is what you need.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"John J. Hughes II" <no@.invalid.com> wrote in message
news:ueBIwtHvEHA.1296@.TK2MSFTNGP10.phx.gbl...
> I have a system with two SQL 2K servers. The two servers are connected by
> dial up 56K lines which will be used to update the system. The
requirement
> is that the first server S1 database match the second server S2 database
on
> a daily basis. S1 will be heavily updated but S2 will mostly have few
> changes.
> I was thinking replication via the SQL server would be the best way to
> implement this but was wondering if it was possible.
> One of my concerns is that since the two servers are not connected all the
> time will they update correctly when they are connected.
> I am also concerned about how the system will handle records that are
> updated on both system and then merged.
> Also can the replication occur while the server is receiving new data?
> Any comments, suggestion, or links would be appreciated.
> Regards,
> John
>
|||Hi John,
From your descriptions, I understood that you would like to make
replication between two SQL Servers in Internet via modem. Have I
understood you? Correct me if I was wrong.
Based on my scope, first of all, you must select a way to setup replication
via Internet. Ways for users to connect to replicated data over the
Internet using Microsoft SQL Server 2000 include:
- Using Virtual Private Networks (VPNs), such as those based on the
Microsoft Windows NT? Server version 4.0 operating system, the Microsoft
Windows? 2000 Server operating system, or a third party provider.
- Integrating replication with Microsoft Proxy Server.
- Using TCP/IP and File Transfer Protocol (FTP) to transfer the initial
snapshot of data over the Internet.
See Topic "Web-Based Applications" in Books Online for more detailed
information about this and I would perfer using VPN. See the following
documents for how to create VPN on the different OS
How To Install and Configure a Virtual Private Network Server in Windows
Server 2003
http://support.microsoft.com/default...b;en-us;323441
Configure a Win2K VPN
http://msdn.microsoft.com/library/de...us/dnw2kmag00/
html/VPN.asp
How to Install Virtual Private Networking in Windows Me/98
http://support.microsoft.com/default...b;en-us;288779
Secondly, it was not necessary for you to connect to the Internet all the
time. You could schedule the synchronization at the same time per day with
a job, however, you will have to dial-up yourself that time before setup
the replication.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||The system will not be interfacing with the Internet at all. System 1 will
connect to system 2 using a 56K lease line or ISDN line. The only problem
is the connection is not permanent and must be initiated before update.
Regards,
John
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:q0rLa$LvEHA.3696@.cpmsftngxa10.phx.gbl...
> Hi John,
> From your descriptions, I understood that you would like to make
> replication between two SQL Servers in Internet via modem. Have I
> understood you? Correct me if I was wrong.
> Based on my scope, first of all, you must select a way to setup
> replication
> via Internet. Ways for users to connect to replicated data over the
> Internet using Microsoft SQL Server 2000 include:
> - Using Virtual Private Networks (VPNs), such as those based on the
> Microsoft Windows NT? Server version 4.0 operating system, the Microsoft
> Windows? 2000 Server operating system, or a third party provider.
> - Integrating replication with Microsoft Proxy Server.
> - Using TCP/IP and File Transfer Protocol (FTP) to transfer the initial
> snapshot of data over the Internet.
> See Topic "Web-Based Applications" in Books Online for more detailed
> information about this and I would perfer using VPN. See the following
> documents for how to create VPN on the different OS
> How To Install and Configure a Virtual Private Network Server in Windows
> Server 2003
> http://support.microsoft.com/default...b;en-us;323441
> Configure a Win2K VPN
> http://msdn.microsoft.com/library/de...us/dnw2kmag00/
> html/VPN.asp
> How to Install Virtual Private Networking in Windows Me/98
> http://support.microsoft.com/default...b;en-us;288779
> Secondly, it was not necessary for you to connect to the Internet all the
> time. You could schedule the synchronization at the same time per day with
> a job, however, you will have to dial-up yourself that time before setup
> the replication.
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
|||Hi John,
Sorry for my misunderstanding.
If so, as MVP Dejan Sarka and I have said, it's OK for replication doing so
when the connection are not permanent. For example, you have scheduled the
run the SQL Agent at 6:00 PM everyday, you will have to dial-up before 6:00
PM everyday and make sure it was connect before SQL Agent doing this.
(Unfortuantely, based on my scope, I am afraid SQL Server do not have the
funcaitonality to dial-up automatically)
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Thanks for the response.
Regards,
John

Replication - will this work?

I have a system with two SQL 2K servers. The two servers are connected by
dial up 56K lines which will be used to update the system. The requirement
is that the first server S1 database match the second server S2 database on
a daily basis. S1 will be heavily updated but S2 will mostly have few
changes.
I was thinking replication via the SQL server would be the best way to
implement this but was wondering if it was possible.
One of my concerns is that since the two servers are not connected all the
time will they update correctly when they are connected.
I am also concerned about how the system will handle records that are
updated on both system and then merged.
Also can the replication occur while the server is receiving new data?
Any comments, suggestion, or links would be appreciated.
Regards,
JohnJohn,
Merge replication should do what you need, it is designed for occasionally
disconnected sites. Check it in Books Online whether this is what you need.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"John J. Hughes II" <no@.invalid.com> wrote in message
news:ueBIwtHvEHA.1296@.TK2MSFTNGP10.phx.gbl...
> I have a system with two SQL 2K servers. The two servers are connected by
> dial up 56K lines which will be used to update the system. The
requirement
> is that the first server S1 database match the second server S2 database
on
> a daily basis. S1 will be heavily updated but S2 will mostly have few
> changes.
> I was thinking replication via the SQL server would be the best way to
> implement this but was wondering if it was possible.
> One of my concerns is that since the two servers are not connected all the
> time will they update correctly when they are connected.
> I am also concerned about how the system will handle records that are
> updated on both system and then merged.
> Also can the replication occur while the server is receiving new data?
> Any comments, suggestion, or links would be appreciated.
> Regards,
> John
>|||Hi John,
From your descriptions, I understood that you would like to make
replication between two SQL Servers in Internet via modem. Have I
understood you? Correct me if I was wrong.
Based on my scope, first of all, you must select a way to setup replication
via Internet. Ways for users to connect to replicated data over the
Internet using Microsoft SQL Server 2000 include:
- Using Virtual Private Networks (VPNs), such as those based on the
Microsoft Windows NT? Server version 4.0 operating system, the Microsoft
Windows? 2000 Server operating system, or a third party provider.
- Integrating replication with Microsoft Proxy Server.
- Using TCP/IP and File Transfer Protocol (FTP) to transfer the initial
snapshot of data over the Internet.
See Topic "Web-Based Applications" in Books Online for more detailed
information about this and I would perfer using VPN. See the following
documents for how to create VPN on the different OS
How To Install and Configure a Virtual Private Network Server in Windows
Server 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;323441
Configure a Win2K VPN
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnw2kmag00/
html/VPN.asp
How to Install Virtual Private Networking in Windows Me/98
http://support.microsoft.com/default.aspx?scid=kb;en-us;288779
Secondly, it was not necessary for you to connect to the Internet all the
time. You could schedule the synchronization at the same time per day with
a job, however, you will have to dial-up yourself that time before setup
the replication.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||The system will not be interfacing with the Internet at all. System 1 will
connect to system 2 using a 56K lease line or ISDN line. The only problem
is the connection is not permanent and must be initiated before update.
Regards,
John
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:q0rLa$LvEHA.3696@.cpmsftngxa10.phx.gbl...
> Hi John,
> From your descriptions, I understood that you would like to make
> replication between two SQL Servers in Internet via modem. Have I
> understood you? Correct me if I was wrong.
> Based on my scope, first of all, you must select a way to setup
> replication
> via Internet. Ways for users to connect to replicated data over the
> Internet using Microsoft SQL Server 2000 include:
> - Using Virtual Private Networks (VPNs), such as those based on the
> Microsoft Windows NT? Server version 4.0 operating system, the Microsoft
> Windows? 2000 Server operating system, or a third party provider.
> - Integrating replication with Microsoft Proxy Server.
> - Using TCP/IP and File Transfer Protocol (FTP) to transfer the initial
> snapshot of data over the Internet.
> See Topic "Web-Based Applications" in Books Online for more detailed
> information about this and I would perfer using VPN. See the following
> documents for how to create VPN on the different OS
> How To Install and Configure a Virtual Private Network Server in Windows
> Server 2003
> http://support.microsoft.com/default.aspx?scid=kb;en-us;323441
> Configure a Win2K VPN
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnw2kmag00/
> html/VPN.asp
> How to Install Virtual Private Networking in Windows Me/98
> http://support.microsoft.com/default.aspx?scid=kb;en-us;288779
> Secondly, it was not necessary for you to connect to the Internet all the
> time. You could schedule the synchronization at the same time per day with
> a job, however, you will have to dial-up yourself that time before setup
> the replication.
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>|||Hi John,
Sorry for my misunderstanding.
If so, as MVP Dejan Sarka and I have said, it's OK for replication doing so
when the connection are not permanent. For example, you have scheduled the
run the SQL Agent at 6:00 PM everyday, you will have to dial-up before 6:00
PM everyday and make sure it was connect before SQL Agent doing this.
(Unfortuantely, based on my scope, I am afraid SQL Server do not have the
funcaitonality to dial-up automatically)
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Thanks for the response.
Regards,
John

Replication - will this work?

I have a system with two SQL 2K servers. The two servers are connected by
dial up 56K lines which will be used to update the system. The requirement
is that the first server S1 database match the second server S2 database on
a daily basis. S1 will be heavily updated but S2 will mostly have few
changes.
I was thinking replication via the SQL server would be the best way to
implement this but was wondering if it was possible.
One of my concerns is that since the two servers are not connected all the
time will they update correctly when they are connected.
I am also concerned about how the system will handle records that are
updated on both system and then merged.
Also can the replication occur while the server is receiving new data?
Any comments, suggestion, or links would be appreciated.
Regards,
JohnJohn,
Merge replication should do what you need, it is designed for occasionally
disconnected sites. Check it in Books Online whether this is what you need.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"John J. Hughes II" <no@.invalid.com> wrote in message
news:ueBIwtHvEHA.1296@.TK2MSFTNGP10.phx.gbl...
> I have a system with two SQL 2K servers. The two servers are connected by
> dial up 56K lines which will be used to update the system. The
requirement
> is that the first server S1 database match the second server S2 database
on
> a daily basis. S1 will be heavily updated but S2 will mostly have few
> changes.
> I was thinking replication via the SQL server would be the best way to
> implement this but was wondering if it was possible.
> One of my concerns is that since the two servers are not connected all the
> time will they update correctly when they are connected.
> I am also concerned about how the system will handle records that are
> updated on both system and then merged.
> Also can the replication occur while the server is receiving new data?
> Any comments, suggestion, or links would be appreciated.
> Regards,
> John
>|||Hi John,
From your descriptions, I understood that you would like to make
replication between two SQL Servers in Internet via modem. Have I
understood you? Correct me if I was wrong.
Based on my scope, first of all, you must select a way to setup replication
via Internet. Ways for users to connect to replicated data over the
Internet using Microsoft SQL Server 2000 include:
- Using Virtual Private Networks (VPNs), such as those based on the
Microsoft Windows NT? Server version 4.0 operating system, the Microsoft
Windows? 2000 Server operating system, or a third party provider.
- Integrating replication with Microsoft Proxy Server.
- Using TCP/IP and File Transfer Protocol (FTP) to transfer the initial
snapshot of data over the Internet.
See Topic "Web-Based Applications" in Books Online for more detailed
information about this and I would perfer using VPN. See the following
documents for how to create VPN on the different OS
How To Install and Configure a Virtual Private Network Server in Windows
Server 2003
http://support.microsoft.com/defaul...kb;en-us;323441
Configure a Win2K VPN
http://msdn.microsoft.com/library/d...-us/dnw2kmag00/
html/VPN.asp
How to Install Virtual Private Networking in Windows Me/98
http://support.microsoft.com/defaul...kb;en-us;288779
Secondly, it was not necessary for you to connect to the Internet all the
time. You could schedule the synchronization at the same time per day with
a job, however, you will have to dial-up yourself that time before setup
the replication.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||The system will not be interfacing with the Internet at all. System 1 will
connect to system 2 using a 56K lease line or ISDN line. The only problem
is the connection is not permanent and must be initiated before update.
Regards,
John
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in messag
e
news:q0rLa$LvEHA.3696@.cpmsftngxa10.phx.gbl...
> Hi John,
> From your descriptions, I understood that you would like to make
> replication between two SQL Servers in Internet via modem. Have I
> understood you? Correct me if I was wrong.
> Based on my scope, first of all, you must select a way to setup
> replication
> via Internet. Ways for users to connect to replicated data over the
> Internet using Microsoft SQL Server 2000 include:
> - Using Virtual Private Networks (VPNs), such as those based on the
> Microsoft Windows NT? Server version 4.0 operating system, the Microsoft
> Windows? 2000 Server operating system, or a third party provider.
> - Integrating replication with Microsoft Proxy Server.
> - Using TCP/IP and File Transfer Protocol (FTP) to transfer the initial
> snapshot of data over the Internet.
> See Topic "Web-Based Applications" in Books Online for more detailed
> information about this and I would perfer using VPN. See the following
> documents for how to create VPN on the different OS
> How To Install and Configure a Virtual Private Network Server in Windows
> Server 2003
> http://support.microsoft.com/defaul...kb;en-us;323441
> Configure a Win2K VPN
> http://msdn.microsoft.com/library/d...kb;en-us;288779
> Secondly, it was not necessary for you to connect to the Internet all the
> time. You could schedule the synchronization at the same time per day with
> a job, however, you will have to dial-up yourself that time before setup
> the replication.
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>|||Hi John,
Sorry for my misunderstanding.
If so, as MVP Dejan Sarka and I have said, it's OK for replication doing so
when the connection are not permanent. For example, you have scheduled the
run the SQL Agent at 6:00 PM everyday, you will have to dial-up before 6:00
PM everyday and make sure it was connect before SQL Agent doing this.
(Unfortuantely, based on my scope, I am afraid SQL Server do not have the
funcaitonality to dial-up automatically)
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Thanks for the response.
Regards,
John

Replication - SQLServer Agent uses System Account

When I attempt to set up replication on my local box, I receive the error -'SQLServer Agent uses the Systems Account, which causes replication betweem servers to fail'.
Why does this happen? And can I overcome this?
Thanks :)
BiancaStart SQLServerAgent service using a domain account. This account must have the appropriate rights on teh Publisher, Distributor and Subscriber computers (depending on the replication type)

Originally posted by Bianca_J
When I attempt to set up replication on my local box, I receive the error -'SQLServer Agent uses the Systems Account, which causes replication betweem servers to fail'.

Why does this happen? And can I overcome this?

Thanks :)

Bianca|||Thank you for your post :)

What is a Domain Account?

Is this set up in Windows 2000? And if I set up a Domain Account in my local Windows 2000 box - how do I have right over a remore server?

Thanks

Bianca

Replication - DB2 as publisher - SQL 2005 as subscriber

I have been looking for a way to replicate data from a DB2 system running on an AIX machine. I found some information related to doing this with SQL2000, but not with 2005. Can this be done - preferably without writing our own provider.

Thanks in advance

I have the same problem. I am trying to replicate data from DB2/AS400 to SQL Server 2005. I am able to connect to DB2 through SQL Analyzer and can query DB2 tables but I don’t know how to replicate data. I have done my research and people told me that it can’t be done but couldn’t find any article or book (I also went to book store) which could tell me the way how to do it. If you find any thing please post it here.

Thanks

|||You have to use something like Data Mirror for this or code your own solution.|||But then what about Heterogeneous Replication. Is it just for Oracle to and from SQL Server?

Replication - DB2 as publisher - SQL 2005 as subscriber

I have been looking for a way to replicate data from a DB2 system running on an AIX machine. I found some information related to doing this with SQL2000, but not with 2005. Can this be done - preferably without writing our own provider.

Thanks in advance

I have the same problem. I am trying to replicate data from DB2/AS400 to SQL Server 2005. I am able to connect to DB2 through SQL Analyzer and can query DB2 tables but I don’t know how to replicate data. I have done my research and people told me that it can’t be done but couldn’t find any article or book (I also went to book store) which could tell me the way how to do it. If you find any thing please post it here.

Thanks

|||You have to use something like Data Mirror for this or code your own solution.|||But then what about Heterogeneous Replication. Is it just for Oracle to and from SQL Server?

Replication - DB2 as publisher - SQL 2005 as subscriber

I have been looking for a way to replicate data from a DB2 system running on an AIX machine. I found some information related to doing this with SQL2000, but not with 2005. Can this be done - preferably without writing our own provider.

Thanks in advance

I have the same problem. I am trying to replicate data from DB2/AS400 to SQL Server 2005. I am able to connect to DB2 through SQL Analyzer and can query DB2 tables but I don’t know how to replicate data. I have done my research and people told me that it can’t be done but couldn’t find any article or book (I also went to book store) which could tell me the way how to do it. If you find any thing please post it here.

Thanks

|||You have to use something like Data Mirror for this or code your own solution.|||But then what about Heterogeneous Replication. Is it just for Oracle to and from SQL Server?

Replication

I was looking at performing replication and would like transactional
replication...but my system says my licence does not allow for transactional
replication.
Does anyone know how to get this enabled? Do I need to buy another licence
or a different kind of licence?
Thx for replies.
Billy
That means you're using Personal Edition or MSDE. You'll need Developer to
develop on. For Production you can use Standard or Enterprise.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .