Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Changing the default port numbers when configuring database mirroring

By Dan Hess,

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
Total article views: 3439 | Views in the last 30 days: 7
 
Related Articles
FORUM

Changind Database Mirroring Endpoints

Changing Database Mirroring Endpoints without resetting db mirroring

SCRIPT

Create Mirror Alerts for All Mirrored Databases

Creates Mirror State-Change Alerts for ALL mirrored databases on a server - can save hours when sett...

BLOG

Two things I wish they'd change about endpoints in SQL Server 2005

I've been playing around with endpoints in SQL Server 2005 looking at how they can be used to enha...

FORUM

SQL Server stopped after changing startup parameters for mirroring.

SQL Server stopped after changing startup parameters for mirroring.

FORUM

SQL Server 2005 - Database Mirroring (Change of Recovery Model)

SQL Server 2005 - Database Mirroring (Change of Recovery Model)

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones