7 Tips Using SQL Server Management Studio
If you are a database engineer, DBA, or developer working on data-centric applications, you probably use SQL Server Management Studio (SSMS) on a daily basis. While SSMS is very easy and intuitive to use, I have found the following 7 tips very useful that can really boost your productivity.
1. Additional Connection Parameters in the Connect window
In making a new connection, you can specify additional parameters to add or overwrite parameters under the main “Login” tab. A typical use case for me is when I need to connect to a SQL server in a different domain, such as production or cloud domain, that requires Windows-only authentication that is different from your current security login context, but the “User name” is grayed out and can’t be changed when you select “Windows Authentication” in the “Login” tab.
SQL Server Connection Main Login
In this example, I need to use domain login “york\install” other than my current login “Hawk\Raymond”, so in the “Additional Connection Parameters” tab, you just need to specify User ID, Password, and Integrated Security. If you, like myself, can’t always remember the exact format for connection strings, you can easily look up from this site www.connectionstrings.com
Additional Connection Parameters
2. Script multiple objects with Object Explorer Detail window
Script function is very powerful in SSMS, but if you need to script out multiple objects, such as tables, logins, or even SQL Server jobs, the Object Explorer (left side of the screenshot below)Â doesn’t always allow you to select multiple using the normal Windows Control button. In that case, you can hit F7 button or use SSMS main menu “View” to bring up Object Explorer Details window (right side), where you can use your familiar Control or Shift button to make multiple selection, and then right click the selected area to bring up the scripting function. This comes particularly handy during server upgrade or migration when you need to script out all server-level objects.
script function in Object Explorer Details
3. Generate data insert script from tables.
Quite frequently you may have to script out all data in one or more tables in the form of INSERT statement to run in a different environment, such as for the purpose of deployment. While you can still painfully hand-type a SELECT statement to generate data in INSERT format (SELECT ‘INSERT INTO {table_name} Values(‘ & ‘blah blah’, you know what I am talking about), or install a 3rd-party utility such as sp_generate_inserts, SSMS has this function built in already, though not very obvious. To do that, in Object Browser window, right click the database node, Tasks-> Generate Scripts…, to bring up the Generate and Publish Script Wizard.
 
- Generate Scripts Menu
After you select the desired table(s), click “Next” to take you to “Set Scripting Options” window:
Generate and Publish Scripts – Choose Objects
Here click the “Advanced” button to bring up “Advanced Scripting Options”. In “Types of data to script”, change the default option of “Schema only” to “Data only” or “Schema and data”, then complete the wizard. Voila, now you have the INSERT statements generated nicely in your specified target file.
Generate and Publish Scripts – Advanced Option
5. Template Explorer
SSMS comes with a set of commonly used script templates that you can drag and drop from Template Browser (on the right panel of the screenshot) into a query window. For example, after locating the template of “Create Unique Nonclustered Index” from “Index” folder, and drag-and-dropping into query window (in the middle panel), you just need to specify values for the parameters, from SSMS menu Query->”Specify Values for Template Parameters…”, then you have a nicely-formatted script ready.
Note that you can further customize the template by adding your own folder and templates under each folder. I have used that to organize some very commonly used scripts for development or production operation. Just keep in mind that those template files are stored in your personal work space path such as C:\Users\Raymond\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\Templates\Sql, so you know where to retrieve those files in case you need to switch computers.
Template browser in SSMS
6. Use Code Snippets
This is similar to Template Brower just mentioned above, without drag-and-drop and replace. Anywhere in a query window you can right-click to bring up the context menu, and select “Insert Snippets…”, then you can follow the folder structure defined in Snippet Manager to insert the code snippet.
Code snippets in SSMS
To bring up the Code Snippets Manager, just go to SSMS menu Tools->Code Snippets Manager, where you can further edit the snippet collection by adding or removing snippets. You can also tell by the location that code snippets are different from query templates.
Code Snippets Manager
7. Shortkeys
You can quickly execute pre-defined scripts in the current query window without even type them out. You can define those scripts through SSMS menu “Options…”, then under “Environment”->”Keyboard”->”Query Shortcuts”, you can define scripts for a total of 12 shortkeys (Ctrl+0 to Ctrl+9, Ctrl+F1, and Alt+F1). I have found those shortkeys particularly handy in production monitoring, where you can easily summon blocking-detection script or Adam Machanic’s famous sp_WhoIsActive to quickly narrow down the problem, without typing, inserting, or dropping scripts.
Shortkeys in SSMS
February 22, 2013
· 
admin · 
No Comments
Tags: code snippet, Connection String, monitoring, scripting, sp_whoisactive, SQL Server, SQL Server Management Studio, SSMS, template · Posted in: Database, SQL Server, Uncategorized
First Law of Microsoft Technologies
Recently I read the book of “Architecting Microsoft .NET Solutions for the Enterprise” by Dino Esposito and Andrea Saltarello (2008 Microsoft Press), where the authors summarized the below “First Law of Microsoft
Technologies” that I couldn’t agree more with:
First Law of Microsoft Technologies
Microsoft tends to supply a low-level framework and some higher-level tools such as controls, components, and designers. These tools are never designed to compete with analogous tools from third-party vendors. In addition, these tools target the average scenario and have the precise goal of doing a great job in most common situations.Â
We’ve experimented with the “First Law of Microsoft Technologies” several times and in a variety of contexts—from ASP.NET controls and components to business logic models, from workflows to AJAX libraries, and including unit testing, code editing, code refactoring and, of course, Windows-based user interfaces. Keep this consideration in mind when you evaluate higher-level Microsoft tools and facilities. They’re excellent most of the time; they’re likely unfit for some situations. But you have to be a good judge of your needs to decide which option is exactly right for you!Â
Some examples that I can think of to validate this First Law:
- Calendar control in ASP.NET that forces an unnecessary postback. You can get around it, though, by using a later technology of ASP.NET AJAX (by placing into UpdatePanel), but read on
- ASP.NET AJAX to adopt AJAX but was abandoned later for 3rd-party libraries like jQuery
- Some ADO.NET wizards that can launch a database driven website with very small amount of coding
As the author pointed out, there is nothing wrong with some of those tools, they do a specific type of jobs very well and behave poorly beyond that. As an architect, you will need to be very careful when trying to use those tools in enterprise-level applications, you have to be aware what those tools cannot do.
To be fair, Microsoft has never been excellent at creating very original technologies, but they are good at adapting and improving. Understandably, you don’t always get everything done right at the first version. Microsoft is aware of that, and its product team has been constantly reviewing the portfolio and making adjustment – it is quite often to see them quietly removing some technologies off the shelf. LINQ-to-SQL is a good example. As an architect or just a professional working with Microsoft technologies, you will need to look at where Microsoft is coming from when promoting a new technology, and do you own homework before adopting it.
In the past few years, a lot of new technologies have started from the open source community, and it seems Microsoft is more of trying very hard to play the catch-ups. Big data/Hadoop is a great example. That sometime leaves those of us on Microsoft technology stack feel like we are at Microsoft’s mercy to bring it over and get it implemented right, and I don’t know if the gap keeps getting wider.
Let me end this article with a concrete example of to testify this First Law: I have a simple .NET solution in Visual Studio 2012, the latest and greatest IDE, with a few projects in it:
- ProductEntityModel – the code-first, Entity Framework 5 assembly that I reverse-engineered against AdventureWorks sample database using Microsoft’s Entity Framework Power Tool. By the way, that tool itself is buggy, it doesn’t work with the SQL Server 2008 version of AdventureWorks database because it can’t recognize data type “Hirarchy”, and a few other annoyances, so I had to jump through several hoops to get the source files generated and compiled.
- ProductService – a WCF project that I plan to deploy to my local IIS as a web service.
VS 2012 comes with Web Publishing Wizard so I decided to give it a try:
Web Publish Wizard
So I created a publish profile “Deploy to Local IIS”, then it asks me to information about the web service. I stumbled a little bit before I can get the connection validated correctly. Notice that you have to manually type in text “Default Web Site” in “Site/application”, and there is very limited information on this screen, so watch out, fat fingers 
Web Publish Wizard Step 2
The next screen asks for database connections, and also a disabled checkbox of “Execute Code First Migration”, with a warning below that you will need to read some articles to configure this. Not very intuitive, but I can understand that.
Web Publish Wizard step 3
The next step is preview your settings and files before publish. After that when I click “Publish”, I got this error in Visual Studio, and nothing got published.
Web Publish Wizard Step 4
Alright, I get what the wizard was complaining, I have to go to IIS Manager to assign a .NET 4.0 AppPool to it. But, the point here is that, this wizard is very premuture for any serious us. You typically go through something like this:
- first, “Oh great, I can use this tool to simplify my work”
- then, “Mmm, what does this step mean…?”
- finally, “Wait, you mean now I have do this and that in addition?…”
January 13, 2013
· admin · No Comments
Tags: .net, architecture, Entity Framework, hadoop, Microsoft, open source, technology, Visual Studio, WCF · Posted in: .NET, Architecture
Programming Lab
yutechnet labs
Recently I started exposing my lab projects, mostly programming, and some non-programming projects later on, through my website. You can access it from “Labs” in the main menu of this website.
I do that for the following two main purposes:
1. To be more hands-on with the latest computing technologies. The technology world is moving fast, and we all have to constantly keep ourselves up to date. In understanding a new technology, nothing beats that, after reading some books or whitepapers or articles, you immediately try it through some realistic projects to get a true grasp at it and get close to “true metal” – the code. I don’t always get the luxury of trying it in the day-to-day software deliveries, so I decided to keep some lab projects going for that purpose. Also, being at managerial position for a while, I don’t write a lot of code anymore, therefore I need those sideline projects to keep my coding skill from rusty. I have quite a long list that I need to do, such as Microsoft .NET Framework 4.5 and up, HTML 5, JavaScript and CSS 3, mobile, big data/Hadoop, but that obviously takes time so I will roll them out at my own pace. Anyway, that’s fun, right?
2. Keep it real. I could just try some sample code snippets or scripts here and there on my development boxes, but I want to keep them in a central production place. To take one step further, I will apply some formal software development principles when working on those projects:
- Use git/github for source control and future collaborating
- Use Fogbugz for project and release management, and bug tracking
- Host in the cloud. I have a few EC2 instances with Amazon AWS, that serves that lab purpose very well. Btw, in terms of cloud computing, I rate Amazon as the clear front runner, ahead of Microsoft (Azure) and Google (App/Computing Engine)
So, let’s go, and stay tuned.
January 5, 2013
· admin · No Comments
Tags: Amazon, cloud, Google, programming · Posted in: .NET, Architecture, Cloud Computing, Google, Hadoop, mobile, Uncategorized
Importance of Backup Plans
Plan B backup plan
We live in an imperfect world, so things could go wrong sometimes. This is particularly true for the world of computing, where software or hardware failure is almost a routine. So for IT professionals, being network/system administrators, software/system architects, application developers, etc., it is important to recognize that fact, and ask us the question of whether we have a backup plan if any part of the system fails.
You can always start by identifying those places with sinple point of failure (SPOF), where one single failure could bring your business down. The remedies could be easy or difficult, depending on your system architecture. If you have an SPOF on a so-called stateless computing node, such as plain web server or network switch, you can implement a spare node with either hotswap with almost no impact to users, Â or cold swap where the system is down for a very short period of time and can be accepted by your business practise. If you have a stateful node, such as a database server, then you will have to use certain mechanism so that you can have the state data stored and synchronized on a secondary node to minimize the down time in the event of SPOF.
That may sound convoluted for a large system, but with cloud computing, the help is on the way. With today’s commercial cloud services, such as Amazon’s AWS or Microsoft’s Azure, you can leverage the built-in fault tolerency functionalities (those APIs) so that the hotswap of failure is instant and transparent to you. In other words, those cloud services will do the heavy-lifting for you to make sure your application or system is proof of SPOF, therefore makes your application so-called cloud-native.
Now, back to the traditional IT shop, the backup plan is equally important to IT operations such as server upgrades or applying system patches, but is less emphasized for some reason. Before carrying out such a big operation, you should always ask yourself about your backup plan if the operation doesn’t go well. Also, that backup plan needs to be well thought through in advance. The key point here is that with a backup plan on hand, you will not be panic and rush into something you haven’t fully thought through with your head cool, if the operation fails. Often times, this may not be a simple yes/no situation to back out so you will need to adapt your backup plan along the way. Your system admin could tell you that 90% of your system is working after the operation and he is working on the remaining 10%. As a leader, you will need to quickly assess the situation, and ask your admin and yourself what’s the backup plan for fixing that 10% if the fix doesn’t work or even create a bigger problem. This has saved me quite a few times in system operations, where things went “unscripted” and I didn’t have time to plan all possible outcomes ahead of time.
As of writing, this 2012 Christmas holiday season witnessed 2 major network/service outages, Amazon AWS outage took out Netflix, and Github.com outage due to network switch upgrade. While the exact cause of AWS outage is still fully disclosed yet, team at github.com detailed the cause of the incident in that post.  Notes to take from that post:
- They appeared to have had a backup plan in place before the firmware upgrade: if the in-service upgrade doesn’t go well, then roll back
- As I mentioned above, the operation team attempted to work around the problem within the maintenance window so it is not always a clear go or no-go situation
- When operations started something unplanned or “unscripted”, you need to be very careful and prepare a secondary backup plan. In Github’s case, things went wrong when they tried to work with the network vendor to gather evidence before the rollback. Again a reminder to us that things can go wrong anytime, or Murphy’s Law.
- Github’s secondary backup plan appeared to have worked – took a lengthy, unplanned maintenance window to carefully recover all file server clusters with minimum or zero data loss.
December 26, 2012
· admin · No Comments
Tags: architecure, cloud · Posted in: Architecture, Cloud Computing
Urgent Unimportant Tasks
The post of “Putting the important-but-not-urgent tasks above the urgent-but-not-important tasks” on “The Simple Dollar” blog strike a cord with me on how to deal with our seemly busier-than-ever lives.
In the post, the author pointed out that we want to important things like make a will or get an advanced degree, but we fill our time with things like stopping at the grocery store for the third time this week. The latter one, which I rename it Urgent Unimportant Task (UUT), easily chip away our precious time, and result in our no progress on really important tasks.
Urgent Unimportant Tasks
In general, it is easy to let our lives to be run by UUTs, because:
- they are obviously urgent, at least from other people’s prospective (A friend of yours are waiting from a photo you took at the party last night)
- they are easy and routine
- sometimes you do it just for the sake of taking it off your “nagging” list
The author listed 4 little things to keep UUTs aside:
- not afraid of turn off phone and email
- block off time for long-term projects
- sometimes utterly drop the UUTs if they’re getting in the way
- Be acutely aware of what’s truly important to me and what’s not
For me, I have found that emails are the biggest source of UUTs at work place. Not only those never-ending messages come in every minute or so to take away your attention to read, but also those urgent unimportant requests from those email that you have to respond on. Now I have established a routine to only batch-process my emails several times a day (beginning of work day, lunch time, and before leaving for home, and some hourly bases in between. I know I yet to educate my co-works that I don’t check emails real time…), and then turn off my email program so I can focus on those tasks that are truly important to me. To make sure I don’t miss truly urgent issues, I do look at my cellphone for a quick scan of subject line and senders every hour or so to determine if there is anything I really need to open my email to respond.
So far this has worked great for me. At one point, I was able to delete 20+ emails from my inbox on cellphone, for a group discussion that at the end people eventually reached a trivial solution over a couple of hours discussion with emails back and forth.
Let me close this post with one of my favorite quotes by Robert Heinlein:
In the absence of clearly-defined goals, we become strangely loyal to performing daily trivia until ultimately we become enslaved by it. – Robert Heinlein, US science fiction author (1907 – 1988)
December 7, 2012
· admin · No Comments
Posted in: Productivity