SQLServerCentral Article

Changing the default port numbers when configuring database mirroring

,

This article outlines how to setup database mirroring but focuses on how to change the default port numbers during setup. Reasons for changing the default port for database mirroring are many, but here are a few:

  1. Security through obscurity
  2. The default port may be already in use

I’ve outlined how to make the changes both through the SSMS GUI and through T-SQL. Make sure that any host based firewalls or network configurations allow for communication on the desired port(s). Note that the ports do not need to match on each server, but simply need to be accessible, e.g., the principal server could use port 11000 while the mirror uses 5800. In the example, I’ve used the same port on all servers.

Begin with preparing the databases for mirroring. If you need help, read this article: http://msdn.microsoft.com/en-us/library/ms189047.aspx

Using the SSMS GUI:

  • On the principal server, right click the database to be mirrored and choose Tasks -> Mirror
  • Click ‘Configure Security’, Database Mirroring wizard opens. Click ‘Next’

Configure Database Mirroring Security Wizard

 

Include Witness Server screen:

  • Choose whether or not to use a witness
  • Click ‘Next’

Choose Servers to Configure screen:

  • Save configuration on both servers
  • Click ‘Next’

Principal Server Instance Screen:

Here is where we’ll change the default port number:

Change the default listener port from 5022 to desired port. Below I have changed it to 10111 in the image.

  • Change name of endpoint, if desired
  • Click ‘Next’

Changing the Listener port

 

Mirror Server Instance screen:

Choose Mirror server instance

Here is where we’ll change the default port number:

  • Change default listener port from 5022 to desired port. Here I have changed the port to 10111 in the image below.
  • Change Endpoint name, if desired
  • Click ‘Next’

Configure Mirror listener port

 

**Optional**

Witness Server Instance screen

Connect to Witness server instance

Here is where we’ll change the default port number:

  • Change default listener port from 5022 to desired port. I have also set this to 10111 as shown in the image below.
  • Click ‘Next’

Set witness server port

 

Service Accounts screen

  • Supply service account owners, if necessary
  • Click ‘Next’

Complete the Wizard screen

  • Review information and click ‘Finish’

At this point database mirroring should be setup on the ports that were chosen during the wizard.

Using T-SQL:

I will show you how to make the same changes using T-SQL. Each login and server shown below will have a variation of principal, mirror, or witness in the name to signify which server this applies to. Note that you can change these names to anything you like, but ensure that the accounts have the correct rights on each server.

On principal server, create the endpoint. Specify the mirroring port in the SQL statement

create endpoint PrincipalEndpoint
 state = started
      as tcp (listener_port = 10111)            --specify port here
      for database_mirroring (role = partner)
go

Create a login(s) and grant ‘connect’ to the endpoint for the login(s). I’ve used domain service

accounts and have included a witness server in this example:

create login [domain\svc-mirr-sqlsvr] from windows;
go
grant connect on endpoint::PrincipalEndpoint to [domain\svc-mirr-sqlsvr]
go

Optional, used for witness server

create login [domain\svc-witn-sqlsvr] from windows;
go
grant connect on endpoint::PrincipalEndpoint to [domain\svc-witn-sqlsvr]
go

On the mirror server, create the endpoint. Specify the mirroring port in the SQL statement

create endpoint MirrorEndpoint
  state = started
      as tcp (listener_port = 10111)            --specify port here
      for database_mirroring (role = partner)
go

Create a login(s) and grant ‘connect’ to the endpoint for the login(s). I’ve used domain service

accounts and have included a witness server in this example:

create login [domain\svc-princ-sqlsvr] from windows;
go
grant connect on endpoint::MirrorEndpoint to [domain\svc-princ-sqlsvr]   
go

Optional, used for witness server

create login [domain\svc-witn-sqlsvr] from windows;
go
grant connect on endpoint::MirrorEndpoint to [domain\svc-witn-sqlsvr]
go

**Optional**

On the witness server, create the endpoint. Specify the mirroring port in the SQL statement

create endpoint WitnessMirrorEndpoint
      state = started
      as tcp (listener_port = 10111)           -- specify port here
      for database_mirroring (role = witness)
go

Create logins and grant ‘connect’ to the endpoint for the logins

create login [domain\svc-princ-sqlsvr] from windows;
go
grant connect on endpoint::WitnessMirrorEndpoint to [domain\svc-princ-sqlsvr]
go
create login [domain\svc-witn-sqlsvr] from windows;
go
grant connect on endpoint::WitnessMirrorEndpoint to [domain\svc-witn-sqlsvr]
go 

Establish mirroring session, beginning on the mirror. Note the port number in the connect strings!

--mirror server first
alter database [adventureworks]
      set partner = 'tcp://principal_server.domain.com:10111'    
go
--principal server next
alter database [adventureworks]
      set partner = 'tcp://mirror_server.domain.com:10111'
go
--optional, if using witness.  Run this on principal server
alter database [adventureworks]
      set witness = 'tcp://witness_server.domain.com:10111'
go

Rate

5 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (4)

You rated this post out of 5. Change rating