• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

help with first replication setup

I'm trying to set up translactional replication from our local server to our host sql database and need a little help getting there.

So far I have successfuly set up a local public replication and now need to set up a push subscription to the remote database. When I go through the new subscription wizard in management studio I do not see the remote database in the window where you choose a subscriber.

I have already registered the remote server and am connected to it..I can see it in the object explorer.
How to I connect to it?
0
Shawn
Asked:
Shawn
  • 12
  • 8
1 Solution
 
HwkrangerCommented:
You have to create the subscriber on the remote machine.  (I dont' do transaction, I do merge, but I think it's the same it's just where the agent lives is the difference)

So what you want to do is create the subscriber from the subscribing machine (that's the remote machine that's recieving the transaction, not the publisher)

Under that replication folder on that machine there's a "Local subscription" which will allow you to make the subscription.

hope this helps!

I'm pretty solid in Merge replication, I've done quite a few handling hundreds of users.  If this doesn't work for you, post back and I'll actually walk through a transactional replication set up.
0
 
HwkrangerCommented:
I just double checked this.  

1) Create the Subscription (You've done this)
2) Go to the Subscriber machine (that's the remove machine) Under "replication", "local subscriptions", right click and select "add new subscription" and voila, a little wizard pops up.

I ussually use scripts to generate mine, but this should work for you just fine.  Good luck!
0
 
reb73Commented:
Here's a pretty detailed link with screenshots, if it helps..

http://blog.csdn.net/longrujun/archive/2006/06/09/783357.aspx
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ShawnAuthor Commented:
as the remote database is an internet host they have given me limited access to the database and subscriptions. I can see the local subscriptions on the remote machine but do not have access to it.
they have recommended a push subscription from my local machine.
0
 
HwkrangerCommented:
The subscription still has to be created, regardless of whatever access you have.  You'll have to script the subscription and have it run on that machine.

That's what I do with my merge replication.
0
 
HwkrangerCommented:
additional note:

If you're using the "internet" (that's extra-inter-webz for u young folks) for replication, you'll also have to set up the webservice for this to work.  But either way, you still need to create the subscription on the subscribing machine.  

Best of luck.
0
 
ShawnAuthor Commented:
>> you still need to create the subscription on the subscribing machine.  
even for a pull subscription? I was under the impression the local machine could link to the remote, then pull..sorry, never done this before
0
 
HwkrangerCommented:
To create the subscription.  yes.  

Think of this as you as a person, and the news paper you get.  The news paper company can deliver the paper -- but first, you have to sign up to get the paper delivered.  

The delivery and subscription are separate.

The push subscription just means that the distributor box will handle the scheduling and delivery of the data (news paper company delivers your paper vrs you going to pick it up)

Pull subscription means the subscriber initiates the delivery.
0
 
ShawnAuthor Commented:
ok, at least I have that concept down now. thx

I can access the remote db another way...through myLittleAdmin for SQL Server 2005
it has in the tools section New Query and Generate INSERT script. will this help6
how do I set up a script?
0
 
HwkrangerCommented:
To create the script, create an example subscriber on your local machine.  At the end of the wizard, it'll have the option to create the script.  You'll have to change some values (Like the name of the target db, etc) but they are rather evident.

You'll also have to register the subscriber with the publisher.

here are some example scripts for *MERGE* replication


/* NOTES:
		MAKE SURE TO CHECK THE DATABASE NAME: [EHD_0]  Change if Needed
		MAKE SURE TO CHECK @HostName, This is the APPLICATION User Name for the snapshot to be applied [eg. foobar]
	*/
 
-- STEP 1:  CREATE DATABASE
 
	USE [MASTER]
	CREATE DATABASE XXXXX
 
-- STEP 2:  ADD USER 
-- DELETED ---
-- STEP 3:  Replication Scrips
		-- A)  RUN Replication Script (EHD)
