Performance Issue Of Adding A New Subscriber
Apr 12, 2007
I Have a replication, that has a publication and a few pull-subscriptions. The replication passes a data to subscribers. New data or update of existed data on a publication database are happen often. Each time a one subscription pulls from the publisher, it gets a new data or updates. All pull occurs every 2 minutes. I need to add a new subscription. Usually a synchronization of new subscription takes 1.5 hour. I see that a synchronization of a new subscription reduces performance of existed subscriptions.
1.How can I monitor influence of synchronization on existed subscriptions?
2.If I use alternate snapshot location, does it reduce an influence of a synchronization on existed subscribers?
3. Does a subscription, that was configured to use snapshot from alternative location, use it always? Can I configure that a subscription does first synchronization from alternative snapshot location and all followed synchronization from default place?
View 5 Replies
ADVERTISEMENT
Mar 19, 2003
Has anyone come across problems after upgrading a Server and adding CPU's. We had 4 originally, with 4GB RAM, the CPU's were 100% with some queries, we added an additional 4 CPU's and the support guys 'did something else'. (I think they actually installed some service Packs on Win2000).
Performance seems to have gone down since the upgrade.
Any ideas ?
View 8 Replies
View Related
Jul 23, 2005
I'm considering adding domain integrity checks to some of my database tableitems. How does adding such constraints affect SQL Server performance? Forexample, I have a simple constraint that restricts a couple of columns tohaving values within the values assigned in my application by anenumeration:(([Condition] >= 0 and [Condition] <=3) and ([Type] >= 0 and [Type] <=2))This enforces domain integrity for two enumerations having values 0, 1, 2, 3and 0, 1, 2 in the application. Is this an efficient way of performing suchchecks? What are the pitfalls of domain integrity checking?ThanksRobin
View 1 Replies
View Related
Sep 3, 2015
Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.
View 7 Replies
View Related
Sep 12, 2004
1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection
above, which way would be faster and get a better performance?
View 2 Replies
View Related
Jun 23, 2006
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies
View Related
Apr 13, 2007
Hi,
Im using transactional replication to replicate data from one database to another database.
my aim is to make a customized replication using c#. so im creating publisher and subscriber through sql code.
but main problem is if i select a table as a publisher then it will fully replace the schema of subscriber.
eg:
Before Creating a subscription
Publication Name :TestPubVer
Publisher Table-->CustomerOrder
----------------------
CustomerID
CustomerName
CustomerAddress
Subscriber Table--> Consumer
--------------------
ConsumerID
ConsumerName
ConsumerAddress
if i create a subscription , it will try to search for CustomerOrder Table , if it is not present it will create a new table with publisher schema.
if it is present then also it will replace the publisher table schema in subscriber table and a same table is created in subscriber database
To avoid this im replacing the subscriber Table name with a different table during article creation
eg:
exec sp_addarticle @publication = N'TestPubVer', @article = N'CustomerOrder', @source_owner = N'dbo', @source_object = N'CustomerOrder',
@type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'none', @destination_table = N'Consumer', @destination_owner = N'dbo', @status = 24,
@vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboConsumer]', @del_cmd = N'CALL [sp_MSdel_dboConsumer]',
@upd_cmd = N'SCALL [sp_MSupd_dboConsumer]'
After this i will create a subscription
After Creating a subscription
Publication Name :TestPubVer
Publisher Table-->CustomerOrder
----------------------
CustomerID
CustomerName
CustomerAddress
Subscriber Table--> Consumer
--------------------
CustomerID
CustomerName
CustomerAddress
After subscription the subscriber tablename will be same but column names are get replaced by publisher table.
How can i avoid this? i tried many ways and im running out of time.
regards
Baji Prasad.B
View 11 Replies
View Related
May 1, 2000
Additional info,
I had recently applied SP2 on two servers (publication and not working immediate updating subscriber).
I found this code in the INSERT trigger which fails
exec @retcode = OpenDataSource
('SQLOLEDB',N'SERVER=MYSERVER;UID=sa;PWD=t00bad;') .[Packageval].[dbo]. [sp_MSsync_ins_Package_1][PDBSASQL1],[Packageval],
@c1 output,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9,@c10 output
Any idea?
Thanks
In article <8e7dqp$lii$1@nnrp1.deja.com>,
skhanal@my-deja.com wrote:
> Hi,
>
> I have two servers configured as Immediate updating subscribers to the
> publication server.
>
> Everything was ok for sometime, but now when I try to insert a record
> in any table in the published database in one of the subscriber I get
> the following error.
>
> Server: Msg 7415, Level 16, State 1, Line 3
> Ad hoc access to OLE DB provider 'SQLOLEDB' has been denied. You must
> access this provider through a linked server.
>
> Inserting the same record in the other subscriber and publisher does
> not have any problem.
>
> I could not find any documentation for this error. Any help will be
> greatly appreciated.
>
> Thanks
View 2 Replies
View Related
Sep 24, 2001
Hi!
How is it possible to set up a repl. scenario, where a subscriber of a DB
is also the publisher of this DB for other subscribers. i mean 3 level repliction. MAINPUB
SUB1 SUB2 SUB3
notebookSUB1...................notebooksubN
SUB1..3 are subscribing to MAINSUB and publishing to a number of notebooks!
Any help would be greatly appreciated!!!
TIA
View 1 Replies
View Related
Jan 31, 2007
Hi,
Can we use transactional replication with updatable subscriptions when we have more updates and transactional changes at the Subscriber side??
We are using SQL Server 2005 SP1. We will be using 6 publications and 4 subscriptions for each publications, so totally 24 subscriptions. Both the Publisher and the Distributor is the same server. It is set to run in continuous mode. Type : Pull Subscriptions.
Regards,
Swapna.B.
View 6 Replies
View Related
Aug 29, 2006
Hi
I'm a bit confused right now. I am using Sql 2005 merge replication. I have a publisher and a subscriber running. Today, I updated some data in my publisher which caused 300,000 updates to be sent to the subscriber. There was no problem here, it took a while though. Later, I added a new subscriber and when it started initializing I suddenly thought that I should have really created a new snapshot, one that included the 300,000 row update. Because I didn't I was epecting that once the subscription had finished initializing from the old snapshot it would then have to apply the 300,000 row update. But it didn't seem to do this last update! But yet all the data was correct!!
Does Sql replication somehow merge the snapshot with pending updates when it creates a new subscription?
Thanks for your help
Graham
View 1 Replies
View Related
Oct 2, 2007
I am having a problem creating a publisher.
I have a database, MobileApp, on a server called Server03, which is a subscriber to a database Stock on Server01. I create the database as a subscriber on Server01, and it gets replicated to Server03 correctly.
I now want to use MobileApp on Server03 as a publisher to mobile devices. I click to add a new publication, and add all the tables. I get all the way to the end, but when I click on finish, I get an error for each table - 'an article with a different subscriber_upload_options value already exists for object.
Any ideas please?
Thanks
Pete
View 6 Replies
View Related
Dec 11, 2006
Hi all,
Publisher
SQL 2000 Snapshot publication timed to run at 23:00 daily All tables & views are published articles.Snapshot agent runs and creates a snapshot of all articles.
Subscriber
SQL 2005 Pull subscription set to run continuously Manually run the SQL agent job and I get the following error... 2006-12-11 15:04:35.687 Agent message code 20164. Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber.
I've tried creating the subscription database from a backup so I know all the tables & views exist and also tried just creating a blank empty database - same error.
There are no user stored procedures or functions in the database.
If I'm publishing all tables & views then why should the data & structure fail to load at the subscriber? Do I need two publications, the first containing the metadata and the second containing the data?
Thanks in advance
Dave
View 6 Replies
View Related
Aug 26, 2007
I have a clustering between Server A and Server B. There is a clustered SQL Server installed.
Server A is a primary node(oltp production server) and Server B is a secondary node.
i've set up transactional replication betw serer A and B. Server A is Publisher and Server B is subscriber. B also has a distribution database. I use serve B for all the reporting purposes.
Currently both servers have sp1.
Now i want to apply sp2 to the server B first since i use it for reporting purposes (just to make sure there are no issues and then after some days i want to apply sp2 on server A which is my prodction oltp server)
So when i apply sp2 on server B , my publisher will have SP1 and subscriber (& distributor) will have sp2. Will this cause any issues with replication?
Any help would be appriciated.
Thanks
View 4 Replies
View Related
Aug 6, 2013
What is the syntax for adding a column where you are adding a year to a date in a date format? For example adding a column displaying a year after the participation date in date format?
View 1 Replies
View Related
Sep 6, 2001
SQL Server 7.0 SP3
I am using the "Pull Subscription Wizard" to set up merge replication. I go through all the steps of the wizard and then I hit "Finish" at the end. When it gets to the third step (Creating the Subscription), I get the old:
__________________________________________________ _________
mmc.exe has generated errors and will by closed by Windows.
You will need to restart the program.
An error log is being created.
__________________________________________________ _________
This happens whether I try to do this from my PC or from the actual server I am creating the subscription on.
Has anyone else had this issue? Is there a hotfix I need?
Any help is appreciated.
Thanks,
Craig
View 1 Replies
View Related
Jul 2, 2003
Hi all!
I hope anyone here can help me with this tough problem:
I have a replication enviroment with several subscribers.
Publisher:MSSQL 2000, SP3
Subscribers: MSSQL200, SP3 and MSDE2000, SP3
Since a few weeks, one of the subscribers fails when
synchronizing with the message
"This process could not enumerate changes at the subscriber"
The erroroutput with verboselevel 3 doesn't help me much, I
append it to the end of this posting. (unfortunately german)
I heard that this error can be caused by a "blank" in an image
column or different Service Pack Versions - definitely not
possible in this case!
The only strange detail is that the SQL Server Versionnumber
of this MSDE shown via EM is not the same as the number shown
in controlpanel->software. By the way, is it ok that here are
two lines for the MSDE?
SP3 is definitely installed on all machines!
Any help would be greatly appreciated - i'm already losing hope...
Best Regards, Gert
Microsoft SQL Server-Merge-Agent 7.00.623
Copyright (c) 1998 Microsoft Corporation
Percent Complete: 0
Verbindung mit Abonnent 'MyServerAT074'
Connecting to Abonnent 'MyServerAT074.MyDB'
MyServerAT074.MyDB: {call sp_MSgetversion }
Percent Complete: 0
Verbindung mit Verteiler 'MyServer1s'
Connecting to Verteiler 'MyServer1s.'
MyServer1s.: {call sp_MSgetversion }
MyServer1s.: {call sp_helpdistpublisher (N'MyServer1s') }
MyServer1s.distribution: select datasource, srvid from master..sysservers
where srvname = N'MyServer1s'
MyServer1s.distribution: select datasource, srvid from master..sysservers
where srvname = N'MyServerAT074'
MyServer1s.distribution: {call sp_MShelp_merge_agentid (0, N'MyDB',
N'MyDB', 9, N'MyDB')}
Percent Complete: 0
Initialisiert
MyServer1s.distribution: {call sp_MShelp_profile (19, 4, N'')}
Percent Complete: 1
Verbindung mit Verleger 'MyServer1s'
Connecting to Verleger 'MyServer1s.MyDB'
Connecting to Verleger 'MyServer1s.MyDB'
MyServer1s.MyDB: {call sp_MSgetversion }
Percent Complete: 3
Ruft Publikationsinformationen ab
Percent Complete: 4
Ruft Abonnementinformationen ab
Connecting to Abonnent 'MyServerAT074.MyDB'
Disconnecting from Abonnent 'MyServerAT074'
Percent Complete: 4
Uploadet Datenänderungen zum Verleger
Percent Complete: 5
Ruft die Liste der Löschungen von MyServer1s.MyDB ab
Percent Complete: 5
Verarbeitet Artikel 'First'
......
SNIP
....
Percent Complete: 5
Verarbeitet Artikel 'LastTable'
Percent Complete: 0
Der Prozess konnte die Änderungen auf dem Abonnenten nicht aufzählen.
Percent Complete: 0
Category:SQLSERVER
Source: MyServerAT074
Number: 0
Message: Attributverletzung eingeschränkter Datentypen
Attributverletzung eingeschränkter Datentypen
Disconnecting from Abonnent 'MyServerAT074'
Disconnecting from Verleger 'MyServer1s'
Disconnecting from Verleger 'MyServer1s'
Disconnecting from Verteiler 'MyServer1s'
View 1 Replies
View Related
Jul 8, 2004
:confused:
I want to call a stored procedure to do the insert into my published arcticle/table. Is this possible? I know you can call a stored procedure to insert into the subscriber arctiles.
The problem is the published database has a generated primary key (max +1 )that is calculated via a stored procedure during all inserts. I would bring the stored procedure over to the subscriber but that would not help since the values in the target table will not be 'current' because the subscriber database is only updated nightly.
Thoughts?
View 4 Replies
View Related
May 25, 2008
I have a sql server 2005 database that I have set as the publisher and an sql express database that I have set as the subscriber.
I set it that the 2005 db should push to the sql express db.
when i look at the replication monitor I get an error
The process could not connect to subscriber name/sqlexpress
what can I do to get this working?
I really am trying to just replicate 1 table every minute from the sql 2005 db to the sql express
Please advise?
View 9 Replies
View Related
Mar 28, 2007
hi,
I'm reading the MS online docs about replication. there's something not so clear until so far.
I have one central server and 7 client servers.
Sometimes new data is entered at one client server. This should be replicated (when client comes online) to the Central Server (merge replication).
Also, the other way, once the central server gets new data (that was replicated from a client), it should be again replicated to all 6 other clients (from the central server).
now, who should i configure as the distributor, publisher and subscriber?
View 3 Replies
View Related
Nov 29, 2007
I have problem like this.
I have 2 server with merge replication
the first as distributor and the second as subscriber.
I'll try to make trigger from database replication on distributor.
the problem is if any change data on subscriber, i need the user name from subscriber.
right now, i used user_sname to get user name.
but on distributor always get name Administrator.
it's because when subscriber update or delete data, distributor always change/delete/add data from subscriber as Administrator.
but, we know data change from subscriber it's from user. I need to retrieve user name that who change the data.
someone have any solution.
This is my triggers
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [tr_insert_jour] ON [dbo].[Jour]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
declare @date1 datetime, @person varchar(30) , @journal_code char(4), @journal_no char(8)
set @date1 = getdate()
set @person = user_name() ----this is user name
select @journal_code = JRN_CODE from inserted
select @journal_no = JRN_NO from inserted
insert into log_insert_Jour values (@date1,@person,@Journal_code,@journal_no)
END
Thanks
Thanks
View 1 Replies
View Related
Oct 31, 2006
Hi all,
I have a table (Products), and the table is like this:
- Product_ID (Primary Key), Not Null, Identity Specification: Yes, Increment 1, seed 1
- Name ...
- Price ...
When i insert in the publisher (PC, Sql Server 2005), it inserts correct
ex: |Product_ID | Name | Price| Inserted by|
| 1 | P1 | X | Pc |
| 2 | P2 | Y | Pc |
Then i replicate to Subscriber(Pocket PC, SqlMobile ), when i insert in Subscriber, it creates strange and big id's, is it normal? is this to avoid conflict inserts?
Ex: |Product_ID | Name | Price| Inserted by|
| 1 | P1 | X | Pc |
| 2 | P2 | Y | Pc |
| 3000 | P3 | Z | PPC |
| 6000 | P4 | Y | PPC |
Thx
View 5 Replies
View Related
Jun 21, 2006
Sql Server 2000. I had a merge publication setup on one table that replicated to several sql servers (all 2000 SP3). The publication was deleted on the publisher - but somehow - the subscription information is still on the subscribers. How can I remove them? When trying to delete the tables at the subscribers I get error that they are part of replication.
P.S. Other tables in the same database are part of different merge replication.
Thanks.
Abe
View 4 Replies
View Related
May 17, 2006
Just setup 2 boxes w/ SQL Server 2005 (Developer Ed) (SP1)
Server A: Tablet PC running XP (publisher/distributor)
Server B: running Win2K (subscriber)
Setup replication and recv'd error: 'server B is not a subscriber'.
This error is very vague and does not offer any direction to look for a solution.
unable to locate help on a solution from online books.
thx. for your review and input to this issue..bt
View 4 Replies
View Related
Nov 6, 2006
I am using merge replication between SQL Server 2005 and SQL Express. Some of the articles in my publication have filter conditions. Changes to subscription databases are being applied to the publication database without regard to these filters.
The easiest way to see this problem is to create a merge publication with a single table article with @subset_filterclause of '0 = 1'. Any inserts to the publication database will not be propagated to the subscription database, but inserts to the subscription database will be propagated to the publication database. Can this be right?
View 4 Replies
View Related
May 27, 2007
Is it possible to create a trigger on a subscriber table so that when something on the subsriber table is changed a trigger can send some of the changes made to the subscriber table to another table?
View 6 Replies
View Related
Feb 24, 2006
I am tring to use SQL Server Mobile 2005 to subscribe to a SQL Server 2005 publication from a pocketPC. In my code I have a SqlCeReplication object. One of the properties that must be set is: Subscriber, but I have no idea what that should be set to. When I was setting up the publication I don't remember specifying anything like that, and the class description of this property doesn't help: "Specifies the name of the Subscriber". Any help will be appreciated.
View 3 Replies
View Related
Apr 5, 2007
Hi,
I have a problem with merge replication on SQL 2000.
-Publisher is Standard edition, subsriber MSDE
-Both SP4
-It always fails at same table.
- CHECKDB shows no errors
- It worked OK for a quite long period...
-Replication fails with this mesage ('verbose ouput'):
The merge process encountered an unexpected network error. The connection to Subscriber 'SV-LESNOSAOP' is no longer available.
Percent Complete: 0
.
Repl Agent Status: 6
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147200999
Message: The process could not enumerate changes at the 'Subscriber'.
Repl Agent Status: 3
Percent Complete: 0
CategoryQLSERVER
Source: SV-LESNOSAOP
Number: 0
Message: Invalid time format
Repl Agent Status: 3
Percent Complete: 0
CategoryQLSERVER
Source: SV-LESNOSAOP
Number: 0
Message: The merge process timed out while executing a query. Reconfigure the QueryTimeout parameter and retry the operation.
Repl Agent Status: 3
Percent Complete: 0
Category:NULL
Source: Merge Process
Number: -2147199469
Message: The merge process encountered an unexpected network error. The connection to Subscriber 'SV-LESNOSAOP' is no longer available.
Some tables in article are merged succesfully so I don't believe it's a network error.
Anyway I will try to recreate the subscription, but there is a problem that subcriber has more data than publisher, so i have to manualy trasfer (DTS) the data to publisher before the new snapshot is applied.
If this also fails i guess i can assume there is a connectivity problem.
Any comments or suggestions are welcome...
Janez
View 4 Replies
View Related
Feb 21, 2006
Hi ...i am new to sql server replication, im using the sql server 2005.
I was configure the replication n publish a database , i wanna add a new subscription, but i am facing the follow error
You have selected the Publisher as a Subscriber and entered a subscription database that is the same as the publishing database. Select another subscription database.
How do i add another subscription database,since in my sql server has only 1 subscriber?
View 4 Replies
View Related
Feb 1, 2006
Hi,
can anyone tell me if there is a way to determine with SMO or RMO if a database is a subscriber when using merge replication. If only have the Server and database at this point too!!
I want to provide a small app that creates a merge publication but only if the database isn't a subscriber.
Thanks for your help
Graham
View 3 Replies
View Related
