Replication across Internet help

[EchoAlertcom]

Hello,

I have 3 MS SQL2000 servers that I have running on my LAN. I have 1 set up as the Distribution Server AND Publication Server. It is set up to perform Transactional Replication in a PUSH Subscription to the 2 other servers. The scenario is working perfect. Very little data changes but it is important that the other servers have the data fast.

My question is: What do I have to do to move these servers to their respective datacenters around the country. I need this replication to happen across the Internet. On the LAN it was easy because the servers could find each other by the Windows Browser Service. How do I configure the IP addresses in SQL Server of each pther server and what permissions do I need to change? Is there a GUI tool or wizard that can help me?

Sincerely,
Steve

 

[EchoAlertcom]

I'm sorry. I forgot to say this was very important.

 

[boing]

I am under a time crunch so forgive my grammar.

Really Bad News: no wizards, no GUI tools, very little info.

I am a few steps ahead of you and can save you some headaches - but not all of them. I am currently doing transactional replication between North Carolina and Chicago of about 40 databases two of the VLDB between 3 Gigs and 30 Gigs.

1. None of the books covers this topic well. Each book covers the topic from a different perspective. No book gives a comprehensive view. No book whatsoever gives any significant info on troubleshooting replication.

2. You will have to synthesize all the info from several books yourself and create your own notes. This is a real pain. Below is one good site I have found. Excellent and free.

http://vyaskn.tripod.com/repl_ques.htm

3. Take each machine and create an ODBC connection on it and map the ODBC to the IP or DNS of the machine you want to communicate with. Finish the ODBC process and test the communication. Keep working on it until it tests ok. Do this for both machines. This will assure both machines can see each other and communicate with each other regardless of where they are at on the Internet.

4. From Enterprise Manager place both machines in a server group. You will need to make SQLAgent passwords for both machines. It is easier to make them the same on both machines or you will go nuts with troubleshooting the permissions.

5. Establish your pubs server and make sure you have your Distribution monitor showing up. The Distribution Monitor is rather poor but it’s the best you have for debugging.

It sounds like you know the basics of replication. Doing it over the Internet is basically the same except....

6. If the initial snapshot of the database is large (large > 200 MB) The snapshot agent can do really weird things. It will transfer part of the data then go to sleep. Then it will renew and continue. Depending on the size of the database it can be extremely long periods where you think the agent is dead. We have had agents literally go to sleep and die in the middle of a large table. Check your data upon completion. Really check it. Assume nothing.

7. You can have problems with intervening routers between the servers chopping your packets up into trash. You may have to increase the thread size and decrease the MAX MPU from 1500 to 576. I have not done this but there is an excellent article from this site I will point you to.

This is all the time I have at the moment. I will be happy to correspond with you. Remember when replication finally works you are God!

 

[EchoAlertcom]

Hey Boing,

Oh my me...I'm god! (sorry bad joke) I got it working!!!!!

Thank you for taking the time to respond. Now, hopefully I can help you a bit. I have a very different situation from you in the regards that I have only two small dbs that I'm pushing so I may not be running into VLDB related issues.

You may have already tried this path and found it didn't work.

I also tried the obdc connection stuff with mixed results. The way I was able to get it to work was to create a bi-directional Named Pipe connection between the two servers.

I used the Client Network Utility (hopefully you installed the client tools) from each server to create an Alias that created a "Well Connected" connection to the remote server.

On the General Tab, I added the Named Pipe Protocol from the Disabled Protocols box to the Enabled Protocols box. I then moved up to the first one in the list.

On the Alias Tab, I clicked the Add button and typed an IP address in the top Server Alias textbox. I probably could have used an FQDN but wanted to eliminate any more services than needed. I'll change that later. Clicked OK.

Nothing to do on the DB-Lib Tab,.

On the Network Libraries Tab, verify that the Named Pipe Libs are installed on the server.

Then click OK.

Repeat on other server.

