We are using Visual Studio 2013 with SSDT mainly for versioning t-sql code, so the sql is being developed on the dev server and then we use schema compare to transfer the scripts into visual studio (and check into Git). Before deployment (which we currently do with schema compare, too) we have to replace database and server references (with [$(database)] etc.). If we change the code in the dev server and compare again, such SQLCMD variables are lost again. (I would expect schema compare to be smart enough to retain the SQLCMD variables but I found no way to accomplish this).
The logical step is to develop sql in visual studio from the start. But so far, it has been hard to convince anybody in the team to do that. One can write sql and execute it in VS, no problem. One can also switch to SQLCMD mode and execute, all right. But when you create e.g. a view in VS, you must write down a create statement and of course this can be executed once but will yield an error when altering the view and executing the create statement again.
So my question is if anybody has some essential tips on how to do database development exclusively in Visual Studio. We were able to get the database references and all that straight, but not the development process.
I've been streamlining local database development and deployment using Visual Studio database projects for a few years now. Here are some tips.
In general...
Use local db instances: Each developer should have their own database instance installed locally. All scripts (tables, views, stored procs, etc.) should be developed in Visual Studio. Create a publish profile for deploying the project to the local db instance.
Use Publish feature: Confusingly Visual Studio provides both a Deploy and a Publish option which ultimately do the same thing. I recommend using just Publish because it's more prominent in the UI and you can create profiles to configure the deployment process for various database instances.
Keep local db up to date: When a developer makes changes in the database project and checks them in to source control then the other developers should check out these changes and republish the project to their local databases.
Create vs. Alter statements
All of your statements should be Create statements. There is no need for Alter statements or existence checks. Everything should be scripted as if you are creating the database objects for the first time. When you deploy/publish, VS will automatically change your Create statements to Alter for existing objects.
Data
Some ideas:
Script your data as a series of Insert statements and include them in a post-deployment script in the database project. However this can be tedious and error-prone.
Keep a database backup that includes all of your test data. When setting up a development environment for the first time, create the database from the backup. After you make significant changes to the data, create a new backup and have your devs recreate their databases from the backup. In most cases it's ok if the backup is out of sync with the schema defined in the project -- simply republish the project to get the db up to date (make sure to turn off the "Re-create database" setting so that only the differences are published and thus the data is not lost).
There may be 3rd party tools to do this in which case they are worth looking in to.
Create your own solution for deploying data. Mine involved the following and worked really nicely (but required a lot of time and effort!):
Test/Production Environment Deployments
Using the Publish feature: You can create publish profiles for your test and production environments. However it will include your pre- and post-deployment scripts which may be configured for development, and also you won't get the versatility that the other options provide.
Using dacpacs: Ed Elliott covered them in his answer. Advantages: no need for Visual Studio to deploy, they can be deployed via SQL Management Studio or the command line with sqlpackage.exe, they can be more easier to work with than a T-SQL deployment script.
Using Schema Compare: Schema compare may be good if you can use Visual Studio for your deployments and you'd like to preview the changes before they are deployed. You can also selectively ignore certain changes which is useful when you aren't lucky enough to have a development environment that completely mirrors production.