Excessive Network Usage With SQL 2000 To SQL 2005 Transactional Replication

Aug 3, 2007

We have a SQL2000 database (Publisher) replicating inserts and updates across a 10Mb link to a SQL 2005 database (Subscriber). The Publisher has two tables we are interested in, 1 with 50 columns and 1 with 15. Both tables have 6 insert/update triggers that fire when a change is made to update columns on the publisher database.
We have set up a pull transactional replication from the Subscriber to occur against the Publisher every minute. We have limited the subscription/replication configuration to Publsih 6 columns from table 1 and 4 from table 2. Any change occuring on any other columns in the Publisher are of no interest. The SQL 2005 database has a trigger on table 1 and table 2 to insert values into a third table. There are around 7,000 insert/updates on table 1 and 28,000 on table 2 per day. All fields in the tables are text.
We are seeing "excessive" network traffic occuring of approximately 1MB per minute (approx 2GB per 24 hrs). We also see that the Distributor databases are getting very large -- upto around 30GB and growing until they get culled. We have reduced the culling intrval from 72 hrs to 24 hours to reduce the size.
Does anyone have any suggestions as to how this "excessive" network traffic can be minimised and how the distributor database size can be minimised. I think that maybe they are both related?

Thanks,
Geoff
WA POLICE

View 5 Replies


ADVERTISEMENT

Transactional Replication From 2005 To 2000 Issue

Nov 8, 2007

Hi,

I setup transactional replication between 2005-> 2000 database for only one table. It works fine no problem. I checked replication monitor everything works well.
My Subscription was Push Subscription on Publisher.

This morning I restored main database at publication server, I saw all my publication configuration were gone.

I then went to create a publication and push subscription again..and did it, But when I went to replication monitor.
I checked that Snapshot are being created, but from distributor to Subscriber is saying below message?

"The concurrent snapshot for publication is not available because it has not been fully generated.....lONG CHPPPED off meassage"


Can any body tell me hwo to get over this issue?

Many Thanks

SKR

View 7 Replies View Related

Transactional Replication : SQL 2000 To 2005. Add New Table

Mar 2, 2007

I have a publication on 2000 machine, replicating by pull subscription to a 2005 machine. I added a table to the Articles in the publication. Reinitialized the subscription, and also restarted both the snapshot and log reader agent. I was kind of assuming the publication would automatically create the table on the subscriber, but after a very long wait it is still not there. Am I missing anything?

PS. Distination Object setting : "Action if name is in use" is set to drop and create new, not that that should make any difference since name is not in use anyway.

Thanks

View 4 Replies View Related

Transactional Replication From SQL 2000 Sp4 Publisher/distributor To SQL 2005 Sp2 Subscriber

Jun 26, 2007

Hello,



Are there any requirements that dictate the SQL Server version for the distribution agent for a SQL 2000 publisher with a transactional push subscription to a SQL 2005 subscriber?



Thanks in advance,



John

View 3 Replies View Related

Transactional Replication From SQL 2005 To SQL 2000 Affects Performance Of Database.

Sep 21, 2007



Hello,

We previously having two servers A and B. Server A is used for updation of data and the data then replicated to server B. Server B is used for

Server A :
purpose : used for database updation/ modification
SQL Server version : SQL Server 2000 SP 2

Server Z :
purpose : used for Reporting
SQL Server version : SQL Server 2000 SP 2


We were doing Transactional replication from Server A to Server B.

Last month we have broght another server (Server B) with same hardware configuration but having SQL SERVER 2005 installed. This is to speed up our database update process. We have moved some of the database on this new server so that we can achieve our deadlines.

Server B :
purpose : used for database updation/ modification
SQL Server version : SQL Server 2005

I have set up the transactional replication from Server B to Server Z and replication works fine.
However, the issue is after it is started replicating from this new server (Server B) performance of all the queries reduced a lot.(making my life harder)

I didnt expected this as our reporting server is still SQL server 2000.
I have restored the backup of database which was replicated from server A (sql server 2000) and compared execution plan for one of our common query (which is used in most of the reports and which is now taking longer time to provide results)

I found that database which is replicated from Server B (Sql server 2005) is having primary keys. which was not present in the database which replicated from server A(Sql server 2000).

