I'm currently working on a Database Synchronization project and I wondered if anyone had any ideas other than what I've got so far. The context of this project is that we have a central server which stores data for 10 different locations in a consolidated database. Each store contains a replica of that database, but with only data relevant to their own operation.
Store owners can change data at the store level or chain owners can change data at the central level, but for either change the other level has to be notified. I currently have 2 ideas on how to do this.
Note: I am using MS SQL Server 7 for the central, but MSDE (Desktop Edition) for the store levels. So, replication from the store to the server is not available. I think I need to actually code this on my own.
I have 2 distributed databases which need to be synchronized at regular basis. I plan to write a service that does it. But I'm just curious how to do the update incrementally. Thanks
I have a website that is running on the intranet and on a notebook. I have a notebook that is not always connected to the internet.. when it is not connected, it will read in some data from a barcode reader; and when it's connected, it will synchronize the data with the db on the intranet.. any idea on how to achieve this?
Hi All, I want to do SQL Server DB Synchronization. The scenario is like this.. DataBase is presently on SQL Server 2005, When end user click on button or on some action event all data to from the server should be fetched & should be saved on client m/c. Afer that when user click on button or on some event, DB should be synchronized. i.e. if there is any change in Server Data, client data should be automatically update... How can i achieve this using C#.. Plzzz Help!!!!!
Once I go to client site (where production DB is located) I want to generate a DataSet (which I am able to do) and compare this DataSet with the DataSet created when I read in the XML file.
## 'read in XML file XMLDataSet.ReadXML(Server.MapPath("xDataDictionary.xml")) ##
There is a unique identifier in the table and XML file called 'FieldID'. What I need to do is compare DataSet (from Production DB) with DataSet from XML file.
If there is a 'FieldID' in the DataSet from XML file that IS NOT in DataSet from ProductionDB I want to insert this node/row into the database.
I was wondering if anyone knows if there is any software available or if it is easy to implement the following.
We are running a SQL Server database locally in house and also have the exact same database running on a hosted webserver. The data changes on both versions of the database daily and what we need to do is figure out a simple easy / cheap way to have the two databases synchronize with each other every evening so that the data is exactly the same every morning.
Hi Guys: I am trying to synchronize my home sqlserver database with a database on my web hosting company. I am not really sure how to go about it, any suggestions?
I am using SQL Server 2000 Ent/Dev edition. I have a 2 identical database in 2 different servers and i'm required to synchronize both database. However, i need to exclude some of the tables in during this process.
I tried to search this forum but can't find any solution. How can i perform the above task? BTW i can't use trigger as i have more than 100 tables.
Hi, I am having this scenario; I have 2 SQL Server 2005 databases, they are basically identical in structure. The first one is used by my web application and the other is by desktop application ... I created the web db for security reasons ,.. i dont want online users to have access to the major backed (desktop db) directly so the web app is writing to the web db ... now, I want to update my major backend as transactions to the web db happen and vice versa to keep the users of both applicaions happy ... Is there a good design patterns for that? I am thinking of using db triggers for this but not sure on the implementation details ... Your help is much appreciated
Our company has an enterprize site (a very large one). From time to time we need to make changes in it. In other words, we had to synchronize one database (local where we make all changes) and database on hosting. So, we did it manually (wrote scripts and inserted them in database on hosting). But as the databases grew it became impossible to synchronize everything manually. So we need some tool which could cope with this task. The problem is that, as I know, there are many tools which can do it, but we are tight in time and can't evaluate them all. Please share your ideas on what tool is worth evaluating?
I am not a SQL developer by any means. I have a application to build for a Valet company that will require a laptop on site with no internet connectivity to function locally. There will be about 15 locations that all operate independently. After the day is done, the laptops will connect to the internet and upload all data to a hosted SQL server on the internet for centralized data storage and reporting. After a successful upload, the client database will be purged and start fresh the next day. Should this be doe with MYSql on the client and SQL server at the data center? I have some people recommending Progress database but it is expensive. I am out of my element when it comes to syncing db's so any advise is appreciated. Thanks-
Dear All, I’m having three databases with same data in three different locations. Now three users adding records to the particular databases respectively. I want all the data from server A to be there at aerver B and C. all the updations at server B should be there at Server A and server C. as well as same updations at C also. It is not possible to implement replication as all the servers are at long distance. How can I update every database with the other server’s data? I’d like to update day wise. Is there any tool to generate script on that particular date period? Experts, I think you understood my scenario, please let me know the tools or list of best possibilities for me.
I’m thinking of Incremental backup. Guide me in this scenario.
I have two SQL Server Database, one is at my company and one is at my company's customer. There are two related databases and two related tables in the servers. When I make a change in the table at my database, I want these changes to be done at the other server. I mean the related tables should have the same records all-time. I made a search and found that "Linked-Servers" is a good idea in this scenario. I made a linked server and run the following query in the Query Analyzer;insert into SAMPLE_LINKED_SERVER.SAMPLE_DATABASE.dbo.SAMPLE_TABLE(id,name) Select id,name From MY_SAMPLE_TABLE Where id Not In (Select id From SAMPLE_LINKED_SERVER.SAMPLE_DATABASE.dbo.SAMPLE_TABLE)This query works as I wanted. But I want this process to be done automaticaly without user interaction. Should I use Trigger? Do you suggest using Linked Servers?Or is there any other methods to make synchronization between two SQL Server Database?If I cannot find a good method in SQL Server, then I will be forced to make this synchronization by writing .NET code, but that would be difficult I think. Any suggestion and comment is wellcome.Thanks in advance.
We need an architecture in which one office is in pune and other in mumbai,
principal server is in pune and mirrored in mumbai,
now we need an architecture in which mumbai user will performing read only operation from mumbai server (local server )and write operation in pune server(remote server)
and pune user will be performing read and write operation in pune server(local server)
now when link between pune and mumbai goes down, mumbai users will not be having acess to pune server so at that time mumbai user will not be performing write operation in pune server (local server) and when link goes up we want mumbai server to get updated with pune server.
how this is possible or any other different solution to this, what mandatory is we want mumbai users to perform read operation from mumbai server ONLY.
Just wondering if someone can tell me exactly what is happening during the "synchronizing" phase during db mirroring. Basically I have 2 questions regarding restoring and synchronizing.
1. Assuming there is no initial mirroring setup. A backup is taken from serverA. After the backup (and before the restore on serverB), there are several INSERTS being performed on the serverA. After which, the backup is restored to serverB and the servers are mirrored from A to B. Does the synchronizing state mean that all transaction logs are being synchronized and serverB will now have those newly inserted transactions?
2. Does this mean that as long as the transaction log is not truncated on serverA, serverB will always have an exact copy of the serverA database?
Hi there, I need to create a synchoronization with the Server Mobile (displayed in the Pocket PC emulator) once the website (SQL server Database) is updated. Is there any coding available? How is it possible? PLSSSSS HELP ME....
Hi all, Well, I have created an applicatin in asp.net and it connect to MSSQL database, and it work fine.
There is another application which was developed in Oracle and the database is oracle 8i.
Well, wat i want is whenever a form is submitted from the asp.net application, the data is inserted in the MSSQL server database and the Oracle8i database also.
There is some strange behaviour i've recently noticed while watching synchronization progress in Replication Monitor on SQL 2005 Server Standard with merge replication configured. The merge process seems to repeat several times.
This is the initial synchronizaion (reinitalization at the subsciber). Client is using Microsoft.SQLServer.Replication objects from .net framework assemblies.
The synchronization starts normally (status is "Running"). The last message of selected session box shows (among other messages): "Beginning evaluating partial replication filters" then "Finished evaluating partial replication filters" and finally "Merge completed after processing xxx changes... etc." after a few seconds. Status changes to "Completed" and then... the merge process starts again!! "Beginning evaluating partial replication filters" etc. And this repeats about 15-20 times.
And so whole process takes about 15 minutes instead about 45 seconds to complete initial synchronization. The number of changes is "Merge completed after processing ..." never change since the first such message.
Is this some bug in web synchronization or some invalid configuration setting? Why does merge process repeat itself so many times??
All- I'm a newbie working on a VWD project. A couple questions arose when attempting to work with a project's dataset and its underlying SQL Server Express database:
Is it true that when changing the schema of a database in SQL Server Express through the VWD Database Explorer, one must take care to manually make parallel changes in the dataset? For example, if you make a relation between two tables in Database Explorer, you must do the same with the counterpart tables in the dataset? For example, I find that if I delete or create a relationship between two tables in the database, I must manually do the same in the dataset.
I have a SQL Server 2005 Compact Edition database that synchronizes with a SQL Server 2005 Standard database via merge replication.
The problem I have is when I cancel a synchronization, the database is left in such a state that I cannot add any new records. I dug around a bit and have found that in the INFORMATION_SCHEMA.COLUMNS view, AUTOINC_MAX has been incorrectly set to the same value as AUTOINC_MIN for the primary keys, which explains the 'out of range' error I get in my program when running off such a database.
In contrast, a non-corrupted database has AUTOINC_MAX set to AUTOINC_MIN + 999, presumably because of my subscriber identity range of 1000.
If this is the only thing that has gone wrong, all I need to do is programmatically set the AUTOINC_MAX values to AUTOINC_MIN + 999, however I can't find out where to do this (and INFORMATION_SCHEMA.COLUMNS is a view so it can't be updated obviously.)
I have also heard that 'compacting' the database may help, but I don't know how to do this either!
My goal is to develop the system on a windows application and pda
There is a central server which will create a publication called inventory
The laptops which host the windows application will be subscribers to the central server using merge replication
The client now wants the PDA using SQL Mobile to synchronize with the local subscirber database on the laptop using active sync. They dont want to do it via WIFI to the IIS Server at the central server
I have been reading for days and I am still unsure whether this is possible to do.
I know Appforge provide a conduit for palm to access synchronization but not local sql databases
We have been using Sql Server 2005 Compact Edition 3.1 RDA synchronization method successfully on Sql Server 2000 database. Recently we moved the database to Sql Server 2005, sync doesn't work anymore, it just hangs on one table. On further investigation, we found out that it's the index on that table that causes this. We removed the index, it works fine. We are wondering the root cause, removing the index is not a solution for us. Any thoughts?. Thanks.
Is it possible in SQL Server to have replication happen immediately aschanges are made. That is, a change is made on server A, and that change isautomatically applied to server B, rather than the replication happening atset intervals?Thanks.
Hello guys! I went through the bunch of documentation pages about configuring the one for providing web subscriptions. The most usefull for me were:
Security Architecture for Web Synchronization How to: Configure an IIS Server for Web Synchronization (SQL Management Studio) How to: Configure a Subscription to Use Web Synchronization Topologies for Web Synchronization Securing the Publisher Subscribing to Publications Mssubscription_properties (Transact-SQL) How to: Configure a Subscription to Use Web Synchronization (Replication Transact-SQL) sp_addmergepullsubscription (Transact-SQL) Web Synchronization for Merge Replication But I still have questions about it. If you could help me with small notes about subscribing to web based merge publication I would really appreciate that!
I believe, I just need a bit of clarification. The target setting is simple as it is: a client should connect to the our hosting server via HTTPS and update his pull subscription on demand by running .NET application. Avoiding the server part, are the following steps correct and enough to accomplish what I need? 1. To create subscription, should I use the script:-- This script uses sqlcmd scripting variables. They are in the form-- $(MyVariable). For information about how to use scripting variables -- on the command line and in SQL Server Management Studio, see the -- "Executing Replication Scripts" section in the topic-- "Programming Replication Using System Stored Procedures". -- Publication must support anonymous Subscribers.-- Execute this batch at the Subscriber.DECLARE @publication AS sysname;DECLARE @publisher AS sysname;DECLARE @publicationDB AS sysname;DECLARE @websyncurl AS sysname;DECLARE @security_mode AS int;DECLARE @login AS sysname;DECLARE @password AS nvarchar(512);SET @publication = N'AdvWorksSalesOrdersMergeWebSync';SET @publisher = $(PubServer);SET @publicationDB = N'AdventureWorks';SET @websyncurl = 'https://' + $(WebServer) + '/WebSync';SET @security_mode = 0; -- Basic Authentication for IISSET @login = $(Login);SET @password = $(Password); -- At the subscription database, create a pull subscription -- to a merge publication.USE [AdventureWorksReplica]EXEC sp_addmergepullsubscription @publisher = @publisher, @publication = @publication, @publisher_db = @publicationDB, @subscriber_type = N'anonymous'; -- Add an agent job to synchronize the pull subscription. EXEC sp_addmergepullsubscription_agent @publisher = @publisher, @publisher_db = @publicationDB, @publication = @publication, @distributor = @publisher, @job_login = @login, @job_password = @password, @use_web_sync = 1, @internet_security_mode = @security_mode, @internet_url = @websyncurl, @internet_login = @login, @internet_password = @password;GO
What is @job_login parameter here? As I understand, I will not run SQL Agent job as our client uses MS SQL Server 2005 Express. And I do not want to run it as we will provide a client with .NET application for performing synchrinization on demand Parameter @publisher means full SQL Server instance name? For example, MYMACHINEMSSQLSERVER? What user will be used to connect to the distributor and to the Publisher? Will it be @internet_login? Do I need running sp_addmergepullsubscription_agent if I€™m going to use the following .NET application code to synchronize over the Internet?
2. To perform synchronization with our hosting servers, should a client use the following code? // Define the server, publication, and database names. string subscriberName = instanceName; string publisherName = instanceName; string publicationName = "AdvWorksSalesOrdersMerge"; string subscriptionDbName = "AdventureWorksReplica"; string publicationDbName = "AdventureWorks"; string hostname = "adventure-works\garrett1"; string webSyncUrl = "https://" + instanceName + "/SalesOrders/replisapi.dll";
// Create a connection to the Subscriber. ServerConnection conn = new ServerConnection(subscriberName);
// If the pull subscription exists, then start the synchronization. if (subscription.LoadProperties()) { // Get the agent for the subscription. agent = subscription.SynchronizationAgent;
// Check that we have enough metadata to start the agent. if (agent.PublisherSecurityMode == null) { // Set the required properties that could not be returned // from the MSsubscription_properties table. agent.PublisherSecurityMode = SecurityMode.Integrated; agent.DistributorSecurityMode = SecurityMode.Integrated; agent.HostName = hostname;
// Set optional Web synchronization properties. agent.UseWebSynchronization = true; agent.InternetUrl = webSyncUrl; agent.InternetSecurityMode = SecurityMode.Standard; agent.InternetLogin = winLogin; agent.InternetPassword = winPassword; } // Enable agent output to the console. agent.OutputVerboseLevel = 1; agent.Output = "";
// Synchronously start the Merge Agent for the subscription. agent.Synchronize(); } else { // Do something here if the pull subscription does not exist. throw new ApplicationException(String.Format( "A subscription to '{0}' does not exist on {1}", publicationName, subscriberName)); } } catch (Exception ex) { // Implement appropriate error handling here. throw new ApplicationException("The subscription could not be " + "synchronized. Verify that the subscription has " + "been defined correctly.", ex); } finally { conn.Disconnect(); }
What does HostName parameter use for? What is that host? Subscriber€™s machine name instance name? Or Publisher€™s? Or Distributor€™s? Why parameters PublisherSecurityMode and DistributorSecurityMode have value SecurityMode.Integrated? How it is used in the replication model for web synchronization? As I understood from the documentation, client (Merge Agent) should connect to the Subscriber, get the changes, connect to hosting server via HTTPS and perform transactions through the SQL Server Replication Listener, thus the internet login to the IIS should be used to perform all the operations on hosting server side? The same internet login will be used to get snapshot from UNC share? If I€™ve chosen to use FTP to provide with snapshots, should I then provide ftp parameters to sp_addmergepullsubscription_agent stored procedure or they will be used automatically by ISAPI filters on hosting server? Thank you.
I'm having to build a smart client application that works with an offline sql express database on the client. And works with several types of databases - that may be configured to work as a central server. The list includes the sql server family including 2000, 2005 and express and also IBM DB2.
I have to address some rather complex merge replication scenarios. By Merge I mean the ability to deal with handling and synchronization of bi-directional changes. More specifically changes to data may be made to any database in the system. either local or central.
I'm afraid, I cannot use sql replication in a heterogenous mode for several reasons. Some being - the database is being auto attached on the client, the application is expected to live up to the 'low impact' install ideology and should not need any major configuration etc.....
Are there any recomended approaches/Ideas one may use to acheive replication. Any links/information/ideas will be great.
I'm developing a Windows Mobile solution (Pocket PC Win 5-6). I have Sql Server CE on the device and I use RDA (Remote Data Access) to pull the tables to the Sql Server CE. I use RdaTrackOption.TrackingOffWithIndexes so I can Alter the table design. Then I use the function SubmitSql to push back my changes. On the server I have a table for example Customers, it has a column called CustomerId with identity(1,1). Let's say the highest Id is 100 and the lowest is 50, when I add a record in the Sql Server CE it gets CustomerId = 1 then next one 2 etc which means identity is working but does not continue after 100. I don't need to use the CustomerId when I push my data back because I have sql syntax taking care of @@IDENTITY when inserting new records. Anyone who knows why it begins on 1 and not 101?
i want to create a web application architechture which having multiple web server. this all web server having sql server database. now what i want is to Keep all data in all databases. means i want to synchronize my all sql servers. it is also possible ki my one webserver can have multiple clusted database. hope to hear soon from u people.
