Codeine .Net RSS 2.0
# Sunday, 30 January 2011

I've been working on a fairly large project the last few months which required SQL replication where the server that hosted the publication didn't run on the default port. I don't know what SQL Server 2008's issue is with replication on anything besides the default port, but my windows service that handled the syncing would always error out. (I'm doing a pull subscription with SQL Express 2008 so there is no SQL Agent to handle the syncing for me.) My work around for this was to create a SQL Server Alias during installation of my application. You need to reference the dll Microsoft.SqlServer.SqlWmiManagement.dll to do this. (It's possible you may need to reference another dll, but I already had several SQL Server dlls referenced for the actual replication code I had written). This dll can be found in the SDK which you get installed when you install SQL Server 2008. Look for it in the folder that was created in your Program Files. The name space you will be working with is Microsoft.SqlServer.Management.Smo.Wmi. Below is some example code on how to do it in C#.

using Microsoft.SqlServer.Management.Smo.Wmi;

 

 public void CreateAlias()

        {

            ManagedComputer mc = new ManagedComputer();

 

            ServerAlias alias = new ServerAlias();

            alias.Parent = mc;

            alias.ServerName = Server Address;

            alias.Name = Name for your alias;

            alias.ConnectionString = Port number as string;

            alias.ProtocolName = "tcp";

            alias.Create();

        }

 

        public bool DoesAliasExist()

        {

            ManagedComputer mc = new ManagedComputer();

            bool result = false;

 

 

            foreach (ServerAlias serverAlias in mc.ServerAliases)

            {

                if(serverAlias.Name.ToUpper() == Name for your alias)

                {

                    result = true;

                }

            }

 

            return result;

        }

 

I tried using a completely different name for the alias, but couldn't get replication to work until I created the alias name as the server name, then things started working nicely. That seemed a bit strange to me as you would think I should be able to name the alias whatever I wanted. Hopefully someone else will find this helpful and it will save them some searching.

Sunday, 30 January 2011 19:28:30 (Central Standard Time, UTC-06:00)  #    Comments [0] - Trackback - Save to del.icio.us - Digg This! - Follow me on Twitter
Development | SQL Replication

Navigation
Archive
<2017 December>
SunMonTueWedThuFriSat
262728293012
3456789
10111213141516
17181920212223
24252627282930
31123456
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2017
David A. Osborn
Sign In
Statistics
Total Posts: 70
This Year: 2
This Month: 0
This Week: 0
Comments: 33
Themes
Pick a theme:
All Content © 2017, David A. Osborn
DasBlog theme 'Business' created by Christoph De Baene (delarou)