-----------------BEGIN: Script to be run at Subscriber 'PQL-XXXXX\SQLEXPRESS'-----------------
use [XXXXXX_0]
exec sp_addmergepullsubscription
	 @publisher = N'XXXXX', -- THIS MUST BE THE MACHINE NAME FOR THE PUBLISHER
	 @publication = N'XXXXX_Replication',
	 @publisher_db = N'XXXXX_Replication',
	 @subscriber_type = N'Local',
	 @subscription_priority = 0,
	 @description = N'',
	 @sync_type = N'Automatic'
 
exec sp_addmergepullsubscription_agent
	 @publisher = N'XXXXXX',
	 @publisher_db = N'XXXX_Replication',
	 @publication = N'XXXXX_Replication',
	 @distributor = N'XXXXXX',
	 @distributor_security_mode = 1,
	 @distributor_login = N'',
	 @distributor_password = null,
	 @enabled_for_syncmgr = N'True',
	 @frequency_type = 4,
	 @frequency_interval = 1,
	 @frequency_relative_interval = 1,
	 @frequency_recurrence_factor = 0,
	 @frequency_subday = 8,
	 @frequency_subday_interval = 1,
	 @active_start_time_of_day = 0,
	 @active_end_time_of_day = 235959,
	 @active_start_date = 0,
	 @active_end_date = 99991231,
	 @alt_snapshot_folder = N'',
	 @working_directory = N'',
	 @use_ftp = N'False',
	 @job_login = null,
	 @job_password = null,
	 @publisher_security_mode = 1,
	 @publisher_login = null,
	 @publisher_password = null,
	 @use_interactive_resolver = N'False',
	 @dynamic_snapshot_location = null,
	 @use_web_sync = 1,
	 @internet_url = N'https://XXXXX/WSXXXXX/',
	 @internet_login = N'XXXX',
	 @internet_password = N'XXXXX',
	 @internet_security_mode = 0,
	 @internet_timeout = 300,
	 @hostname = N'newuserwithdata'
GO
-----------------END: Script to be run at Subscriber 'PQL-DALEXANDER\SQLEXPRESS'-----------------
 
 
	/**  I (RLV) WILL RUN THIS ON THE SERVER DO NOT RUN **/
		-- B)  RUN Replication Script 		use [XXXXXX_Test]							-- DOUBLE CHECK THIS LINE.  This is DatabaseName of the publisher
		exec sp_addmergesubscription
		 @publication = N'XXXXXX_Replication_Test',
		 @subscriber = N'XXXXXX\SQLEXPRESS',   -- DOUBLE CHECK THIS LINE.  THIS IS SERVER/INSTANCE NAME
		 @subscriber_db = N'EHD_0',						-- DOUBLE CHECK THIS LINE.  THIS IS TARGET DB NAME
		 @subscription_type = N'pull',
		 @subscriber_type = N'local',
		 @subscription_priority = 0,
		 @sync_type = N'Automatic'
		GO
 
-- SCRIPT TO SYNC THROUGH DOS
-- CHECK:
--		-Subscriber [XXXXXX\SQLEXPRESS]
--		-SubscriberDB [EHD_0]
--		-HostName [foobar]
--replmerg -Publisher [PQL-SQL1] -PublisherDB [CurrentSafe_Replication] -Publication [CurrentSafe_Replication] -Distributor [PQL-SQL1] -SubscriptionType 1 -Subscriber [PQL-DALEXANDER\SQLEXPRESS] -SubscriberSecurityMode 1 -SubscriberDB [EHD_0] -HostName [foobar]
 
	

Open in new window

0
 
ShawnAuthor Commented:
ok here's the script I generated locally. tested and it works. Now to replace local values for remote values.

here is the remote info. now let's try to put it together :-)

Database Type:  Microsoft SQL Server 2005
Database Name: araxi111sqlsite_rem <<--this is a blank db ready to be populated
HOST/Server Address: VINDRIZI-H3.LNC.NET (ip 62.197.38.206)
user:      sfadmin
pw:      mypassword




-----------------BEGIN: Script to be run at Publisher 'FS1\FS12K5'-----------------
use [araxiSQLsite]
exec sp_addsubscription @publication = N'pub_araxi111_HomeToHost', @subscriber = N'FS1\FS12K5', @destination_db = N'test_remote_replDB', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'pub_araxi111_HomeToHost', @subscriber = N'FS1\FS12K5', @subscriber_db = N'test_remote_replDB', @job_login = N'araxitc\administrator', @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20090429, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher 'FS1\FS12K5'-----------------

Open in new window

0
 
HwkrangerCommented:
That's the part to add the subscriber to the publisher.  You'll also have to create a subscription at the subscriber.
0
 
HwkrangerCommented:
It should look something like this:


	-- B)  RUN Replication Script 		use [XXXXXX_Test]							-- DOUBLE CHECK THIS LINE.  This is DatabaseName of the publisher
		exec sp_addmergesubscription
		 @publication = N'XXXXXX_Replication_Test',
		 @subscriber = N'XXXXXX\SQLEXPRESS',   -- DOUBLE CHECK THIS LINE.  THIS IS SERVER/INSTANCE NAME
		 @subscriber_db = N'EHD_0',						-- DOUBLE CHECK THIS LINE.  THIS IS TARGET DB NAME
		 @subscription_type = N'pull',
		 @subscriber_type = N'local',
		 @subscription_priority = 0,
		 @sync_type = N'Automatic'
		GO

Open in new window

0
 
HwkrangerCommented:
Actually I relooked.  taht should be all you needed.  The script you generated just has to be run on the Publisher.

0
 
ShawnAuthor Commented:
yes but I'm not sure how to replace the values

how does the remote site find our local site?
I have opened our port 1433 to the host. our ip to the firewall is 174.1.32.207
0
 
HwkrangerCommented:
You need to use the webservice if you're doing this over the internet.  

http://msdn.microsoft.com/en-us/library/ms345206.aspx

You should first set everything up and test it between two boxes that you have -- then add the webservice component.  it requires SSL -- which if you don't have a certificate, you'll have to get one.
0
 
HwkrangerCommented:
Just so you know how it all works, this is my recommendation to you

1) Set up a publisher and subscriber on your local machine.
2) Run the replication
3) Make sure you're happy with how it works
4) Move this to 2 separate boxes
5) Install the webservice components
6) update the syncronization to use the webservice
7) Test it
8) Make sure you are happy with how it works
9) Learn from everything you did and deploy it to your scenario

using the webservice is a little tricky.  I found a bunch of issues when I was doing it -- like you can't use a fake SSL certificate.  You can get a 90 day one from Komodo and that will work.  

In the original script i supplied, there's an example of calling the replication through dos.  The subscription holds the connection information so you can use that DOS command to sync across the network or across the internet.

Replication isn't something you jump into in an afternoon.  you're doing pretty good for your first try.  I've been doing it awhile -- and I still hit bumps.
0
 
ShawnAuthor Commented:
are you sure I need to do this for transactional replication?
the article specifically says "procedure in this topic is the third step in configuring Web synchronization for merge replication"

transactional replication is read only.

good pointers and thanks for the encouragement :)
0
 
HwkrangerCommented:
I'll be honest, I don't do a lot of Transactional replication.  Web syncronization is supported by Merge replication, if it's not supported by Transaction replication -- I'm not sure how they communicate over the internet.  You could always set up a merge replication and set all the tables as "Download Only."  This would in essence be the same as transactional.  

If you have further questions, feel free to shoot me an e-mail.  My username at AOL
0
 
ShawnAuthor Commented:
I'm giong to stick with Transactional replication for now. thanks fr your input so far.
0
 
ShawnAuthor Commented:
I did this locally and it worked perfectly. After several discussions with the host it turned out replication was disabled on that server and will take some time to change settings as it's shared hosting.
In the meantime I found a workaround and find it actually better.
FWIW I split the database on the site in to 2 sections: section 1 - "relatively" static pages"  and 2 - the dynamic pages. Section one resides on the host server and is accessed for quick display. Section 2 resides in our office db and is accessed through a Coldfusion DSN.
thanks for the help.
Shawn
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 12
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now