Merge Replication Performance Issues, Enumerating Inserts And Updates For Articles...generations And Batches

Oct 8, 2007


We have a SQLServer 2005 Enterprise merge replication publication with SQL Mobile 3.0 subscribers (Windows Mobile 5.0 and 6.0). We do not use pre-computed partitions due to trigger performance issues with an SSIS/ETL application that supplies data to the merge database. We do use the "Optimize" (=true) option, though we have tried this both ways with no significant differences. We use filters and joins for each worker ID (as HOST_ID) from the subscriptions.

The sync times become increasingly worse after we run the snapshot and bring the publication online. I have tried rerunning the snapshots, this helps little, as it often behaves like the subscription was set to reinitialize and forces a big sync (reload of all data) to the subscriber. We have tried much of the obvious (e.g., flattening filters and joins, adding indexes, etc.).

When users are synchronizing, we watch replication monitor and notice that a lot of time is spent processing "enumerating inserts and updates for article [any article]", especially processing the many generations and batches. This is true for any follow-up syncs after the 1st big sync (initializing the subscription).

I read several posts regarding the batches and generations of changes, and decided to try increasing the €śDownloadGenerationsPerBatch€?. I tried adding this parameter to the snapshot agent job, and the job fails each time with a vague message, even with the default value of 100. How do you change this parameter for SQLServer 2005 Enterprise?

Any suggestions?

Thanks in advance,
Matt

View 5 Replies


ADVERTISEMENT

Replication Performing UPDATES As INSERTS And DELETES On The Subscriber

Mar 1, 2006

I have transactional replication set up between two SQL Server 2000 databases. In some cases when I perform an UPDATE on a published table on the the publisher, SQL Server attempts to perform a DELETE followed by an INSERT on the subscriber using the stored procedures created during the initial snapshot.

Why does it do this?

How can I stop it doing this and force an UPDATE on the publisher to call the UPDATE procedure on the subscriber?

Thanks

View 3 Replies View Related

Merge Replication : Immediate Updates

Apr 3, 2007

hi,

after reading docs, i always thought that when using merge replication, only at specific moments the whole thing was merged.

now when doing first tests, it seems that everything is merged immediately to my subscribers. is this normal?

View 1 Replies View Related

Merge Replication Not Replicating Updates

Sep 26, 2006

I have an issue that is only occurring in a production
environment. The architecture is filtered merge replication between two SQL
Server 2000 SP4 databases. The publisher is standard edition and the subscriber
is personal edition.

The issue is that is I update certain rows on the subscriber the data is not
replicated to the publisher. Inserts and deletes seem to replicate correctly.
The issue is specific to certain rows in some tables. If I update other rows
the updates replicate correctly. The subscription has been re-initialized once
and it did not fix the issue. The replication process indicates that there was
not data to merge and the subscriber updates remain and are at that point
different form the publisher row values after the sync.

I can reproduce the issue by updating the row, running the sync process, and
checking the publisher database. The synchronization process is executed using
the merge agent ActiveX control.



This issue started occurring after a large conflict was generated during an
earlier synch on this particular subscriber. We have other subscribing
databases that are working fine.



I have already spent 8+ hours on the phone with TCS trying to solve this issue.
If anyone has any ideas or has seen this type of issue before I would appreciate
any direction to try to fix this issue.



Thanks

View 15 Replies View Related

Merge Replication Updates On Initialise (SQLServer 2005)

Jun 15, 2006

I have a merge replication scenario where the technicians are filtered by their individual codes. Each technician has his own mobile device for collecting and synchronising data.

On initialisation of the subscription, the user should only get the data that is relevant to their code.

The client is staging the rollout to all of their technicians and as the system has grown, we have noticed that there are a number (getting larger every day) of updates that are creeping into the initialisation of each subscription.

My understanding is that an initialisation will only ever apply the inserts necessary to create the subscriber database.

Is my understadning incorrect?

Is the some parameter in the creation of the publication that we have set incorrectly?

Is there something wrong with SQLServer 2005 itself?


I am very interested to hear anyones comments or advice around this issue.

Thanks
Steve

View 4 Replies View Related

Replication Performance Degrade In Unidirectional Direction And Lock Time Out (Update Are High Than Inserts)

Feb 21, 2007

 

We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.

1)   One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).

2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..

 

on subscriber database  response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.

can any one can also suggest me server level settings for aviding locking time out.

 

looking for any experieced solution/suggestion.

Thanks in advance. 

View 3 Replies View Related

How Can I Do A Multiple Insert Or Multiple Updates Or Inserts And Updates To The Same Table..

Oct 30, 2007

Hi...
 I have data that i am getting through a dbf file. and i am dumping that data to a sql server... and then taking the data from the sql server after scrubing it i put it into the production database.. right my stored procedure handles a single plan only... but now there may be two or more plans together in the same sql server database which i need to scrub and then update that particular plan already exists or inserts if they dont...
 
this is my sproc...
 ALTER PROCEDURE [dbo].[usp_Import_Plan]
@ClientId int,
@UserId int = NULL,
@HistoryId int,
@ShowStatus bit = 0-- Indicates whether status messages should be returned during the import.

AS

SET NOCOUNT ON

DECLARE
@Count int,
@Sproc varchar(50),
@Status varchar(200),
@TotalCount int

SET @Sproc = OBJECT_NAME(@@ProcId)

SET @Status = 'Updating plan information in Plan table.'
UPDATE
Statements..Plan
SET
PlanName = PlanName1,
Description = PlanName2
FROM
Statements..Plan cp
JOIN (
SELECT DISTINCT
PlanId,
PlanName1,
PlanName2
FROM
Census
) c
ON cp.CPlanId = c.PlanId
WHERE
cp.ClientId = @ClientId
AND
(
IsNull(cp.PlanName,'') <> IsNull(c.PlanName1,'')
OR
IsNull(cp.Description,'') <> IsNull(c.PlanName2,'')
)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Updated ' + Cast(@Count AS varchar(10)) + ' record(s) in ClientPlan.'
END
ELSE
BEGIN
SET @Status = 'No records were updated in Plan.'
END

SET @Status = 'Adding plan information to Plan table.'
INSERT INTO Statements..Plan (
ClientId,
ClientPlanId,
UserId,
PlanName,
Description
)
SELECT DISTINCT
@ClientId,
CPlanId,
@UserId,
PlanName1,
PlanName2
FROM
Census
WHERE
PlanId NOT IN (
SELECT DISTINCT
CPlanId
FROM
Statements..Plan
WHERE
ClientId = @ClientId
AND
ClientPlanId IS NOT NULL
)

SET @Count = @@ROWCOUNT
IF @Count > 0
BEGIN
SET @Status = 'Added ' + Cast(@Count AS varchar(10)) + ' record(s) to Plan.'
END
ELSE
BEGIN
SET @Status = 'No information was added Plan.'
END

SET NOCOUNT OFF
 
So how do i do multiple inserts and updates using this stored procedure...
 
Regards
Karen

View 5 Replies View Related

Updates, Inserts

Jan 22, 2001

I have a number of columns with predefined character length but user can input more from gui. i want to trucncate automatically to the desired length and insert or update the database right now it does not allow me to update , or insert the values can i do it and how this is urgent

View 2 Replies View Related

Cascaded Inserts And Updates?

Nov 28, 2007

Hi

I have 3 table (tbl1, tbl2, tbl3).

tbl1 colums ---Pid(autonNumber),Pcode(unique values),PGuid

tbl2 columns---Pid, CatId,Fid

tbl3 Colums----Pid,BundleId

Using Trigger(After Insert) in tbl1 to insert the data of tbl1.Pid to tbl2.Pid and tbl3.Pid . tbl1 will be uploaded with new rows and existing rows will be updated from the csv file.

Trigger in tbl1 works fine by inserting data to tbl2.Pid and tbl3.Pid.

My questions are :
1. Is it possible to use trigger to update tbl2 and tbl3 if records exist if not insert new record to colum Pid in tbl2 and tbl3 from Pid of tbl1

2.Or is it possible to use sql procedure to do cascaded updates or inserts by merging datasets from tables and csv file using asp.net

3.Or How can I use single Trigger to insert if not exist and update if data exist.

4.Or Do I have to use two trigger one for insert and one for update. If this is the case how do check for data existance.

After insert Triger I am using at the moment is below (replied as an answer in this forum for my previous thread)

create trigger t1 on tbl1
after insert
as
begin
declare @id int
select @id=pid from inserted
insert into tbl2(pid) values(@id)
insert into tbl3(pid) values(@id)
end

View 5 Replies View Related

Inserts And Updates Issue Using Sp

Sep 15, 2006

Hi, i have the following sp. im using vs.net2005, sqlserver2005.

now how do i implement functions like - > okay using datasets, exception is thrown that the row has been modified and i get to inform the user, reload the modified row, .

how do i get same functionality using sp? is that possible ? how/

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER Procedure [dbo].[sp_Insert_Or_Update_Bill]

@BillID_All uniqueidentifier,

@BillID uniqueidentifier,

@Pono nvarchar(25),

@Date_ smalldatetime,

@SupplierCode nvarchar(25),

@Reference nvarchar(25),

@AmountType nchar(10),

@BillType nvarchar(25),

@TypeCode nchar(10),

@AmountFC decimal(18,2),

@ROE decimal(9,2),

@Currency nchar(5),

@LinkBill uniqueidentifier,

@multiplicityID uniqueidentifier,

@payment_or_bill smallint

as

If Exists(Select * from bill where billid_all= @BillID_All and amounttype = @amounttype)

begin

update bill

SET [BillID_All] = @BillID_All

,[BillID] = @BillID

,[Pono] = @Pono

,[Date_] = @Date_

,[SupplierCode] = @SupplierCode

,[Reference] = @Reference

,[AmountType] = @AmountType

,[BillType] = @BillType

,[TypeCode] = @TypeCode

,[AmountFC] = @AmountFC

,[ROE] = @ROE

,[Currency] = @Currency

,[LinkBill] = @LinkBill

,[multiplicityID] = @multiplicityID

,[payment_or_bill] = @payment_or_bill

where billid_all= @BillID_All and amounttype = @amounttype

end

else

if not @AmountFC = 0

begin

begin

INSERT INTO [Costing].[dbo].[Bill]

([BillID_All]

,[BillID]

,[Pono]

,[Date_]

,[SupplierCode]

,[Reference]

,[AmountType]

,[BillType]

,[TypeCode]

,[AmountFC]

,[ROE]

,[Currency]

,[LinkBill]

,[multiplicityID]

,[payment_or_bill])

VALUES

(@BillID_All

,@BillID

,@Pono

,@Date_

,@SupplierCode

,@Reference

,@AmountType

,@BillType

,@TypeCode

,@AmountFC

,@ROE

,@Currency

,@LinkBill

,@multiplicityID

,@payment_or_bill)

end

end

View 1 Replies View Related

The Maximum Number Of Articles Allowed In A Merge Publication Is 246.

Jun 4, 2007

Has anyone had problems with this limitation before or could explain to me what the purpose of this limit is for? It obviously does allow you to publish more than 246 (we have been publishing more than 700 for years). Is this new with SQL 2005? Is my publication going to stop one day and tell me there are too many articles?



Thanks in advance.

Martin

View 15 Replies View Related

How To Switch Off Transaction Logging For Inserts/updates?

Nov 22, 1999

Is there a way to switch off transaction logging for insert and update statements and not only for select into bulk copy?

View 1 Replies View Related

Encoding Strings For INSERTS And UPDATES - Newbie

May 26, 1999

Can someone point me to some code that properly prepares a string for an INSERT or UPDATE into a char or varchar column? I need to be able to handle single & double quotes and any other possible issues. thanks

View 1 Replies View Related

SQL Server - Checking Inserts / Updates After DTS Package

Oct 30, 2007

Hi All,

I'm a relative novice on SQL Server and am a complete beginner at SQL, so am looking for a little help.

I currently use a DTS package to perform inserts / updates to a "production" table.

The DTS package transforms a comma separated file into a "temporary" table that is truncated / cleared before the load starts.

The temporary table has a column denoting Insert or Update. The production table is almost identical, however, doesn't contain the Insert / Update column. The DTS package then, depending upon the Insert / Update flag, either inserts data into the production table or updates data in the production table.

When the DTS package has completed, I'd like to be able to run an SQL Query that validates everything in the "temporary" table is identical to that in the "production" table, which it should be.

I have managed to do some queries to verify that everything has loaded / updated i.e. select primary_key from temporary table where primary_key not in (select * from production table), however, what I haven't been able to do is verify that all the columns on the temporary table match the values in the production table (excluding the Insert / Update flag).

I tried concatenating the columns in each table and comparing the concatenated values, however, this failed due to the different data-types, i.e. decimal, text etc.

Any help will be greatly appreciated.

Many thanks.

Cheers,

David

View 8 Replies View Related

Inserts And Updates To A Table That Contains A Unique Key Constraint

Nov 5, 2007

I am looking for pros and cons for the following scenarios:

When a table contains a unique key constraint is it viable to always do an insert and immediately check the @@ERROR value and if @@ERROR states a duplicate key exception then perform an update statement?

Another possible solution would be to always check if the key exists and then do the insert / update based upon that result. This method will always require two steps.

