Hot answers tagged

35

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 ...


18

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 ...


8

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 - ...


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 ...


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 ...


7

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 ...


6

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 ...


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

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 ...


5

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 ...


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 ...


5

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 = ...


5

You could also solve this with a trigger that populates the copy of the table on insert/update/delete. It wasn't clear in the question that these tables are actually on different servers, and that the subscriber was unreliable. In that case you could simply log ship to the subscriber - you can get pretty close to real time here, though you will have to kick ...


5

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 ...


4

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 ...


4

I believe you are running into the desktop heap issue described here (support.microsoft.com/kb/949296) and here (support.microsoft.com/kb/824422).


4

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 ...


4

1. Does the transaction replication have any load on the primary server(pardon my use of generic terms) when its moving data out. Many factors that it depends -- On the activity on the primary server which will be the Publisher server. Also, if the distribution agent on the same or different server matters a lot as it will be doing the heavy work of ...


4

Putting the comments into an answer so the question can be marked answered: @crummel4 says, "See the third section from the bottom of http://replicationanswers.com/NoSyncOn2005.asp titled How "initialize with backup" Works, and How to avoid Pitfalls" @Kin says, "For step by step instructions, please refer to ...


4

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 ...


4

Panic over ! When adding the stored procedure to the Article, in the properties there is a setting called 'replicate'. By default this is schema only. When i changed this to Execution of the stored procedure' instead of 'Execution in a serialized transaction of the SP'. and restarted all the agents, it worked as one transaction this time


4

Please don't. You don't want replication. It's a data distribution technology: it's not suitable for HA. Just to name one of the many shortcomings of using replication for HA, you won't have automatic failover (which seems to be a requirement) and you'll need to code it yourself. If you need HA, pick one among clustering (you can have a two-node cluster ...


4

Those aren't different versions just different service packs. Replicating between 2008 and 2008 R2 would better fit that definition of replication between two specific versions. So The way I understand the MSDN article on the subject here is this When you replicate between or among different versions of SQL Server, you are usually limited to the ...


4

Why do you expect nearly instant replication? That is not in the definition of replication. See the SQL Server Replication page. It says in part: "remember that replication is an asynchronous data-movement process that is likely to have some latency." If you are using Availability Groups with synchronous connections, the secondary server will keep in ...


3

Is there a way I can find the name of the publisher and distributor (server and database), from the subscriber? For push subscriptions from the subscriber you can query MSsubscription_agents to get the publisher and publisher database. For pull subscriptions from the subscriber you can query MSsubscription_properties to get the publisher and ...


3

I need assistance to execute/run automatically below .SQL file/Query automatically at subscriber once publication is done You should use sp_addscriptexec tsql to run sql script on all subscribers of a publication once the snapshot is applied to subscribers. Make sure you use @skiperror = 0 to stop the distribution agent when an error is encountered. ...


3

The schema on the left is your own DDL statement. The schema on the right is a DDL statement generated by your schema comparison tool. The statement on the right shows constraint names for your DEFAULT constraints. Yours does not have them but that does not mean SQL Server allows you to create a nameless constraint. When you do not provide a name ...


3

The SQL Server 2012 version of the MSDN page you have linked says (emphasis mine): A Subscriber to a transactional publication can be any version within two versions of the Publisher version. For example: a SQL Server 2005 Publisher can have Subscribers running SQL Server 2005, SQL Server 2008 (including SQL Server 2008 R2), or SQL Server 2012; and a SQL ...


3

Is this a good candidate for transactional replication? I would use SSIS to incrementally load data (if the data set is too big) or just freshly load the data (if the data is less) every night (or anytime). This way you dont have to create any publication, distribution database or subscription. Easy to manage when you do upgrades on your main database ...


3

Yes, this still holds true. It is covered in Upgrade Replicated Databases in the SQL Server 2014 documentation.



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