I have had to set up Merge replication on a small custom database located in the US to a server in China. Our company’s WAN and uplinks at the branch offices leaves a LOT to be desired. Anyways, here’s the steps I took.

Publication database is on a Windows 2008 R2 Highly Available Cluster.
Databases are both on SQL Server 2008 R2 instances.
Create a clustered file share for the replication snapshot files to be located.
Create an AD service account to run the Merge Agent and Snapshot Agent.
Grant the appropriate permissions for the service account to the cluster share.
Run the create merge publication script (I’ll share later).
Log onto the remote server.
Create the database that will be the subscriber.
Run the subscription script (I’ll share later).
Back on the publisher DB, execute sp_addmergesubscription with the details needed.
On the subscriber, add the users and logins as needed and enjoy.

Since this is a small database, and there aren’t a lot of heavy hitting databases on either server, I decided to allow the publisher also act as the distributor. I can always break and change that if needed later on.

Wow, is this released, and are people using it? I figured I’d install this at home on a VM in my HyperV server to pre-test before doing the same at work. We’re entertaining the idea of dropping NetApp’s Snapdrive for Windows and Snapmanager for SQL on our primary datacenter filerheads, and I wanted to find a replacement or possibly an upgrade.

After 3 failed attempts to install, all I can say is “Sloppy”. The failures have been during DPM “Reporting Services configuration”. All the reports are deployed, and I can view the reports via the URL. But I get the “dreaded” 812 error saying some generic reporting services error ocurred. Advice on the web has to do with SSL and RS being configured to use HTTPS. Not in my case. Digging in the logs, I see this error pop up “Mojito error was: PasswordTooShort”. References on the web suggest that the password does not match the domain GPO policy. However, the password is the same that I’m using for my domain account, so forget that idea. No resolutions that I have found, one attempt from some MS guy was to try to do a net user /ADD. Guess what, that works without error. Great one MS.

Trying to install on Windows Server 2008 R2. Oh, and here’s another problem, probably a Server 2008 R2 problem. Try to reinstall, get an error that the database DPMDB on instance MSDPM2010 exists, and to delete prior to reinstall. Guess what, if you open a command window and try to bring up the DAC via sqlcmd -S localhost\MSDPM2010 -A you get “Login Failed for user that did the install”. Oh but forget it – you, open a command window “As Administrator” and it works fine. I absolutely hate running SQL Server on Windows Server 2008 R2.

Our company already has an infrastructure monitoring tool, and it plays very nicely with SQL Server versions 2000 through 2008. It is primarily a monitoring tool, and so is somewhat limited on historical reporting or trending.

I’m currently working on an SSIS package that I can run on a schedule from a central SQL Server that will go and gather data from my other servers. One function that I’ve been testing is the sp_readerrorlog procedure to capture and then merge the SQL Log files into a single table.

Other options I’m looking to start include an hourly file sizing report by database. This would allow me to make some fancy trending reports to the management types so they can watch the growth of their data.

Another is performance information like Plan Cache and Buffer Cache, User connections, Agent Job history… etc.

Here’s how I query to get sizing info for each table in a database

BEGIN TRAN GETTABLESIZES

DECLARE @TempSIZINGTable  TABLE
( [Table_Name] varchar(50),
Row_Count int,
Table_Size varchar(50),
Data_Space_Used varchar(50),
Index_Space_Used varchar(50),
Unused_Space varchar(50)
)
INSERT INTO @TempSIZINGTable EXEC('sp_msforeachtable ''sp_spaceused "?"''')

/*
sp_spaceused adds a KB to the end of the sizing fields
we use substring and a Pattern Index search for a space,
so that 1024 KB turns into 1024 which we convert to an int
for sorting
*/

SELECT [TABLE_NAME],
[ROW_COUNT],
CAST(substring( [TABLE_SIZE],1,PATINDEX('%[ ]%', [TABLE_SIZE])-1) AS INT) AS [TABLE_SIZE in KB],
CAST(substring( [DATA_SPACE_USED],1,PATINDEX('%[ ]%', [DATA_SPACE_USED])-1) AS INT) AS [DATA_SPACE_USED in KB],
CAST(substring( [INDEX_SPACE_USED],1,PATINDEX('%[ ]%', [INDEX_SPACE_USED])-1) AS INT) AS [INDEX_SPACE_USED in KB],
CAST(substring( [UNUSED_SPACE],1,PATINDEX('%[ ]%', [UNUSED_SPACE])-1) AS INT) AS [UNUSED_SPACE in KB]
FROM @TempSIZINGTable
ORDER BY [TABLE_SIZE in KB] desc

COMMIT TRAN GETTABLESIZES