I have then removed the primary key and make the indexes same as previous copy of database(which was replicated from server A) But still the query takes long time.

Execution plan now shows "Table Spool" which was not present in previous copy of database.

Almost every query for this database is taking longer time now.

Can someone suggest me what is wrong and what should I need to fix.

Am I going on the right direction?


View 14 Replies View Related

SQL 2005 Server (SP2) To SQL 2005 Express (SP2) Replication Looping/Excessive Repl Times

Sep 25, 2007

We have a large number of clients attempting to replicate two publications on 2005 Express databases (2 publications subscribed to the one subscriber database) with our 2005 Server (9.00.3042.00 SP2 Standard Edition) and experiencing two significant problems:

1) Users experience the following message:

The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload).

This problem should not apparently occur with SQL Server 2005 (or 2005 Express) instances with SP2 applied. All clients experiencing this problem have SP2 installed as does our Server and the retention period is 30 days. The subscribers have been replicating well under that.


2) Replications never succeed after appearing to replicate/loop around for hours

This issue is the most critical as we have clients who have been installed and re-installed with new instances of SQL Server 2005 Express, new empty databases (on subscriber before snapshot extraction), and using fresh snapshots (less than an few hours old) which cannot successfully replicate.

Interestingly there is at least 1 instance where several computers are subscribed and successfully replicating the same database as another where replication refuses to succeed.

To test we have taken a republished database from another 2005 Server which is working fine and restored it to the same server as the one holding the database with which we are experiencing problems and subscribed to it. This test worked fine and replication of both publications went through fast and repeatedly without showing any signs of problem.

This indicates that the problem is perhaps data related as it appears localised to that database.

Below are two screenshots which may assist.

Screenshot 1 Shows that on the server side the replication attempts look like they are succeeding despite the fact that the subscriber end does not indicate success. Also the history indicates the the subscription has spent all it's time initialising and not merging any changes.

Screenshot 2 Shows a rogue process which has appears on many of the problem child subscribers. It shows a process running with no end time even though the job indicates failure in the message and even though other replication attempts appear to have succeeded after it. This process stays in the history showing that it is running even when I can find no corresponding process for it.

Can anyone suggest a further course of action/further testing/further information required which may assist?

This is extremely urgent and any assistance would be greatly appreciated!

Thanks in advance!

Scott

View 5 Replies View Related

Excessive Memory Usage

Nov 27, 2006

Hi there group.

Could some please point me in the right direction?

We have a database and it's about 28GB in size, recently the SQL server process that runs uses approximately 1.6GB of Memory.

I have tried running SQL profiler to find out which Stored Procedure is causing this but came up unsuccessful.
When restarting SQL the process it run's at about 50MB for about 20sec and then starts climbing up to 1.6GB of memory usage.

Please assist.

View 12 Replies View Related

Transactional Replication From SQL Server 2005 To SQL Server 2000

Nov 8, 2006

I am trying to test simple replication (only tables) of a database that resides on a SQL Server 2005 instance to a SQL Server 2000 instance. The Publisher and Distributer are set up on the SQL Server 2005 instance for Transactional replication. The subscriber is set up on in the 2000 instance. Replication Monitor shows the following error after applying a few scripts:
"Category: SQLSERVER Source SQLSERVER2000 Number: 170 Message: Line 6: Incorrect syntax near
'max'."


Here SQLSERVER2000 is the name of my 2000 instance, as should be obvious.

Beyond this point, replication fails. Any pointers as to where the problem could lie? Is this a known backward compatibility issue? I've checked all tables in the database and none contain any datatype that is new to 2005 (the database was actually created in and for SQL Server 2000.

Replication from 2000 to 2005 works fine, but the other way round is failing as described above. Any clues?

View 3 Replies View Related

SQL 2000 Transactional Replication

Feb 20, 2008

I doing a transactional replication with two servers. Server A, the Publisher and Distributor. Server B, the Subcriber. I did the replication using a dummy DB for practice purpose and it works fine. When I try to implement the replication in original DB, I found that some tables don't have primary keys and to be able to implement transactinal replication all the replicated tables need to have a primary key. I want to add the primary keys to those tables and also implement the replication but the problem is those tables have data in it. I'm thinking in create the new table and do a DTS to import the data to the new table or it's fine just to modify the table in design to add the primary key "Those table have record in it".

