Table Synchronization Across Databases On The Same Server
Oct 26, 2005
Hello Everyone
We have a third party tool which maintains its own custom
database,DB1 on SQL Server 2000 on Server A (Windows 2000). We built an
inhouse application which maintains a transactional database, DB2 also
on Server A derived from DB1. Since the DB1 is frequently updated the
synchronisation between the databases is currently maintained by using
views in DB2 which pulls in all the required data from across tables in
DB1.
But we found that the cost of employing views, referencing tables
across databases has made the inhouse application slower and so as a
apparent performance tuning measure we plan to maintain a subset of
data(for a specific time period) from DB1 in actual tables with same
schema in our transactional database DB2. But since the tables in DB1
are frequently updated during the day we are required to the
synchronise the corresponding tables in DB2 with their counterparts in
DB1. The only constraint in this synchronization being that the third
party custom database,DB1 cannot not be subjected to any changes (like
creation of triggers) as it is maintained by a different group of
personnel.
I read about Triggers,Maintenance Jobs and Replication but not sure
which would suffice our needs.Are there any recommendations on how this
could be carried out? Any suggestion would be welcome.
I have a Winform application with a database SQL CE 3.1 and remote databases SQL SERVER 2000 SP3 (or Server 2005). Now I have to make a merge replication between this databases and I never touched the synchronization.
Someone could tell me in detail the steps to do for successful synchronization.
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.
Hi All, I would like to synchronize a table to another table on different server by script. For example, tableSource, databaseSource, serverSource to tableDestination, databaseDestination, serverDestination. Thank you.
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??
We have a requirment, we have different databases in different servers, we need to syncronize the data in data bases in different server. created Partitioned on these tables.Â
We tried some options:
1) Snapshot replication 2)CDC (Change Data Capture)Â
We have two databases with same schema and tables (same table names, basically main DB and a copy of the main DB). following is example of table names from 2 DBs.
CREATE TABLE #SourceDatabase (SourceColumn1 VARCHAR(50)) INSERT INTO #SourceDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6') SELECT * FROM #SourceDatabase DROP TABLE #SourceDatabase CREATE TABLE #ArchiveDatabase (SourceColumn2 VARCHAR(50)) INSERT INTO #ArchiveDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6') SELECT * FROM #ArchiveDatabase DROP TABLE #ArchiveDatabase
We need a T_SQL statement that can create one view for each table from both the databases(assuming both databases have same number of tables and same table names). so that we can run the T_SQL on a thrid database and the third DB has all the views (one view for each table from the 2 DBs). and the name of the view should be same as the tables name. and all 3 DBs are on the same server.
the 2 temp tables are just examples, DBs have around 1700 tables each. so we ned something like following for each table.
CREATE VIEW DBO.TABLE1 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE1] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE1] CREATE VIEW DBO.TABLE2 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE2] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE2] CREATE VIEW DBO.TABLE3 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE3] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE3] CREATE VIEW DBO.TABLE4 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE4] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE4] CREATE VIEW DBO.TABLE5 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE5] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE5] CREATE VIEW DBO.TABLE6 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE6] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE6]
I need to create a new table on a SQL Server 2005 Database, but the data needed for the table is kept in different databases on different servers.
Half the data is on the SQL 2005 database, so that shouldn't be too much of a problem, but the other half is on a SQL Express database on a different server, is it possible to write a T-SQL query to retrieve this data on the other server?
Normally I use a create table + insert into kind of query to create custom tables but I don't know how to connect to the SQL Express server/database using T-SQL (if it's possible at all?) to do this.
Any tips, hints, ideas very welcome. Please use small words and short sentences.
Hi, In SQL Server 2005 Express I currently have 2 databases which I€™m working on, my problem is I want to transfer a table from one of the databases to the other but don't know how to go about achieving this? I want the table structure and all the info held within the table transferred across. Any ideas are welcome.
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.
Say: Server "A" contains "123" database. Server "B" contains "123" database.
But changes is made on Server A. I want that every changes made on server A "123" database should reflect on Server B "123" database on real time basis.
whether it is done through replication or any other way, i want practical answer so that i can try it out.
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.
I have two SQl Server 2000 in two different location, at one point I wand to Transfer the information in one table (in Sql server 1) to the same table (in SQl server 2) using Stored Procedure.
Can any one through some input on how to write the SP?
We are working on data synchronization application. This application will synchronize data which are stored in flat file on several client machines generated by windows application to central database (Probably My SQL or SQL Server).
This application/agent will continuously monitor all the modifications done by windows application on client machine and then synchronized data with the central database.
Currently we are looking for unidirectional synchronization i.e. from client to server only.
If you have worked on similar kind of project, it would be great help if you share technology/architecture used in your application.
I have a VB app which stores information in an Access 2000 db. The VB apphandles multiple users (it's a logbook type of application, users sharelookup tables but don't share information among themselves - IOW, I couldn'tsee your log entries and you couldn't see mine).We're adding a web interface to the package - basically a scaled down dataentry interface to allow users to add log entries without being at theirworkstations. The web version will be storing data in a SQL Server 2000 db.I need to be able to synchronize data between the two - if a usersedits/adds a log entry in the desktop version, they need to be able tosynchronize the desktop and web (this is done via a button click, no"realtime" updates). Same scenario with the web side, but the web interfacewon't acutally be able to synch with the desktop - the user would have toinitiate the synch from their workstation. We've pretty much worked out thesynch logic path, and we're working on acutally interfacing with the SQLServer.Can this be done reliably using a standard ADO connection between thedesktop and the web site? I would imagine traffic would be somewhatminimal - on average, no more than 100 records would be added or updated.I've done some testing on this, and with 20 - 50 records it seems to workfine both ways.Suggestions of better/more reliable methods would be most appreciated, andthanks for your time
Hi I am stuck while doing synchronization between SQL Server 2000 and SQL Server CE 2.0. getting an Error "Header information is either corrupted or missing."
My Specification is follow : Windows XP with SP2 Visual Studio 2005 Enterprise edition SQL Server 2000 (SP3a) SQL Server CE 2.0 Application is Smart Device application (Pocket PC 2003) using C#. Microsoft ActiveSync 4.5 IIS 5.1
I followed steps as per given in this URL : http://msdn2.microsoft.com/en-us/library/ms839425.aspx#sql_serverce_replication_net_topic2I performed eeach steps successfully, My code is as per given below.
I am not getting whats going wrong. My Firewall is disable and i have not installed any antivirus softwares (including Norton internet Security....in many forums i got this suggestion but i think its not an issue,even though for solving this issue i performed new installation of Windows XP SP-2 )
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....
How can I keep a production SQL server and DR SQL server in sync? I'm not referring to data - I can use SAN replication for that. I'm referring to the many configuration settings and objects that are external to the databases..Server settings (xp_cmdshell, etc.)LoginsServer- and database-level rolesServer- and database-level permissionsObject level permissionsMaintenance plans/SSIS packagesSQL Agent jobsJob schedulesDatabase mail configurationObjects (proxies, alerts, credentials, operators)Linked serversScheduled tasksODBC connections
I realize that many of these items can be scripted out, and periodically run on the DR server; but not all of them can be. And some, although they can be scripted, may be problematic to restore on a DR server, such as SQL Agent jobs. And even with backup scripts, it seems like developing a process to refresh a DR server would be overwhelming given the number of elements and exceptions. I'm not adverse to doing the work to create a completely scripted solution, but was curious if one already exists that handles all or most of the elements in my list.
I need to move my database servers from one location to another. The issue is that I have over 200 databases to move and my clients can't afford a downtime. The collective volume of all the databases is over 2.5 TB and growing.
I am thinking to copy these databases in batches over the WAN to the new location and replicate them using Transactional replication till I have all the databases moved and synchronized.
Will it be wise enough to use replication for synchronizing 200 databases or is there a better approach which I can use to move these databases with minimum downtime and compromise on performance of applications.
Note: Migration is from SQL Server 2000 to SQL Server 2005.
I create a distributed database for mobile application. I replicate a table that distribute on mobile device. I follow instruction how to create distributor, publication, replication, web synchronization, and subscriber database. I have done fine for synchronization between mobile database into desktop database (in this case SQL Server 2005 Standard Edition). But the problem is how can setup publication so it can bidirectional, not only from mobile database into desktop database, but also from desktop database into mobile database. So in the mobile database can have same data with desktop database even on mobile database lost some old data.
Its like data exchange between both engine. Desktop and mobile have same data. For filtering I can put filter on the desktop server for replicated table, so don't worry how I split the data.
I have a master database containing data from many client databases located in various locations. I have to synchronize data between the master & client databases. If data in client changes, the master needs to be updated. If the data in Master changes, the client needs to be updated.
Because of the security issue, the client location will initiate this process of Synchronization. It has to be automatic process scheduled once everyday. The Client databases are SQL-Express Databases and the Master is Sql Server 2000 database.
What is the industry standard or the best way to achieve this task ? Are their any tools that easily accomplish this? Can SQl Server Replication achieve this ?
I am running SQL Server 2000 and SQL Server CE on the handheld device. I have created the publication on the SQL Server.
I can subscribe to the publication successfully on the handheld and it creates the .sdf file just fine.
When I then attempt to synchronize, the process will run for a long time and eventually error out. (The database has a couple large data tables that are used for value lookups)
The error says the connection was broken.
Any suggestions on correcting this?
I am thinking of breaking the replication database into 2. One with the large lookup files and one with the updated tables. I still need to get the large database with the lookup tables onto the handheld though.
Any thoughts, advice or similar experience handling a situation as this would be appreciated.
We have a window based application which uses sql server express as a backend...
Currently, We are sending xml files to the users to update their application with data users also sends data in xml format to us.
We receive data from various users and collate it on a central server for analysis..
We prefer having sql server express as a local data store so that user does not depend on live connection to the central server. But we would like to have a on demand syncronization or scheduled snyncronization with central sever ,,,
There are many approaches to it... my question is which is the best for our given scenario. Is it using web service or sql sever broker or sql synnchronization.
If you can direct me to some practical implementation then it will be great help.
- SQL Server on server - SQL Server Express on notebooks - SQL Mobile on Windows Mobile 5.0 devices.
How can I create a replication between SQL Express and SQL Mobile ? SQL Server express cannot act as a Publisher server.
In this post a Senior Program Manager says It is possible but I don't find how to : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=55236&SiteID=1
I'm updating some tables in a subscriber database with a stored procedure. After the tables get updated I'd like to sync them with the other subscriber dbs and the publisher db in that same stored procedure.I can do it manually in SSMS with the View Synchronization method. Are my only alternatives a batch job or C#?