View 4 Replies View Related

SQL 2005 Rolls Back Inserts/updates

Apr 2, 2007

Hi:

We have a weird problem in our test SQL 2005 SP1 - Windows 2003 Enterprise SP1 - Server (after upgrading from SQL 2000).

While changing data via JBOSS - JDBC connection, we get all our data modifications rolled back.

Selects are fine. Same SQL 2005 Inserts/Updates through JBOSS are fine on other machines with Windows XP (JBOSS is on the same machine there, while in troubled configuration it's on its own server).

But in production imitation environment we can't modify data.

Please advise where to look for the solution.

Thanks.

View 2 Replies View Related

Odd: Inserts And Updates Happen Before Running Code In Debugger

Aug 15, 2006

Hi,Before stepping into ado.net code to perform an insert or update, the insert / update has already taken place, just on starting the debugger. I use VS 2005 on SQL Server 2000. This did not happen with VS 2003 and SQL Server 2000.Anyone else encountered this?

View 2 Replies View Related

Inserts And Updates Question (newbie)...should Be Simple To Explain

Sep 6, 2005

Hello people!Here is the situation:We have an old propriatary database that is used for daily tasks.  Not much we can do with it, but we have to use it.I can create basically a read only connection to it through ODBC.I would like to, on a timed interval copy certain data for reporting from this slow thing to my SQL server so that I can learn to program, and create some cool reports etc without having to wait on this server all day.So here is what I don't quite understand.I  had originally planned on just deleting the contents of the tbl on my SQL server just before I populated it each time, but found out that my AutoNumber field will continue to increase, and I'm assuming that eventually I'm going to run into a problem as a result.Should I be doing some kind of update instead?  if so do I need to first CHECK if the record exisit, if not then do an insert, if so do an update type thing?Or is there a way to basically do it in one command?I hope this makes sense.  I would show you some code but there really isn't much to show you other than my insert statement :->Thanks for any advice!Josh

View 5 Replies View Related

Counting Inserts & Updates From A Specific Or Group Of Tables?

Mar 11, 2002

I am kinda new with SQL and am trying to get a count of on the number of updates and or inserts to any given or group of tables and cannot get the syntax correct...can anyone help with this?
Thank you in advance.
Colin P.

View 1 Replies View Related

Help: How To Detect Inserts, Updates, Deleted On A Table From Within C++ Application?

Jul 20, 2005

Hopefully someone can at least point me in the right direction for moreresearch (e.g.: correct terminology). My only previous experience was justdumping data into a database using ODBC, and that was some years ago so nowmostly forgotten.I need to write an NT Service/Application (in C/C++) that will be gettingdata sent to it via SQL Server 2000. The data will arrive in my SQL Server(read-only access), via replication of tables from another remote SQLServer.My application needs know when new row are inserted, or updated so it can toread this data (needs to be quick/timely so hopefully no polling) to theninterface with other remote proprietary systems.T.I.A.PS: If you can recommend appropriate books on SQL Server 2000 that wouldalso be useful.

View 2 Replies View Related

Counting The Inserts And Updates On A Table In A Sql Server Database

Jul 20, 2005

Hello,Can someone point me to getting the total number of inserts and updates on a tableover a period of time?I just want to measure the insert and update activity on the tables.Thanks.- Vish

View 3 Replies View Related

Slow Inserts/updates As The Database Size Grows

Oct 23, 2007

Hi all,

This managed application was written to run on a Symbol 3090 Win CE 5.0 scanning device. We are using the symbol provided classes to access the scanning interface, and SQL Compact database on the device to collect the scanned data, and then using merge replication to synchronize scanned data when the device is docked. The problem we have experienced seems to be releated to the performance when inserting and updating records in the database.

We have tested some randomly generated 1000 records and inserting/updatating into a database. At first the time to commit a record increases when the database is flushing into the memory (The flush interval in the connection string property is 10 seconds by default). and then as the database size grows increasing the time to commit every single record which is causing the application to perform slowly as they scan items into the database. However, the device program memory remains consistant as they are scan items. From our tests, I found the time to execute either a update/insert command on 2MB sqlMobile database (upto 10000 records, depending on the size of the columns) is taking nearly 2 to 2 and half seconds to complete. Below is the only code I am executing,


If Not sqlObj.UpdateItem(1061022, itemNo, 1) Then

sqlObj.InsertResultSet(1061022, itemNo, itemObj.Style, itemObj.Color, itemObj.Size, itemObj.Description, 0, 1)

End If

For the notes, I am using prepared updated command and resultset.insert methods to perform update and insert commands into the database.

Any help on this issue is highly appreciated.

Thanks
Ravi.



View 1 Replies View Related

Measuring Daily Inserts/updates On A Production Databse

Apr 16, 2007



I want to measure updates/Insertion rate of my databse in order to measure that how percent the databses is booked for insert and update.



can some one can suggest me the mechanism or other resource for doing this work...

thanxs in advance

View 4 Replies View Related

Access To SQL Server 2000 Pauses With Lots Of Inserts, Updates, ...

Nov 21, 2007



Hi,
I have the following problem: Within a VBScript, I use a component (written in C++ I think with use of ADO) for sending "Insert", "Update" Statements to an SQL Server 2000 for inserting, updating data. If I insert 100 - 120 Records in a Loop, all works fine. If I insert 1000 records, approximately 150 records will be inserted very quick, then the program pause fo approx. 8 - 15 minutes and then it proceed for the next 150 recs, pause for 8 - 15 minutes and so on.

If I use a SQL Server 2005 for the database, all works fine. The same happens with another customer and another program written in Visual Basic 6.0 with ADO. The access to SQL 2000 pause and with SQL 2005 all works fine. It seems to me that this is a problem with some buffers, timeout, or so. Has anyone an idea on what screw I can turn?

Thanks
Hans

View 1 Replies View Related

Does The Configure Data Source Wizard Work For Updates,Inserts, And Deletes ?

Jun 8, 2007

Ok, I think this may have a simple answer. Basically I have no problems in setting up QueryString/Control/etc parameters when I use SELECT in the Configure Data Source Wizard as it prompts me for the necessary parameters. But when I try to use the Configure Data Source Wizard with an UPDATE, INSERT or DELETE it does NOT prompt me for the required parameters.Is this a bug or am I just missing something? Do I have to put them in manually or something?Thanks! 

View 5 Replies View Related

Would A TLOG File Size Change When Inserts/updates/deletes Are Not Involved?

Jan 3, 2008

I have a 14GB database whose data content is legacy and is described as static. The log file is significantly large and continues to change size mostly increasing by 2-5GB a day (~60GB now) I have observed over the past two days; it shrank once unexpectly by a few GB. The instance is hosting other databases such as: EnterpriseVaultDirectory, EnterpriseVaultMonitoring, EnterpriseVaultStore, and NetPerfMon - might these seemingly unrelated data sources be involved?


I am trying to a trace to find traffic against the tables, no such luck.


Web applications are playing against it for queries but there should be no UPDATEs beign applied. I can only suspect that other unknown applications are performing operations but have yet to find unexplained connections.

Are there any other reasons why this type of log file activity would happen merely due to queries or stored procedure calls?

Lets also state, "mirroring, indexing, replication" are not at play. I know logging "Full" is not necessary as "Simple" should suffice but I am still hunting down why UPDATEs might be getting through. I realize I might adjust the migrated SQL 2000 security model to deny updates to find what breaks but would rather not take that iniative yet.


The installation is a fresh SQL 2005 Standard setup with SP2 applied; the databases were upgraded.

View 6 Replies View Related

SQL Server 2014 :: Stored Procedure That Inserts And Updates A Table With Excel Data?

May 27, 2014

I need a script that inserts the data of an excel sheet into a table. If something already exists it should leave it, unless it's edited in the excel sheet and so on and so on. This proces has to go through a stored procedure... ...But how?

View 6 Replies View Related

Replication Not Creating Articles

Aug 13, 2015

I'm new to replication and set up a Snapshot Replication on the tables in my sample database.  Everything seemed to work (database created on Subscriber instance, jobs set up, etc) but the articles (tables) are not being created in the subscriber's database.  

I can see the files created in the folder but I don't see the tables or the data.  Also, I confirmed that the articles are checked so they should be included.

View 6 Replies View Related

Snapshot Replication - Adding Articles

Mar 4, 2004

Hey All - Another replication question:

Using VBScript in an ActiveX module of a DTS package...

Anyone have an idea how to add ALL articles to a Subscription.

The only way I can figure it is to load an array from sysobjects, then run sp_addarticle on each one.

Thanks for your help

RobbieD

View 2 Replies View Related

SQL 2012 :: Transactional Replication - How To Remove Orphaned Articles

Jan 27, 2015

We are runnning on SQL Server 2012 SP1 + CU9.

I have found some articles with no publication in our transactional replication.

For example, running this:

select p.publication,
a.publication_id,
a.article
from dbo.MSArticles as a
left outer join dbo.MSpublications as p
on a.publication_id = p.publication_id

shows this:

NULL1org_Community
NULL3org_Community
Purchasing to EDW5org_Community
NULL1org_Division
NULL3org_Division
Purchasing to EDW5org_Division

How can I get rid of the articles that are not part of a publication?

I can't use sp_droparticle because it requires a publication which these articles do not have.

View 1 Replies View Related

Can A Log Shipping Destination DB Be Used As The Source For Transactional Replication Articles?

Aug 6, 2007

Hello,

My company is moving to a SQL Server-based packaged application early next year. We€™re planning our SQL Server architecture but have some questions that I can€™t readily find answers for. I€™m hoping someone here can point me in the right direction.

We have three servers, I€™ll call them A, B, and C. We want to duplicate all changes to certain databases on server A to server B, then duplicate changes to selected databases and tables on server B to server C.

Ideally we€™d run SQL Server 2005 Enterprise Edition on all three servers, but the packaged application vendor does not support SQL Server 2005 yet, only SQL Server 2000. Our license agreement with them does not allow us to use replication on server A. We€™re free to do whatever we want on our other SQL Servers, but server A must sit alone, untouched, like a monolith on a far-away moon. (I€™m lobbying to have the server named Tycho, or TMA2.) Stranger still, they€™re OK with log shipping from server A to other servers. We€™ve tried to explain that replication and log shipping are both core function built into SQL Server, and that if one is acceptable, then both should be. Their fear is that replication could cause performance and stability problems, and to eliminate this possibility they€™re ruling out replication on server A.

Given these constraints we€™re resigned to using SQL Server 2000 Enterprise Edition on servers A and B, and SQL Server 2005 Enterprise Edition on server C. We plan on periodically shipping logs from server A to server B and applying them at server B.

We€™d like to know if it is possible to also use transactional replication on server B to duplicate changes from server B to server C. I€™ve used log shipping and replication in the past, but never at the same time. My understanding is that a database goes into recovery mode while a transaction log is being applied and that any user changes to the database after the log has been applied will cause later log applications to fail. The scripts I€™ve seen that are used to apply the transaction logs put the database into single user mode after the log has been applied to prevent this.

This raises a few questions:



If we try to RESTORE a log to a database being used as a source for transactional replication articles, will the RESTORE fail? Or will the RESTORE start and break the transactional replication? I€™ll test this on my own, but it€™d be nice to know if anyone has already experienced this.


Is it possible for us to have a database in read-only mode serve as the source for transactional replication articles? (I can€™t imagine why not, ever though it seems counter-intuitive - why would you want to replicate transactions from a database that has no transactions?)

If the answer to number two is yes, can we suspend transactional replication on a database, RESTORE a log to the database, put the database into read-only mode after the RESTORE, and restart the replication on the database?
Thanks in advance for sharing your wisdom, everyone!

--
Thomas C. Mueller

View 1 Replies View Related

SQL 2012 :: Adding Articles To Existing Publication In Transactional Replication

Sep 26, 2014

I have an existing publication in sql 2012 with 2 articles, and then I add 2 more articles. After that when I generate a snapshot, will the snapshot be generated for 2 new articles only or for all 4 articles?

I remember adding 1 new articles to one existing publication with 150 articles and when I generated snapshot, it was generated only for 1 article. But I don't remember clearly.

Does it behave differently for small and large number of articles?

-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------

View 0 Replies View Related

SQL Server 2008 :: Replication Articles Read-only AND Updateable At Same Time

Apr 21, 2015

We have many users with a mobile application running SQL Mobile and using merge replication to get data back to the SQL 2008 R2 database. This has worked very well for many years.

We now have a requirement to have this data reported on using Reporting Services. This is where it gets messy.

Due to a limitation of Report Builder(see this blog) we cannot provide access to users for creating their own reports. The report database is remote from the host and there is no VPN.

We hit upon the idea of creating an almost identical publication but the articles as read-only. It was only after this was done that we started having trouble with our existing mobile users.

It seems that a published article is EITHER Bi-directional OR Read-only even if they are in separate publications.

I then thought of using Transactional Publication but this too is blocked on creation with "automatic identity range support is useful only for publications that allow updating subscribers"(Merge and Transactional publication are mutually exclusive)

So in the final analysis is there a way for me to have merge replication AND some other form of SQL replication/data transfer that can have the same data transmitted readonly to a separate full SQL server database?

View 9 Replies View Related







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