*Help - Offsite mirroring/logshipping/replication

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

*Help - Offsite mirroring/logshipping/replication

Author

Topic

mattc321
Starting Member

11 Posts

Posted - 2015-04-20 : 13:43:30

Hi everyone, I'm reaching out to the community to hopefully get some help. I was working on this all last week, and I am running out of steam :crazy: . I'm also extremely surprised there is not more information out there on remote log shipping and mirroring setups.

Scenario
We have a production sql server in house that we use with a commercial program for attendnace for around 200 employees. We have an identical hosted Sql server and interface offsite that is available for web traffic. Everything works perfectly when I manually backup and restore to this offsite database. We want to keep this offsite database refreshed, as much as possible. So I configured a log shipping scenario and automated it. In doing so I soon realized that with log shipping, the destination database has to be left in the offline state in order to apply transactions logs. As soon as you apply a tlog with recovery, boom, you cannot apply any more tlogs.

This remote database needs to be online and allowing traffic. Everything I can find on jerry-rigging remote log shipping (which is not much) ALL refers to DR and fail over configurations, which is not what we need. The transactions going on on our remote sql server instance don't matter. All the matters is that the data is being refreshed as often as possible, and that people can see it in the interface. So if the database needs to go offline for a few during the tlog restore, thats fine. But currently the only way to do that would be to apply a full backup to it with norecovery, which means we would be shipping multiple full and differential backups daily along with logs, which of course is not desired (30gb db).

Main Question
It seems like I am really missing something in my thought process here. Why is it that there is not more information out there on replicating to a live database from a primary production database? Everything about mirroring, log shipping, replication etc, always involves the same server/instance. Isn't replicating to a remote server a common request? Is this not a common high availability scenario? Is there any way I can apply transaction log backups to a database that is ONLINE? Is there any other way I can accomplish this that you can think of?

Please consider that opening our internal network to web/database traffic is off the table for now. The primary idea is replication to a remote database, that is online and available. Also keep in mind, I have already successfully made a log shipping configuration to this remote database that works, but keeps the db offline.

Thank you so much for any insight or help you can provide!

Matt

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-20 : 13:48:17

You can use two-way replication, however I would recommend using Availability Groups in this scenario, with the off-site location accepts read requests. If you need writes at both locations, then two-way or merge replication.

Log shipping or tlog backups is not possible for your scenario.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2015-04-20 : 22:09:37

When you say "online", do you mean read-only, or read-write? You can restore a database WITH STANDBY that makes it read-only. You apply transaction logs in sequence to keep it up-to-date, you don't need to restore the entire chain each time, as long as you never specify WITH RECOVERY. The database is only unavailable during a restore, but small/frequent log backups should restore in seconds.

If it needs to be writable then you're stuck with your current solution, or the replication options Tara described. The main issue with writing to a replica is synchronizing them with the master and/or other replicas. Merge replication would be the answer for that (if that's indeed what you're trying to do).

I'm not sure why you'd say "Everything about mirroring, log shipping, replication etc, always involves the same server/instance", every example I know of demonstrates cross-server functionality. (Mirroring cannot replicate to the same instance, neither can Availability Groups).

"Please consider that opening our internal network to web/database traffic is off the table for now". How are you managing your current log shipping scenario, if the sites can't directly communicate? Can you include a secure FTP site in either data center? I just recently used a customized log shipping setup using SFTP between our physical data center in the UK and an Azure VM, something like this may work for you too.

mattc321
Starting Member

11 Posts

Posted - 2015-04-27 : 14:49:58

Thank you guys very much for the responses. To answer robvolks questions briefly, the interface does use some write, so when I restored transaction logs with standby, some of the application was able to work but not all of it. So log shipping and leaving in stand by will not work.

CURRENT ISSUE: CANNOT CONNECT TO REMOTE SQL SERVER INSTANCE AS A SUBSCRIBER FOR THE REPLICATION TASK

I went ahead and setup replication. My local sqlserver is setup as the distributor. I've also set it up as a publisher and made a transactional publication for the database I am trying to replicate. I did not try merge replication, because transactional replication sounded like the right solution for our needs. As I want my remote subcriber to receive transaction from my publisher, and not send any back.

The problem at the moment is that I cannot add my remote sqlserver as a subscriber. It keeps getting connection errors. I can however connect to the remote sqlserver just fine from my instance tree. It's only when adding it as a subscriber do I get the errors attached. In the connection properties I am trying both "default" and "tcp/ip" protocols.

I opened up mssql ports on the edge as well as shut off the windows firewall on the remote server. Today, I temporarily set a rule to allow ALL traffic on all ports on my remote network for this remote sql server.

Attached are are the setup and errors.

2 questions:
1.Is transactional Replication the correct way to go for this scenario? While people are using the remote database, will it be able to receive its push subcription and replication instructions?

2. Can you think of anything I would be missing in trying to connect this remote instance as a subscriber? Should me local sqlserver have any port rules made on our internal networks firewall, in order for replication to work between the two? Like I said, I can connect to it fine in the object explorer, but cannot connect to it on the subscriber add button. Also, the list of subscribers does not include my remote instance, even though it has been added in the object explorer.

ATTACHMENTS
Our setup:[url]http://ashlandfood.coop/replication.pdf[/url]
error:[url]http://ashlandfood.coop/replication1.png[/url]
error:[url]http://ashlandfood.coop/replication2.png[/url]

mattc321
Starting Member

11 Posts

Posted - 2015-04-27 : 15:08:05

quote:
Originally posted by tkizer

You can use two-way replication, however I would recommend using Availability Groups in this scenario, with the off-site location accepts read requests. If you need writes at both locations, then two-way or merge replication.

Log shipping or tlog backups is not possible for your scenario.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

I was not sure exactly how I could set this up or need to. I know with availability groups I need the windows cluster feature installed and configured to point to my remote instance. But still with availability groups I would be using my remote instance as a failover and not a live database right? This app does use some write features, although only for building it's various view. NONE of the write needs to make it back to our actual production db.

mattc321
Starting Member

11 Posts

Posted - 2015-04-29 : 12:12:29

BUMP. Can anyone help? To recap, I cannot seem to connect my remote sql server as a subscriber? I CAN connect to the remote sql server just fine in management studio. But when I go to add a subcriber in the subscription wizard, it says username pw incorrect, or host not known. Am I missing something?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-29 : 12:46:02

Availability Groups provide failover but also can provide a read-only copy for scaling reasons. So you could have a write load or a read/write load on one instance and then a read load on the other instance. Both instances could be identical if you set them up as synchronous, otherwise async and there'll be some latency.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mattc321
Starting Member

11 Posts

Posted - 2015-04-29 : 13:30:23

I would need to setup windows cluster feature on the server to use availability groups like this correct? Also, do I need to have sql server 2012 enterprise to use HA Groups? Will standard edition do what you've described?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-29 : 13:34:41

Yes you needs Windows cluster feature and yes you need Enterprise edition.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mattc321
Starting Member

11 Posts

Posted - 2015-04-30 : 11:41:02

Wow, so I am definitely missing something here with replication. I just created another brand new sql server in AWS RDS. I can connect to it just fine, but as soon as I try to add it as a subscriber it errors out. I'm almost on page 3 of google and cannot find any more information of adding a remote sql server as a subscriber. I've tried everything it seems.

The real problem at the moment is that I cannot seem to figure out how to add any remote subscribers. I've tried aliases, dns entries, straight static IPs to the servers. Everything I can find on google practically and everytime it fails connecting at the add subscriber dialog.

Does anyone have any pointers they can give me? I'm definitely missing a serious step, and/or messing up my connection string somehow. I feel like it has something to do with the server name, but I ran select @@servername on my remote sql server and used that information for trying to connect to it. I will post some errors below, but since i have tried so many things, I've received many different kinds of errors.

distributor configured - done
publisher and publication configured - done
port 1433 on network and machine opened on local and remote server - done
sql services running, tcpip protocol enabled, sql browser service running - done
allow remote connections on both instance - done
can connect to remote machine just fine in mgmt studio but not a subscriber

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-30 : 12:19:33

Add the alias for the subscriber to both the distributor and publisher. RDP to both servers (dist and pub) and do a telnet test for the subscriber (telnet servername listeningport). You may need to add the telnet feature to the servers, but it can be done online and without a reboot.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mattc321
Starting Member

11 Posts

Posted - 2015-04-30 : 12:37:15

The distributor and publisher are the same server (my local server on my network). I added on this server:
Alias=rSQL
port=1433
protocol=tcp/ip
server=54.x.x.x

is that correct? I will telnet to the subscriber on port 1433, but I'm betting it will work since I can add this remote server into mgmt studio just fine. Is it possible that replication should be happening on a different port?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-30 : 12:42:36

Replication uses the SQL listening port.

Is the name of the remote server "rSQL"? The alias name must match. If it's a named instance, it needs to be in the alias name too. Replication requires the usage of the exact @@SERVERNAME. Management Studio does not.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mattc321
Starting Member

11 Posts

Posted - 2015-04-30 : 14:05:12

yea, remote server: select @@servername = rSQL\SQLSERVER
select srvname from sys.sysservers = rSQL

on the local system I added an alias for rSQL that points to that IP, and could not add as subscriber
I also added an alias for rSQL\SQLSERVER and tried to connect as subscriber with no luck

while adding the subscriber I tried rSQL and rSQL\SQLSERVER as the server name. rSQL alone made an instant error siting the need for a server name. Using rSQL\SQLSERVER sited this connection error :A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.

It definitely seems like something funky with my server name/alias situation.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-30 : 14:46:24

Are you adding the alias to both the 64-bit and 32-bit locations? The only one needed is rSQL\SQLSERVER, but it needs to be in both registry locations.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-30 : 14:47:26

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

Show me what you have in both.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mattc321
Starting Member

11 Posts

Posted - 2015-04-30 : 15:52:53

OMGGGG that was it!! I didn't have the alias in the 32bit location! It connects now. Holy hell, 2 full pages of google links and not a single one mentioned that, and I only found 1 link stating that remote servers HAD to be added as an alias in order to use them as a subscriber. It seems like this sh&T should be at the top of a replication 101! Both of these servers are identical 64b so I never would have realized it needed both.

Thanks Tara, now I will get back to trying to get transnational replication to work.

Search keywords: sqlserver mssql add a remote subscriber - mssql replication to remote server - create a remote alias in mssql - mssql remote subscription

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-30 : 19:08:37

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2015-05-01 : 03:45:08

quote:
Originally posted by mattc321

Search keywords:

Good idea

Points to Tara

mattc321
Starting Member

11 Posts

Posted - 2015-05-04 : 14:27:03

Alright, thanks again for all your help. So currently, replication is failing, and was failing all weekend, although I got through configuration successfully on Friday, the repl job failed 4000 times over the weekend :P

Here is a screen shot of everything I could squeeze on the screen
http://ashlandfood.coop/ps1.png

The error is general and says to see agent job history in the jobs folder for more details. When I go to the agent job folder, I don't see anything specific, other than a netsend error regarding notifications not going out. I restarted the agent today, and it came back with the same general error. I am going to turn on verbose logging, but do either of you have any idea as to what it could be?

When I reinitialize the subscription and tell it to use a new snapshot, does that force a new snapshot to be taken of the publication at that moment?

Matt Campbell

mattc321
Starting Member

11 Posts

Posted - 2015-05-04 : 15:02:28

Ok so verbose logging gives me this error:
Message
[181] Step 2 of job SQLSERVER-Attendance-Attendance Publicatio-RSQL\SQLSERVER-3 failed but will be retried in 1 minute(

So I opened this agent job and step 2 is:
Run Agent, Type=Replication Distributor
command being executed is:-Subscriber [RSQL\SQLSERVER] -SubscriberDB [Attendance] -Publisher [SQLSERVER] -Distributor [SQLSERVER] -DistributorSecurityMode 1 -Publication [Attendance Publication] -PublisherDB [Attendance] -Continuous

This looks like a job that was automatically created when setting up replication. Here is a screen shot of step 2:
http://ashlandfood.coop/ps2.png

Matt Campbell

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources