Purpose

There are some excellent instructions for working with SQL Azure on the web. This is not intended to replace those, but rather to augment and consolidate instructions for connecting to and working with SQL Azure for those who are not proficient with SQL Server.

Resources on the Web

How to: Migrate a Database by Using the Generate Scripts Wizard (SQL Azure Database)

Connecting to SQL Azure

Prerequisites

In order to do this you will need to meet the following requirements

• Connect from an authorized IP address to comply with IPSec rules on the Azure SQL database
• Utilize SQL Server Management Studio r2 (You can get this by installing SQL Express if you don't want to load full SQL Server)

Steps to Connecting

1. Open SQL Server Management Studio.
2. When asked to connect, click [Cancel]. This will open SSMS with no data or connections.



3. In the upper left side on the toolbar press [New Query].



4. A new connect window will appear. Use the provided login information. Remember to set “Authentication” to “SQL Server Authentication”.



5. Press [Options >>] to provide extra connection information.
6. On the “Connection Properties” tab type in the name of the database you wish to connect to. Please note that even though the “Connect to database” field is a dropdown you will need to manually type in this name as management studio will not be able to gather a list of database names for you, and you will not be able to change this name once connected. If you are running more than one database (For example [dev] and [prod]), you will want to make sure you are connecting to the proper database during this step.



7. Press the [Connect] button.
8. You will now be able to run sql commands directly against the SQL Azure db.

NOTE: In this version of SQL Server Management Studio this is the only way to connect to the database. You cannot connect using the object explorer as it requires access to both your database and the “Master” database. Due to the way SQL Azure is implemented this is not possible and you will receive an error. Future versions of SSMS and/or updates to SQL Azure may correct this.

TIP

One of the downsides here is the lack of the object explorer. If you have a local copy of the database, you can do this little trick to help overcome this deficit.

After you have connected to SQL Azure as described above, you can connect the object explorer to your local database. As long as you spawn new query windows from your existing Azure connection, the new query windows will inherit that connection. This will allow you to view the objects in the object browser and even drag and drop table and column names over to your query window.

While this is still far from perfect, it does ease the pain.

A few weeks ago I talked about reading blogs to keep up with trends. This time I thought I’d share some of the blogs I’m currently tracking. I use google reader to manage my blogs and separate every thing into folders. Here is a list of my folders with a brief description, and also a few notes about the blogs I’m tracking in them as well as at what activity level I track them at.

Development -  Blogs primarily about, what else, Software development. I don’t actually track a lot here, but what I do track I tend to read through. There are a few others I track, but they post so infrequently I’m not even going to list them here.

• Coding Horor: Jeff Atwood’s blog
• Scott Hanselman’s Computer Zen
• ScottGu’s Blog: Scott Guthrie’s blog (Or as Hanselman refers to him “His Gu-ness”
• iPhone Developer News: This is really just so I don’t have to get Spam for Apple.
• Martin Fowler’s “bliki”: Mostly about Agile and customer interaction. More the business of software development as opposed to the implementation.

Marketing - These are blogs I follow to try and keep up with what is being discussed in the brand and user interaction world. While I’m not a marketer I like to know what is going on in this industry. Remember that the same poeple that marketers are trying to reach work at the companies that internal systems are also interacting with. If you ignoring what people are doing and interacting with outside of work, how can you expect to be able to build systems that help and engage them in the work place. I mostly just track through these quickly.

• Adverblog: I skim this one
• Advertising Age: I skim this one too
• BuzzMachine: It’s a skimmer
• Seth’s Blog: Seth Godin’s blog. This is probably the only one in this folder that I actually make an effort to read. If you’re familiar with Mr. Godin you’ll see some influences from him in my recent posts. While I don’t always agree with him I do find him interesting and thought provoking.

Geek Tech - These are blogs about cool technology. Everyone should be familiar with these, but I don’t think anyone has time to read all the posts. Besides most of them tend to report on the same thing. I usually skim for a while an only stop on items I think are interesting.

• Endgadget
• Gizmodo
• Mashable!
• Slashdot

Well I think that’s it. There are some others I track that are way off topic here such as Comic Book Resources, but this mostly covers the technology blogs. These add up to a lot of posts in a day and a week. It’s important to keep in mind that I many of these categories I skim in a few minutes and then mark everything as read. You don’t need to kill yourself to try and stay relevant.

A few weeks ago I had lunch with a former colleague that I hadn’t talked to in several years. During the course of our conversation I learned that they are still maintaining vb.6 code and classic ASP in the current generation of the product. Now this isn’t meant to condemn anyone. It happens. The reason I bring it up is that the question of how to keep up to date was raised. He said he’s still learning about some of the features in .NET 2.0 and here we are getting ready for 4.0. So how do you keep up with what’s going on in the here and now when day in, day out you are working and thinking about ten year old technologies?

Here are a few suggestions I made to him and I pass them along in the hopes that they might spark some ideas in someone else.

Work on personal side projects. It doesn’t matter if you ever complete the project. It matters that it be something your interested in. Build it with a technology you’ve never used before. If you can complete it and get it out in the world then all the better, but don’t get hung up on having to deliver something, you may never get started. The world is full of deadlines and commitments, have fun and learn something.

Re-think what your currently doing and about how you might re-architect it. You may be working with older technology out of necessity, but there is nothing stopping you from taking notes about how you could update things. This can also be the basis of a modernization strategy for your company. Not a bad way to help your career either.

Read. Not just technical books either. Read about trends in your industry. If you’re in a situation where you need to seriously up-level your skills, you may have several choices as to where you go. For example if your only getting started with .Net for VB or classic ASP why not learn C#. Interested in mobile devices? How about a little objective-C? The point is there may be trends in your industry or specialty that help determine where you go.

Read technical blogs. This can be a good way to keep up with what’s coming down the pipe, and give you ideas for personal projects. It can also be good if you only have limited time. Be careful here. It can be addictive, and also a bit overwhelming. If you find you have too may entries to keep track of, think about cutting back on a few. I have a few that I skim and then mark them all as read, and I have others that I try to read every entry. Be realistic as to what you can read, and be consistent with staying on top of it. That might mean checking in once a week or once a month. Find out what’s right for you and stay with it.

While this isn’t by any means an exhaustive list, I hope it gives you some ideas, and that you come up with even better ones you can share.

As sites and applications begin to scale on the data side of the equation, you can quickly find your database to be a bottle neck to performance. Index covering is one technique that can help speed up your queries dramatically.

Technique: Index Covering
Applies To: SQL Server, MySQL, Sybase (Possibly also Oracle, DB2)
When to use it: Thin queries (limited columns) that do not operate on the clustered index.

So what is index covering? In SQL Server when you perform a query on a table that returns only columns contained in a non-clustered index, then SQL will return the values form that index, thus bypassing reads to the actual data pages. This is called Index Covering, and can greatly reduce the IO needed to perform the query.

Let’s say you want to make a call to your database that returns all of your customer names when passed in a particular state. The clustered index on the Customer table is CustomerID.

Select FirstName, LastName From Customer Where State = ‘Oregon’;

SQL Will scan through all of the data pages looking for all of records where ‘Oregon’ is the state. If you have large data rows this can be fairly costly in terms of IO. The field State is a horrible candidate for an index as there are only a finite number of distinct values that can be placed in the field. So what’s a princess to do when her turing machine slows to a veritable crawl? Index covering.

Create Index cust_state On Customer(State, LastName, FirstName);

Since all of the fields needed for the query are now contained in the index SQL will go to the index, find all rows where ‘Oregon’ is the state, and then, seeing that all of the other columns it needs are in the index, it will return the results. In this scenario SQL will never touch the actual data pages. Because the size of the index pages are significantly smaller than those of the data pages, SQL uses less IO. Now place your clustered index in a different filegroup and your performance gain can be even greater. But that’s for a different article. Also note that our covered index would also work with the following query.

Select State, FirstName, LastName From Customer Group By State;

“I’m confused. You said earlier that State was a bad candidate for an index?!” Under normal circumstances it is. Good index practice would tell us to order our index columns as so (LastName, FirstName, State), with the most unique (selective) values first. Remember that when working with index covering we are creating an index for a special purpose. When dealing with large scale databases you need to start thinking differently about your data.

Large scale data systems like the ones I’ve been thinking about lately can seem more like data warehouses than OLTP systems. In some cases they are both at the same time. I think the biggest mistake a developer or architect can make is to become rigid in their thinking about how data needs to be defined and structured.

PS: For those of you who got the veiled reference to Diamond Age. Gr@75!

I’ve been filling my brain with thoughts of online community building and involving fans more in how you define your brand. I’ve been reading what I can in the space, whether it’s blogs or books trying to form a clear picture of where we are headed. Not just as an industry, but also as a connected society. For those of you paying attention, that’s right I said “fans” and not customers. If you aren’t thinking about your customers as fans, then you probably also think that this whole internet thing is just a fad.

So what does this have to do with Scalability? Lots! For those of you who don’t already know, it’s my job to translate what our marketing and design teams come up with into deliverable solutions for clients. As a digital agency this mostly consist of ways to communicate with brand fans, and a campaign that takes off can need to scale-up quickly to handle large amounts of users, then scale back down over time. At the same time you don’t want to spend an exorbitant amount of time (and client money) building a monolithic site that can infinitely scale if you are only expecting a few thousand users going to it. Don’t get me wrong. A few thousand well targeted users who are fanatic (there’s that word again) about your brand, can be a higher measure of success than 20 times that number who visit your site once, and never give you another thought. But what if your site takes off virally? Would you have to throw out your existing code and start over, or would you have all the pieces in place from the start that will help you scale?

While not every site needs to scale like facebook or twitter, there are several things that can be done with no impact to existing budgets that will help with scalability down the road and performance immediately. This post is the start of a thread on the subject of scalability and performance. While some posts will be short snippets of practical advice that any site can take advantage of, others will be targeted to specific problems involved in scaling a system.

So here is the first tidbit of information that can be used in any web site. It involves client side browser performance.

Most browser cache is case sensitive as it does a straight string compare of a named resource. Therefore if include a reference to an image file on a web page like so

<img src="Image.jpg" />

it will download the file and place it in the browser cache. If you later reference the same image on the same or another page but change the case in the source url as such

<img src="image.JPG" />

the browser will download the file from the server again as it perceives it as a different file/url, and create a second entry in the browser cache.

This isn't a problem if your web server acts in the same manor as Apache where all urls are case sensitive to begin with. In this case the server will not recognize a resource unless the url is presented in the same case as the file on the server. Because IIS isn’t case sensitive a browser could potentially download a file hundreds of times if the case in the source url is slightly different every time.