MS-SQL Replication

I need help setting this up, I am not a DBA but I am a MCSE, and network administrator. My company has moved to MS-SQL from as400 and I need to set up replication between California and Pennsylvania. I am having many problems doing this.

I get login errors. timed out errors, or "name cannot be NULL"

Is there someone that can walk me thru this to get this replication set up and working. I am on a tight time line our go live date is March 1 and I have to have PA set up by Feb 21 for testing.

mjedsLogin error: ensure that domain accounts of trusted domains are used;
Timeouts: can be of different nature (login, command?), be specific;
NULL name: you're probably including BE code with dynamic SQL, which is not a good candidate for replication.

You also need to tell us what type of replication you're trying to set up.|||A. Determine replication model - update frequency and connectivity are key factors. Push vs. Pull is also important. For example, don't attempt near-real time replication over dial-up. Bottom Line - what does your business need/expect? Note: what they want sometimes != what is possible.

B. Start simple - like, with 0 and 1 and then progress to a whole byte. Document what you do as you do it. You won't succeed on your first attempt, so be prepared for the second (i think i finally saw the light on my 417th attempt).

C. Make sure your servers have names for themselves in sysservers. This doesn't qualify as high-level guidance, but it will solve the problems you quoted. Timeout is likely due to the fact one server it is attempting to connect to another server that does not exist - like, NULL, for example.

D. Chill, if you have reliable network connectivity and required database resources, you'll have this working in less than two days.

E. More info: http://www.dbforums.com/showthread.php?p=4093086

Just remember, we're all counting on you. Leslie Nielsen, Airplane|||Ok let me go in detail on this:

Server in California: Attached to a Windows Server 2003 Active Directory Domain, I have an internal DNS server and this SQL is being resolved.

Server in PA: Is a domain contoller (back up to CA server), also has DNS for the PA site.

CA is hooked to PA via IPSec VPN tunnel. via T1's on both sides. all Externel IP's are static and NAT'd. Both systems are on same/shared subnet.

What is needed:

Real time replication, when changes are made at either site the databases must be updated and match. Both side use unique invoice and shipping numbering sequences so over lapping invoices and shipping information won't occur.

So now what I need (and excuse me for sounding novice) is a step by step instruction on how to set-up, test, and run replication between the servers via the IPSec VPN.

Thanks for any help you can provide.

mjeds--|||Sounds like you need Merge Replication, the most difficult to understand and implement. FYI - I lean towards Pull vs. Push, especially if the servers aren't on the same 1 Gigabit or better subnet. In otherwords, create Pull Subscription(s).

There is no such thing as real-time replication. You must communicate this to your business and help them understand they should be expecting five minute delays b/w the machines. You should be able to deliver < 2 minute delays, consistently. Wow 'em.

Do not attempt continuous (sorry, forget the correct term and don't have access to BOL, but I would encourage you to expect connection problems, and attempting continuously updating subscriptions is not for the replication newbie) or real-time replication on this network environment unless the business can afford extended downtime and you enjoy staring at replication monitors 24x7 (it gets old, fast).

Before attempting merge replication, start simple (see B. supra).

0. You will remove the following - it is a suggestion/excercise to familiarize you with replication. START SMALL.

1. Set up a pull subscription from PA to CA. CA is the publisher (with the distribution database on the same server) and PA is the subscriber.

2. Use pubs or a test database of your own. Create YourTest0 table.

3. Create a publication/article on CA.pubs.YourTest0 table. Make sure the CA server is in the master..sysservers table.

4. Create a subscription on PA.pubs.

5. Insert a row in CA.pubs..YourTest. Ensure it replicates to PA.pubs.YourTest0. Now do an Update and confirm the change on PA.pubs.YourTest0.

6. You're 45 % there! Repeat steps 2-5, swapping CA for PA and PA for CA , and YourTest1 for YourTest0. When successful, you will have bi-directional replication between CA and PA.

7. You're 90% there! Unfortunately, the final 10% is 90% of the effort.

8. Final 10% - merge YourTable0 and YourTable1. Too much to explain with my weak typing skills, so memorize replication in BOL. J/K - but, become extremely familiar with it. When done, make a plan and then work on your real data. Note: I hope you have a test environment or you are really good at testing in production or you like to work during off-hours.

PLAN, PLAN, PLAN or regret. You have to understand replication before you can plan for it. After you get the tests working, you should be able to write out and execute a plan for your production environment. Trust me, the benefit from creating the plan is > 10X the cost of winging it. Been there done that.

Make it so. Captain Picard|||OK I setup a merge replication, CA as the distributer, PA as subscriber. I made a backup of our production DB and restored it under a different name (orig name was LOALIVE, test is MJEDSTEST1) so this is a full production DB.

I am able to push but not pull, I set the schedule up to snapshot every 15 mins (it takes 11 min just to create the snapshot) and the replication also at 15 min.) my testing IPSec is a 384k DSL, the live connection will be a 1.5mbps T1, so I think the timing should be better.

