Getting the SQL Server PowerShell tools

They're apparently not that easy to find. The SQL Server PowerShell Tools come as part of the SQL Server Feature Pack for their respective versions. You can find the SQL Server 2014 Feature Pack (along with the PowerShell Tools) here.

SqlPackage.exe fails to deploy to Azure with error "The database platform service with type Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider is not valid."

I've recently started trying to deploy to one of my own databases in Azure using SqlPackage.exe. I've used it numerous times at work without problem, but on my own system at home, I keep running into the following error:

"Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider is not valid. You must make sure the service is loaded, or you must provide the full type name of a valid database platform service."

As it turns out, you need at least SSMS CU#6 to get a version of SqlPackage.exe recent enough to deploy to Azure with SQL Azure v12. The copyright on the SqlPackage.exe executable must be 2015 (or later). Such a version comes with the latest versions of SQL Server Data Tools (SSDT). If you're unable to use the version of SqlPackage.exe that comes with SQL Server 2014 because it's too old ("C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe"), you can use the version that comes with SSDT for Visual Studio 2013 ("C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\sqlpackage.exe").

Creating users in a SQL Azure database

... is not as straight forward as you would think. It has some slightly different requirements for syntax than what you'd normally use to create users in a local or on-premise SQL Server instance.

You can see the tutorial page on MSDN here.

A really quick note about deploying SQL Server .dacpac files to SQL Azure

When you're using SqlPackage.exe to deploy a .dacpac to Azure, you must have access to the 'master' table in order for the tool to retrieve information necessary to create a diff of the database and deploy the differences during the Publish action.

For that reason, you need the 'sa' account, because simply having 'db_owner' on the Azure database is not sufficient.

Beginning with SQL Server

Given changes in the environment in which our business does it's business, I've had to start learning to use SQL Server and various other Microsoft technologies lately. A quick note on connecting to a foreign SQL Server instance (ie another company's ...) :

Create an alias for the database so that Enterprise Manager has something to work with

Open up the Client Network Utility which should be included in the Start menu programs group for your SQL Server installation.

Start -> Programs -> Microsoft SQL Server -> Client Network Utility -> Alias (tab) -> Add... (button).

Under 'Server alias' enter an easy to remember name for the connection. Under 'Network libraries' select the TCP/IP option. Under 'Connection parameters' enter the DNS name or IP address under 'Server name' of the server you wish to connect to. Change the option for 'Dynamically determine port' if the server you're attempting to connect to doesn't run under the standard port of 1433 for SQL Server. Hit 'Ok' to save your settings, then 'Ok' to close out of the Client Network Utility.

Create a new SQL Server Registration to add your SQL Server instance to the Enterprise Manager

Open Enterprise Manager. It should be in the same programs group in the start menu as the Client Network Utility. Once open, create a new SQL Server Group if you haven't already got one (or haven't got one that you want to add your new SQL Server instance to). Then add a new SQL Server Registration. You should be prompted by a Wizard at this point. (If you're not, then you've probably already disabled the Wizard and don't need to be reading this tutorial.) In the wizard, click on 'Next' to proceed to the SQL Server selection screen. Under the list of available servers, you should see the alias you just created in the previous step. Select it and click on 'Add >' to add it to the list of added servers, and then click on 'Next'. After this, you should be prompted for credentials, and the rest is pretty self explanatory.