SQL Server full data synchronization and value analysis [Final Version], server Value Analysis

SQL Server full data synchronization and value analysis [Final Version], server Value Analysis
[Final Version] All rights reserved for full data synchronization on SQL Server. Please indicate the source for reprinting. Thank you! After two days of simultaneous writing and testing, the first Release version was released:

1. This function only supports one-way synchronization, that is, to synchronize multiple slave databases from a primary database

2. Any addition, deletion, and modification of the primary database will be synchronized to all slave databases.

3. the most important thing is the value of Database Synchronization: when the master database server is unavailable, the program can use other slave databases or backup databases, which will be of great value to public and private cloud applications in the future!

Code:

<span style = "font-size: 18px;"> /// <summary>
        /// Note: for columns, the first string must be primary key name!
        /// </ summary>
        /// <param name = "server"> </ param>
        /// <param name = "database"> </ param>
        /// <param name = "uid"> </ param>
        /// <param name = "password"> </ param>
        /// <param name = "tableName"> </ param>
        /// <param name = "columns"> </ param>
        /// <param name = "ignoreUpdateColumns"> </ param>
        /// <param name = "ignoreInsertColumns"> </ param>
        public void BulkUpdateTo (string server, string database, string uid, string password, string tableName, List <string> columns, List <string> ignoreUpdateColumns, List <string> ignoreInsertColumns)
        {
            string primaryKeyName = columns [0];
            string connectionString = "Server =" + server + "; Database =" + database + "; User Id =" + uid + "; Password =" + password;
            // Create destination connection
            SqlConnection destinationConnector = new SqlConnection (connectionString);

            SqlCommand cmd = new SqlCommand ("SELECT * FROM" + tableName, destinationConnector);
            // Open source and destination connections.
            this.EnsureConnectionIsOpen ();
            destinationConnector.Open ();

            Dictionary <int, string> Index_PrimaryKeyValue = new Dictionary <int, string> ();

            SqlDataReader readerSource = cmd.ExecuteReader ();
            Dictionary <string, Dictionary <string, string >> recordsDest = new Dictionary <string, Dictionary <string, string >> ();
            int i = 0;
            while (readerSource.Read ())
            {
                Index_PrimaryKeyValue.Add (i, readerSource [primaryKeyName] .ToString ());
                string recordIndex = Index_PrimaryKeyValue [i];
                recordsDest [recordIndex] = new Dictionary <string, string> ();
                foreach (string keyName in columns)
                {
                    recordsDest [recordIndex] .Add (keyName, readerSource [keyName] .ToString ());
                }
                i ++;
            }

            // Select data from Products table
            cmd = new SqlCommand ("SELECT * FROM" + tableName, mySqlConn);
            // Execute reader
            SqlDataReader reader = cmd.ExecuteReader ();
            Dictionary <string, Dictionary <string, string >> recordsSource = new Dictionary <string, Dictionary <string, string >> ();

            Dictionary <int, string> Index_PrimaryKeyValue2 = new Dictionary <int, string> ();

            int j = 0;
            while (reader.Read ())
            {
                Index_PrimaryKeyValue2.Add (j, reader [primaryKeyName] .ToString ());
                string recordIndex = Index_PrimaryKeyValue2 [j];
                recordsSource [recordIndex] = new Dictionary <string, string> ();
                foreach (string keyName in columns)
                {
                    recordsSource [recordIndex] .Add (keyName, reader [keyName] .ToString ());
                }
                j ++;
            }
            reader.Close ();
            readerSource.Close ();

            foreach (var record in recordsSource)
            {
                string setScripts = string.Empty;
                string insertKeysScripts = string.Empty;
                string insertValuesScripts = string.Empty;
                int setScriptsIndex = 0;
                int insertScriptsIndex = 0;
                string primaryKeyValue = record.Key;
                if (recordsDest.ContainsKey (primaryKeyValue))
                {
                    foreach (string keyName in columns)
                    {
                        if (! ignoreUpdateColumns.Contains (keyName))
                        {
                            if (recordsDest [primaryKeyValue] [keyName] == record.Value [keyName])
                            {
                                // do nothing
                            }
                            else
                            {
                                if (setScriptsIndex == 0)
                                {
                                    setScripts + = keyName + "= '" + recordsSource [primaryKeyValue] [keyName] + "'";
                                }
                                else
                                {
                                    setScripts + = "," + keyName + "= '" + recordsSource [primaryKeyValue] [keyName] + "'";
                                }
                                setScriptsIndex ++;
                            }
                        }
                    }
                }
                else
                {
                    foreach (string keyName in columns)
                    {
                        if (! ignoreInsertColumns.Contains (keyName))
                        {
                            if (insertScriptsIndex == 0)
                            {
                                insertKeysScripts + = keyName;
                                insertValuesScripts + = "'" + recordsSource [primaryKeyValue] [keyName] + "'";
                            }
                            else
                            {
                                insertKeysScripts + = "," + keyName;
                                insertValuesScripts + = ", '" + recordsSource [primaryKeyValue] [keyName] + "'";
                            }
                            insertScriptsIndex ++;
                        }
                    }
                }

                // update source to dest
                if (setScriptsIndex> 0)
                {
                    cmd = new SqlCommand ("Update" + tableName + "set" + setScripts + "where" + primaryKeyName + "= '" + recordsSource [primaryKeyValue] [primaryKeyName] + "'", destinationConnector);
                    cmd.ExecuteNonQuery ();
                }

                // insert source to dest
                if (insertScriptsIndex> 0)
                {
                    cmd = new SqlCommand ("insert into" + tableName + "(" + insertKeysScripts + ") values (" + insertValuesScripts + ")", destinationConnector);
                    cmd.ExecuteNonQuery ();
                }
            }

            // after update and insert, the count still not match, means we delete some records in source db, then we also need to delete the records in destination db
            foreach (var re in recordsDest)
            {
                // get the delete record primary key value
                if (! recordsSource.ContainsKey (re.Key))
                {
                    cmd = new SqlCommand ("delete from" + tableName + "where" + primaryKeyName + "= '" + re.Value [primaryKeyName] .ToString () + "'", destinationConnector);
                    cmd.ExecuteNonQuery ();
                }
            }

            // Close objects
            destinationConnector.Close ();
            mySqlConn.Close ();
        } </ span>

 

