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
ADVERTISEMENT
May 18, 2015
I'm in a project to replicate a database from the production environment to another sql server.
The idea is use it for some biggest querys because in the production database we had experimenting various problems. What is the best way for do it? Transactional replication? Log Shipping? I'm reading absolutely everything but I can not get decide.
Both servers are SQL Server 2008 R2 and they are in the same LAN.
View 6 Replies
View Related
May 26, 2015
We have restored a database on new server without keeping replication settings. Now while creating the publication no tables are shown in new publication wizard window. In fact we have hundred of tables in database and included in replication on source server from where backup was taken.
What may be the reason that tables are not appearing?
View 1 Replies
View Related
Mar 6, 2007
Will Sql Express handle being a subscriber for an updateable transactional publication from Sql Server 2005?
View 6 Replies
View Related
Feb 14, 2003
Does anyone have advice as choosing between merge replication and updatable subscriptions? I've read the documentaion, they seem to offer the same functionality, and I don't see any clear guidelines as to why to pick one over the other...any suggestions would be appreciated!
Ed
View 1 Replies
View Related
Jul 11, 2006
Hello,
We have two updateable subscribers and a publisher using transactional replication. The distributor is located on publisher. Currenetly all 3 sites are SQL 2000 SP3. We plan to upgrade all sites to sp4 with minimum downtime (if possible zero). Is it possible to upgrade publisher/distributor first and subscribers after few days?
Thanks,
np70
View 1 Replies
View Related
Oct 28, 2006
Replication Type: Transactional With Updateable Subscriptions
SQL Server Version: SQL Server 2005 Enterprise Edition
Publisher, Subscriber1 and Subscriber2 all on same SQL Server
Problem: I am trying to set up a transactional replication with two updateable subscriptions. All three databases are a carbon copy of each other. Every table to be replicated contains an identity key column. I've been asked to create the replication without assigning separate identity ranges for the publisher and the updateable subscriptions. In other words they want a continous ID range. Can this be done without managing identity ranges programatically outside of SQL Server itself?
Example:
Publisher inserts into table test1 in database1. This creates ID 100 for the record. ID 100 replicates to Subscriber1 (database2) and Subscriber2 (database3).
Subscriber1 and 2 identity seeds are then incremented to ID 100. So far, all three databases have the same identity seed in table test1. Subscriber1 inserts into table test1 in database2. This creates ID 101 for the record. ID 101 is replicated to Subscriber2 (database3) and to the publisher (database1).
Subscriber2 identity seed is then incremented to ID 101. However, the Publisher retains it's original identity seed of 100.
Insert into Publisher table test1 will fail since it will try to insert the identity seed of 101 which is taken.
I have tried identity range management Automatic and Manual. Both seem to have some limitations in managing identity seeds unless different identity ranges are assigned for publisher and subscriber.
Can someone recommend a solution to this problem? Can this be done natively in SQL Server 2005 replication. Or can some recommend another approach to make this work?
View 3 Replies
View Related
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
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
Oct 7, 2015
I have a table called employee_punch_record that we use to store employee time clock punches.
The columns are:
employeeid,
punch_timestamp,
punch_type (In / Out),
closed (bit used as status for open or closed pay periods),
ident
Here are some examples of a record:
bkingery62015-10-06 16:59:04.000In0
bkingery72015-10-06 16:59:09.000Out0
bkingery82015-10-06 16:59:13.000In0
bkingery92015-10-06 18:22:44.000Out0
bkingery102015-10-06 18:22:46.000In0
bkingery112015-10-06 18:22:48.000Out0
bkingery122015-10-06 18:22:51.000In0
tfeller52015-10-05 17:00:05.000In0
We are using SQL Server 2008 as our database and use Access as a GUI. I am looking to create a form in Access where employees can access their time card and request changes from management. I want to use the format from the attached screen shot for the form. I pretty much know how to do it all, the only point of complication is trying to figure out the easiest way to get the transaction punch record data on employee_punch_record into a format where I can easily populate the form in the horizontal format you see in the screen shot.
I am not super strong in SQL, but figure I can do it using a formatting table of some sort. quick and easy way to move transaction records into a more horizontally oriented record?
View 0 Replies
View Related
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
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
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
Jul 22, 2015
I am setting up filtered replication(tran repl) for a table that is 2+ TB in size. The filtered rows are less than 1% of total size.When I run the snapshot, sometimes it complete in 27 mins(I have already optimized the snapshot agent profile) and sometimes it runs for ever. Mostly it runs forever, so the chances of it finishing soon are really very less.I have infact created filtered index just to cater to the query in the filter i.e. on the filter predicates. The reason its flipping flopping is due to the incorrect plan that it chooses. I can force the right index and the filtered query runs just fine but. I cannot use the query hints when I am adding the filter in replication as its not allowed.
I then went ahead and created the plan guides to force the best plan. But the plan guide was not used because even if there is a single space in the actual query that replication runs in the background the plan guide will not be used(testing on plan guides has proved this). The query needs to be ditto same including whitespaces and carriage returns. I then ran profiler and found that there is a view that replication creates and executes to extract the filtered rows and BCP it. Got the text of the view and created the plan guide on it but it was still not used because sp_helptext output seems to be different than what is actually being fired by replication internally.
Further, I collected the verbose log with level 2 and tried to find out if the main query shows up there with no success.
(a) I can't use the plan guide because I do not know the exact syntax(I mean I have the query but I do not know the white spaces, carriage returns, etc) and
(b) I cannot use query hint while filtering the article.
1. I cannot afford to have downtime.
2. I cannot use backup and restore option either.
View 5 Replies
View Related
Oct 28, 2015
I have a file 'c:extrasiadt1.xml' the content is:
<?xml version="1.0" encoding="UTF-8"?>
<FlsAssDom_1 xmlns="http://flussi.mds.it/flsassdom_1">
<Assistenza >
<Trasmissione tipo="I"/>
<Assistito>
<IdentificativoUnivoco>XYZZYX66P17G920L</IdentificativoUnivoco>
[code]....
how to read the data to view the content like recordset (rows end columns).
View 0 Replies
View Related
Feb 26, 2015
How would I find the last read/write dates for all the tables within a database.
View 6 Replies
View Related
Mar 9, 2015
I was given a 2 GB XML file without an XSD. I need to find the element names. I know how to do that using openxml but it takes forever on a file this large. Is there a faster way to do it using xquery?
View 0 Replies
View Related
May 12, 2015
I have a requirement to read a value from table and increment it by 1. There can be multi-threads doing the same operation and would need a ROW LOCK so that read and write both are atomic. How can i put an exclusive lock on the row before I read the value from the table.
CREATE TABLE [dbo].[tblOnboardingSequence](
[OnboardingSequenceID] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[NextNumber] [bigint] NOT NULL,
CONSTRAINT [PK_OnboardingSequence] PRIMARY KEY CLUSTERED(
[OnboardingSequenceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
My Stored Procedure has below logic
DECLARE @NextNumber BIGINT
----------- Acquire row lock here
SELECT @NextNumber = NextNumber FROM tblOnboardingSequence WHERE Name = 'TPO'
UPDATE tblOnboardingSequence SET NextNumber = @NextNumber + 1 WHERE Name = 'TPO'
----------- Release row lock here
I would like to have a row lock for the row having Name "TPO" before SELECT query and release after UPDATE query.
What si the best way to deal with this?
View 2 Replies
View Related
Jul 31, 2015
The below query will read the data in XML format but any query to read from each column of XML tags easily?
SELECT CAST(record AS XML), record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'
View 5 Replies
View Related
Oct 18, 2015
way to read data from database already stored as question marks .that because by mistake i insert the data "arabic" from  the VS as sqltext but the field in the database is nvarchar now i want to get the data back.
View 4 Replies
View Related
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
View Related
Feb 10, 2015
I have a stored procedure that updates a table. I also have an UDF that allows dirty reads (nolock).
What's the precedence level in SQL server? If I add xlock,rowlock to the update statement, will the dirty read wait for the update transaction to commit, or will it perform a dirty read regardless of the locking scheme in the update statement?
View 0 Replies
View Related
Jul 26, 2015
I have the following query:
Select p.Id [SenderId], p.Username, up.PhotoId,
CASE
WHEN mr.ReadDate is null then 1 -- New message
ELSE 0 -- Message has been read
END AS NewMessage,
p.LastLoggedIn, p.LoggedIn
[Code] ....
The above query returns me all messages (inbox functionality) that have been sent to mr.ReceipientId, the issue I have is when I send another email to the recipient the readdate field will be null, and the other emails linked to the recipient which have also been sent via me will have a readdate date. This causes duplicate rows to appear due to the case statement, I'm trying to figure out if / how it is possible to only display the one row per conversation and set newmessage to 1 if there is an un-read message otherwise show 0 ?
So instead of showing this:
2Person102015-07-26 17:07:24.9370
2Person112015-07-26 17:07:24.9370
I'm trying to get it to look like this:
2Person112015-07-26 17:07:24.9370
Or if we have no new mail (read date is not null) then it will appear like this, and I can confirm when there is no new mail it works as expected.
2Person102015-07-26 17:07:24.9370
View 1 Replies
View Related
May 9, 2008
Hi all,
I am new to SQL Server 2005 and databasing in general, I have had a webhost restore a copy of my online database but now it is read only.
Could someone tell me how I go about letting the database to be writeable etc for online use.
If I right click the db and go to permissions there is nothing in there, should there be?
Do you have to manually set permissions for each table after restore???
Any help would be great
View 5 Replies
View Related
Apr 4, 2015
I am inserting updating few tables from snapshot and reading same bunch of tables from reporting using readcommitted . It is showing some deadlocks i think it is write in this situation as " x" is not compitable with "s" ,"is".
View 4 Replies
View Related
Apr 7, 2015
How can I create a SQL authentication account with read-write access to only 1 table in a SQL database.
View 1 Replies
View Related
Mar 2, 2015
Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: xxx.xx.xxx.xx]
Client IP address is same as the server its producing the error on. I get these messages around 12pm everyday.
View 3 Replies
View Related
May 6, 2011
How do I convert (n)(var)char to time? I tried several ways including a simple:
SELECT CONVERT(time,'143825',108)But it always gives me this error:
Conversion failed when converting date and/or time from character string.
View 9 Replies
View Related
Apr 9, 2015
My data is looks like this,
Date---------------------------------------A
2015-03-01 13:38:07.343----------------1
2015-03-01 14:04:04.460----------------1
2015-03-02 19:33:55.117----------------3
2015-03-02 19:33:55.117----------------4
2015-03-02 19:39:26.580----------------1
I want data looks like this
Date-------------------------------------------A
Day 1------------------------------------------2
Day 2------------------------------------------8
View 7 Replies
View Related
Apr 30, 2015
how can I get the time differences between them.Let's say , a person who click on break @ 12:00 PM and he is back and select I am back option @ 12:15 the total break time is 15 minutes. However, can I display this difference of break time.
View 4 Replies
View Related
May 27, 2015
I need to change one of the column to run utctime 2am. Whenever I declared and set the time, it has to run and set up to the UTC 2 am time.
How to declare the variable like that?
If I run that now, it has to set up next utc 2 am time, If I run after 2 hrs also or tomorrow whenever, it has to set the next utc 2am time
View 1 Replies
View Related
Nov 2, 2015
If I have a datetime field, RequestDate, and a time field, DecisionTime, how do I add just the date portion of RequestDate to DecisionTime?
View 6 Replies
View Related