The "pull" still gives me the NULL error. But push seems to work just fine.

Now am I to understand that I should also make the PA server a distributor and reverse the setup so that both PA and CA are pushing to each other in non concurrent time frames? i.e. CA (distributor) --> PA every 15 min and PA (distributor) --> CA every 20? min or something to that effect?

mjeds--

Update: the push errored out the error (Unable to bulk copy $$$$$$ to table $$$$$)

also when I try to connect to the database with the client software I get the following: "The object table has no ROWVERSION column in the SQL server table description"|||You've made tremendous progress and although you have been more successful with Push, you still want to go w/ Pull. Let's get that working.

Recompile your stored procedures, or better yet, see article in General Replication Info., below, to fix the ROWVERSION problem.

The Null problem is because sysservers does not have correct info. Go here. (http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/1ab91a3bab5d438f/931ee622e49ac83a?q=replication+name+sysservers+@.@.s ervername&_done=%2Fgroups%3Fhl%3Den%26q%3Dreplication+name+s ysservers+@.@.servername%26qt_s%3DSearch+Groups%26&_doneTitle=Back+to+Search&&d#931ee622e49ac83a)

Bi-directional Replication. (http://support.microsoft.com/default.aspx?scid=kb;en-us;820675)

General Replication Info. (http://www.replicationanswers.com/General.htm)

You owe me a nickel. Next post will cost you a cup of coffee.|||I'll buy you a cup of coffee, hell I'll send you a pound of coffee beans if I get this to work.

I don't understand where to find or change the info in sysserver, remember I am not a DBA this is my first experience with SQL.

Screen shots would be great if possible, my email is medwards@.lightsofamerica.com.

Thanks for your help you have been great so far. :D

mjeds--

update: another issue, after a successful push i attempted to open the database in our client software (navision 3.70a) and got this error:

the (database name) database on the (sql server name) does not contain Microsoft Navision system objects and cannot be opened.|||Test everything from Microsoft SQL Server Tools (Query Analyzer, SQL Enterprise Manager, iSQL, oSQL) whatever blows your skirt up. Test your app. last. Don't do battle with Navision, yet.

Punt the Push - stick to the Pull or we're going to have difficulty seeing eye-to-eye (or, keyboard-to-keyboard or post-to-post :D ).

Per the Go here. mentioned previously:
Check to see that the entry in sysservers for your database says "local." @.@.servername should "work" if this is the case.

To make sure that the local server is correctly described in sysservers, you need to do 'exec sp_addserver <SERVERNAME>, 'local'

Provide a current status after confirming my suggestions above, then I'll email you if necessary to earn that pound of beans (my consulting rate just jumped!).

You're very close to flying... just gotta miss the ground.|||I know this sounds stupid, but I don't know where to find, check or change the sysservers table. okay I found the table in the master - what do I do with it. Again my apologies for sounding stupid, but a week ago I had never seen SQL.

And like a lot (a lot) of companies; mine does not feel the necessity to hire an SQL consultant or otherwise, I'm the "computer guy" the ASSumption is I know everything and anything related to the computer world. And though I am pretty good I don't know everything yet (however as Comm Wil Riker once said "I plan to live forever")

Anyway, I am a newbie at this, how where what do I do to fix change modify the sysserver table.