Hi all,
I was hoping someone can assist me in a problem that has been going on
for some time.
We have been experiencing replication problems for a while, when
replicating data (1 table of approx 150,000 records, 1 of 75,000) to
our SQL server on our website.
We have noticed a lot of general network errors, and although it often
sorts itself out, it sometimes fails and needs to be started by hand.
The data changes in batches every hour and sometimes 5% of the records
may be replaced.
This has recently got worst, to the point that SQL server is now
refusing to connect to our web database when I delete and re-push the
replication job. It simply waits for quite a while and then times
out. If I try and replicate a small table from the same publisher to
the same subscriber it works fine.
Does anyone have any ideas?
By the way
Using SQL server 2K, transactional replication over a 2Mb leased line.
Many thanks
Chris
enable replication logging to see where it is failing.
Follow the instructions here on how to enable logging.
http://support.microsoft.com/default...b;en-us;312292
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Chris Bartington" <chris.bartington@.orange.net> wrote in message
news:175eba94.0411231017.76ce38d3@.posting.google.c om...
> Hi all,
> I was hoping someone can assist me in a problem that has been going on
> for some time.
> We have been experiencing replication problems for a while, when
> replicating data (1 table of approx 150,000 records, 1 of 75,000) to
> our SQL server on our website.
> We have noticed a lot of general network errors, and although it often
> sorts itself out, it sometimes fails and needs to be started by hand.
> The data changes in batches every hour and sometimes 5% of the records
> may be replaced.
> This has recently got worst, to the point that SQL server is now
> refusing to connect to our web database when I delete and re-push the
> replication job. It simply waits for quite a while and then times
> out. If I try and replicate a small table from the same publisher to
> the same subscriber it works fine.
> Does anyone have any ideas?
> By the way
> Using SQL server 2K, transactional replication over a 2Mb leased line.
> Many thanks
> Chris
Showing posts with label network. Show all posts
Showing posts with label network. Show all posts
Wednesday, March 7, 2012
Saturday, February 25, 2012
MSSQL Server connectivity
Hi,
I have two Windows machines in the Local Area Network. One of the machines has MSSQL Enterprise Server 8.0 installed whereas the other has MSSQL Server 2005 installed. When I try to establish the connectivity between these two servers, I can not do so. When I try to look up Network servers, the two machines can't see each other. How can I make them talk to each other? Is there some type of driver or some such thing to be installed which can help the matter?
Please let me know.
Need a bit more information to help you:
Are the instances default or named?
Do you have any firewall software running on either server?
What is the error msg you're getting?
Can you use SQLCMD to connect to the remote server?
Thanks for the response.
Answers to your questions:
1. The MSSQL Server instances are the default ones.
2. I don't think I have a firewall running on the servers.
3. When I go to SQL Server Management Studio and try to register the server by looking on the network, I can not find my other machine with Enterprise Manager 8.0. So I don't get any error, but I can not see the machine either.
4. Login failed for user 'xyz'.
HResult 0x2746, Level 16, State 1
TCP Provider: An existing connection was forcibly closed by the remote host.
Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
This is the error that I see on the command prompt.
Here's what I suggest: You may want to go through my answers above, but I think I don't know much about this new SQL version? Can you quickly list a set of steps I can follow to connect from SQL Server 2005 to another machine having SQL Enterprise Manager 8.0 running on it? Thanks in advance.
|||Enterprise Manager has nothing to do with remote connectivity. This is what I understand you're doing:
Machine 1: Running SQL Server 2000, default instance
Machine 2: Running SQL Server 2005, default instance
From Machine 2 running SQL Server Management Studio you want to connect to Machine 1.
First, make sure that Machine 1 is setup to accept remote connections. This is documented in Books Online for SQL Server 2000.
You'll want to make sure you're providing the correct credentials: Windows or SQL Server depending on how the instance is configured.
|||Hi Dan,
Thanks for the response. However, we somehow managed to do the connectivity between the 2 servers. Apparently, the SQL user which we were using to establish the connectivity did not work initially. We dropped and recreated the user and then it went through fine. Don't know why it did not connect initially.
I have another problem though. And I would really appreciate if someone could help with this real quick.
So, there's this MSSQL 2005 installed on a new machine, which has 40 G on C Drive and 250+ G on a D Drive. But all of my C Drive has been taken up by "tempdb.mdf". It has taken up nearly 26 G of space on this drive. Why should that be the case? I know that this is a System database that gets created when one installs MSSQL Server, but I have never seen it this big. Is there a problem with the installation that I can fix? Please let me know.
Thanks.
|||This is certainly suspicious. I'm not expert on tempdb so I recommend you post this issue to http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1. Someone there will certainly be able to help you.MSSQL Server connectivity
Hi,
I have two Windows machines in the Local Area Network. One of the machines has MSSQL Enterprise Server 8.0 installed whereas the other has MSSQL Server 2005 installed. When I try to establish the connectivity between these two servers, I can not do so. When I try to look up Network servers, the two machines can't see each other. How can I make them talk to each other? Is there some type of driver or some such thing to be installed which can help the matter?
Please let me know.
Need a bit more information to help you:
Are the instances default or named?
Do you have any firewall software running on either server?
What is the error msg you're getting?
Can you use SQLCMD to connect to the remote server?
Thanks for the response.
Answers to your questions:
1. The MSSQL Server instances are the default ones.
2. I don't think I have a firewall running on the servers.
3. When I go to SQL Server Management Studio and try to register the server by looking on the network, I can not find my other machine with Enterprise Manager 8.0. So I don't get any error, but I can not see the machine either.
4. Login failed for user 'xyz'.
HResult 0x2746, Level 16, State 1
TCP Provider: An existing connection was forcibly closed by the remote host.
Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
This is the error that I see on the command prompt.
Here's what I suggest: You may want to go through my answers above, but I think I don't know much about this new SQL version? Can you quickly list a set of steps I can follow to connect from SQL Server 2005 to another machine having SQL Enterprise Manager 8.0 running on it? Thanks in advance.
|||Enterprise Manager has nothing to do with remote connectivity. This is what I understand you're doing:
Machine 1: Running SQL Server 2000, default instance
Machine 2: Running SQL Server 2005, default instance
From Machine 2 running SQL Server Management Studio you want to connect to Machine 1.
First, make sure that Machine 1 is setup to accept remote connections. This is documented in Books Online for SQL Server 2000.
You'll want to make sure you're providing the correct credentials: Windows or SQL Server depending on how the instance is configured.
|||Hi Dan,
Thanks for the response. However, we somehow managed to do the connectivity between the 2 servers. Apparently, the SQL user which we were using to establish the connectivity did not work initially. We dropped and recreated the user and then it went through fine. Don't know why it did not connect initially.
I have another problem though. And I would really appreciate if someone could help with this real quick.
So, there's this MSSQL 2005 installed on a new machine, which has 40 G on C Drive and 250+ G on a D Drive. But all of my C Drive has been taken up by "tempdb.mdf". It has taken up nearly 26 G of space on this drive. Why should that be the case? I know that this is a System database that gets created when one installs MSSQL Server, but I have never seen it this big. Is there a problem with the installation that I can fix? Please let me know.
Thanks.
|||This is certainly suspicious. I'm not expert on tempdb so I recommend you post this issue to http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1. Someone there will certainly be able to help you.Monday, February 20, 2012
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.
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.
Subscribe to:
Posts (Atom)