Some of you have any advice to me?

View 4 Replies View Related

SQL 2000 Transactional Replication Issue

Nov 17, 2006

I have tried setting up transactional push replication using stored procedures and using wizards. I have two Publishers that are their own Distributors, and 1 Subscriber. All servers are running SQL 2000 SP4 build 2187. Replication appears to be working correctly, but on the Subscriber, under Replication > Subscriptions, the replication type is showing as 'Snapshot' instead of transactional for both subscriptions.

Any ideas?

Thanks.

View 4 Replies View Related

Initializing Transactional Replication Without A Snapshot In SQL 2000

Nov 10, 2006

SQL Server 2005 Books Online provides an article entitled, "Initializing a Transactional Subscription without a Snapshot". Is it possible in SQL Server 2000 to initialize transactional replication without a snapshot?

So far, I have been unable to find a similar procedure mentioned in the SQL 2000 Books Online. I was able to follow the 2005 procedure using SQL 2000 until I got to the step that says to enable the "Allow initialization from backup files" option on the "Subscription Options" tab of the "Publication Properties" dialog. But that option does not appear in the SQL 2000 version of the specified dialog box.

Any ideas?

View 4 Replies View Related

SQL 2000 Transactional Replication-skiperrors Parameter With Error Logging

Mar 15, 2007

I have used skiperrors parameter for 2601,2627 and 20598 errors in my Transaction replication setup. When transactions with above error are encountered, its skipped but I€™m not getting sql command that is skipped in MSrepl_errors table. Since SQL server 2000 is not saving transaction sequence number in the MSrepl_errors we are not getting the command, which have the errors.


Is there any way to track the commands those are skipped in transactional replication.

View 11 Replies View Related

Transactional Replication - 2005

Jun 20, 2008

Hi All,

I am trying to configure Transactional replication with updatable subscription sql server 2005 with service pack 2.

Creating distributor and publisher on the same box, but subscriber on a different server.

Distributor and publisher are configured without any problem but when I try configuring subscriber, it throws below warning.



TITLE: New Subscription Wizard
------------------------------

Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication.

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "REPLLINK_QA-WIN2K3-1413475264_ADVENTUREW1643387368_WELLCORE-542849619_ADVENTUREW1643387368" was unable to begin a distributed transaction.
Changed database context to 'AdventureWorks'.
OLE DB provider "SQLNCLI" for linked server "REPLLINK_QA-WIN2K3-1413475264_ADVENTUREW1643387368_WELLCORE-542849619_ADVENTUREW1643387368" returned message "The transaction manager has disabled its support for remote/network transactions.". (Microsoft SQL Server, Error: 7391)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3152&EvtSrc=MSSQLServer&EvtID=7391&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------



I tried setting this up directly on the Subscriber machine using sp_link_publication but the same error.


Any help or suggestion are welcome.



Thanks
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

View 7 Replies View Related

How Do I Measure Bandwith Usage For Merge Replication SQL 2005 To Sql Server Mobile 2005?

Jan 24, 2007

Hi,

Is there any way to measure bandwith usage during merge replication between sql server 2005 and sql server mobile 2005 running on a cradled wm5 mobile device.

Attaching the windows performance monitor to the network connection established over usb would work although I was wondering if there was something specific for this case integrated into Sql server 2005 / sql server mobile 2005 / Sql server management studio / third party tools that i could use ?

thnx,

pdns.

View 4 Replies View Related

Transactional Replication - Updatable Subscribers - Image Data Type- Sql Server 2000

Aug 21, 2007

Sql Server 2000


I am looking for bidirectional transactional replication using updatable subscribers (queued or immediate) . Is it possible to replicate the image data from the updatable subscribers to the publisher. I understood that the Image data can't be replicated to the publisher from the updatable subscriber. I am not using the WRITETEXT or UPDATETEXT. I am using just INSERT and UPDATE for image data type transactions.

Any thoughts on this is greatly appreciated.

Thanks in advance

View 3 Replies View Related

Sql Server 2005 -Not Allow The Transactional Replication

