Guru follow up on MP Replicas

I'll track updates to my Guru session yesterday regarding MP replicas in this post.

First off, I've had positive feedback from others saying they're excited to try SQL replication to MPs which is great. That was really the goal of the session. I just don't know how many people know about them.

Here is a testimonial I just received today from a large corporation who is running SQL offbox. They know now that they could have gone onbox by using replicas:

Btw, replicas are the way to go (as you already know). Here's a Monday morning on our (offloaded) SQL, without SQL MP replicas (when we had approx. 60K clients assigned to this primary):

Once I configured the replicas, we went down to usually under 20% utilization. So WITH replicas, I'm sure we could have done database on the primary, but I think we would have been bursting without the SQLl replicas for MPs.

For CM07, the MP replication docs are here. Someone asked for an easy way to create the publication for CM07. There isn't anything as easy as CM12 that I know of, but there is this: MPPublish.sql

Someone wanted a cheat sheet for replication on CM07 (since the Guru session showed only CM12), so here is a copy of what we use. Note that it assumes we're running the SQL agent as system.

ENABLE REPLICATION ON THE PRIMARY
Create a folder on the primary called SnapShot in R:\SQL2K8\ and share it to everyone\full as SnapShot$. Give NTFS read to the replication acct. System needs full perms.

Enable replication by going to Server management Studio, right click replication\configure distribution, default\next, set the snapshot folder to \\server\SnapShot$, Distribution database name should be <sc>_REPL. (Next\Next\Finish).

CREATE REPLICA DB ON THE PRIMARY WITH MPPUBLISH.SQL
Grab a copy of the .sql file, available here.
(Change dbname, servername, and replica name) and run it from SQL Server Management Studio,

Then: New Query, copy/paste contents and execute.

Be sure that the publisher is set for \\server\SnapShot$ and not R:\SQLR2\SnapShot$. (Replication + Local Publications, right-click on CM_<sc>_REPL. Look at "Snapshot"

Be sure the machine$ of each MP is in the local SQLServerSQLAgentUser$ and has been granted db owner to the replication DB on the primary site.

MP REPLICATION DATABASE SETUP
An empty database must be created to host the site database replica on each MP.

1. In the SQL Server 2008 SQL Server Management Studio console connected to the MP, right click Databases and click New Database...
2. In the New Database dialog, General page, enter a name for the replicated site database in database name (D:\SQLR2\<sc>_REPL). Set initial size to 3500 MB and Tx to 128 MB and both set to grow at 10%. Put a size cap on the Tx so it can never outgrow its drive.
3. Click OK to close the New Database dialog.
4. On the primary: Add MPmachine$ to the primary site's local SQL User group, then add it as dbowner on the replica DB.

On the replica destination. Create a subscription from the MP Server (Replication, right-click Local Subscriptions, New Subscriptions) Pick Publisher, "Find", and select or type in the Primary site name; reconnect. Select <sc>_REPL, Next. Select the default (Pull subscription), Next. Select the Subscription Database from the pull-down. Next. Click on the ellipses (...) Choose the SQL Agent impersonation option.

Accept the default of "Run Continuously", Next.

Initialize Immediately, Next, Next, Finish

On the MP, SQL Server Agent, Jobs, Modify the job to run under NT Authority\SYSTEM (not you).

Verification: For each server (Replica sources and destinations), right-click Replication, and pick "Launch Replication Monitor" and confirm "All Subscriptions" show Running and "Excellent" Performance. If you *just* created the replica, it may not show Excellent for a few minutes until the initial replication is complete. Hit F5/refresh until Performance is Excellent. Otherwise, stop & troubleshoot.

Check D:\sms\logs\MPControl.log on the MP servers, confirm success messages.

Some Q and A during the session:

Q: For CM07 how does MP Replicas differ from using Proxy MPs?
In CM12, the same replica data is already sent down, but in CM07, you'd definitely benefit from a replica as it's less traffic coming back to the primary; clients get their answer from their local MP.

Q: CM12 suports multiple SMS_Providers - isn't ot an HA for SMS Executive? I see the service is up on my second server
The smsexecutive service manages all the threads on a primary to do everything CM does that SQL doesn't. The provider is just a conduit for a console or script to use to get to WMI to access the CM database. Installing another provider doesn't give you another smsexecutive.

Q: Does Brian have the .sql script for creating the MPReplica db saved either on myitforum or on the Minnesota User Group somewhere, that people can grab to easily make their own CM07 replica MP?
See above

Q: I'm having trouble finding a good link for step by step for CM07 MP replication. Can you share that too?
See above

Q: How does it balance between multiple MP's if you create 2 or 3 replicas?
Replica or not, MPs balance like DPs now. Clients get a list of what's available from AD and pick on. Failover is to another from the list.

Q: How MP and DP identified client? Is it possible client can send inventory report to server if AD not extended?
Clients can try DNS or WINS to find them if the schema isn't extended. But, heck, extend that schema if at all possible.

Q: can we talk a bit about MPs in DMZ? Should it be a member of the same domain(or a Trusted domain) as servers on Internal network? I am keeping my DMZ servers in Workgroup; does it mean I cannot bring MP/DPs in DMZ?
I think the MP has to be in a trusted domain so that the primary can be an admin of it.