Replication on MSDE2000 and db size problem

Hi,
We have following scenario:
2 PC on WinXP + MSDE2000 + SP4 with production db. Secondary PC works as
failover backup. DBs are connected by merge replication where backup is as
subscriber.
The problem is related to one table with binary column (2K) and its growing
size (over 100MB per day).
sp_spaceused product_map
name rows reserved data index_size unused
Product_map 24238 1056648KB 130448KB 13520KB 912640KB
It seems that on insert rows server allocates more space than need. This
problem occures only when system works on publisher db. It is ok when I
switch it to backup PC.
I have the same systems on W2k + MSDE2000 + SP3a + merge replication and
don't observe the problem like this.
could you help me how to diagnose it more deeply and solve the problem?
Janek
A couple of points 1) are you using text in row?
2) Are you sure the data in this column is the same on the publisher and
subscriber. Depending on how you update your text or image data it may not
be replicated.
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
"Janek" <Janek@.discussions.microsoft.com> wrote in message
news:456DC0D6-FEDC-43BF-8A22-B6CE22069464@.microsoft.com...
> Hi,
> We have following scenario:
> 2 PC on WinXP + MSDE2000 + SP4 with production db. Secondary PC works as
> failover backup. DBs are connected by merge replication where backup is as
> subscriber.
> The problem is related to one table with binary column (2K) and its
> growing
> size (over 100MB per day).
> sp_spaceused product_map
> name rows reserved data index_size
> unused
> Product_map 24238 1056648KB 130448KB 13520KB 912640KB
> It seems that on insert rows server allocates more space than need. This
> problem occures only when system works on publisher db. It is ok when I
> switch it to backup PC.
> I have the same systems on W2k + MSDE2000 + SP3a + merge replication and
> don't observe the problem like this.
> could you help me how to diagnose it more deeply and solve the problem?
> Janek
|||1) data type for table is: int, smallint, int, char(11), smallint, smallint,
binary(2001), bit, datetime, int
2) All data is replicating, I can see it on reports on each machines. From
sp_spaceused report I see almost 1GB of unused space.
I know only one workaround:
dbcc dbreindex (.... + shrink db - a few cycles makes db to similar
backup db size.
At the moment system is working on backup PC without problem and new data is
refreshed to publisher.
Janek
"Hilary Cotter" wrote:

> A couple of points 1) are you using text in row?
> 2) Are you sure the data in this column is the same on the publisher and
> subscriber. Depending on how you update your text or image data it may not
> be replicated.
> --
> 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
>
> "Janek" <Janek@.discussions.microsoft.com> wrote in message
> news:456DC0D6-FEDC-43BF-8A22-B6CE22069464@.microsoft.com...
>
>

Replication Monitor could not connect to Distributor

Hello Guys,

I have the following scenario:

PC1 : located in Dubai representing my laptop

PC2: located in Denver ,

Both pcs are connected to each other via the internet as i am using Aliases ,they can both access and see each other using SQL Auth.

I want to implement Merge Replication to Synchronize TestDB ,

I have NO DOMAIN connnecting them.

I Created a new publication ,under security i asked the agent to use the Sa account to connect

When launching the rep monitor i get the following error:

"Replication Monitor could not connect to Distributor 'laptop'."

"Login failed for user ''. The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider)"

Both Publisher and Distributer are on my Laptop ,why cant they connect to each other?

Another Question : could i implement Traditional Merge replication to synchronize my DBs or do i need to configure Web Synch in Merge rep to enable connecting via the internet?!

Thanks Guys!

Moodi

First off while you are free to use web synchronization where appropriate you probably can use traditional merge replication.

It is probably that you have configured replication monitor to connect to your distributor with an account which does not have the necessary rights to monitor SQL server. Can you ensure that SQL Server is able to authenticate using mixed authentication (right click on it, select properties, select security and ensure that SQL Server and windows authentication is selected. Add an account in the sysadmin role or verify you know the sa password. Then right click on your distributor in Replication Monitor and select remove. Then right click and add it again, this time use the sa account and its password to add it.|||

Thanks Hillary,

i was able to fix this by configuring the property of the connection used to connect to distributer ,it was windows auth. so i changed it to run under SQL auth. now replication monitor can run and show all subscribers but i have another problem.

I get the following error when i try to initialize the subscription located on my USA server :

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 192.168.1.1]
2007-07-05 11:55:11.00 Logon Error: 18456, Severity: 14, State: 11.

Please note that i am using merge replication via the internet with out web synch. ,when i run the SQL Agent under the local system account i get the above mentioned error although i configured Sa account with the same pwd to be used on both machines.

I created a windows account RepUser on both machines as part of Admin group ,then i configured the agent on both machines to run under this account ,i got the following error:

Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 192.168.1.1]
2007-07-05 11:55:06.15 Logon Error: 17806, Severity: 20, State: 2.

Looks like the SQL agent process account is the one thats being authenticated and NOT the Sa account ,why is this happening?

|||

Hi Ali

I am a little lost here, but it sounds like you need to configure your publication to use a ftp snapshot and then configure ftp for anonymous authentication.

HTH

|||

Hello Hilary ,

Are u saying that if i have two mahcines replicating via the internet then i should have the snapshot folder accessible via FTP?

Ali

Replication Mechanism

Hello,
My Replication works fine when my PocketPc is connected via USB Cable with
the Desktop. Do I have to implement any additional things that the
Replication Mechanism also works with WLAN? (My PocketPc has WLAN). Is the
oRpl.InternetUrl the decisive factor? - because at this url I have stated the
following url: http://machineName/HelpDesk/sscesa20.dll.
thx
robert
That depends.
On your Publisher do this: http://machineName/HelpDesk/sscesa20.dll
I think this gives you a message saying something like "SQL Server Agent CE
Runnning" or something like this.
Then on your Subscriber issue the same query through the Ineternet Exporer
program. You should see the same result.
If you do, you have connectivity and your problem is likely with setting up
security in your script. If you can't you have to configure security on your
publisher.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"robert" <robert@.discussions.microsoft.com> wrote in message
news:770EC375-0C9F-4304-9D33-E03AE7583C48@.microsoft.com...
> Hello,
> My Replication works fine when my PocketPc is connected via USB Cable with
> the Desktop. Do I have to implement any additional things that the
> Replication Mechanism also works with WLAN? (My PocketPc has WLAN). Is the
> oRpl.InternetUrl the decisive factor? - because at this url I have stated
> the
> following url: http://machineName/HelpDesk/sscesa20.dll.
> thx
> robert
|||Okay thank you for your reply, unfortunately I have two more questions:
1)Do I not have to replace the machineName with my IP-Adress of the desktop
when I call this in the IE of the PocketPc?
2)And what can be the problem if this would not work, although with
connectivity of the USB it works?
thx
robert
|||1) You may have to. The pocket pc will attempt to do name resolution on its
own using a local broadcast. If the IP address works and the name doesn't
you have a problem with name resolution.
2) with the USB you have two options in active sync, connecting to the
INternet or work. If you were connected to work it will find your host no
problem as it is using USB. If not, it has to do the same name resolution as
it will using Wireless.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"robert" <robert@.discussions.microsoft.com> wrote in message
news:C6222C3B-F64B-4219-BF2C-2DFEB166EA93@.microsoft.com...
>
> Okay thank you for your reply, unfortunately I have two more questions:
> 1)Do I not have to replace the machineName with my IP-Adress of the
> desktop
> when I call this in the IE of the PocketPc?
> 2)And what can be the problem if this would not work, although with
> connectivity of the USB it works?
> thx
> robert