Aug 29, 2006

we have the standard licensed copy for sql server 2005

while configure transaction replication not allow to move

message is required licesence for replication again i have to get license for

replication

please advice

View 2 Replies View Related

2005 Transactional Replication Statement Delivery

Oct 3, 2006

Hi There

WHen creating publciations under 2005 i saw a very interesting option under stament delivery, for inserts , updates , deletes there is an option that simply says insert/update/delete statement.

I could find very little in BOL about this under "Article Properties", is this what it soudns like ? FOr example if you say :

update Table set Cloumn = 'whatever', this will not trigger the update sp for each row at the subscriber , will it actually deliver the update statements and literally do the update/insert/delete statement at the subscriber?

Thanx

View 9 Replies View Related

2005 Transactional Replication Performance Problems - Help Please

Sep 27, 2007



I have just upgraded from 2000 to 2005 and my transactional replication is running very slow, I already have a latency of 10 mins and its getting worse. I'm just using the default agent profile, is there anything I need to change?

Help please.

View 19 Replies View Related

Oracle 10g -&&> SQL 2005 Enterprise Transactional Replication

May 18, 2007

I have one Oracle Server (Version 10g) with some simple tables which should be replicated to an SQL 2005 Enterprise Server.

i got this error:

Error messages:
The process could not bulk copy into table '"dbo"."S_AGRZ"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
Batch send failed
Violation of PRIMARY KEY constraint 'MSHREPL_38_PK'. Cannot insert duplicate key in object 'dbo.S_AGRZ'. (Source: MSSQLServer, Error number: 2627)
Get help: http://help/2627
To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below. Consult the BOL for more information on the bcp utility and its supported options. (Source: MSSQLServer, Error number: 20253)
Get help: http://help/20253
bcp "RS"."dbo"."S_AGRZ" in "F:Microsoft SQL ServerMSSQL.2MSSQLReplDatauncACPRS2_DISTRIBUTION_S_AGRZ20070518023422S_AGRZ_2.bcp" -e "errorfile" -t"<x$3>" -r"<,@g>" -m10000 -SSVIESQL04RS2 -T -w (Source: MSSQLServer, Error number: 20253)

i set the publisher to drop the table and create a new one when i reinitialize with a new snapshot. it only works when i remove the unique key constraint. but after that the data in the replicated table has wrong entries. a select count ... will show the exacly same number as an select count ... on the original table direktly on the oracle server. i reinitialzied the replication multiple times with new snapshots. but the problem does not resolve. first i got a unique key constraint. then i typed truncate table ..., after that i removed the the unique constraint and than all data will be imported but there are some entries that are not existing in oracle.

my server is patched with the newest updates for sql 2005 (Version 9.0.3159).

for exempla the following statement will show the same results on oracle and sql 2005:

Oracle:
SELECT COUNT(*) FROM S_AGRZ
Result: 471.066

MS-SQL:
SELECT COUNT(*) FROM S_AGRZ
Result: 471.066

but, for example this will show differences:

Oracle:
SELECT * FROM S_AGRZ WHERE art_nr='C7972A'
Result:





ART_NR
AGRA_CD
AGR_CD
AGRZ_DTANLAG
AGRZ_USERANLAG
AGRZ_DTAENDG
AGRZ_USERAENDG

C7972A
HW
005
NULL
NULL
NULL
NULL

C7972A
KO
490
NULL
NULL
NULL
NULL

C7972A
R
KEIN
NULL
NULL
NULL
NULL

MS-SQL:
SELECT * FROM S_AGRZ WHERE art_nr='C7972A'
Result:

C7972A R KEIN NULL NULL NULL NULL
C7972A HW 005 NULL NULL NULL NULL
C7972A HW 240 NULL NULL 2007-05-11 09:13:19.000 SCHWARZJ
C7972A KO 490 NULL NULL NULL NULL
C7972A R AUT NULL NULL NULL NULL
C7972A KO 490 NULL NULL NULL NULL


The scripts for the tables:

Oracle:

