Bidirectional transactional data replication

"MSSQL load distribution on 2 servers"

Good afternoon, habrazhiteli, so I decided to write about my history “Distribution of the load of MSSQL on 2 servers by the method of bidirectional transactional data replication”. And not just 2 servers, but what would they work like mirrors. Anyone interested, I invite you to read.
Who wants to go straight to the point
And of course, data replication came to the rescue. After reviewing the types of replication, let's consider the architecture that suits us best.
In our case, we were chasing the load distribution between 2 servers. Well, and walked through the logical chain of "load distribution - 2 server mirrors - minimum delays - transactional replication." And since there was little experience at all, again I went to msdn. And then I went into the article, started reading one, went through a couple of explanations of the terms, and was completely distracted. In other ways, like me from the main topic ...
First, I had the type of transactional replication as "Peer-to-Peer Transactional Replication ". I do not know how many, but I often first look at the topology in the form of a diagram, and then I just get a grasp of it. This time, again, I got caught by my own mistake.

I looked at this topology, well, I liked it, that on the left there is a “read-write” to both servers, everything is beautiful, the load is distributed. In case of a fall, for a while, one server will cope.
Well, in joy, I began to customize, or rather get a grasp of the setting and try it on test bases. At first, I visited this page and I liked the phrase “ When setting up the topology of active databases to add the first and second nodes (node ​​A and node B), the following procedure is used. The next procedure is then used to add node C and all subsequent nodes . ” In particular, these 2 phrases “the next procedure ” and “the next procedure ” and further in the text about the three-node peer-to-peer topology.
And then, by luck, I decided to return to the page where I started and read about this topology and again I’ve come across the key phrase “ On the left, update operations are partitioned between two servers. If the database contains a product catalog, you can, for example, create a custom application that directs updates of product names starting with letters from “A” to “M” to node A, and updates of products starting with letters from “H” to “I "- on node B. Then updates are replicated to another node ." So this is not what I wanted, and it means that when server “A” crashes, there will be only a part of the data.
And again, in sadness and grief, a long surf through the expanses of links, although the decision was under the nose. I stumble on the article " Bidirectional Transactional Replication ." And again, a smile on his face, and again his hands are torn into battle. And when I read the setting of this replication, a little smile subsides, and I understand that I do not understand anything in the setting of replication. After several hours of reading, trial and error, the situation begins to clear up. In the configuration of bidirectional replication, most of it is described in the configuration through the procedures, I will show my own configuration method according to which this topology works for me.

Well, let's start. I advise you first do this on the test bases . Basically I will throw links, well, describe the " pitfalls " that may occur on the way

• 1. Configure distribution . Run on both servers (distributor (publisher) and subscriber). In this part of the problems did not meet any special, distribution server and publisher, in my case, the same. When installing a folder for snapshots, you must specify a local folder (specify a link to the shared folder that will be accessed by both servers). Further, since the publisher and distributor are the same server, we leave it unchanged. And the last is to enter the password between the distributor and the publisher (in our case, the password will not be used further).
• 2. Next, add publications and articles (on both servers). "Replication - Local Publications - Create Publication." We select the database, the publication type “Transaction Publication”, and select the available objects, you can skip the agent configuration, fill the security of the agent.
  
  Pebble

  One of the conditions of the article is the Primary Key in the table.
  When creating a Primary key, I advise you to check that there is no clustered index on the key field, and if there is, then delete it. When creating a key, this index will be created, and if you do not delete it, it will no longer be clustered and data processing will be increased due to the presence of 2 identical indexes.
• 3. At the stage of creating subscriptions, I was confused at the beginning, but I will try to explain intelligibly.
  Let's start in succession:
  
  1. On server A:
     
     (1) Create a subscription procedure

     EXEC sp_addsubscription @publication = '_ Base_repl_1', --        2. @article = N'all', @subscriber = 'B', --    @destination_db = N'Base_repl_2', --    B @sync_type = N'none', @status = N'active', @update_mode = N'read only', @loopback_detection = 'true'; --   . 

     (2) Enable Agent

      EXEC sp_addpushsubscription_agent @publication = '_ Base_repl_1', --         2. @subscriber = 'B', --   A @subscriber_db = N'Base_repl_2', --     @job_login = 'domain\user', --  @job_password = 'pass'; --  

  2. On server B:
     
     (1) Create a subscription procedure

      EXEC sp_addsubscription @publication = 'B_ Base_repl_2', --        2. @article = N'all', @subscriber = 'A', --    @destination_db = N'Base_repl_1', --    B @sync_type = N'none', @status = N'active', @update_mode = N'read only', @loopback_detection = 'true'; --   . 

     (2) Enable Agent

      EXEC sp_addpushsubscription_agent @publication = 'B_ Base_repl_2', --         2. @subscriber = 'A', --   A @subscriber_db = N'Base_repl_1', --     @job_login = 'domain\user', --  @job_password = 'pass'; --  

  
  
• 4. To create stored procedures used for articles, you can use the article . I consider it not in demand, since for each table it is very expensive to create manually 3 procedures (ins, upd, del). Therefore, I used the following procedure sp_scriptpublicationcustomprocs
  On server A:
  

   sp_scriptpublicationcustomprocs @publication ='_ Base_repl_1' --      

  
  then copy the result of the procedure and execute it on the server “B” in the Base_repl_2 database
  On server B:
  

   sp_scriptpublicationcustomprocs @publication ='B_ Base_repl_2' --      

  
  then copy the result of the procedure and execute it on the server “A” in the Base_repl_1 database

Well, almost everything. I will welcome any advice and criticism.