You may want to verify that both servers are able to accept RPC connections from other SQL Servers. Right-click on the specific SQL Server under your SQL Server Group and click on the Connections Tab and you will find the appropriate checkbox.

Then, fire it up Sparky!

Please let me know if this worked for you. I'll keep my fingers crossed.

sfunk[at]EchoAlert.com

Cheers,
Steve

 

[EchoAlertcom]

P.S.

For these RPC connections your SQL Services need to be started with something other than the System account. Must have Admin rights and the right to log on as a service.

This had me in tighter circles than a one-legged duck.

Cheers,
Steve

 

[boing]

DuckMan AKA sfunk AKA Steve:

From god to one-legged duck so fast...The greater they are the harder they fall!

Steve:

Thanks for the response. Have you deployed it? We're going to look at your solution today. We'll also try it on VLDB and see what happens. Will post results. Articles on packet chopping, MTU and routers are here on Tek-Tips:

faq581-1995
faq581-1996

I know the author and these are top notch. This may be one of our agent problems on VLDBs.

Boing AKA Fred

PS.

For anyone else reading this.

Pub server was NT 4.0 SQL 7 in heavy production 24 x 7 located in NC. No room for mistakes and pushed to Distributor server W2K Advanced SQL 7 test machine in NC. Distributor Server pushed to Subscriber server W2K Advanced SQL 7 test machine in Chicago. All replication was transactional with continuous update on full T1.
Having administrative rights remote control of each server will help keep you out of anger management seminars.

 

[MeanGreen]

Be careful running Named Pipes over a WAN. This article appears in BOL:

Named Pipes vs. TCP/IP Sockets
In a fast local area network (LAN) environment, Transmission Control Protocol/Internet Protocol (TCP/IP) Sockets and Named Pipes clients are comparable in terms of performance. However, the performance difference between the TCP/IP Sockets and Named Pipes clients becomes apparent with slower networks, such as across wide area networks (WANs) or dial-up networks. This is because of the different ways the interprocess communication (IPC) mechanisms communicate between peers.

For named pipes, network communications are typically more interactive. A peer does not send data until another peer asks for it using a read command. A network read typically involves a series of peek named pipes messages before it begins to read the data. These can be very costly in a slow network and cause excessive network traffic, which in turn affects other network clients.

It is also important to clarify if you are talking about local pipes or network pipes. If the server application is running locally on the computer running an instance of Microsoft® SQL Server™ 2000, the local Named Pipes protocol is an option. Local named pipes runs in kernel mode and is extremely fast.

For TCP/IP Sockets, data transmissions are more streamlined and have less overhead. Data transmissions can also take advantage of TCP/IP Sockets performance enhancement mechanisms such as windowing, delayed acknowledgements, and so on, which can be very beneficial in a slow network. Depending on the type of applications, such performance differences can be significant.

TCP/IP Sockets also support a backlog queue, which can provide a limited smoothing effect compared to named pipes that may lead to pipe busy errors when you are attempting to connect to SQL Server.

In general, sockets are preferred in a slow LAN, WAN, or dial-up network, whereas named pipes can be a better choice when network speed is not the issue, as it offers more functionality, ease of use, and configuration options.


Hope this helps!

 

[EchoAlertcom]

will the replication work if i establish a TCP/IP Alias in the Client Networking Utility?

 

[boing]

MeanGreen:

Thanks a bunch. Sure would like to keep this thread going since there is so little info out there.

Boing

 

[EchoAlertcom]

Boing,

I have now switch my servers over to using the TCP/IP Protocol Libs for the Client Network Utility.

It seems to work fine. The person who posted the BOL clipping about Named Pipes may have just saved me lots of strangeness in the future. So a big hug and kiss to MeanGreen.

Let me know how your scenario plays out.

Good Luck, from the one-legged duck.
Steve

 

[MeanGreen]

What little you may loose in speed, you will gain in stability. And in a replication world, stability is everything. Glad I could help. Good luck with the replicating!