August 16, 2010 at 2:41 pm
I am in the process of exploring SS 2008. I have 64-bit Standard Edition (version 10.0.2531.0) that I am trying to setup a Snapshot Replication to a SS 2008 Express Edition with Advance Services (version 10.0.2531.0). I have set up the a Publication and the Snapshot occurs with no problem.
When I try to set up a Push subscription SS Express (on my desktop), I get either permission problems or exceeds the number of retrys.
I've tried all the accounts I can think of, made sure that they were in the PAL (Publication Access List), SysAdmin Accounts on both ends, etc.
I have made sure the SS Exp allows Remote Connections.
I have tried setting up the Push Subsription through SSMS by right clicking the Publication on SS 2008 and following the wizard for New Subscriptions and also tried right clicking Local Subscriptions on the SS 2008 Exp.
Is there something that I might not have done on the Express side to enable subscriptions? Do I have to set up the subscription through scripts instead of SSMS. I'm ready to give up.
Steve
August 30, 2010 at 7:55 am
Haven't seen any response yet. Also, haven't seen it on the Newsletter.
Steve
August 30, 2010 at 9:01 am
SQL Server express does not have SQL Server Agent. So you have to use Replication Management objects to set it up. Please keep in mind that express can act only as subscriber. MSDN has a good write up on how to set up replication on SQL Server Express using RMO.
-Roy
August 30, 2010 at 11:46 am
Roy,
Thank You. I will check it out and let you know how it goes.
Steve
August 31, 2010 at 1:28 pm
Roy,
I found out that my problem was not starting the SQL Server Browser on the instance of Sql Server Express so it did not allow remote connections. I had enabled ftp for the service, set up Express to allow mixed security and selected allow remote connections, but had missed this piece somewhere along the way.
http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/
I did create the subscription from a script and they worked fine.
Thanks
Steve
August 31, 2010 at 1:37 pm
Steve, Thanks for posting back. If anyone else have problem, then they will see this solution.
-Roy
January 11, 2011 at 12:31 pm
Roy,
Hi I'm having the same problem, actually I have not tried to run my replication yet, but need some help understanding what to do next.
I have SQL Server 2008 installed as my production server, and need to do a snapshot replication to my SQL Server Express 2008 R2 server. I understand that I can have the production server to generate the snapshot, to where the snapshot will be avaliable and filtered the way I need it via the GUI on the production server. But what about the SQL Server Express 2008 R2 server (which is my subscriber), which is on the same network not the same machine.
Since SQL Server Express 2008 R2 has no SQL Agent, what T-SQL replication stored procedures will I have to use in a T-SQL Script to establish the snapshot to my SQL Server Express 2008 R2 server (which is my subscriber). Most of the work is done on the SQL Server 2008 (production server), so what else in there of the subscriber side. Or can someone point me to a script template that can be used to do this...
Thank you for your help
January 11, 2011 at 12:44 pm
Make sure the distribution server runs on SQL Server 2008 that has SQL Server agent running in it. You cannot use the Express (Your subscriber) to act as distributor. The distributor will need the SQL Server Agent running. Either use a dedicated box or use the publisher to host distributor as well.
-Roy
January 11, 2011 at 1:04 pm
Roy,
Great that's how I have it pictured at the moment, My SQL Server 2008 (production server) will be the publisher and distributor. The only thing SQL Server Express 2008 R2 is the subscriber, so your saying that I dont have to generate a script to get this snapshot replication to work without issues? Or just because the SQL Server Express 2008 R2 server has no SQL Agent? Only if I wanted the distributor to be on the subscriber server (SQL Server Express 2008 R2 server)...right?
Again thank you for your help... 😉
January 11, 2011 at 1:08 pm
You can set up snapshot replication with Script or by just using the GUI. It is easy to use the GUI.
Even if you have a script, you cannot use the SQL server 2008 as your distributor.
-Roy
January 11, 2011 at 1:14 pm
Be sure to do a push subscription to the Sql Server Express. The push subscription will run from the ss agent on your publisher / distributor.
Steve
January 11, 2011 at 1:17 pm
You mean the SQL Server Express 2008 R2 as a distributor...Right? I'm sure you can use the SQL Server 2008 as a distributor... :w00t:
January 11, 2011 at 1:19 pm
Yes yes... Sorry. Typing fast and not reading before you post will give you embarrassing moments.. 🙂
-Roy
January 11, 2011 at 1:21 pm
Thanks Steve..... thank you very much.....
January 11, 2011 at 1:22 pm
Thank Roy, I was getting ready to jump off the roof..... 😛
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply