A Little Basic Help Needed (SQLServer 2005) Merge Replication

Jul 29, 2006

Hi all,

We are using SQL Server 2005, on Windows server 2003 R2.

We Have Two Database Servers say DBServer1 and DBServer2, Now I wants to do Replication between these to servers, such that

1. The Changes at DBServer1 should be reflected at DBServer2
2. The Changes at DBServer2 should be reflected at DBServer1
3. Changes includes Data changes and Schema Changes
4. After every Synchronization Both Databases should be Identical

I tried doing so, what i did was
I cofigured Distribution at DBServer1, also Publisher and Publication at DBServer1
and Made a Subscription at DBServer2.

What I successfully done is
If Publisher means DBServer1 do some changes then it gets updated at DBServer2.
But New Rows added at DBServer2 doesn't gets added at DBServer1

Thanks in Advance,
Vishalgiri Goswami
Kalptaru Infosoft Pvt. Ltd.

View 4 Replies


ADVERTISEMENT

Help Needed In Merge Replication, SQL Server 2005 Mobile Edition And VC# 2005

Apr 10, 2008

I have written following code in my application

I just want to display all the data of a Single table into a Data Grid, I know that we can drag and drop the table on to a form and datagrid is generated, but here I want to retrive those values through my code, how should i do that

I am getting following errors while running the program
Error 1) Error No. 28037, MS SQL Server 2005 Evrywhere Edition
Error: A request to send data to the computer running IIS has failed. For more information see HRESULT
Error 2) Error No. 0, SQL Server 2005 Evrywhere Edition ADO.Net Data Provider
Error: The specified table does not exist [ JobLists ].

Can anybody please tell me, where I went wrong ??? In this code anywhere else????

Note: While adding a Data Source of SQL Server 2005 Mobile Edition, I have added that .sdf file into my project, thats why I have written the Data Source as : .DbFile.sdf



@"Data Source = .DbDotNetCF.sdf";


The code is as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;

namespace DeviceApplication1
{
public partial class Form1 : Form
{
string filename = @".DbDotNetCF.sdf";

private DataSet dsJobLists;

public Form1()
{
InitializeComponent();
}

private void DeleteDB()
{
if (System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);
}
}

private void Sync()
{
SqlCeReplication repl = new SqlCeReplication();

repl.InternetUrl = @"http://localhost/WebsiteDotNetCF/sqlcesa30.dll";
repl.Publisher = @"RAHU";
repl.PublisherDatabase = @"DotNetCF";
repl.PublisherSecurityMode = SecurityType.NTAuthentication;
repl.Publication = @"PubDotNetCF";
repl.Subscriber = @"SubDotNetCF";
repl.SubscriberConnectionString = @"Data Source='" + filename + "';Max Database Size=128;Default Lock Escalation =100;";
try
{
if (!System.IO.File.Exists(filename))
{
repl.AddSubscription(AddOption.CreateDatabase);
}
repl.Synchronize();
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
finally
{
repl.Dispose();
}


// Display Same Data In Another DataGrid : dataGrid1
SqlCeConnection cn = new SqlCeConnection(@"Data Source='" + filename + "'");

SqlCeDataAdapter daJobLists = new SqlCeDataAdapter("SELECT JobListsID, JobID, PersonID FROM JobLists", cn);
if (dsJobLists == null)
{
dsJobLists = new DataSet();
}
try
{
dsJobLists.Clear();
daJobLists.Fill(dsJobLists, "JobLists");
dataGrid1.DataSource = dsJobLists.Tables["JobLists"];
}
catch (SqlCeException ex)
{
DisplaySQLCEErrors(ex);
}
}

private void DisplaySQLCEErrors(SqlCeException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
MessageBox.Show("Index #" + i.ToString() + ""
+ ex.Errors.Source + ""
+ "Error: " + ex.Errors.Message,
"Error No. " + ex.Errors.NativeError.ToString());
}
}

private void Form1_Load(object sender, EventArgs e)
{
Sync();
DeleteDB();

if (DbDotNetCFDataSetUtil.DesignerUtil.IsRunTime())
{
// TODO: Delete this line of code to remove the default AutoFill for 'dbDotNetCFDataSet.JobLists'.
this.jobListsTableAdapter.Fill(this.dbDotNetCFDataSet.JobLists);
}
}
}
}



I have created a merge replication correctlly( I suppose, there were no errros)
Please help

Your help will be appriciated

View 1 Replies View Related

Merge Replication Updates On Initialise (SQLServer 2005)

Jun 15, 2006

I have a merge replication scenario where the technicians are filtered by their individual codes. Each technician has his own mobile device for collecting and synchronising data.

On initialisation of the subscription, the user should only get the data that is relevant to their code.

The client is staging the rollout to all of their technicians and as the system has grown, we have noticed that there are a number (getting larger every day) of updates that are creeping into the initialisation of each subscription.

My understanding is that an initialisation will only ever apply the inserts necessary to create the subscriber database.

Is my understadning incorrect?

Is the some parameter in the creation of the publication that we have set incorrectly?

Is there something wrong with SQLServer 2005 itself?


I am very interested to hear anyones comments or advice around this issue.

Thanks
Steve

View 4 Replies View Related

Merge Replication - One Way Data Flow - Help Needed...

Apr 24, 2007

Hi,

We have configured one-way merge replication in our topology. That is data flows from Subscriber to Publisher only. We have a publisher and a subscriber. There are 3 publications in this category and each publication has a subscription. We use SQL Server 2005 SP1 in both the servers. The retention period is the 14 days (default). After this period, I get the following error in the subscription in Replication Monitor. The Error message is



Error messages:

The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet sent to the Subscriber. You must reinitialize the subscription (without upload). (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402)


I read the post http://forums.microsoft.com/MSDN/ShowPost.aspxPostID=372790&SiteID=1

which said that this error might be solved in the SP2. We have not yet applied SP2, but even after applying SP2, will this error be solved for One Way Merge Replication since the data from publisher will not go to the subscriber always in this type of topology??.



Kindly get back to us regarding this as soon as possible. Thanks in advance.



Regards,

Swapna.B.


View 3 Replies View Related

Merge-Replication - Transaction Log Is Growing Largely - Help Needed...

Apr 25, 2007

Hi,

We have configured the following in the Publisher server..



1) Merge Replication - Synchronisation to be running in the continuous mode.

2) Merge Replication - Synchronisation in Scheduled mode.



The issue that we are facing here is the transaction log file of the databases which are in replication are growing very largely. And we get this error message in the Subscriber :



Error messages:



The Merge Agent failed to retrieve article information for publication 'MCC_Pos_CashlessPub'. Increase the -QueryTimeOut parameter and restart the synchronization. When troubleshooting, use SQL Profiler or restart the agent with a higher value for -HistoryVerboseLevel and check the output log file for errors. Correct any database engine conditions that may be causing internal replication stored procedures to fail. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201017)
Get help: http://help/MSSQL_REPL-2147201017

The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases (Source: MSSQLServer, Error number: 9002)
Get help: http://help/9002



In fact for the past two days there are no data movement or any changes to the table, still we are able to see the growth in the transaction log file.



As mentioned in the error description when checked the log_reuse_wait_desc column of the sys.databases table, it showed the value "LOG_BACKUP". So took database backup 3 times. and took transaction log backup 2 times from the subscriber server in which the error was thrown. Still the issue persists. There is no change in the transaction log size.



What is the reason behind the growth of LDF?? Is it because of Merge Replication Configured in the server??



Kindly check and let as know regarding this issue. We are facing lot of problems because of this issue. Thanks in advance.



Regards,

Swapna.B.



View 1 Replies View Related

VB Express SQL Expert Needed For Merge Replication Support.

Sep 3, 2006

I have done a couple of little projects in VB express and was pleased with the results. Now I have something bigger in mind and need to know if VB express version of SQL will support merge replication. If not will the full version support it?
Thanks
Dave

View 3 Replies View Related

Basic, Perhaps Silly SQLServer 2005 Question

Apr 19, 2006

I've previously used version 7 and 2000.I've recently started to learn ASP.net. I've installedVisual Web Developer 2005 Express and this includesSQLServer 2005 in the installation. The task managerand and the list of services definitely show I've gotSQLServer running on my computer.Here's the problem: how do I actually use it? I can'tfind any tools to create and access databases.I've tried installing the Enterprise Manager from 2000but this gives me an error message saying I must use the 2005tools to connect.So, is there an Enterprise Manager (or similar) for 2005?Where do I find it?

View 2 Replies View Related

Deployment Files Needed By Sqlserver 2005 Driver

May 26, 2007

Hello all

I built an application that connect to MS SqlServer2005 using Native driver (sqlncli.msi) I install that file from MS site, I need to deply my application to the end-user, and I would like to know what files do I need to deploy to make sure the application is gona run okay on the client PC's.



I search in the registry for the driver, and I found this "sqlncli.dll", is it enough or I need to include more files !!



Thanks and best regrdas

Waleed

View 1 Replies View Related

SQL Express 2005 Replication Via Visual Basic

May 9, 2006

yiotis writes "Is there a way where you can programmatically (via visual basic) replicate two or more SQL Express databases located on multiple computers without the need of SQL Server 2005 acting as a publisher. From what i understand this is a limitation in SQL Express.

Right now i have SQL Express installed on 2 machines. I am able via vb to communicate with each database, read and write data between databases but cannot seem to figure out how to perform a simple replication.

Bottom line is, i need to be able to replicate via vb code 2 sql express databases with each other.

Thanks"

View 1 Replies View Related

SQL 2005 Replication Help Needed

Apr 12, 2007

Hey all

I am in the process of implementing transactional replication.

Site 1 : Sql server with 3 Databases

lets just call them DB1 to 3



Site2 : Sql server with 1database

DB1 replicated from site 1



Site 3: Sql server with 1 database

DB2 replicated from site 1



Site 4: Sqlserver with 1 database

DB 3 replicated from site 1



site 5: Sql server with 1 database

DB1 replicated from Site2



I need to know how to go about this

Site 1 will be Publisher/Distributor

Site 2 will be subscriber from site1, but site 2 needs to replicate the same DB down to site5, can site 2 be subscriber and distributer

Site 3 and 4 will subscribe to site 1

The various sites must also be able to update the DB back up to Site 1

The things i need info on is the security settings

The servers are stand alone servers and dont have active directory

Need info on how to configure the Various service accounts, must they be windows accounts.

Is there any content i can read up on to help me on this

Hope all this info helps

Any help would be Great

Thanx a lot

View 7 Replies View Related

How Do I Measure Bandwith Usage For Merge Replication SQL 2005 To Sql Server Mobile 2005?

Jan 24, 2007

Hi,

Is there any way to measure bandwith usage during merge replication between sql server 2005 and sql server mobile 2005 running on a cradled wm5 mobile device.

Attaching the windows performance monitor to the network connection established over usb would work although I was wondering if there was something specific for this case integrated into Sql server 2005 / sql server mobile 2005 / Sql server management studio / third party tools that i could use ?

thnx,

pdns.

View 4 Replies View Related

Merge Replication - SQL Server 2005, Windows Vista And MSVisual Studio 2005

Jul 11, 2007

Hello everybody!

I hope that someone could help me.

I have a problem when i start sincronyzing with the emulator of MSVS2005 to SQL2005 in Windows Vista. I have the same program in the emulator, but sincronyzing with windows XP Pro and no problem...



I configure the connection to use the IUSR.



The source code that i use:



repl.InternetUrl = @"http://laptop/SQLMobileIIS/sqlcesa30.dll";

repl.Publisher = @"laptop";

repl.PublisherDatabase = @"database";

repl.PublisherSecurityMode = SecurityType.NTAuthentication;

repl.Publication = @"Pubdatabase";

repl.Subscriber = @"SQLMobile";

repl.SubscriberConnectionString = @"Data Source='" + nomeFicheiroBD + "';Password='3409'";



The error that returns is:



"Failure to connect to SQLServer with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect"



Does anybody knows what i can do?

HELP I NEED SOMEBODY TO HELP!

Thanks!

View 1 Replies View Related

Help Needed In Accessing A Sqlserver 2005 Express Edition From Client Systems To The Server 2003 R2 Servermachine.

Dec 4, 2007



hi guys,
i am having a truble in accesing the server 2005 express edition from my client systems in server 2003 domain network. i get server not accessible. whereas my domain is working perfectly. i really tried alot and i have enable tcpip and named pipes and i am still getting errors while accessing. i have also created user reights for the domain users to access sql server. still i couldn't able to access it.

please guide me to over come this issue. I'm dying.My project is similar to SAP concept project and i am doing it in a production industry.so each department as to access the data from the server to update their daily production and activity.

thnks in advance.

Viswa

View 1 Replies View Related

Merge Replication And SQL 2005, Help!

Aug 9, 2006

I'm working on developing a software solution using Mobile Client Software Factory. Let me start off by saying that this package is such a dream come true, and I can't thank the guys who put this together enough. The problem is, we're having some trouble getting the components setup for the Framework to work, in particular with SQL Server 2005.

We have SQL Server 2005 running on a machine, and we have our entire database setup what I believe to be correctly. Last night, we tried for hours to get the PPC Emulator running our version of the Framework to connect to the SQL Server (On another machine on the same LAN) to no avail. 

We published just one table to test with, we have the snapshot created, the web service is running, and yet it won't let us connect. The most common error we're getting from the webservice log is: Hr=80004005 ERR:OpenDB failed getting pub version 28627. 

I was just wondering if someone out there had a guide for correctly setting up SQL Server 2005 for merge replication so that remote clients can access the server through the webservice over the internet. If there is anything specifically special that needs to be done to work with the Mobile Client Software Factory, that would be great to know too. 

I'd really appreciate some help, and thanks in advance to anyone who can lend some advice.

Cheers!

Andrew

View 1 Replies View Related

SQL 2005 Merge Replication Question....

Mar 5, 2007

Is it possible to set some tables to PULL and others to PUSH from within the same replication job?
 Dave

View 1 Replies View Related

Support For 3.5 Merge Replication Under SQL 2005

Dec 3, 2007

Been fiddling with SQL 2008 Nov CTP but now looking to deploy a 3.5 .sdf project using Merge replication. The 3.1 install sets up a web endpoint with sqlcesa30.dll and I have been running subscriptions just fine with that. But now I want to be able to create and manage my 3.5 publicatoin/subscriptions under SQL 2005 management console. I don't see how to create 3.5 sql ce databases there or to use 'configure web synchronization' to get a 3.5 web endpoint for syncing.

Am I missing something simple like registering the SQL CE 3.5 dlls to work with SQL management studio?
I want to install to this a production server with the smallest possible footprint and I do NOT want to inject SQL 2008 Nov CTP bits into that production server at all! So, how to deploy SQL CE 3.5 to production and set up 3.5 merge replication under SQL 2005?

View 9 Replies View Related

Merge Replication With SQL Mobile 2005

Apr 25, 2006

We are using SQL 2005 (SP1) and mobile agents with SQL Mobile. We are seeing an excessive number of updates after a device's database is reinitialized. My understanding was that if I generate a new snapshot or reinitialize all subscriptions (from the server), the devices would only get what the database looks like right now (inserts only) after syncing and/or reinitializing, but they are actually getting the right number of inserts and a huge number of updates in Replication Monitor. Any ideas?



Thanks!

Mike

View 23 Replies View Related

Synchronizing DDL On SQL 2005 Merge Replication

Apr 18, 2007

Hello

We're deploying the db schema changes via an T-SQL Script.

Now I've tried synchronize the changes to the (merge-)subscriber via replication.



I've tried something like that:

create table dbo.c(
c_id uniqueidentifier rowguidcol not null default newid(),
c_desc nvarchar(60) not null,
constraint PK_c primary key (c_id)
)



// table a already existed

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FK1_c_a]') AND type = 'F')
ALTER TABLE a DROP CONSTRAINT [FK1_c_a]
go

exec sp_addmergearticle @publication = N'publicationname', @article = N'c', @source_owner = N'dbo', @source_object = N'c', @type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop', @creation_script = null, @schema_option = 0x000000000000CFF1, @article_resolver = null, @subset_filterclause = null, @vertical_partition = N'false', @destination_owner = N'dbo', @auto_identity_range = N'false', @verify_resolver_signature = 0, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @force_invalidate_snapshot = 1
go

alter table c
add constraint FK_a_c foreign key (a.col2)
references katalog_zivilstand (c)
not for replication




In the end, the update runs well on the publisher (Replication compabilitiy-Level =90, Replicate DDL =1). But on synch to the subscriber, the Foreign Key cannot be propagated "FK.. references invalid table... error number 1767)



I don't see why?

Aren't the statements executed in the same order as on the publisher - in my script?



Thanks much for your advice

Aline

View 3 Replies View Related

SQL Mobile 2005 Merge Replication With SQL 2005 X64

Jan 10, 2006

I am having trouble with getting Merge Replication up with a new SQL 2005 x64 Server.  I already have it running smoothly with SQL 2005 Mobile and SQL 2000, but I want to migrate the backend to SQL 2005.

