SQL 2012 :: Subscriber DB Movement From One Server To Another
Apr 23, 2015
I have transactional replication setup from server A to Server B. I wanted to move the subscriber from B to C. What could be the best approach.
1. Backup the DB from Server B and restore on Server C. set the replication between A & C.
2. setup the transaction replication between A & C along between A & B. Test A& C working fine and then remove B.
If I am going with approach 2 , I have to replicate data approx. 70 GB so If I ran both the replication on Server A that will stress as 140 GB of data moving out. How do I control this large movement ? Can the replication be manual synch?
The secondary server for my availability group was recycled. When SQL Server came back online the data movement for a database was suspended. The error log shows:
"AlwaysOn Availability Groups data movement for database 'XXXXXXXXX' has been suspended for the following reason: "system" (Source ID 5; Source string: 'SUSPEND_FROM_RESTART'). To resume data movement on the database, you will need to resume the database manually. For information about how to resume an availability database, see SQL Server Books Online."
I was able to resume data movement with no issue. I would like to understand the technical reason as to WHY the data movement was put in the suspended state and left there upon recycle. I searched for an article that would list possible reasons (BOL, Google, Bing, etc..). I just could not find much information out there on 'SUSPEND_FROM_RESTART'.
I am aware that TDE protects data at Rest and not during communication or data in motion (UNLESS you use Encrypted communication channels using SSL certs etc). Hence I am thinking of doing data export from a TDE encrypted database to a database on the instance where TDE is not enabled or supported. I believe it works and need to take care of relationships between tables.The target database is hosted on SQL 2012 standard edition on which TDE is not supported.
I have a setup of transaction replication between one publisher and subscriber in the Same server.Now, I need to add a new subscriber to the existing publisher. So publisher database name is DB_A and Subscriber 1 name is DB_B. So the new subscriber will be DB_C. Is this kind of setup possible on one server?
If yes then at the time of reinitialization is it going to apply the snapshot on DB_B as well as DB_C?Also let say if due to disk error DB_B gets corrupted then will data be still replicated between DB_A and DB_C? (Assuming publisher, subscriber 1 and 2 are sitting on individual disks).
I have added a new table to a database (existing publication) using T-SQL, I then opened up publisher properties, and ticked the new table/article so that it would be added to the subscriber. It did not show up.
I did not use a snapshot to initialize the subscription.
Immediate Synch is 0. allow_anonymous is 0.
I mark the subscription to be reinitialized. When I start the snapshot agent I get '0% A snapshot was not generated because no subscriptions needed initialization'.
What could I be doing wrong, or missed out? Do I need to drop and recreate the subscription to get the article to show up?
We have a database which is (a subset of tables are) replicated to another via transactional replication. Whilst most changes made at the published database reach the subscriber within a matter of seconds, we have a SQL Agent job which performs a calculation in the published database and then immediately exports data from the subscriber using log shipping. The result is that the calculated changes do not make it through to the exported transaction logs in time.
Is there a way to manually "refresh" the subscriber databases using T-SQL?
I have a transactional replication environment that creates subscribers on another server as a staging area for an ETL process to a data warehouse application on a 3rd server which is the report repository. Currently the ETL process runs every 10 minutes and performs it's function across approx 150+ subscriber databases and consolidates it to the data warehouse.
I have an SLA of 2 minutes. I'd like to rework the ETL process (which run as SSIS job at the moment) to be specific to a single database and fire that one ETL proces when changes have been applied to that subscriber database only. Of these 150+ databases generally only about 8-10 are updating the subscriber at any given time per Repl Monitor. I'm thinking that if I only have a few transactions to apply to a single db the ETL would run in seconds dynamically as the subscriber is update.
The issue is how to fire the ETL process upon completion of updates to the subscriber DB? I'm thinking of using SP_Start_job passing the DBID to update the warehouse but unsure whether this is possible but if so where to trigger it.
I have a table named sales, num of rows are 20. total number of rows for stor_id is 5 rows I assume that when I create a cursor like this one which will have 5 rows from the cursor Now if I print a statement , I assume that I will have the statement 5 times( one for each row) Am I correct... well, if this is the concept, I am still having one printed statement.. I do not know why , am I doing something wrong? thanks for help
CREATE PROCEDURE p_cursor @ord_nbr char(4) declare rst cursor for select * from sales where stor_id= 123 open rst fetch rst if (@@fetch_status=0) print ' I may have 5 rows '
We're very interested in having our application use SQL/e but we can't have the 4 gig limit. It makes sense to me that SQL/e simply should not be able to access a file over the network, and then you wouldn't have any reason to put a 4 gig limit on it.
At that point it becomes a very flexible alternative for remote users that need to have a large amount of data (i.e. documents, images etc.) with them.
We'd love to start building an abstraction layer so that we can support both SQL Server and SQL/e so that we can support network and remote users and not have the nightmare that is SQL Server Express installation. (care of the windows installer group's bugs...)
I am wanting to continuously monitor a source table throughout the day and as data becomes available, process it and insert it into one of a number of tables.
I have tried achieving this using a FOR LOOP and setting the halt condition such that it is not stisfiable. However, this has a couple of problems:
1) It runs in a tight loop and consequently degrades system performance enormously.
2) I can't get transactions to work. I would like each iteration of the loop to spawn a new transaction under which the tasks in the loop can run. Therefore, if one of the tasks fails during such an iteration, only the updates affected by that iteration are lost.
Ideally, I would like to be able to put a wait statement within the loop container so that it runs every couple of seconds. And would also like to implement transactions as described above.
I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"
The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...
Hello everyone,There's an interesting SQL problem I've come across that I'm currentlybanging my head against. Given the following table that contains itemlocation information populated every minute :location_id date_created=========== ============5 2000-01-01 01:00 <-- Don't need5 2000-01-01 01:01 <-- Don't need5 2000-01-01 01:02 <-- Need7 2000-01-01 01:03 <-- Don't need7 2000-01-01 01:04 <-- Need5 2000-01-01 01:05 <-- Need2 2000-01-01 01:06 <-- Don't Need2 2000-01-01 01:07 <-- Need7 2000-01-01 01:08 <-- Needhow would you generate a result-set that returns the item's locationhistory *without* duplicating the same location if the item has beensitting in the same room for a while. For example, the result setshould look like the following :location_id date_created=========== ============5 2000-01-01 01:027 2000-01-01 01:045 2000-01-01 01:052 2000-01-01 01:077 2000-01-01 01:08This is turning out to be a finger twister and I'm not sure if itcould be done in SQL; I may have to resort to writing a stored-proc.Regards,Anthony
I'm getting this error on expanding the Databases node in SQL Server Management Studio Express Object Explorer:
Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum) An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo) Could not continue scan with NOLOCK due to data movement. (Microsoft SQL Server, Error: 601)
A similar error message also appeared when I tried opening an existing data connection within Database Explorer in Visual Web Developer Express. (However, the web application ran fine and it managed to access the database normally.)
These errors only appeared recently. Any ideas how to go about solving this issue?
In my organization, we have a database which exceed unto 2 GB. The application on which we are working is developing since the time of asp / cb 6 and in short it is the baby of many developers. It is a kind of ERP which has no documentation.
Problem 1: ========= Now, to reduce the size of the database we have examined some of the orphan procedures, tables, and columns. Is it possible for me to create a DTS package which create the backup of all the orphan stuff to some other database and delete it from the actual one. Moreover, We still have doubts that some of fields which we think are orphan, might not be. So we need another DTS package which rollback all the changes. (Any Idea for that)
Problem 2: ========= We want another DTS package which would move the data from one database to another and same rollback DTS for this one. (Any Idea for that)
Problem 3: ========= How can we use DTS programming to do these tasks ? what benefits do I got from DTS Programming over DTS Wizard.
We have a stored procedure that failed with: Could not continue scan with NOLOCK due to data movement
We are running with ISOLATION LEVEL READ UNCOMMITTED. There are other jobs running, some of which might be hitting these tables (all using the ROWLOCK hint - though I know that's not guaranteed), however, this stored proc would not be going near the same rows. But even if they were, we'd be happy with either the before-look or after-look. This needs to be a low-impact job and should have minimal impact on ther other jobs, so we can't take out locks. Is there any hint we can use to do this? e.g. can we tell the query to just wait until the data has stopped moving then try again?
I'm trying to set up a transactional replication from SQl Server 2005 to SQl server 2000. The Distributor and the publisher are on our server and the subscription is supposed to be on a SQL 2000 server on a different location. Before upgrading to 2005 I didn't have problems - the replication from 2000 to 2000 was working perfectly.
After I succesfully created the distributor and the publication the first problem that I encountered was that when I tried to create the subscription it was giving me an error that I cannot use an IP to acces the server. I realized to fix that issue by creating an Alias in the SQL Server Configuration Manager for the server where I wanted to create the subscription (a push subscription).
Now when I try to add the subscriber it gives me another message "Execute permission denied on object 'sp_MS_replication_installed', database 'master', owner 'dbo'" - so I cannot create the subscriber.
The user that I use to create the subscriber on the 2000 server is dbo for the subscriber database but it doesn't have rights on the master database. Also I realized that on the 2000 server I still have the old subscription but I cannot delete it - for the same reason - no access on the master database. Before upgrading to 2005 I had the exact same rights on the 2000 server.
I know that SQL Server Express 2005 can subscribet to SQL Server 2000 publications. However, it seems that SQL server 2000 cannot perform a push to SQL Express.
Can anyone refer me to any online help or documentation on how a proper replication can be done between SQL 2005 Exress and SQL 2000??
Hi Nerds There are 2 production servers (SQL Server 6.5). One is publisher and the other one is subscriber. Data is replicated from publisher to subscriber which is a transaction based replication. Each day at 8:00PM data comes into publisher and the updations,replication,backup,reporting are carried out till 11:00AM next day. The subscriber server is used as a reporting server and the publishing server is used as the data warehouse server. I want to cleanup a table on the publisher. Since it is replicated on the subscriber, can I do delete operation on the subscriber, make the subscriber server a publisher and the publisher server a subscriber, do replication from publisher(new) to subscriber(new) and again change publisher to subscriber and subscriber to publisher without affecting the production line of the databases. I have to do this between 11:00AM to 8:00PM. Pranav.
I am getting "Row not found on subscriber" error on the subscriber. I am trying to find the root cause.
1. Records get lost during publication initialization from backup, could not reproduce it though. I was inserting records in a loop into a replicated table while initializing the publication from backup. After the backup/restore finished, I have stopped the insert, allowed the replication to catch up and compared the row count, it was identical.
2. Someone or some process is deleting the records on the subscriber. Setup Audit, but could not confirm this theory so far.
3. Records get deleted twice, as we publish the execution of some bulk delete archive jobs, to fix latency issues. Found this article, which says, this is not the case:
[URL]
4. Records are deleted on the subscriber by triggers. The impacted database has no triggers.
i have replication setup and working, there are 3 subscribers and now i want to add a 4th one. is it ok if i just add the subscriber and reinitialize replication?
We have a central office with a SQL2005SP4 server (yeah, I know... old as heck) that's the main database and it has multiple subscribers in regional offices. Well... one of the regional offices server is failing, and it needs to be replace.
The original server is an ancient Win2003 86x
The Server team will build a new Win2008r2 64x, and use the same name and IP address
And I'm tasked with the SQL part.
I'll be installing the same version/patch of SQL, but 64x instead, and migrate all databases, including the system databases.
How do I handle replication? Do I need to reintialize from scratch? or can I just use the backup as a starting point?
Any experience of success or failure setting up CDC on the subscriber end of transactional replication?
Also for a bonus answer, why are explicit index operations not permitted (I'm assuming this is even on the publisher?) From BOL:
• Explicitly adding, dropping, or altering indexes is not supported. Indexes created implicitly for constraints (such as a primary key constraint) are supported.
When creating the snapshot I didn't choose the option copy non-clustered indexes. I created some indexes manually on subscriber database. Replication failed later with some issue.
so reinitialized with new snapshot but all the indexes are gone from the subscriber database.
How to keep the indexes without dropping from subscriber database whenever we reinitialized with snapshot
I have been adding a lot of Articles for Publication.
They make it to the distributer but they do not make it to the subscriber.
If I add articles before the previous batch of articles get applied to the subscriber could that cause a problem and for replication to become out of sync?
It is a pull transactional replication.
I have had to generate a snapshot for the problem to go away but it keeps happening.
I have Two databases,that same schemas and same tables. that both servers are different location. but data must be update and input from both sites.that data must be same both.
so i've planed to replication data both servers,then i must chose merge replication right?
so i'm set
server1 (assumed server1 as main) are Distributor/Publisher
and
server2 just Subscriber only
that i can configuration successfully.
then i'm chose push replication from server1 but it's not work.
this is error message that show
The process could not deliver the snapshot to the Subscriber.
please advise me what's wrong and how to fix problem?
I am getting the error The transaction log for database 'ReplicationDB' is full due to 'LOG_BACKUP'.log_reuse_wait_desc from sys.databases is showing logbackup
The database is subscribed database. We configured transactional replication. But the transactional replication is getting errors and failed. Is there relation b/n this replication failures and log growth in subscriber db?
