Sql-server – Data Tier Applications (DAC) on replicated databases

data-tier-applicationreplicationsql server

We are exploring options to accomplish 3 things: 1) source control the database, 2) centralize and coordinate all database changes and tie them to application changes, and 3) automate database deployments.

I'm considering using DACPACS for this but we have 4 production databases that have replication between themselves set in place. The replication was set up a long time ago and was implemented in an odd fashion. Essentially, not all 4 databases are exactly the same. Some data is replicated and some is not.

My question is about applying DACPACS. If we were to apply the DACPACs to each database during a deployment, how would this affect replication?

Thanks

Best Answer

The short answer is: SSDT (dacpacs) does not support SQL Server replication.

The longer answer is that you might be able to work around this limitation by using pre and post deploy scripts. And maybe write your own deployment contribution extension to handle replicated objects better.

For the pre and post deploys scripts approach you could write something that captures the current replication state (publications, articles, subscribers and subscriptions) and then pulls all that down. Then the dacpac updates the current schema and a post deploy script(s) recreates the replication.

If you're strict on your replication topology and configuration this could work. It won't be easy though! Another similar approach, which would also buy into your "get the database under souce control" is you only allow replication to be configured from scripts and these become your pre/post deploy changes (much like above but less dynamic work).

Another approach would be to use another system for database control and deployments. Something like:

These both take the database migrations approach rather than model first approach. These would probably make the replication aspects easier to handle.

Related Solutions

MySQL master/slave replication… structure not being replicated

Using replicate-wild-do-table=% is not the correct way to get complete replication. That would seem sensible, but here's why it isn't:

The replicate-* options are restrictive by their presence. The *-do-* options seem to be telling the server what to "do," but in fact they are telling the server what to only do.
The complete absence of any replicate-* configuration variables means "replicate everything."

In the simplest case, when there are no --replicate-* options, the slave executes all statements that it receives from the master. Otherwise, the result depends on the particular options given. -- http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html

That, I think, is the point you are needing. It holds true for all MySQL 5.x.

Once you enable binary logging, set the server-id values on each machine, synchronize the data sets, CHANGE MASTER TO ..., and then START SLAVE, then you should have a working configuration where all DML and DDL will be replicated and your servers will be identical replicas of each other.

If you started out with identical data sets, everything should behave exactly as you would expect it to.

What MySQL replication does best and simplest is replicate entire data sets among servers without any restrictions. Trying to restrict replication to a subset of the data is a process that should carry a warning label that ends with the phrase "...unless you really know what you're doing."

When you use the --replicate-* options, the document cited above also offers this tip:

it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options

You are using a mix of these, which adds complexity and may explain why your DDL didn't replicate as you expected it to.

Sql-server – Automatic Version increment on Data-Tier Applications

I found this forum thread had an answer that worked well: Automatic Version increment on Data-Tier Applications

Minimum steps are:

Create Properties\AssemblyInfo.cs if it doesn't already exist, and make sure it's included in the project and contains at least this code: using System.Reflection; [assembly: AssemblyVersion("1.0.*")] Or however you want your version number to go.
Edit the *.sqlproj file in an outside editor (notepad etc) and add the following XML:

    <Target Name="SetDacVersionToAssemblyVersion" AfterTargets="CoreCompile">
        <GetAssemblyIdentity AssemblyFiles="$(IntermediateTargetFullFileName)">
            <Output TaskParameter="Assemblies" PropertyName="IntermediateTargetAssembly" />
        </GetAssemblyIdentity>
        <PropertyGroup>
            <DacVersion>$(IntermediateTargetAssembly.Split(',')[1].Split('=')[1])
            </DacVersion>
        </PropertyGroup>
        <Message Text="DacVersion set to $(DacVersion)" Importance="high" />
    </Target>

Best Answer

Related Solutions

MySQL master/slave replication… structure not being replicated

Sql-server – Automatic Version increment on Data-Tier Applications

Related Question