Tag Info

Hot answers tagged

15

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


8

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


7

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


6

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

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


6

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


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.


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


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

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

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

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


3

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


3

A few thoughts. It sounds to me like you may be able to get away with a couple options if replication is what you end up with. First a word of caution - Replication isn't something that should just be configured in production and enabled/used unless you have some experience with it. This becomes truer with more important workloads and busier systems. ...


3

According to Define an Article: If the publication has existing subscriptions and sp_helppublication returns a value of 0 in the immediate_sync column, you must call sp_addsubscription to add the article to each existing subscription. After executing sp_addarticle, execute sp_addsubscription to add the article to each existing subscription. Then ...


3

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


3

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


3

By default, replication will push out the following DDL changes to subscribers: ALTER TABLE ALTER VIEW ALTER PROCEDURE ALTER FUNCTION ALTER TRIGGER And by default, I mean unless you have specified otherwise when you made a call to sp_addpublication when setting up the publication (even if done through the GUI, this is the underlying stored procedure that ...


3

I Googled it -- http://www.sqlrepl.com/sql-server/accidentally-dropped-distribution-database/ -- try to follow his advice!


2

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


2

There are perfmon counters that you can probably use for what you're trying to do. Since it's a counter provided by SQL, it's available in sys.dm_os_performance_counters. Try select * from sys.dm_os_performance_counters where object_name like '%repl%'. Slice and dice as necessary.


2

I'm not sure about the core of your question ("Can you set up replication between SQL Server 2005 and SQL Azure Database?"), but my assumption is a resounding "NO". There are tools to migrate from SQL Server to Azure though - it's been discussed on Stack Overflow here and here. The consensus seems to be that This SQL Azure Migration Wizard (CodePlex hosted ...


2

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


2

In the end I simply did what the server wanted, i.e. created all the "publication" tables (dbo.MSpeer_* and dbo.sys*) in the problematic database (the one that was the publisher), through Script Table As -> CREATE To dialog. Thanks to this server allowed me to disable publishing and distribution. Then I only needed to reconfigure replication. Hope, next ...



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