53

The way I always like to visualize high availability solutions is the following: SQL Server Failover Cluster Instance (FCI) What is highly available? The entire instance. That includes all server-objects (logins, SQL Server Agent jobs, etc.). This also includes databases and their containing entities. It's a great solution for highly available SQL ...


23

two (or more) servers in a Windows Failover cluster, SQL Server as a clustered instance What Kind of workload? "It depends" - but seriously, this is useful for an online application where you need to have local in data center High Availability. You are protected against a failure of one machine, or of one operating system. The logins, jobs, new databases, ...


18

After doing some online search,I found what you needed,hope my answer helps you. Answer 1 Note: It Only works with transactional and transactional peer to peer replication T-SQL script which you can use to monitor the status of transactional replication and performance of publications and subscriptions. Things to be considered before executing the ...


10

Does Transactional replication suit my problem the most among SQL Server replication mechanisms? Out of all of the replication options - Transactional sounds like it is what helps you the most here. It gives the minimal latency and it doesn't need to be (but can be) bidirectional. You aren't merging changes and don't need Peer to Peer. That said - ...


10

TLDR: It appears that disabling and re-enabling replication probably fixed the issue: exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'false' exec sp_replicationdboption @dbname = N'DatabaseName', @optname = N'publish', @value = N'true' I guess this is the equivalent of switching it off and then back on again... ...


9

It is also important to consider what is shared. Failover Clustering uses two or more server nodes sharing one disk array. If the disk array goes down then you lose service, regardless of how many server nodes there are. If the server room where that disk array is located catches fire or floods then you lose service. AlwaysOn Availability Groups and ...


9

This configuration can be changed using Management Studio by right-clicking the server -> Properties -> Advanced tab -> Miscellaneous -> Max Text Replication Size. Or you may want to try : EXEC sys.sp_configure N'max text repl size (B)', N'-1' GO RECONFIGURE WITH OVERRIDE GO


8

You can do this, but keep in mind that your new column will be wiped out if the subscription is ever reinitialized. ie. The DBA at the publisher alters the publication to add a table, corruption, maintenance, etc. and the snapshot needs to be reapplied, any changes you have made to the replicated schema are dropped and recreated from the publication. So any ...


8

I was wondering this running an instance of SQL Server Express would suffice for the role of a the Distributor database? Is this remotely feasible? SQL Server Express cannot serve as a Publisher or Distributor. SQL Express can only be Subscriber. Refer to : Replication Considerations (SQL Server Express) for more details. EDIT : To make my answer more ...


7

Just to add that you don't have to drop and re-create the replication just to change the "not for replication" bit. You can do it using T-SQL without generating a snapshot or breaking your replication -- sys.sp_identitycolumnforreplication 1 = not for replication 0 = for replication and this causes the problems with Identity colums on subscriber side To ...


7

You should be able to make DDL changes as described in this article . Make Schema Changes on Publication Databases make sure you dont use SSMS. Use T-SQL only. How to: Replicate Schema Changes (Replication Transact-SQL Programming) specify a value of replicate_ddl for @property and a value of 1 for @value. EXEC sp_changepublication @publication = N'...


7

3 important pieces of information are missing to pinpoint exactly what went wrong in your particular scenario: What where the exact steps you took, from the moment you found a problem until the moment you discovered that replication didn't work? What was broken, what was repaired, what was lost? All of this information is available in the output of CHECKDB. ...


7

Since you have this requirement, They do not want to DELETE data from this secondary reporting database, if data gets deleted from the live database AlwaysON gets eliminated. I would suggest you to setup Transactional replication. Frequently (every 30mins) move the transaction data out of live database to another database You can schedule the log ...


7

I had to finally call Microsoft support and just a simple command called DBCC LOG INFO on publisher revealed a possible root cause. I saw more than 8600 VLFs! and this was the cause of that latency. Also, our log file is preallocated to 538GB. Replication was out of sync by almost 19 hours by the time I got follow up call from Microsoft help 4:00pm ...


7

TL;DR: This error can occur if you restore (or move) a database that was previously replicated to a server of a lower version and/or patch level and then re-enable replication. See below for solutions and a longer explanation. Replication uses database triggers for certain things When a database is published for transactional replication in SQL Server, as ...


