Database Edition support for replicated environment deployment

Question

Where can I find either guidance and/or what is supported for using VS 2008 TS Database Edition (and/or 2010) to deploy to SQL Sevrver environments using replication?

Answer 1

Hi Brian

I guess what you want is Xcopy deployment of SQL Server, you can see http://msdn.microsoft.com/en-us/library/ms165716.aspx for detailed steps.
Note that to use Xcopy deployment, SQL Server Express must be installed on the target computer and must have an instance running.

In general,
I've a blog talking about how to deploy SQL Server with your client Windows application:
http://www.cnblogs.com/feishunji/archive/2009/07/31/1536131.html
And Scott has written a blog for the scenario that apply to ASP.NET application and to remote hosting environment:
http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx

Especially for VS Database GDR, it provides a powerful tool called vsdbcmd.exe which can be used to

• Importing a database schema from a live database into a dbschema file;
• Deploying a database schema file to a live database (full and incremental deployments);
• Generating deployment scripts based on a dbschema file and a live database (full and incremental deployments).

See its command usages at http://msdn.microsoft.com/en-us/library/dd193283.aspx
And if you just use between the development machines, you can use the UI sweet which gives a deploy option when you right click a DB project(and deploy settings can be edit in the properties of the project).

If you have anything unclear, please feel free to let me know.

Thanks.

Figo Fei

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Unfortuantly the "FOR REPLICATION" and "NOT FOR REPLICATION" keywords are not supported by Database Edition. We have some level of awareness around them. You will not get syntax errors on them, however we will drop them from scripts during deployment, and in most cases we will not update objects that are under replication.

Christian.

---

This posting is provided "AS IS" with no warranties, and confers no rights

Answer 3

Do you mean deploying database projects to sql server that are either publishers or subscribers in a replication topology? This is not handled very well in gdr r2 - there is some discussion about it at http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/83471c80-91ca-4a12-abb7-4228ca4fb7b2

Designing which tables are published and which subscribers they go to is also not handled in gdr r2 and so any scripts to do this must be kept outside of a database project.

However we do use a lot of replication in our systems and have all our databases in database projects (and scripts for replication), so it can be done.

Please feel free to ask any specific questions

Answer 4

Do you mean deploying database projects to sql server that are either publishers or subscribers in a replication topology? This is not handled very well in gdr r2 -

...

Designing which tables are published and which subscribers they go to is also not handled in gdr r2 and so any scripts to do this must be kept outside of a database project.

...

I was hoping to find some canned guidance (or even a how-to) on using VS 08 to deploy database changes to a publisher and subscriber (single-subscriber context).

In our case, our site already has a tool for deploying scripted changes across all our environments.  However, with the recent introduction of single-subscriber replication we're evaluating options for deploying scripted changes across both publisher and subscriber.

So, I'm thinking of the following scenarios:
1) change affects publisher only
2) change affects publisher and replicates through
3) change affects publisher and subscriber and publication must be changed
4) change affects subscriber only

The closer I can get to running a single deployment process to affect all database, the better.

It sounds like GDR r2 doesn't handle some of these out of the box - so how are you handling the distributed changes?  Are you treating them as three scripted projects (publisher, subscriber and publication?)

For example, how do you handle scenarios like new table when deploying?

Thanks!