Tom Hall's Blog : SQL Server

Connect to SQL Azure using SQL Server Management Studio 2008

Tom Hall — Mon, 26 Oct 2009 00:04:11 GMT

SQL Azure is now in CTP and for those who have received an invitation to test it out, you might be wondering how you can manage your databases via SQL Server Management Studio…. Here’s a few steps to do it:

Set up your SQL Azure server and Administrator Username via https://sql.azure.com/
On the SQL Azure website under Firewall Settings ensure you check “Allow Microsoft Services access to this server” and add your IP address to the IP Address Range
Open SQL Server Management Studio
Close the “Connect to Server” dialogue and click “New Query”
In the “Connect to Server” dialogue box type the server name provided via the SQL Azure website, e.g: <servername>.database.windows.net
Select Authentication to SQL Server Authentication
Your username is the Administrator Username you set up in this format: <username>@<servername>
Password is obvious?

From here you can run as much T-SQL as you like :)

Exporting your SQL Server Schema and Data to a Single Script File

Tom Hall — Tue, 09 Dec 2008 21:54:20 GMT

I host my website, and manage several other websites using shared hosting, these hosting packages all contain some sort of SQL Server database allowance. Using shared hosting, problems can and do arise when you’re ready to deploy from development/test into the live/production environment, e.g. you have your database fully constructed and in working order on your development/test environment with all your static tables populated, with a bit of sample data thrown in on other tables.

SQL Server has a few built-in tools to enable you to copy data to and from servers and databases, “Copy Database…”, “Export/Import Data…” and of course you can always perform a “Backup and Restore” of the database and copy the backup file to another server. These all work great on your local network (most of the time!) where you hold a lot of security rights compared to when you are using a shared hosting environment where you hold a lot less security rights, therefore you cannot perform these built-in tasks (well not always). Problem!

Well, actually, not so much of a problem anymore since there is a tool, Microsoft SQL Server Database Publishing Wizard, which will enable you to script the entire database schema AND data to a single script (.sql) file. If you want, you can script individual parts of the database, and you can also set a target SQL Server, so if you are developing using SQL Server 2008 Express Edition but your host is SQL Server 2000 or 2005 you need not worry about compatibility.

SQL Database Publishing Wizard is built-in to Visual Studio 2008 and can be accessed under the “Server Explorer” window as seen to the left.

Otherwise, if you don’t have Visual Studio 2008, you can download and install the SQL Server Database Publishing Wizard 1.1.

When you’re ready, either going through Visual Studio or sqlpubwiz.exe, first things first, you’ll have to set up your connection string and if you are connecting to a remote server then you may come into difficulties if the database is large and your connection is slow, so bare that in mind and perhaps tweak your connection string, “Connect Timeout”, “Packet Size” and “Max Pool Size”.

I really don’t need to explain anything else since these screenshots speak for themselves:

It really is as simple as that… Once you click finish, the wizard will whiz away generating the T-SQL you need to deploy your database schema and data to any SQL Server, providing you set “Script for target database” appropriately.

Some notes: When executing scripts onto the remote shared host SQL Server, this can take a long time, depending on the size of the database script, and while the script is executing your database will be unavailable.

Also, you can use sqlpubwiz.exe via the command line (CMD), so obviously with that in mind, you could run batch files to generate a script backup.

Hope that helps, it’s certainly made my life a lot easier!

Microsoft SQL Server Management Studio 2008: Error 916

Tom Hall — Mon, 22 Sep 2008 22:38:00 GMT

I came across a problem when using Microsoft SQL Server Management Studio 2008 to connect and manage a database which is housed on my shared host’s SQL Server 2005, the problem was that I couldn’t actually access my database, as I clicked the “Database” node within “Object Explorer” I received the message:

The server principal “username” is not able to access the database “databasename” under the current security context. (Microsoft SQL Server, Error: 916)

Anyway, after an hour, I decided that I would try and access my database within Microsoft Visual Studio 2008, using the “Server Explorer”. I set myself up with a connection to the database server, and hooray, I could connect and access everything. That left me wondering why it wasn’t working in the Management Studio, so I spent some more time trawling the net for an answer (since I’m hardly a SQL Server guru!). I eventually found something to help me out…

When I clicked on the “Databases” node within SQL Server Management Studio, the software would (in the background) execute an T-SQL query to retrieve a list of databases along with additional information about those databases, one of those pieces of information is collation, which I didn’t have permission to do for every database, only my own.

Fix:

In Object Explorer, click Databases
Display “Object Explorer Details” (F7) or “View –> Object Explorer Details”
Right click the column headers and deselect “collation”
Refresh Databases, and hopefully bingo!

Hopefully that helps anyone who finds themselves in the same situation! :)