Hot answers tagged

40

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


9

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


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


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


6

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


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

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

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

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


6

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


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

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


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

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

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


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 http://www.mssqltips.com/sqlservertip/2386/...


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

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

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


4

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


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

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


4

Yes, the transactional replication data will build up while waiting for distribution to start again. Of course, if not tracked it is possible that a long delayed set of transactional replication data can fill the log space that you have available, thus causing you problems. Replication is hostage to the resources available, including the log space since ...


3

It depends on your workload. As a baseline, use Performance Monitor and log the network counters when not using Log Shipping or Transactional Replication and measure for 1 hour. Then setup Log Shipping and use Performance Monitor to log the network counters for 1 hour. Then setup Transactional Replication and use Peformance Monitor to log the network ...



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