Go SQL Server Master-slave database configuration

This article transferred from: http://www.cnblogs.com/yukaizhao/archive/2010/06/02/sql-server-master-slave-mode.html

The size of the site to a certain extent, the points are also divided, the optimization has also been optimized, but still can not meet the performance requirements of the business, we can consider the use of master-slave library. Master-Slave library is two servers on the two databases, the main library with the fastest speed to do additions and deletions and the latest data query operations, from the library is responsible for querying the older data, do some analysis of the effectiveness of the small, report generation work. In doing so, the pressure on the database is shared between the two servers to ensure the timeliness of the overall system response. If the business needs are not yet met, we need to consider creating a server farm, where we don't think about it!

1. Open SQL Server Enterprise Manager, select Copy Local publishing in Object Explorer, right-click New Publication

2. Open the New Publication Wizard, click Next, select the database that publishes the data

3. We select the test database and click Next to select the release type

4. Click Next to select the object to publish, here we only publish the table

5. Click Next to go to filter data settings, here we want to copy all the table data so do not set

6. Click Next to specify when to run the snapshot, we select the initial session data and select the default run snapshot frequency

7. Proceed to the next step, set up the Snapshot Agent run account, we select the SQL Server Agent account

8. Click Next to select Create a publication, click Next again to set the name of the publication

9. Click Finish, complete the release settings, and create the release, now publish locally to add the release we created

Now that the publication has been successfully created, we also need to create a subscription: right-clicking on a new subscription on a local subscription folder makes it easy to create a subscription with the wizard, and you can choose to create it either as a publisher push or a subscriber in a proactive way. The steps are as follows:

1. From the right-click menu, open New Subscription, click Next, select the publication we just created as the feed

2. Select Yes to push back is to synchronize the data in the active request, we choose the active subscription

3. Set up an account to execute the Distribution Agent

4. Set the frequency of Agent request synchronization

5. Set whether to initialize the data immediately

6. Completing the Create subscription

Once created, we can verify that the replication was successful by inserting n data into the main library table and then querying from the library.

Replication in SQL server2005 is easy to create, and we need to set the type of replication and frequency of synchronization based on business needs.

Let's take a look at some of the usage scenarios and a simple small example of the master-slave library.

The master-Slave library is a publish-subscribe relationship that is not synchronized between the Publisher and the Subscriber in real time, usually with a few minutes of delay and, more often, a few hours of delay. So we need to use reasonable to avoid the problem of delay. We want the main library to participate in the query as little as possible, to improve the timeliness of writing, at the same time, from the library without affecting the accurate and timely reading of the data to the extent possible to share the main library pressure. The master and slave two libraries need to configure two connection strings in the configuration file, Conn_master and Conn_slave. We need to set some rules to determine whether the current query should be checked from the main locker or from the library. This rule has no formula and can only be determined according to business needs. Let me give you a few examples to illustrate:

1. Take the detailed page of the Watercress book as a hypothetical scenario, you can click here to see the structure of the page (I am not a watercress technology, here just take this page example)

We analyze the data needed to present this page and the effectiveness of these data requirements

1) detailed information of the book timeliness requirements: Timely request

2) The common label effect of watercress members: do not need to be timely

3) Those who like to read this book also like to read the book belongs to the analysis of data, do not need to be timely

4) The latest book review requires timely

5) The number of users reading this book is not high

6) People who like this book often go to the group of analysis data do not need to be timely

From the above analysis can be seen only 1), 4) Two data need to read from the main library, and 2), 3), 5), 6) for non-timely data from the library read. Of course, we can cache these less effective data.

2. Take the Forum post list page as a hypothetical scenario, the people who play the forum like the top sticker, put their posts to the first page to let more people pay attention to, and for 50 pages after the post is very few people read, we can according to this business logic feature to determine the first 50 pages of the post list data from the main library read, Queries from the library are made when the user accesses more than 50 pages of data.

3. Take orders as an example, usually more than three months of orders will no longer change, assuming that we design the order number as a date format, according to the order number to check the order can be based on the order number to determine whether the access to the main library or from the library.

With a few suitable scenarios, let's take the third scenario as an example and write a simple schematic code to see

[CSharp]View Plaincopyprint?

1. the format of <p>//orderno is 20100528120105000001 YYYYMMDDHHMMSS + serial number </p>

The format of the OrderNo is 20100528120105000001 or YYYYMMDDHHMMSS + serial number

[CSharp]View Plaincopyprint?

2. Public OrderInfo GetOrder (string orderno) {
6. String connstring = Connstringgetter.getfororder (OrderNo);
10. using (SqlConnection conn = new SqlConnection (connstring))
14. {
18. ...
22. }
26. }
30. Public class Connstringgetter
34. {
38. Public static string Getfororder (string orderno) {
42. int year = int.  Parse (orderno.substring (0,4));
46. int money = Int.  Parse (orderno.substring (4,2));
50. int date = Int.  Parse (orderno.substring (6,2));
54. DateTime ordertime = new DateTime (Year, money, date);
58. TimeSpan ts = datetime.now-ordertime;
62. Depending on the time of the order, whether to use the main library or the library
66. if (TS. Totaldays >) return configurationmanager.connectionstrings["Conn_slave"].  ConnectionString;
70. return configurationmanager.connectionstrings["Conn_master"].  ConnectionString;
74. }
78. }

Public OrderInfo GetOrder (string orderno) {string connstring = Connstringgetter.getfororder (OrderNo); using ( SqlConnection conn = new SqlConnection (connstring)) {...}} public class Connstringgetter{public static string Getfororder (string orderno) {int year = Int. Parse (orderno.substring (0,4)); int money = Int. Parse (orderno.substring (4,2)); int date = Int. Parse (orderno.substring (6,2));D atetime ordertime = new DateTime (year, money, date); TimeSpan ts = datetime.now-ordertime;//Determines whether to use the main library or from the library if (TS) based on the time of the order. Totaldays >) return configurationmanager.connectionstrings["Conn_slave"]. Connectionstring;return configurationmanager.connectionstrings["Conn_master"]. ConnectionString;}}

The correct use of master-slave library, can improve the performance of the system very well. The choice of whether to use the main library or the library is determined in the hands of the business logic.

Go SQL Server Master-slave database configuration