CREATE TABLE S_AGRZ
(
ART_NR VARCHAR2(20 BYTE) NOT NULL,
AGRA_CD VARCHAR2(2 BYTE) NOT NULL,
AGR_CD VARCHAR2(15 BYTE) NOT NULL,
AGRZ_DTANLAG DATE,
AGRZ_USERANLAG VARCHAR2(10 BYTE),
AGRZ_DTAENDG DATE,
AGRZ_USERAENDG VARCHAR2(10 BYTE)
)

ALTER TABLE S_AGRZ ADD (
PRIMARY KEY
(ART_NR, AGRA_CD));


ALTER TABLE S_AGRZ ADD (
FOREIGN KEY (AGRA_CD)
REFERENCES S_AGRA (AGRA_CD));

ALTER TABLE S_AGRZ ADD (
FOREIGN KEY (AGRA_CD, AGR_CD)
REFERENCES S_AGR (AGRA_CD,AGR_CD));


MS-SQL:

USE [RS]
GO
/****** Object: Table [dbo].[S_AGRZ] Script Date: 05/18/2007 03:13:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[S_AGRZ](
[ART_NR] [nvarchar](20) NOT NULL,
[AGRA_CD] [nvarchar](2) NOT NULL,
[AGR_CD] [nvarchar](15) NOT NULL,
[AGRZ_DTANLAG] [datetime] NULL,
[AGRZ_USERANLAG] [nvarchar](10) NULL,
[AGRZ_DTAENDG] [datetime] NULL,
[AGRZ_USERAENDG] [nvarchar](10) NULL
CONSTRAINT [MSHREPL_37_PK] PRIMARY KEY CLUSTERED
(
[AGRA_CD] ASC,
[AGR_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

does anyone know how to solve this problem?

View 5 Replies View Related

Large SQL Update - Effect On SQL 2005 Transactional Replication

Feb 1, 2007

I'm a newbie to Replication and recently setup the following.

Publisher and Distributor on the same SQL2005 server, then I've got 7 subscribers(SQL2000 servers) and I'm using push subscriptions. I'm replicating 5 SQl tables which don't have too many changes and these are scheduled to run every 3 hours. In a few days a large one off SQL update with add an additional 10,000 rows to one of the replicated tables. I was wondering what impact this would have on the above setup i.e are there any sort of limitations here. I'm assuming not but thought I would check. I'm thinking it will just cause additional overhead on the server, but the update is being applied when no users will be using the database.

Any feedback greatly appreciated.

Thanks

View 1 Replies View Related

SQL 2005: Transactional Replication &> Executing Sp On Subscribershould Execute On Publisher

Jan 16, 2008

Hi,I am trying to set up a Updatable Subscriptions for TransactionalReplication with SQL 2005 (queued updating).I have a stored procedure "SYS_spShellTest" which is replicated to thesubscriber viathe replication option "Execution of the stored procedure":-------------------------------------------------------------ALTER procedure [dbo].[SYS_spShellTest] @cmd varchar(1000)WITH EXECUTE AS ownerasBEGINEXEC @result = master..xp_cmdshell @cmd, NO_OUTPUTReturn (@result)END-------------------------------------------------------------1) If I call "exec SYS_spShellTest" on the publisher the call is alsoexecuted on the subscriber.2) If I call "exec SYS_spShellTest" on the subscriber the call is NOTexecuted on the publisher.How can I make 2) work so that it is also executed on the publisherplease?Thanks a lot,Patrick

View 1 Replies View Related

SQL Server 2005 - Transactional Replication Involving Depricated Text And Ntext Data Types

Dec 2, 2005

I'm currently trying out transactional replication with updatable subscriptions across two 2005 servers.

View 3 Replies View Related

Combining Peer-to-Peer Transactional Replication With Standard Transactional Replication

Dec 1, 2006

Hello,

I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:

We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.

Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?

We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.

That's the scenario.

Any feedback is welcome and appreciated.

Thanks,
Terence

View 4 Replies View Related

SQL Server 2000 Cluster - Excessive Page Faults

Jul 31, 2007

We are running SQL Server 2000 Enterprise Edition on a 2-node cluster with IIS/ASP.NET front-end hosting 150-200 active connections. There is a SVCHOST process running under LOCAL SERVICE account - hosting the Remote Registry process that is using only 4,200K but is page faulting 200-500 times per second. I realize this process is used for failover, but the page fault seems excessive. Any thoughts on this?

The servers are running Windows Server 2003 with 4 processors and 4gb RAM.

View 1 Replies View Related

Replication Issues After A Database Restore - Unable To Drop Or Create Transactional Replication

Sep 13, 2007

Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.

View 2 Replies View Related

Trouble Enumerating SQL Server Instances With SQL 2000/2005 On Network

Dec 4, 2007



Hi,

We have (after several weeks of testing in all kind of environments) send out a new version of our application to several of our customers. Within days problems where drippin in; After looking for the problem on various customer situations we found a problem which I think is rather disturbing and very odd. I'll describe the situation, on which we finally managed to recreate the problem, here.

In my problem I use the following configuration:

Windows 2003 (standard edition) AD network with 2 domain controllers, multiple Windows XP workstations, some without SQL instances, some with SQL 2000 instances, some with SQL 2005 instances and even one with SQL 7 running.
All run a 32 bit OS.

Tools to reproduce:

ListSQLSvr application (found on SQLDev.net) to enumerate the instances.


Problem description:
--------------------------------------------------
I am running the machine called DEV001, which has SQL 2000 (instancename DRUMIS) and SQL 7.0 (has no instancename so this is the root instance) installed.

In any 'normal' situation all the runnings SQL instances are visible on the network like this:




Code Block
C:>listsqlsvr -X
(local);Clustered:No;Version:7.00.623
ADM002DRUMIS;Clustered:No;Version:8.00.194
DEV001DRUMIS
DEV001DRUMIS;Clustered:No;Version:8.00.194
DEV002DRUMIS;Clustered:No;Version:8.00.194
DEV002EXPRESS;Clustered:No;Version:9.00.3042.00
DEV002EXPRESS
INSADBACKOFFICEDRUMIS;Clustered:No;Version:8.00.194
INSADBACKOFFICEEXACT;Clustered:No;Version:9.00.3042.00
INSADOFFICEWSUS;Clustered:No;Version:8.00.194
SUP001DRUMIS;Clustered:No;Version:8.00.194




When I turn the SQL 2005 Browser service off on the machine called DEV002 the list looks like:




Code Block
C:>listsqlsvr -X
(local);Clustered:No;Version:7.00.623
ADM002DRUMIS;Clustered:No;Version:8.00.194
DEV001DRUMIS;Clustered:No;Version:8.00.194
DEV001DRUMIS
DEV002
DEV002EXPRESS
DEV004DRUMIS;Clustered:No;Version:9.00.3042.00
INSADBACKOFFICEDRUMIS;Clustered:No;Version:8.00.194
INSADBACKOFFICEEXACT;Clustered:No;Version:9.00.3042.00
INSADOFFICEWSUS;Clustered:No;Version:8.00.194
SUP001DRUMIS;Clustered:No;Version:8.00.194




Notice that the browser service might be off on DEV002, you can still see the EXPRESS instance and a new root instance has appeared (though it doesn't exist!)??

After restarting the Browser service all is OK again.

When I turn on Hide Server in the SQL 2000 TCP/IP properties (or turn it on in the registry [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerDRUMISMSSQLServerSuperSocketNetLibTcp] "TcpHideFlag"=dword:00000001) on the DEV002 computer something real scary is happening..
The list looks as follows:



Code Block
C:listsqlsvr -X
(local)
ADM002
DEV001
DEV002
DEV004
INSADBACKOFFICE
INSADOFFICE
SUP001






When someone has entered a database (for example the backoffice database on INSADBACKOFFICEEXACT) the list looks as follows (for a short moment; 5 secs or so):



Code Block
C:listsqlsvr -X
(local)
ADM002
DEV001
DEV002
DEV004
INSADBACKOFFICEDEVELOP;Clustered:No;Version:8.00.194
INSADBACKOFFICEDRUMIS;Clustered:No;Version:8.00.194
INSADBACKOFFICEEXACT;Clustered:No;Version:9.00.3042.00
INSADOFFICE
SUP001




Notice now that ALL instances are gone and no extended information is available. In the Query Analyser and in the SQL Management Studio when browsing you'll see this as well!
When someone is accessing a database instance it appears for a few seconds again.

Since our installation and applications rely on selecting a existing instance it will fail in the above situations (or at least not showing all available instances).

In my opinion this is a bug somewhere!
Note that even when the SQL Services are stopped on DEV002 (leaving the Browser service running) it still seems to block out ALL instance on the ENTIRE network!

I don't mind that one INSTANCE or even the entire MACHINE is hidden from the network, but ALL instances on ALL machines??

And the SQL Browser issue also worries me a bit since it does not stop the possibily to browse the SQL instances; it removes the SQL2000 instances but adds a root instance which doesn't even exist! Also the extended info is stripped.

Can anyone help me solve this/advise?

Also mind that in any situation there might run a lot of computers with a lot of SQL instances and I cannot tell our customers to find which machine has the SQL TCP/IP properties set to Hide...
It even seems that in some situations SBS 2003 does the hiding automatically on Install? And if so, when and why?

Regards,

Albert van Peppen
Senior System Engineer
Insad Grafisch b.v.

View 31 Replies View Related

Replication :: Transactional Replication Removal Sequence

Sep 2, 2015

I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication.  I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS.  I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication.  SQL-P (publisher), 

SQL-D (distributor) and SQL-S (subscriber).  Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.

View 6 Replies View Related

Merge Replication Set Off Transactional Replication

Oct 9, 2007

I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.

This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??

Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??

thanks...

View 5 Replies View Related

ASP.Net 1.1 With SQL 2005 : General Network Error. Check Your Network Documentation

Oct 4, 2007

Hi Every one,

I am using .Net 1.1 for my web application.

And my database server is SQL Server 2005.

My application is running fine, as i can login to it and also able to view pages. But when i open Order management(having 3K records) its give me error,

General Network Error. Check your network documentation


I have also searched many articles and tried following solutions but nothing working

- connectiontimeout = 0, max pool size = 7500/100, pooling = false

- SSL disabling enforce security false as mentioned in microsoft kb article.

And there's nothing any issue with hardware/firewall as my application's login and other forms are working fine(which use same database with same connection string)

Can any one please help me to solve this error?

With Regards,

View 3 Replies View Related

What Will Going From Latin1_General_Bin To Latin1_General_Bin2 In SQL 2005 Do To Replication From 2000 To 2005?

Aug 3, 2006

If we changed the sort order from BIN to BIN2 but kept everything else the same will it have any effect on replication? So in SQL 2005 if I were to change my default collation from Latin1_General_Bin to Latin1_General_Bin2, would that cause replication to break? I suspect that it will not be an issue since it is just sort order that is changing and the code page stays the same.

BTW, this is transactional replication.  Sorry, I left that out of my original post.

Thanks,
Grant

View 1 Replies View Related

Transactional Replication

Jul 30, 2001

On my distribution server there is a directory called mssql7
epldataunc that houses the bcp, sch and idx files for replications. A directory is created for each publication. Most directories are empty but some contain all the table idx, sch and bcp files for the publication. Some of these files are over 6 month. Does anyone know if these files are neccessary to keep and why do only some publications have these files. I am wondering if they are kept when you push the publicaiton and it errors out originally and you have to restart it? If any one can shed some like on this I would appreciate it.

View 1 Replies View Related

Transactional Replication

Dec 28, 2000

Hi,
I am planning to do a transactional replication from production server to reporting server. In one of my database is having 150 tables in it, only 100 tables are having primary key . Remaining tables are not have primary keys. So i am not able to do transactional replication for those 50 tables. Could any one tell me how i have to approch transactional replication. Because i need to have production data at my reporting server in regular interval.
So pls suggest me .
I am using Sql 7.0 with service pack2.

Thanks!
Kavira

View 1 Replies View Related

Transactional Replication

Feb 13, 2001

I have a problem with transactional replication in SQL Server 7. I have two databases (a publisher-distributor server and a suscriber server) and when I do an UPDATE in the publisher database the Log Agent Reader reads the UPDATE as an DELETE-INSERT and when the suscriber pull the suscription and try to do first the DELETE the transactional replication stops because of the Foreign Keys in the suscriptor database (that are the same as the publisher).
If anybody knows how to solve it, please tell me

Thanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved