Featured Post

Organize and rename photos by EXIF data with PowerShell

This PowerShell script organizes and renames all photos in a selected folder using EXIF data. It will also create thumbnails of the images i...

Showing posts with label MSSQL. Show all posts
Showing posts with label MSSQL. Show all posts

Friday, May 2, 2014

Configure SQL Server IPAll Static Port using C#

Note: You will need to add references for a few Microsoft.SqlServer dll's
        // using Microsoft.SqlServer.Management.Smo.Wmi;
        public static void SetStaticPort(string ServiceName = "MSSQL$SQLEXPRESS", string InstanceName = "SQLEXPRESS", int PortNumber = 2433)
        {
            try
            {
                ManagedComputer c = new ManagedComputer();
                c.ConnectionSettings.ProviderArchitecture = ProviderArchitecture.Use32bit;

                int count = 0;
                foreach (ServerInstance si in c.ServerInstances) { count++; }

                if (count == 0)
                {
                    c = new ManagedComputer();
                    c.ConnectionSettings.ProviderArchitecture = ProviderArchitecture.Use64bit;
                    foreach (ServerInstance si in c.ServerInstances) { count++; }
                }

                if (count == 0) throw new Exception("Unable to locate SQL Instances, Please contact support.");

                Service svc = c.Services[ServiceName];
                var state = svc.ServiceState;
                if (state == ServiceState.Running) svc.Stop();
                ServerInstance s = c.ServerInstances[InstanceName];

                if (null == s) throw new Exception("Unable to locate SQL Service, Please contact support.");

                ServerProtocol prot = s.ServerProtocols["Tcp"];

                foreach (ServerIPAddress ip in prot.IPAddresses)
                {
                    if (ip.Name == "IPAll")
                    {
                        ip.IPAddressProperties["TcpPort"].Value = PortNumber.ToString();
                        ip.IPAddressProperties["TcpDynamicPorts"].Value = String.Empty;
                    }
                }

                prot.Alter();

                svc.Start();
            }
            catch (Exception ex)
            {
                throw;
            }
        }