This is the third installment in the 12 day series for SQL tidbits during this holiday season.
Previous articles in this mini-series on quick tidbits:
Remember back in the day when Napster was really popular? I’m sure it is still popular now – just not in the same league as the early years. Napster pretty much made some revolutionary changes in file-sharing across the internet. Now the algorithms and the method have become more advanced and use a hive approach, but it still pretty much boils down to the setup that Napster used – Peer to Peer.
In the P2P file-share world, every node had a part to play. If you downloaded a file, your machine could also upload that file or other files to the network for other users. This approach required full files in order to work.
In the Hive approach, the files are broken up into chunks. So you still participate on a P2P basis, but you no longer need to have the complete file to participate. (I am probably over-generalizing, but that is ok – the point is coming soon.) This helped transfers be quicker and the P2P network/hive to be larger (in case you were wondering).
Now, let’s take that idea and move it down to a smaller chunk of data. What if we did that with a database and only sent a record at a time to a partner and that partner could send a few records back to the first partner/peer? Now we have something that could be pretty usable in many scenarios. One such scenario could be to sync data from the same database at ten different locations (or maybe 100 different locations) so all locations would have current information.
Well, SQL Server does have that technology available for use. Coincidentally enough, it is called Peer-to-Peer replication. Truth be told, it is really more of a two transactional replication on steroids. In SQL 2008, you had to setup transactional replication in order to create the P2P. But in SQL 2012, there is now an option on the publication types for Peer-to-Peer.
Setting up P2P replication in SQL 2012 is pretty easy to do. Here is a quick step-through on doing just that. I will bypass the setup of the distributor and jump straight into setting up the publication through to the point of adding peers. From that point, it will be left to you to determine what kind of subscription (push/pull) you use and to figure out how to configure those types.
Step-through
The first step is to expand the tree in SSMS until you see replication and then to expand that to see “Local Publications.” From “Local Publications,” right click and select “New Publication.”
Then it is almost as easy as following the prompts as I will show in the following images. You need to select the database you wish to be included in the P2P publication.
Then it is a matter of selecting the publication type. Notice here that Peer to Peer has been highlighted.
Of course, no replication is complete without some articles to include in the replication. In this case, I have chosen to just replicate a few of the articles and not every article in the database. When replicating data, I recommend being very picky about what articles (objects) get included in the replication. No sense in over-replicating and sending the entire farm across the wire to Beijing, London, Paris and Moscow.
Once the articles are selected, it will be time to setup the agent security. Again, this is pretty straight forward. And in my contrived setup, I am just going to rely on the SQL Server Agent Service account. The screen will inform you that it is not best practice. I will leave that as a exercise for you to explore.
With that last piece of configuration, the publication is ready. Just click your way through to finish.
Once the publication is complete, it is time to add a subscriber to the publication. That is easily accomplished by right clicking the publication. Since this is a P2P publication, we need to select “Configure Peer-To-Peer Topology…”
Selecting that menu option will bring up the Wizard. First step in the new wizard is to pick the publisher and the publication at that publisher that needs to be have the topology configured.
After selecting the publisher and publication then I can add nodes to the P2P topology by right-clicking the “map” (as I like to call it) area. Select “Add a New Peer Node” from the menu and then enter the appropriate details for the new subscriber.
It is here that I will conclude this short tutorial. Configuring the topology is an exercise best left to each individual circumstance. Configuring where the pull subscribers will be and where the push subscribers will be is almost an art. Have fun with it.
I have had the opportunity to use this kind of setup on a large multi-node setup across several sites. It runs pretty smoothly. Sometimes it can get to be a hair-raising event when a change gets introduced that borks the schema. But those are the events that permit you to learn and grow and document what has happened and how to best handle the issues in your environment.
I have even taken a multi-site P2P setup and just added a 1 direction subscriber (as if it were a transactional publication) so the subscriber could just get the information and run reports without pushing changes back up into the rest of the topology. That also works pretty well. Document the design and be willing to change it up in case there appears to be latency and too much peer pressure.