For more information about the basic class of the Code, see the following article:

1. C # synchronize data in the SQL Server database-Database Synchronization tool [synchronize existing changed data] 2. analyze the performance and defects of the Self-written SQL Server synchronization tool 3.C# synchronize data in the SQL Server database-Database Synchronization tool [synchronize new data] 4.C# synchronize Schema of the SQL Server database

 

 

 

 

SQL Server Data Synchronization

There are many methods:
1. bcp.
2. rowset functions (such as openrowset)
3. replication in database high availability.
4. linked server

Ms SQL SERVER 2005 data synchronization how to do

Use Database Replication Technology to synchronize data updates

Concept of Replication
Replication is a technology that copies a set of data from one data source to multiple data sources. It is an effective way to publish a set of data to multiple storage sites. With the replication technology, users can publish a copy of data to multiple servers, so that different server users can share the data within the permitted range of permissions.

The replication technology ensures that data distributed in different locations is automatically updated synchronously to ensure data consistency.
The basic elements of SQL replication include
Publishing Server, subscription server, distribution server, publications, articles
How SQL replication works
SQL SERVER mainly uses publications and subscriptions to process replication. The server on which the source data is located is the publishing server, which is responsible for publishing data. The Publishing Server copies all changes to the data to be published to the distribution server. The distribution server contains a distribution database that can receive all changes to the data and save the changes, and then distribute the changes to the subscription server.
SQL SERVER replication technology type
SQL SERVER provides three Replication technologies:
1. Copy a snapshot (this will be used if we stay there)
2. Transaction Replication
3. Merge and copy
As long as the above concepts are clarified, we will have a certain understanding of replication. Next we will perform the copy step.
First, configure the Publishing Server
(1) Select the specified [server] Node
(2) Select the [Publish, subscribe server, and distribute] command from the [copy] sub-menu in the [tools] drop-down menu.
(3) In the dialog box that appears, click [next] And then follow the prompts until the operation is completed.
(4) After setting the Publishing Server, the system adds a replication monitor to the tree structure of the server. A distribution database (distribution) is also generated)
Create a publication
(1) Select the specified server
(2) Select the [create and manage release] command from the [copy] sub-menu in the [tools] menu. A dialog box is displayed.
(3) Select the database for which you want to create the publication, and click [Create release]
(4) In the prompt dialog box of the [Create release wizard], click [next]. A dialog box is displayed. The dialog box contains three types of copies. Now we select the first one, that is, the default snapshot release (the other two can be checked for help)
(5) Click [next] system requirements to specify the type of database server that can subscribe to the release. SQLSERVER allows data replication between different databases, such as ORACLE or ACCESS. But here we choose to run the database SERVER "SQL SERVER 2000"
(6) Click [next]. A dialog box for defining the document is displayed, that is, the table to be published.
(7) then [next] until the operation is completed. After the publication is created, the database for creating the publication becomes a shared database.
Design subscription
(1) Select the specified subscription Server
(2) select [request subscription] from the [tools] drop-down menu in the [copy] submenu
(3) Click [next] until the system prompts you to check the running status of the SQL server proxy service. The precondition for performing the copy operation is that the SQL SERVER proxy service must be started.
(4) Click [finish]. Complete the subscription operation.
The above steps are actually copied successfully. But how can we know whether the replication is successful? This method can be used to quickly check whether the operation is successful. Expand copy under the Publishing Server -- publish content -- Right-click Publish content -- properties -- click live -- status, and then click Run Agent immediately. Then click agent properties, click live scheduling, and set Scheduling to every day. occurred, every minute, between 0:00:00 and 23:59:59. The next step is to determine whether the replication is successful. Open C: \ Program Files \ Microsoft SQL Server \ MSSQL \ REPLDATA \ unc \ XIAOWANGZI_database_database to see if there are some Files that use time as the file ...... remaining full text>