There is a front end server running IIS 6.0 on Windows 2003 x86 Standard (Server A) with SSL required and Basic Authentication set as the only authentication method (default Domain and Realm are also set).  The backend server is running SQL 2005 x64 Standard on Windows 2003 x64 Standard (Server B).  The mobile devices are running Windows Mobile 5.0 with SQL 2005 Mobile (Client).

From both a standard Windows XP workstation (IE) and the Client (PIE) I can browse to the HTTPS site on port 444 for the sqlcesa30.dll on Server A and get a response in normal and ?diag modes.  The ?diag gives success in all but the 8.0 Database Reconciler (I didn't install the SQL 2000 replication components on Server A, my understanding is they should not be needed).  Sql Server Module Versions are as follows: sqloledb.dll - 2000.86.1830.0, 9.0 replrec.dll - 2005.90.1399.0, 9.0 replprov.dll - 2005.90.1399.0, 9.0 msgprox.dll - 2005.90.1399.0.

The publication on Server B is enabled for Web Synchronization and the Snapshop Agent has run.  There are no reported errors on that side.

Since I ran into issues getting synchronization to run I have opened up permissions fairly wide in this test environment.  The PAL and the Database both have the user I am attempting to sync with listed (I am using the Administrator account in the test domain).  I have opened full rights on the Snapshot share on Server B to Everyone (both NTFS ACL on the directory and the Share permissions).  The virtual directory on Server A containing the sqlcesa30.dll has also been opened very wide, and the Application Pool does have access to it.

The Client is running a custom C#.NET 2005 application.  With the exception of changes to set the application to sync with the Server A/B duo instead of the production servers, this application is identical to the code currently being used in production with SQL 2000 Standard.

The persistent error that I am getting from the Client being thrown by SQL 2005 Mobile when I attempt to sync is "An instance of the SQL Server Reconciler error object cannot be created.  Try reinstalling the replication components."

SQL 2005 Mobile components on Server A are set to Logging Level 3 but not returning any errors that I can see to troubleshoot with.  All replication components appear to be installed and operational, although I have re-installed them.

Besides the obvious question of how to solve the error listed above, is there an issue with am x85 front end IIS 6.0 server talking to an x64 back end SQL 2005 server for merge replication over HTTPS with Windows Mobile 5.0 clients running SQL 2005 Mobile?

Based on everything I have read off the MSDN Forums, MSDN articles, BOL, log files, and web searches it seems like I should have an operational synching topology, however clearly I have missed something because I have yet to get a successful subscription through to the publication.

Any insight/enlightenment would be greatly appreciated, this seems to be a very narrow and specific area of replication and mobility and information is relatively sparse from my experience so far.

Thanks!

 

 

View 5 Replies View Related

Merge Replication Issues SQL Server 2005 X64

Jul 12, 2007

Hi ppl,

I have installed SQL Server 2005 x64 Enterprise edition with Service Pack 2 on a Windows Server 2003 x64 Standard Edition with Service Pack 2.

Now I have to configure Merge Replication that will work with SQL Server Compact Edition database on Windows Mobile devices.

Distributor and the Publisher are the same server.

IIS 6.0 is installed on the windows server. I have installed the SQL Server Compact Edition Server tools on the server. However the compact edition server tools are only available for 32bit servers and I have also found out from the article http://support.microsoft.com/default.aspx/kb/912430 that you cannot replicate data from SQL Server 2005 to SQL Server Compact Edition by using the 64-bit version of IIS.
So if this is true does that mean I can not use merge replication on 64 bit server? Does that mean I have to get another 32 bit server with 32 bit IIS on it to make this work or is there another work around. Am i missing something here?

Regards
Nabeel

View 1 Replies View Related

SQL 2000 -&&> SQL 2005 Merge Replication Compatible?

Apr 19, 2006

Hi everyone,

I'm currently trying to setup a SQL 2k (SP3, build 922) merge replication publisher and a push subscription to a SQL 2005 (RTM release, no hotfixes) subscriber. The distribution database resides on a separate SQL 2k server (SP3, build 1007). I get the error below leading me to believe merge replication is not compatible between versions.

Replication-agentclassname: agent Agent_Name failed. Procedure or function sp_MSupdatesysmergearticles has too many arguments specified..

I say this b/c I've tried SQL 2005 32-bit and x64 subscribers and both give the same error. Anyone have any ideas if this is by design or just a bug that will be fixed later? Thanks.

LSC

View 1 Replies View Related

SQL 2005 Merge Replication Extended Logging

Feb 6, 2007

Hello, I'm trying to enable extended logging to resolve some conflicts. After doing a google search, I found KB312292 which says to follow what's in books online on modifying the merge agent. I tried to add the following parameters in the agent profile:

-Output x:merge.txt

-OutputVerboseLevel 2

When I save the profile I get an error saying that SQL was expecting an integer for -Output. I've also tried using the -OutputMessageFile parameter but this didn't create the file, nor did it change the logging detail in the msmerge_conflicts_info table.



Any help would be great.



Thanks,

View 6 Replies View Related

SQL Server 2005, MS Access Merge Replication?

Feb 13, 2006

Hey there!



In a nutshell, I want to do a merge replication with a SQL Server and
several Access databases. I haven't been able to find anything in the
documentation or 3rd party books.



Is this possible?



Thanks!

- Erik

View 7 Replies View Related

Merge Replication Issue SQL Server 2005

Jul 18, 2007

I have set up merge replication with 1 publisher and 1 subscriber. Distribution is handled by a 3rd server.



I can generate a snapshot at the publisher and apply it to my subscriber. But when I insert some data (approx 30,000 rows) , the Synchronization agent gives the following error when It runs:


The merge process is retrying a failed operation made to article 'xxx' - Reason: 'The Merge Agent was unable to synchronize the row due to one or more unanticipated errors in the batch of changes. When troubleshooting, increase the -OutputVerboseLevel setting, restart the agent, and check for and resolve any errors generated by the database engine. '.





I have increased the OutputVerboseLevel setting and specified a file path in the -OutputMessageFile but the File is not being populated. All the references on books online say to put the file path in the -Output parameter but when I do it says it can only hold an integer value. So I cant see the errors generated by the database engine.



Could anybody please assist with this issue?



Thanks

View 1 Replies View Related

Merge Replication Differences Between SQL 2000 And SQL 2005

Jun 27, 2006

We have developed a mobile system that uses merge replication for SQL Mobile to SQL 2005. Previously we have developed mutliple mobile systems using merge replication for SQL Ce to SQL 2000.

Based on the knowledge we had gathered over about 4 years, we applied the synchronisation parameters for the SQL 2005 solution as we would for the SQL 2000 solution.

We have found there are some differences. Not too surprising I suppose, only some of these have us a little baffled.

For instance, there was a little flag called keep_partition_changes in SQL 2000 that is supposedly superceded by the use_partition_groups flag. However, if you don't set up your filtering to conform to the standards required by the use_pre_computed_partitions flag if you want it set to true, then the use_partition_groups flag gets set to false - also the @partition_options falg gets set back to 0 (static or non-unique data) when we want it at 3 (Single Parition, One subscriber).

To top it all off, when you get the use_partition_groups flag working, there are restrictions on which columns you can update on the device. WTF? This seems ludicrous, to be unable to update data at the subscriber - particularly information that allows you to effectively "delete" data from your subscription.

Examples of the current behaviour are as follows,

On initialize for a subscriber, the subscriber will receive their own data as inserts, plus exact multiples of that data as updates. Say there are 100 rows in TableA, the subscriber gets 100 inserts, plus 6000 updates. TableB has 20 rows, the subscriber gets 20 inserts, 1200 updates.

Further to this, performance goes out the window when synchronising changes. Typically the data flow will be between 5 and 200 changes in both directions for a synchronisation. We are seeing sync times in the replication monitor of over 20 seconds per user. Surely the calculations do not take that long. The tables in the database are not very large.

This behaviour gets significantly worse as we load the system. The application has an auto sync function which is timed to operate evry 10 minutes. However, now that there is in excess of 50 or so users on the system, those synchronisation times blow out to multiple minutes and the server starts to thrash. We have looked at indexing and maintenance but to no avail.

Everything still points to the merge replication setup.

So, it seems obvious to me that we are mising some key information about how to set up merge replication in SQL 2005. We woudl be very gratefull if someone could point out the errors of our ways.

Sorry for the convoluted post. Hope someone can help us.

Cheers
Steve

View 6 Replies View Related

Merge Replication Between SQL Server 2005 And SQL Mobile

Oct 18, 2007

Hi,

I'm currently investigating the feasibility of having SQL Mobile on a number of devices running Windows CE. These devices will have to synchronize with the server (either using merge replication or RDA) over GPRS. The problem I have is that some of these devices will never be online. Is there are a way of synching these offline devices? The model I had in mind was using a USB key (or some other storage device) to download the latest updates from an online device and transferring this data to the offline device, and vice versa e.g. from offline to online and then merging. Is this feasible?

Thanks

Csharper

View 2 Replies View Related

Very Basic Help Needed Please !

Mar 15, 2007

I have just started to use SQL Reporting services with asp.net but I must admit I am getting very confused !

The web server and sql server are on different boxes. When I use asp.net to access the server through a web page for database stuff it works fine. but I cannot get report viewer using serverreport to work.

I realised I had to configure reporting services on the sql box

( SQL Server machine called ABC1 and the database DB1 with a sql user and pwd )

When I go into database setup in configure report server the server appears in the drop down list and I select the database name as DB1

I then put Credentials type as SQL Server credentials and put in the user and password for DB1

When I click APPLY the SQL Server Connection dialog box appears.

If I select SQL Server as the credentials type and OK I get the error ' a connection could not be established....invalid object name sysdatabases'

If I select current user-integrated security two errors appear in the task status box

1. Current connection string is invalid and cannot be used to connect to SQL Server
2. Assigning Reporting servce rights to user - The reporting services rights were not applied properly

Can someone please help.

I looked at MSN but it all seemed quite difficult

View 1 Replies View Related

Microsoft SQL Server 2005 RTM Merge Replication Issues

Jan 9, 2007

We are trying to implement merge replication between 2 servers. Database size is around 11GB.

When I tried to create publication, snaphot agent is failing with following errror.

Error: 14151, Severity: 18, State: 1.
Replication-Replication Snapshot Subsystem: agent NBTENTSQL1X-PayDirectWeb-PayDirectPub-2 failed. The
replication agent had encountered an exception.
Source: Replication
Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
Exception Message: String or binary data would be truncated.
Cannot find the object 'MSmerge_sel_4D388


The table where the snapshot is breaking contains around 169 columns. Can someone please help me get through this error ?





View 6 Replies View Related

Weird Conflicts - Merge Replication 2005 -&&> 2000

Aug 3, 2006

Publisher is 2005 x64, subscribers SS2000 (SP3) and SS2005 x64. Pull agents, no filters on subscriptions. We are seeing many seemingly random conflicts on between SS2000 subscriber and publisher. It happens on several different tables.

One table is never editted, only inserts happening everywhere and deletes happening on the SS2000 subscriber. Deletes will sometimes generate conflict. Reason is '"he row was deleted at 'CTS11.CTS' but could not be deleted at 'cts4a.cts'. Unable to synchronize the row because the row was updated by a different process outside of replication." CTS11 is SS2000 subscriber, CTS4A is publisher.

Probably unrelated bug but when looking at conflicts on this same table in SS2005 conflict viewer, get error "ID is neither a DataColumn nor a DataRelation for table summary (System.dATA)" and then "Column ID does not belong to table summary (System.Data)". ID column is rowguid, only unusual thing about table is that it has varchar(8000) field plus some other fields.


Other tables generate conflicts with this reason "The row was updated at 'CTS11.CTS' but could not be updated at 'cts4a.cts'. The merge process was unable to synchronize the row." I enabled verbose logging in the merge agent but the log file didn't contain any further explanation.

This same topology and schema worked fine when all publishers and subscribers were SS2000.

Any insight into how to fix this would be appreciated.





View 9 Replies View Related

Merge Replication: Problems Upgrading SQL-2000 To 2005

Dec 25, 2006

Hello!

We have a module in our business-application, that automatically installs merge replication of the business-application's database, both at publisher and subscribers. It's intended for Sql-2000. Now we need it to be applicable also for Sql-2005, so the module requires some changes, because, as we noticed, sql-2005's replication technology differs from Sql-2000's one.

A few questions to experts, familiar with Merge Replication in SQL-2005:

1. Is it possible to create hybrid replication, with publisher running at SQL-2000 (MSDE) and subscribers running at SQL-2005 Express? Merge publication is not supported in SQL 2005 Express, but some users may require option to use the application at free-of-charge database platform.

2. How deep are changes in merge replication implementation at system level? Is it just modified a bit since SQL-2000, or changed entirely? This knowledge is needed, because the module uses some low-level features (executing system sp's, querying replication-specified tables, etc.). For example, when we tried to create subscription of existing publication in SQL 2005 using the module as is, we found out that sp_addmergepullsubscription_agent doesn't use @encrypted_password parameter anymore, and subscription creation process failed.

3. If anybody has experience using merge replication creation/deletion/detection scripts, generated by SQL 2000, in SQL 2005! Please, tell - what more problems may happen?

Thanks!

View 3 Replies View Related

SQL 2005 Mobile Merge Replication Size Limits?

Feb 8, 2006

I am in the process of testing a SQL 2005 Std x64 server with merge replication using Windows Mobile 5.0 clients and SQL 2005 Mobile. The test DB is a copy of the currently active DB, but has been expanded to include some new tables to support planned application functionality extensions.

Once the publication exceeds 97 Articles, the error is thrown that "The buffer pool is too small or there are too many open cursors". If I drop one article everything is fine. I ran a test with dummy DB that had 100 blank tables, and this initialized just fine on the client. The additional articles I am publishing (the 98th table) is also empty, but it throws the error anyway.

Is there a limit on the total size/number of changes that can be sent? Since I have run tests sending over 64,000 changes to a client during initialization this does not seem to be the case (I am only attempting a little more than 9,700 changes on this initialization).

Some other ideas that have been tested without success are to stop the user triggers from propagating, and toggling the AWE setting for SQL. The Replication Monitor does say the client completes replication, and it seems to choke at the very end of completing replication when it attempts to write to the tracking tables. The last successful action is sys.sp_MSadd_merge_history90, and it appears to be acting on the last table added to the publication.

There does not appear to be a limit on the number or articles, since I can publish more articles in a dummy DB than I am able here, so it seems to be something to do with size. Any information would be helpful, this is a very frustrating issue. Thanks!

View 1 Replies View Related

Alter View Hangs - Merge Replication SQL 2005

Dec 18, 2006

Background - I have a publication that propigates schema changes. I have a view in which I want to remove a column.

Error - Going by what the BOL says, I use Alter View and delete the column from my select statement. I issue the alter view command against the Publication database and it just "churns". I do not get any locking errors or any other type of error, but the statement never completes execution. I watched it run for 10 minutes and cancelled the query. Executing the same statement against a copy of the database that is not being published executes in 1, 2 seconds.

Here is what I am doing:

Old View: Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc,

table2.txt_sort_order

FROM table1 join table2 on table1.status_code = table2.status_code

The query I am executing:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER VIEW myview

AS

Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc

FROM table1 join table2 on table1.status_code = table2.status_code

View 1 Replies View Related

High CPU Utilization On Merge Replication With SQL 2005 Mobile

Jan 10, 2006

I have a question for anyone who mas some tips/pointers for optimizing SQL merge replication publications. 

The front end web server is running IIS 6.0 on Windows 2003 x86 Server Standard (Server A).  The back end database server is running SQL 2000 Standard on Windows 2003 x86 Standard (Server B).  The merge replication clients connect via HTTPS over the Internet from a custom C#.NET 2005 application using SQL 2005 Mobile running on Windows Mobile 5.0 (Client).

The publication itself has several filters on it.  The entry point uses the user's Windows username to start the filter.  Based on the user, it then filters the records in multiple tables.  There are 68 articles and 44 filter statements.  The filters extend multiple layers deep, in other words they are not all filtering off the HOST_NAME() variable, some tables filter from records in tables that filter from the HOST_NAME() variable.  The publication is set to minimize data sent to the clients, and considers a subscription out of date if it has not synced in the last 4 days.  All the rowguids are indexed as well.

There are approximately 35 clients actively using the application at any given time.  On average, a client will initiate a merge replication 3-4 times per hour from 8am-5pm.  Generally, a sync will take between 10 seconds and 2 minutes to complete, with most of them being around 30 seconds on average. 

When a client starts a sync, there is a spike to about 50% on the server's CPU graph.  If multiple clients attempt to sync at the same time the CPU utilization can be pushed to 100% for extended periods (more than 30 seconds). 

I recently completed a project to increase the bandwidth available to the clients, and plan to reduce the number of filters significantly (although this will obviously increase the amount of data going to the clients and the storage needs on the individual devices).  I also plan on changing the setting to not minimize the amount of data sent to the clients.

Having said all that, does anyone have any information about how to further optimize merge publications to mobile clients?  The next publication will be on SQL 2005 x64 Standard if I can solve the issues in the text environment.  I would like to enhance the publication as much as possible to make the end user experience better than it currently is.

Thanks!

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved