| Author |
Topic |
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
 Posted - 2003-12-09 : 15:10:11
|
| I have a publication setup that I want to distribute to multiple laptops. The laptop servers are on my network so I thought I could just do a Push Subscription. It sets it up but then says it failed because the database does not exist on the subscriber.1. How can I have this DB automatically created when pushing a subscription?Regarding synchronization, I am confused on whether I should have the subscriber agent do this or the publisher agent. Of course I would like to keep it on the server if at all possible. These laptop will only be connected to the network occasionaly to replicate data.2. How does the publisher agent know to sychronize to a subscriber when the subscriber connects to the network?3. How can I setup the tables on a subscriber to be read-only? And/Or, how does the synchronizing agent know that data should not be accepted from a subscriber?Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-09 : 15:56:53
|
| 1. Can't be done automatically. Create a script to do it:USE masterGOIF NOT EXISTS (SELECT * FROM dbo.SYSDATABASES WHERE name = 'QMap')CREATE DATABASE QMapON ( NAME = QMap_Data, FILENAME = 'E:\mssql\data\QMap_Data.mdf', SIZE = 50 )LOG ON( NAME = 'QMap_Log', FILENAME = 'E:\mssql\data\QMap_Log.ldf', SIZE = 10 )GOYou should have the server do the work, so a push subscription.2. It doesn't know when the subscriber connects to the network. The synchronization is done through a job on the distributor server. Job is going to fail when it can not connect to the laptop. 3. You can't because replication would fail. What do you mean not be accepted from a subscriber?Tara |
 |
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-12-09 : 16:07:13
|
| 1. Thanks. How do I rerun the Push?2. So should the synchronization agent occur on the laptop subscriber? How will it know when to perform the synch if the publisher server is not available?3. I don't want any changes to occur on the publisher database from the subscriber. Only the publisher should send data to the subscribers. This data is a list of customers/employees that are used as cross-reference tables by the subscriber, and should not be changed by the subscriber.Thanks Tara. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-09 : 16:16:41
|
| 1. What do you mean rerun the push?2. I wouldn't recommend replication on laptops. Are we talking about a lot of data? If not, I would create a batch file or something similar to refresh the database either through BACKUP/RESTORE, bcp, DTS, etc...3. What you are describing is two-way replication or merge replication. You are not implementing that, so that will not happen. Only data is pushed from publication to subscriber, unless you configure two-way or merge replication.Tara |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-12-09 : 16:20:56
|
| OK, I reran the push through the SQL Agent Jobs. Thought I would be able to do that from the Distribution Agents folder in Replication Monitor but the "Run Agent at Distributor" RightClick option was disabled. How come? How will I clear up all of the Red X's?Now for another problem. My publisher tables are owned by DevDta, not dbo. But when they were replicated over to the subscriber it assigned dbo as the owners. This causes my views to fail. How do I get around this?Thanks again, & sorry for the stupid questions. Just beginning replication so I don't know squat. I have read BOL as much as possible but some things are just hard to grasp until you try it out! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-09 : 16:24:49
|
| You can't start the agent from there because it is already started. Wasn't there a green arrow on it already?You clear up the red X's by fixing the problem that occurred then running the jobs to synchronize them.Not sure about that one. Hopefully Vyas will see this thread and respond to it.Tara |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-12-09 : 16:33:46
|
| 1. See above.2. I thought this was the whole idea behind replication? So, I could have SQL Server Data offline on other machines using MSDE? I also intend on having another merge replication where data will be transferred from the subscriber to/from the publisher. Am I off my rocker here?3. I setup this pubilsher as transaction replication, and did not check any of the options for the subscriber to send updates. When I look at the properties of the publication it shows the one subscriber as a "push" type, so I assume that means that the data will only be sent from publisher to subscriber, and not the other way around. Is this correct? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-09 : 16:37:07
|
| Replication is used to update systems that are online almost all of the time. It will work in your situation, but it is going to error out when the laptop(s) is not available. Use a push subscription for the laptop database to update the main database server. Merge replication is used when you have two copies usually in two different regions or something similar and both databases are being updated and they have to be merged together. Yes the data will only be sent from the publisher to the subscriber.Tara |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-12-09 : 16:54:30
|
| Funny (not really) how many other Microsoft Reps told me that this is what I should use. Now I am really confused. I guess I could write my own data transfers when the publishing server is available but why should I when the replication tool is available? I'm gonna try it anyway.Can I manually trigger a synchronization? Let's say I have a program on the laptop that would check to see if the publisher is available. If so, can I start a synchronization then via T-SQL? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-09 : 16:57:40
|
| Microsoft Reps are right that it would work, but they need to give you more information about what to do when the laptop is unavailable and then comes back online. Yes you can start it manually with sp_start_job. You can also stop a job with sp_stop_job.Tara |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-12-09 : 17:03:24
|
| Yeah, how about it. I've known that feeling too many times!OK, so I could setup a job on the distributor server that could be called from the subscriber laptop to start/stop synchronization? Or, should I just put the synchronization agent on the laptop subscriber? Guess I will try out both. Still looking for an answer on that ownership problem. ERP's are such a PITA.Thanks alot Tara. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-09 : 17:06:28
|
| Vyas (Vyaskn is his member id) probably has the answers for the questions that I did not answer. Hopefully he'll check out this thread.Tara |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2003-12-09 : 17:18:21
|
| Ken, regarding your table ownership probelm, see if the following helps:http://vyaskn.tripod.com/repl_ans3.htm#ownerI wrote this FAQ entry in SQL 7.0 days -- hopefully the solution will work for SQL2K too. Try it and let us know, I am too lazy to try it now :-)Regarding synchronizing with laptops on the move, many people use the replication ActiveX controls, to write their custom apps, and control replication. Search BOL for replication and ActiveX. I've also seen cases, where people made use of Windows Synchronization Manager. Again, BOL has info on this.--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-12-10 : 12:54:05
|
| Thanks Vyas, I will definitely be looking into the Activex controls.I did the changes for the ownership as suggested, but now the push fails because:The process could not connect to Subscriber 'KTB-OPTIPLEX'.Login failed for user 'PRODDTA'.I have established that I am passing the correct user/pw by creating an ODBC connection to the KTB-OPTIPLEX.JDE database with the PRODDTA user/pw.Here is the Run Agent statement arguments...-Subscriber [KTB-OPTIPLEX] -SubscriberDB [JDE] -Publisher [KTB-D5BM2331] -Distributor [KTB-D5BM2331] -DistributorSecurityMode 1 -PublisherDB [JDE] -SubscriberLogin [PRODDTA] –SubscriberPassword [MyPDPW] –SubscriberSecurityMode 0I also added PRODDTA to the Publication Access List in the Publication Properties.Any thoughts?Thanks |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2003-12-10 : 13:08:33
|
| Ken, the parameters look okay to me, but could you remove those square brackets around login name and password? I don't like them, and avoid them when not necessary :-)Also, could you use that login and password in Query Analyzer to connect to the subscriber server, and then change the context to the subscribing database? Wondering if the login is not mapped to a user in the subscriber database.--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-12-10 : 13:17:17
|
| 1. I've tried it with/without brackets and quotes to no avail, but I will remove them.2. Query Analyzer ran just fine logging into KTB-OPTIPLEX as PRODDTA with password and then USE JDE. |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2003-12-10 : 13:29:18
|
| If it works fine from QA, then I don't see why the merge agent will fail.Just in case, are you testing the connection from the distribution server?--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-12-10 : 13:47:17
|
| That makes two of us.Yes I am testing from the distribution server (KTB-D5BM2331) machine connecting to server KTB-OPTIPLEX JDE database using PRODDTA user.I also tried eliminating the arguments and changing the login properties of the KTB-OPTIPLEX server (subscription server) to PRODDTA and that added the tables with dbo owner, and of course failed on the views. Did an sp_who on subscriber server and saw a runnable connection from publication server with loginname of PRODDTA. So much for that idea.Any possibility you could try this for me?Thanks. |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2003-12-10 : 15:00:50
|
| Too late today, but I'll try to test it tomorrow - but then we have a big Christmas party at work tomorrow, so lets see how it goes :-)--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-12-10 : 15:37:03
|
| OK, thanks. Just to make sure I have updated to SQL2K SP3 - no change. Interesting thing is that I also added a -DistributorLogin PRODDTA -DistributorPassword PRODDTA -DistributorSecurityMode 0 and that works without a problem.The only other way I could think of to do this would be to create a "replication" dbo owned database of my PRODDTA production database on the same server, and then create all of my laptop subscriptions off of the replication database.However I think it's time for me to contact MS... |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2003-12-11 : 11:01:23
|
| Boy, this is the first time that MSDN technical support worked in under 15 minutes.The answer is that you can set the Destination Owner for each article in the publication through the Articles pagetab of the Publication Properties. Click on the elipsis of each Article and you will see where they can be set. |
|
|
|
Next Page
|
How to deploy/synch Subscriptions