How to Connect to SQL Azure through SQL Server Management Studio 2008 (SSMS 2008)

Most developers on the Microsoft stack that use SQL Server, and who are migrating their databases to SQL Azure, likely work in SQL Server Management Studio 2008 (SSMS 2008), Visual Studio, or a mixture of both, for DBA and data management tasks. Fortunately, SQL Azure has two options for those who need to administer, create, maintain, and develop using SQL Azure: The SQL Azure Management Portal and SSMS 2008.

Before you start — If you want to try out the online tools or see take SQL Azure for a spin with SSMS 2008, sign up for a 90 day free trial.

Connect to SQL Azure from SSMS 2008

You can use SQL Server Management Studio (including, and especially, SQL Express) to connect to SQL Azure, but you first need to have some information handy that you can find in the SQL Azure Online Management Portal. Once there, you can view your subscription information including the information you need to connect to a SQL Azure server or database.

image3

Here is the information you need:

  1. The fully qualified server name. See the blocked out, red, parts of above image, as to where you can locate your server info.
  2. Valid credentials that you have already setup via the online SQL Azure Management Portal (of course, the password is not available for viewing, as it should be memorized anyway)

Enter this information into the SQL Server 2008 Connect to Server dialog, and click the Connect button to authenticate. Don’t forget you must choose SQL Server Authentication before you may enter credentials.

image16

Upon successful authentication, SQL Server Management Studio opens. This is the exact same SSMS you are familiar with, with the only difference being that you have connected to SQL Azure instead of a SQL Server on your LAN.

SNAGHTMLa64d4e1

From here you can run queries, manage tables, and do all the SQL administrative tasks you need to. Note there is a SQL Azure Database node in the Template Explorer that you can access from the View menu. Of course if you are using SQL Azure you’ll want the SQL Azure SDK for Visual Studio 2010.

Connect to SQL Azure from Visual Studio

The same credentials and authentication happen in both tools, SSMS and Visual Studio. This means all that you need to do is open the SQL Server Object explorer and connect exactly as you would any SQL Server in your LAN. Once connected, you can enjoy administering and working with SQL Azure inside of Visual Studio.

SNAGHTMLcf0cc28_1

Troubleshooting SQL Azure Connectivity

The Firewall check failed error is very common, as you need to enter an IP Address range to connect to SQL Azure from various client programs (i.e., SSMS). Here’s the error text:

Firewall check failed. Cannot open server ‘SERVERNAME’ requested by the login. Client with IP address XXX.XXX.XXX.XXX is not allowed to access the server. To enable access, use the SQL Azure Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.

If you get this error you can reset it from the Windows Azure Mgmt Portal. Just navigate to the server you need access to, then add in the IP Range, as shown in the image below:

image_4

Alternatively, you can use SQLCMD in the Windows command prompt which will look something like this:

C:\>SQLCMD –U<user>@<server> -P<password> -S<server>.database.windows.net

exec sp_set_firewall_rule N’Allow Windows Azure’,’0.0.0.0′,’0.0.0.0′

SQLCMD Azure commands: http://msdn.microsoft.com/en-us/library/windowsazure/ee621783.aspx

NOTE: Only the server-level principal login (this is the primary/master login that you use to connect to the Windows Azure Portal online, a Windows Live Id), while connected to the master database, can configure firewall settings for your SQL Azure server. Also, check out the SQL Azure troubleshooting for other common errors, troubleshooting, and help.

Common JavaScript mistakes and pitfalls you can avoid

Having solid JavaScript skills is more important than ever in today’s web applications, and will continue to grow in importance as more browsers, IDEs, etc…, boast of HTML5 feature support. New HTML5 elements such as <canvas> , and features like Web Storage, Web Sockets, or Geolocation depend heavily on JavaScript. Despite tons of freely available, open source, or 3rd party libraries such as jQuery, Prototype, or Script.aculo.us, we still need to keep in mind that they’re just JavaScript layered on top of JavaScript – a level of abstraction. Knowing the under the hood workings and “gotchas” of JavaScript will certainly give you a boost with writing better code.

Developers that use strongly typed languages tend to treat JavaScript as a restricted rather than an expressive language, as they’re used to working with many compiler restrictions. On top of that, JavaScript’s reputation as a “toy, browser-only, language” precedes itself; many developers don’t bother to look deeply at some of its powerful features, as well as some language quirks that can really cause trouble.

The truth is out there: Dealing with truthy/falsy values

Dealing with comparisons and equality is usually straightforward, but JavaScript has a few caveats. Because of the way JavaScript deals with expressions, it has two sets of equality operators and some complicated rules around one of them (==/!==). The first set…

The “Equals” operator  ( = = )  and The “Does-not-equal” Operator ( ! = )

And the second set…

The “Strict Equals” operator ( = = = ) and The “Strict Does-not-equal” Operator ( ! = = )

If you’re wondering why JavaScript has two sets of equality(ish) operators, it’s because JavaScript works on the premise of truthy and falsy values.  The == / != operators will work as you might expect if the operands are of the same type (boolean), but if they are not, the == and != operators try to coerce the values, using a rather large and drawn out ritual to determine the result. Truthy/falsy values are a means for JavaScript to deal with non-Boolean expressions, by pretending as if they are Boolean anyway.

As a rule of thumb, JavaScript evaluates the values in the list below as false, and everything else as true:

  • false
  • null
  • empty string (“”)
  • 0
  • NaN

Since values like null, “”, and NaN, aren’t boolean values but are still treated as such, that makes them falsy. In short, when you compare expressions that aren’t actual boolean values, JavaScript can produce results that are inconsistent with what you might expect. For example, if you compare the value 0 to false, this is what you get:

(0 == false)  // true 
(0 === false) // false (correct)

When expressions accept anything, including NaN, undefined, etc…, there’s a lot of room for mistakes, even by experienced developers. Amplify that with the many rules involved to mentally juggle, and it too easy to see how == and != disrupt your thought processes while coding.

The take-away: the strict === and !== operators are the best way to go, but don’t shrug off the equals operator just yet, as you’ll likely run across them daily in the JavaScript world, making this valuable information for the maintenance coder.

What exactly is an isNaN(NaN); anyway?

NaN, or Not-A-Number, is a construct of JavaScript that’s useful only in very specific edge cases. Why then, is this important to know? Being a mischievous little code gremlin, NaN pops up everywhere. It’s a property of the Number object, (Number.NaN), and default value of NaN is NaN, yet it’s a falsy value. When trying use NaN in an expression, NaN returns a false every time. Since NaN is no help in determining what is actually non-numeric, you’ll need an alternative. Fortunately there is a very reliable isNaN() function you can use. Notice when the isNaN() function evaluates an alphanumeric string, the result is true, and a false for numeric values. To further investigate how isNaN() behaves, enter a few of the following statements in the Console tab of any browser’s dev tools and browse the results. (IE F12 dev tools featured below).

SNAGHTML6fc7755

isNaN() works as expected with numbers and alphanumerical data.

The conculsion: use isNaN().

Is eval() evil or just misunderstood?

eval()is the little function that wants to do everything for you, including dynamically creating and executing JavaScript.  Want to parse JSON? eval(). Want to clump a bunch of stuff together? eval(). Want to make up code to run on the fly? eval(). eval() finds ways to do something, anything, with what you give it, making it easy for developers to do both awesome and horrible things with it.

In an attempt to keep eval() innocent, many suggest limiting eval() usage to the following scenarios:

1) You need to allow script to run dynamically.

This opens up your page to potential security issues, so only use this when it’s necessary, as there are a few different ways to execute code dynamically in JavaScript (a quick Bing search shows many different techniques). That leaves…

2) Parsing JSON

You can use eval() for JSON parsing; however, you are still going to run into the same security issues. On top of that, the JSON.parse() method works much better as it’s meant just for JSON handling.

In general, stay away from eval() where you can, it has the potential to get evil quickly, and there’s usually a decent alternative.

Avoid function faux pas.

Functions live a first class life In JavaScript, so they’re not just functions, they’re objects too. It all depends very much on how you them. Take, for example, the following simple function to calculate two numbers:

var result = function (a, b) { return a + b;};

In many languages, the result passed back from the function will contain the sum of the values of the arguments, a, and b. In JavaScript; however, there is more to the story. Opening any browser’s developer tools and inspecting the code, shows the type as an “Object (Function)” and its value as the function itself.

SNAGHTML45e09f_1

You don’t need to, but you can, define and name functions. If you don’t provide a name when creating an inline function, it’s an anonymous function.

Having the knowledge that functions are objects allows you to better understand and work with Open Source or 3rd Party libraries, such as jQuery or Script.aculo.us. Consider jQuery’s chaining feature, that allows you to “tack on”, i.e., chain, other methods to the end of the previous method, in a single statement. Since the function is also an object, you can expect to call methods on it using dot notation, as the following simple jQuery chaining example demonstrates:

$("#inputBox").removeClass("normal").addClass("error");

In addition to function behavior, it’s necessary to know how to write good, clean, functions. Since JavaScript throws everything into a global namespace, you’ll want to encapsulate your functions into namespaces with classes and members (see below). And watch out for globals…

A variable of global proportions.

The authors of JavaScript intended it to be a language with a very low barrier for entry for the internet pioneers circa 1995. Because of this, they made the decision to allow, and even encourage, globally scoped variables. One or two global variables in a very small program can be useful, even manageable, and was certainly not a big issue at the time since HTML had only a few tags and the DOM a handful of nodes. However, JavaScript has grown into arguably the most popular language in the world, and has powered a spectrum of applications from the smallest to the largest. Anything other than the simplest of sites can contain nasty bugs because of the global space.

When possible, avoid global variables, and here’s why:

  • It’s too easy to have variable naming clashes in large applications.
  • It’s too easy to overwrite the wrong variable, or the right variable at the wrong time, when there are many variables accessible from anywhere.
  • Not needing to declare variables means there are often “floaters” hanging around in globally-scoped memory, but doing nothing.

A good workaround to this language gotcha is to create your own namespace and classes in it, and store any would-be global variables as properties of that object instead. Though they’re not real namespaces, they are objects that can behave like namespaces, making it easier for you to organize your code. The code below provides an outline of how you can create and access your own namespace:

var SampleNamespace = new function () { };
SampleNamespace.SampleGlobalObject = new function () {
 
    // Private function 
    var privateFunction = function ()
    { }
 
    // Public function
    this.publicFunction = function ()
    { }
}
// Calling the public function 
var publicFunctionTest = function () {
    SampleNamespace.SampleGlobalObject.publicFunction();
}

Global variables aren’t the only problematic variables in JavaScript. JavaScript doesn’t perform block scope, so variables you define can inadvertently become as error prone as global variables. The only scope smaller than the global scope is function scope. This means that you can access variables where you might expect otherwise, such as outside an if/else or for loop.

Although JavaScript is a dynamic, expressive, language, you need to organize it into namespaces, classes, and other units for a solid maintenance experience.

Summary

Watch out for some of the most common pitfalls that developers encounter, even while using Open Source libraries such as jQuery. There are, of course, many more language quirks and features that can also cause trouble, as with any language. If you want to go deeper into JavaScript, here are two highly recommended books.

JavaScript: The Good Parts by

JavaScript Patterns by Stoyan Stefanov