0

I have an OLTP server that I am thinking of setting up transactional replication for in order to support OLAP Reporting.

I have a server named Yoda that we are using for our OLTP and one named Chewie for our OLAP server (Both are VM servers). I know I can setup transaction replication on them easily. However, in thinking of network IO and performance and talking it over with the network admin we were thinking of adding a 2nd nick card to both servers and running the replication commands across there.

So they would have something similar to below:

Yoda:  192.168.XX.51:1433  (Main OLTP Transactions)
       192.168.XX.52:874   (Replication Transactions)
Chewie: 192.168.XX.21:1433 (Usual SQL Agent Transaction)
        192.168.XX.22:874  (Replication Transaction)

In Microsoft forums I was told that this could be done using Routes. However, I am new to routes in SQL Server so I am not sure where to begin. How would I create one that I can tie to Transaction Replication?

Alexei - check Codidact
  • 22,016
  • 16
  • 145
  • 164
crusso
  • 1
  • 1
    The routes tag you are using is not related to SQL Server or networking. It is about web development. I am guessing here but I think that the routes your were told about are at the network layer; switches and routers. You can set multiple IP addresses to your SQL Server in the configuration manager> network configuration > TCP/IP >properties . You can also create aliases to each SQL Server instance. – Ricardo C Mar 08 '16 at 20:38
  • actually I found this post which seems like what I am trying to do. However, the guys didn't get to finish the project so it doesn't help. LOL!!! http://www.sqlservercentral.com/Forums/Topic586521-291-1.aspx – crusso Mar 09 '16 at 13:35

1 Answers1

0

A Windows server may have multiple IP addresses, either on one or across multiple NICs.

After a second IP has been assigned to the host, configure SQL Server to listen to that IP/port using SQL Server Network Configuration.

Here you may modify the port to listen to, but probably unnecessary to use different ports when using multiple IPs. Each IP can be listening to port 1433.

SQL Server Configuration Manager

You may create additional CNAMES or aliases to reach the database instance with different host names.

Creating a Valid Connection String Using TCP/IP

SQL Server also support aliases.

Ricardo C
  • 2,205
  • 20
  • 24
  • How would you tell it to route certain tasks over one ip address vs another? – crusso Mar 10 '16 at 12:56
  • You tell because you create the connection on the initiating server (client). The client connection string specify what IP/host you connect to. This setting is for the receiving server, the initiating server is the one that chooses where to connect. – Ricardo C Mar 10 '16 at 15:35
  • The server initiating the connection cannot chose what IP to use, but the network admin can set those IPs in a separate vlan, and configure the routing where only the second IP has access to the receiving server second IP. – Ricardo C Mar 10 '16 at 15:40