Adding an SMS Provider with MP Replica in existence

I’ve been implementing a Configuration Manager 2012 R2 environment this week and stumbled across a few ‘quirks’ which I thought I would share. To set the picture, lets say we have 2 servers – 1 Primary Site Server with a local SQL installation and a second server which will have Management Point, Replicated DB and SMS provider.

image

Now I previously installed the management point and database replica on Server 2 and configured the MP to use that replica according to method used in the technet library - http://technet.microsoft.com/en-us/library/hh846234.aspx. In this guide lets pay specific attention to the following section which states to set the Max Text Repl Size to 2GB:

image

The remaining task on this implementation is to add the extra SMS provider to Server 2. The way to perform this is on the primary site server *through the start menu* to go into Configuration Manager Setup and perform site maintenance then add an SMS provider – pretty straight forward. However in the scenario outlined above I found that adding the SMS provider failed essentially due to the 2GB restriction on Max Text Repl Size outlined above. Incidentally not only does it fail it does actually show the SMS Provider as being installed.

The error you will see in your C:\ConfigMgrSetup.log as below:

ERROR: SQL Server error: [42000][7139][Microsoft][SQL Server Native Client 11.0][SQL Server]Length of LOB data (9094) to be replicated exceeds configured maximum 2048. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536. A configured value of -1 indicates no limit, other that the limit imposed by the data type.

CSiteSettings::WriteActualSCFToDatabase: Failed to insert item (3|Client Configuration|Status Modules) to database

ERROR: Failed to insert the default site control image into the database.

You can see from here that it specifically quotes the 2048 figure as suggested by technet library article.

 

So the easy way around this one is to *either* make sure you add your SMS Provider before you do your MP Replica setup or if it’s too late for that then simply set the Max Text Repl Size back to it’s default of 65536 or even –1 (unlimited) for the duration of you adding the additional SMS Provider. Assuming you added the MP replica already you should know how to amend the Max Text Repl Size however as so often is the case you might be the one picking up the pieces so here’s the guide - http://technet.microsoft.com/en-us/library/ms179573.aspx

[EDIT]

So I maybe made a bit of a schoolboy error here, although I think I can be forgiven. After a little more investigation it seems 2GB is the maximum value you can set the Max Text Repl Size to, but the thing that isn’t stated anywhere in SQL Management Studio or the library guide is that the value is actually stated in bytes not MB. Therefore my initial entry of 2048 was wayyy too low and the value should actually be set to 2147483647.

I thought I’d leave this blog post up there in case anyone comes across this scenario as there is very little out there in the way of guidance.

[/EDIT]

Once you do that you should find that all is well.