7

If you do this: SELECT * FROM sys.databases And the log_reuse_wait_desc shows AVAILABILITY_REPLICA, that means SQL Server is waiting to send log data to one of your Always On Availability Group replicas. One of the replicas may be lagging behind due to a slow network, or it may be down altogether. If you check the AG dashboard and it shows no queues, you ...


7

Short Answer: Nothing at all is wrong with a domain account. When all the computers participating are in the same domain, go that way. Easier to manage and maintain and secure. Your confusion here lies in the way the documents you are looking at are structured. In the document you reference in the comments above you see this section: If your replication ...


6

Replication across non-trusted domains or workgroups can be done using Windows Authentication by configuring pass-through authentication. Create a local Windows account on both the Publisher and Subscriber that has the same username and password. Use this account for the replication agent process account and have the connections to the publisher, ...


6

Just for completeness, there is the option of using plain old mirroring. The advantages here include having two copies of the database without the complexity of using Availability Groups, and without needing shared storage for Failover Clustering. Disadvantage, although slight, is mirroring is deprecated. Failover times with mirroring are on the order of ...


6

Tranactional Replication works by reading the transaction log. This kind of "non updating update" often won't generate any transaction log records. See Paul White's article The Impact of Non-Updating Updates for more about this.


6

If you change a user's permissions to a particular table BEFORE a Subscriber has been initialized or reinitialized, if the article property Copy permissions is set to true, the permissions will be copied to the Subscriber when the snapshot is applied. If you change a user's permissions to a particular table AFTER a Subscriber has been initialized, the ...


6

What could/should we do? Discard the assumption of synchronous replication and redesign your application accordingly. Replication was never designed for this. Latency of hours can happen and you should design accordingly. None of the existing SQL Server technologies, including Availability Groups readable secondaries in synchronous commit mode, make a ...


6

No, It is not possible. when the table is involved in replication it does not allow you to drop the primary key. below is an example as how I have changed the primary key of a replicated table: table is dbo.CategoryImportMap, database is Product_Staging. --------------------------------------------------------------------------------------------------- --...


6

Will changing isolation level to Read Committed Snapshot Isolation (RCSI) help here? This is not a straight forward change and it comes with additional tempdb penalty. I would not suggest you to just change the isolation level to RCSI without properly testing and seeing benefits in your environment. Trust me, this is a sledge hammer approach. We recently ...


5

1.Does the transaction replication have any load on the primary server(pardon my use of generic terms) when its moving data out. There will be an impact on the publisher, but depending on how you set up your transactional replication schema that can be very small. One big problem that can become an issue is if you have a local distributor, especially if ...


5

I tested it out, and yes, this works, even under Peer-to-Peer. (My original comment was incorrect as that only applies to physical columns.) Given that you don't control the publication, I would recommend naming the column(s) you add very carefully so as to not interfere with any future schema changes. Also, if this database backs a 3rd-party ISV ...


5

As far as I can tell, the table master.dbo.MSreplication_options, amongst various other replication-related tables, are supposed to exist on any SQL Server 2008 r2 install whether or not you've set up replication. Somehow the one on my SQL Server instance got dropped. I eventually found this handy script on Google (cached!) which 'fixes up' various ...


5

Is this pretty straight-forward? Yes and No .. depends on what is your downtime (maintenance window), database size or amount of data you are replicating (whole or subset of articles). Script out current replication setup (both create and drop). First make sure that you don't have any pending commands to replicate and no user connections while you are ...


5

Yes, initializing a large database through a snapshot can be very slow and time consuming. If it is appropriate to your case, you should start by restoring a copy of the database to the target machine. In setting up the subscription choose "allow initialization from backup files". Because of limitations in the user interface for setting up replication, ...


5

I don't need a real time sync as mirroring does it, also I don't need any king of cluster solution, just read only syncing copy of my PROD database. As Mike mentioned, T-Rep is not free. You have to maintain distributor database and there are dependencies on log-reader agent. If the amount of changes occurring on Publication are large and depending on the ...


Only top voted, non community-wiki answers of a minimum length are eligible