Visual Studio 2010 Lab Management
The integration between Visual Studio 2010, System Center Virtual Machine Manager (SCVMM) 2008 and Hyper-V 1.0/2.0 for virtual lab automation scenarios is now a reality called Visual Studio 2010 Lab Management.
The Visual Studio Team System (VSTS) Lab Management Team has recently started a blog that introduces to the virtual lab automation and the capabilities of Visual Studio 2010 that is well worth a subscription.
Here’s a small excerpt from their first article:
The lab management service in TFS uses System Center Virtual Machine Manager (SCVMM) for management of lab infrastructure and provisioning of virtual machines across multiple virtualization platforms. You get a copy of SCVMM with Lab Management.
Microsoft Test and Lab Manager is a Windows Presentation Foundation based rich client. The Lab Center in Test and Lab Manager allows you to
· Create and manage virtual or physical environments
· Take environment snapshots or revert to existing snapshots for virtual environments
· Interact with the virtual machines in the environments through environment viewer
· Define test settings for the environments
You can define test plans, test suites and test cases in the Testing Center and execute them on the lab environments.
At the heart of this product there is the concept of the workflow:
Lab Management workflow activities are bundled with Team Foundation Build Service. You can drag and drop these activities in Windows workflow designer to create custom workflows that allow you to
· quickly provision a virtual environment
· revert to ‘clean’ environment in tens of seconds by using environment snapshot instead of running multiple ‘cleanup’ scripts or reinstalling OS and application prerequisites
· using distributed workflow, run setup and configuration scripts on virtual machines
· Take post deployment environment snapshots, etc
The release is out by Microsoft VS Team in the Visual Studio 2010 Products.
Source: http://virtualization.info/en/news/2009/06/microsoft-launches-visual-studio-lab.html
Microsoft’s SQL Server 2008 R2 is finalized
Microsoft announced on Wednesday (21) that the MSDN subscribers will have SQL Server 2008 R2’s finalized version on 3 May and the general download starts on 13 May. It comes with a set of BI (business intelligence) capabilities. Previously it is known as Kilimanjaro and it will be released in the 1st half of this year.
Ted Kummert, vice-president said that the customers can create more data and they continue to receive data due to the impact of the information in their social lives and business. Our main aim is to help the customers extract business insight and value from that information. The information may be stored in the cloud, datacenter or in their PC.
Microsoft’s report says, community technology review has been downloaded for more than 300,000 times and it was released to the developers in the month of August.
The RTM (release-to-manufacturing) version of this database product includes many new features that are designed to reduce the costs of running this database product in enterprise environments. For instance, the database administrators can manage multiple SQL server databases with the help of the new centralized administration.
There is much support in this software for running it in Microsoft’s Hyper-V 2008 R2. The company says that there is full support for the Live Migration.
The significant change in this product is the addition of the self-service BI engine. The end-users can create and run reports without calling the database support staff.
Tom Casey, Microsoft BI’s general manager said that the new storage mode that is added in this product supports the in-memory analytics. Large amount of data can be fitted into the RAM by applying the compaction and smart compression algorithms and also by working the in-memory.
Casey said that the data compression allows the queries to run at the speed of its bus.
To benefit from BI engine the users should have Office 2010 in every desktop for creating BI reports in Excel 2010. The users can get the solution quickly by the combination of flexibility and responsiveness. It lets you to iterate quickly and work without constraint.
Source: http://www.toptechreviews.net/microsoft/microsoft%E2%80%99s-sql-server-r2-is-finalized/
Top 10 Features of SQL 2008 R2
Introduction
Microsoft SQL Server 2008 R2 is the latest release of SQL Server. This article will introduce the top 10 features and benefits of SQL Server 2008 R2. The “R2” tag indicates this is an intermediate release of SQL Server and not a major revision. However, there are a number of interesting new features for both DBAs and developers alike. At the time of this article, R2 is available as a CTP (Community Technology Preview). In addition to new features, there are two new editions as well, SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel Data Warehouse.
Report Builder 3.0
Report Builder is a tool set for developing rich reports that can be delivered over the web. Some of the features of Report Builder include the ability to create reports containing graphs, charts, tables, and printing controls. In addition, Report Builder also supports drill downs and sorting. If you are familiar with the third party tool Crystal Reports, then you have good idea of what to expect from Report Builder.
New features in SQL 2008 R2 / Report Builder 3.0 include: Map Layers, which can hold spatial and analytical data and will integrate with Microsoft Virtual Earth. Indicators, these are gauges used to show the state of one value. Report Parts, this object can be reused or shared between multiple reports. Aggregate Calculating, this allows you to calculate the total value of other aggregate calculated totals.
SQL Server 2008 R2 Datacenter
The new Datacenter edition of SQL Server 2008 R2 is targeted towards Enterprise Edition users who require a greater performance platform. The new edition will support 256 logical processors, high numbers of instances, and as much memory as the operating system will support.
SQL Server 2008 R2 Parallel Data Warehouse
Another new SQL Server edition, Parallel Data Warehouse, formally codenamed “Madison”, specializes in handling extremely large amounts of data. This new version uses massively parallel processing to spread large tables over multiple SQL nodes. The multiple nodes are handled by a propriety Microsoft technology called Ultra Shared Nothing. This new technology is described as a Control Node spreading queries to Computer Nodes, evenly distributed, then collecting the results.
StreamInsight
New in SQL Server 2008 R2 is component called StreamInsight. This interesting component allows streaming data to be analyzed on the fly. Meaning the data is processed directly from the source stream prior to being saved in a SQL Server table. This could be extremely handy if you’re running a real time system and need to analyze data but can’t afford the latency of a committed write to a table first. Examples usually cited for this application include stock trading streams, click stream web analytics, and industrial process controls. Multiple input streams can be simultaneously monitored.
Master Data Services
Master Data Services (MDS) is both a concept and a product. The concept of a Master Data Service is that there is a central data gate keeper of core business data. Data items such as customer billing addresses, employee/customer names, and product names should be centrally managed so that all consuming applications have the same information. The Microsoft example given is a company that has a customer address record in the customer table but a different address in the mailing table. A Master Data Service application would ensure that all tables would have only one correct address. While an MDS can be a homegrown application, SQL Server 2008 R2 includes an application and an interface to manage the central data.
PowerPivot for SharePoint
PowerPivot is an end-user tool that works in conjunction with SharePoint, SQL Server 2008 R2, and Excel 2010 to process large amounts of data in seconds. PowerPivot works like an Excel Pivot Table, and includes analytical capabilities.
Data-Tier Application
A Data-Tier Application (abbreviated as DAC –no idea what the C stands for, and not to be confused with the Windows Data Access Components also abbreviated as DAC ) is an object that stores all the needed database information for a project, such as login, tables, and procedures into one package that can be consumed by Visual Studio. By creating a Data-Tier Application, a SQL Server package version could be saved with each Visual Studio build of your application. This would allow application code builds to be married to a database build in an easily managed way.
Unicode Compression
SQL Server 2008 R2 uses a new algorithm known as Simple Compression Scheme for Unicode storage. This reduces the amount of disk spaced used by Unicode characters. This new format happens automatically and is managed by the SQL Server engine so no programming changes are required of the DBA.
SQL Server Utility
The new SQL Server Utility is a repository object for centrally controlling multiple SQL Server instances. Performance data and configuration policies can be stored in a single Utility. The Utility also includes an Explorer tool where multi-server dashboards can be created.
Multi Server Dashboards
While the SQL Server Management Studio could always connection to multiple servers, each was managed independently with no central view of all of them. Now with SQL Server 2008 R2, Dashboards showing combined server data can be created.
Conclusion
SQL Server 2008 R2 is the latest release of Microsoft SQL Server. The “R2” tag indicates this is an intermediate release of SQL Server and not a major revision. There are a number of compelling features in this version for both developers and DBAs alike. Here are the Top 10 new features in SQL Server 2008 R2:
1. Report Builder 3.0
2. SQL Server 2008 R2 Datacenter
3. SQL Server 2008 R2 Parallel Data Warehouse
4. StreamInsight
5. Master Data Services
6. PowerPivot for SharePoint
7. Data-Tier Application
8. Unicode Compression
9. SQL Server Utility
10. Multi Server Dashboards
Multiple Server Query Execution in SQL Server 2008
SQL Server 2008 SSMS introduces a new feature, Multiple Server Query Execution, in Query Editor. This feature intends to increase the productivity of running same query against multiple servers at once. Some of usages include:
- Configure group of servers or server farm
- Generate report or document from multiple servers
- Analyze result from multiple servers
- or Run any sql query against multiple servers
Pre-requisite
To deploy or test Multiple Server Query Execution, you need to setup SSMS in SQL Server 2008.
Setup Registered Server Group
Multiple Server Query Execution requires at least one registered server group to run a script against multiple server. To setup a registered server group and servers, refer to
Create a new query for Multiple Server Query Execution
On the existing or newly created registered server group, click right mouse button and select New Query menu item. SSMS opens a new QE session with multiple connections against all registered servers in the group. Note that QE status bar indicates multiple server connection with a background color and text.
From this point on, script editing and execution are identical to single connection mode. Some behavioral differences include that:
- Database drop-down-list on QE toolbar only displays common databases among connected server instances.
- Status bar visually indicates multiple server connections with color and text; it can be customized through Option.
- F4 Property tools-window displays attributes for multiple connection and execution.
To open existing sql script file, click on a registered server group and then click Open File toolbar button or File | Open | File… (Ctrl + O) menu item.
Execute a script and retrieve results from multiple servers
Let’s run the following sample query on a database; this query retrieves size and used space in MB per database filegroup, and calculates percentage of the used space.
-- Database space as a whole (per filegroup)
SELECT FGName = CASE df.type_desc WHEN 'LOG' THEN 'Transaction log'
ELSE (SELECT MIN(Name) FROM sys.data_spaces ds
WHERE ds.data_space_id = df.data_space_id) END
,SizeMB = SUM(Size * 8 /1024) -- Size is 8-K pages, converting to MB
,UsedMB = SUM(FILEPROPERTY (Name, 'SpaceUsed')/128)
,UsedPercent = CONVERT(numeric(5,2), 100.00*SUM(FILEPROPERTY(Name, 'SpaceUsed'))/SUM(Size))
FROM sys.database_files df
GROUP BY df.data_space_id,df.type_desc
Now, execute the query in Result to Grid mode. Note that the result grid combines retrieved data sets from multiple servers by indicating each row with Server Name column. You can copy the grid to Excel or save it as *.csv or tab delimited format for further analysis and documentation.
User preference options
You can access options for Multiple Server Query Execution in two different places.
- Query | Query Options | Results | Multiserver; this applies only to the current editor session but does not reset the default options.
- Tools | Options | Query Results | SQL Server | Multiserver Results; this applies to New editor session and remains as a default.
Options in this page allow you to customize:
- Show / hide login name per row.
- Show / hide server name per row.
- Merge results in to a single grid or let each grid display the result per server.
You can also customize the default color of status bar in Tools | Options | Text Editor | Editor Tab and Status bar.
- Group connection to indicate multiple server connection.
- Single server connection for traditional single connection mode.
Office 2010: 10 cool news features
My first impression was that Office 2010 is going to be to Office 2007 what Windows 7 is to Vista. Although personally I liked both of them, Vista and Office 2007 inspired an inordinate number of complaints from consumers and IT folks alike. Maybe the changes were a little too drastic or maybe it’s just that the timing wasn’t right. Whatever the reasons, many people skipped the new OS and apps and stuck with Office 2003 on XP.
1: The Ribbon
Why would I list the Ribbon as the number one new feature in Office 2010, when the Ribbon was introduced in Office 2007 — and in fact, was the feature that caused the most controversy? Although some of us loved the new Ribbon interface, many others hated it, so much so that third-party developers soon devised programs to restore the old familiar menus.
Office 2010 not only keeps the Ribbon; it has now been added to all the Office programs, including Outlook and OneNote. But don’t panic: The Office 2010 implementation is Ribbon Done Right. The difference is that now you have control over your Ribbon and what items appear on its tabs, and you can even add tabs of your own and put your favorite commands on there. No more despair because a favorite command that was on an Outlook 2003 menu can’t be found anywhere on the Ribbon.
All you have to do is right-click the Ribbon and select Customize The Ribbon. This opens a dialog box from which you can make new Ribbon tabs and add or remove commands from the tabs, as shown in Figure A.
Figure A
The Ribbon in Office 2010 applications is completely customizable.
2: Office button options
Office 2007 users are familiar with the Office button, the big round button in the upper-left corner of Office applications, from which you can select a variety of tasks and options. Figure B shows the Word 2007 Office menu.
Figure B
The Office button in Office 2007 provided a number of options.
The Office button in Office 2010 has a new look, and it’s been added to Outlook and OneNote, which didn’t have it before. Figure C shows what you see when you click the Office button (now implemented as a Ribbon tab) in Word 2010.
Figure C
The new Office menu has a whole new look and layout.
You’ll find many of the same options as before, along with a number of new ones. For example, in the Sharing section, you’ll now see options to save the document to SharePoint or change the file type, as you can see in Figure D. The Back button at the top of the page returns you to the document itself.
Figure D
You’ll discover some new options on the menus.
3: Outlook improvements
Outlook is the Microsoft Office program I use most often. It’s the first application I fire up when I sit down at the computer in the morning and it’s the last application I close when I shut down for the night. And I’m checking my mail and calendar and looking up contacts every 15 minutes (or more often) throughout the day. This makes changes to the Outlook interface very important to me — I want it to work better, but I don’t want to have to relearn everything and I don’t want to lose functionality.
The Ignore button that’s been added to Outlook is just what those of us who belong to lots of email discussion lists have been waiting for. It allows you to get rid of conversation threads that you aren’t interested in. Not only will it delete all messages in your Inbox that belong to the thread, but it will automatically delete any messages pertaining to that thread that come in later. Highlighting a message and clicking the Ignore button displays the dialog box shown in Figure E.
Figure E
The new Ignore button in Outlook makes it easy to bypass entire conversation threads.
Outlook 2010 also makes it easier to manage conversations. When you view messages in Conversation View, you can right-click a conversation title and select from a number of actions that you can perform, as shown in Figure F. If you select Clean Up Conversation, redundant messages in the conversation will be deleted.
Figure F
You can perform more actions on a conversational thread, including clean up.
Another great new Outlook feature is Quick Steps. This is a section on the Ribbon’s Home tab where you can create single-click links to perform tasks that normally require multiple steps. For example, if I want to forward a message to my husband, instead of clicking forward and then typing his address into the To box, I just click the To Tom link and the forwarded message appears with his address already entered. Figure G shows the Home tab of the Ribbon with the Quick Steps section highlighted.
Figure G
The Quick Steps section of the Ribbon lets you perform multi-step tasks with one click.
4: Easier screenshots
If you’re a tech writer, you’ll appreciate a new feature in Word 2010 — the ability to capture screenshots from inside the application and paste them into the document, all in a couple of clicks. The Screenshot button has been added to the Insert tab of the Ribbon, as shown in Figure H.
Figure H
Inserting screenshots into Word is easier than ever.
When you click the Screenshot button, you’ll see the available screenshots, and you can click on the one you want to insert into the document. After you insert a screenshot, the picture tools will automatically appear to allow you to perform photo editing.
5: Photo/video/graphics in Word and PowerPoint
The photo-editing tools have gotten more sophisticated in Office 2010. Now you can apply artistic effects, similar to those available in third-party photo editing programs, such as PhotoShop, from within Word, Excel, and PowerPoint. You even get a thumbnail preview of what the effect will look like when applied to your picture, as shown in Figure I.
Figure I
You can now apply artistic effects to photos from within Office programs.
In PowerPoint, you can apply artistic effects, reflections, shadows, etc., to both photos and videos, as shown in Figure J.
Figure J
You can apply artistic effects to pictures and video in PowerPoint 2010.
The Office 2010 applications also include several new SmartArt designs, to make it more likely that you’ll find one that fits the needs of your document or slide, as shown in Figure K.
Figure K
Office 2010 includes a number of new SmartArt designs.
6: Drag-and-drop navigation pane
One of my favorite features in Word 2010 is the new drag-and-drop navigation pane. It’s a little like the Word 2007 document map — on steroids. Whereas the document map only gives you a view of your headers and document sections, graphics, etc., the navigation pane lets you rearrange your document easily by dragging and dropping within the pane. To turn on this feature, click the View tab on the Ribbon and in the Show section, check the box labeled Navigation Pane, as shown in Figure L.
Figure L
Go to the View tab to display the new drag-and-drop navigation pane.
Want to move that third first-level heading (and all the text under it) up above the second one, without having to copy and paste it? No problem: Just drag the heading where you want it in the navigation pane, which displays to the left of your document, as shown in Figure M.
Figure M
You can move whole sections of text by dragging and dropping the headings in the navigation pane.
7: Open in Protected View
When you open an existing document for the first time in Word 2010, if you try to start editing it, you may be surprised to find that nothing happens. If you look more closely, you’ll see that the Ribbon is hidden. What’s up with that? The document has opened in Protected View, as shown in Figure N.
Figure N
The first time you open a document in Word 2010, it opens in Protected View.
As you can see, a red box across the top of the document notifies you that you’re in Protected View and tells you that the file originated from an Internet location. This gives you the opportunity to determine whether it’s safe to open. If you click the Enable Editing button, the document downloads, the Ribbon appears, and you can make changes to the document as usual.
8: Excel sparklines and slicers
The most notable additions to Excel 2010 are two new features called sparklines and slicers. Sparklines are tiny charts that fit into a cell, as shown in Figure O.
Figure O
Sparklines are charts that fit into a cell on an Excel spreadsheet.
The sparklines shown in the figure use the line format, but you can also create column or win/loss sparklines. You can edit the design of the sparklines, too, as shown in Figure P.
Figure P
You can edit the design of your sparklines.
Slicers are objects you can use to filter the data in pivot tables, which you can move around or resize on the screen. When data in the pivot table changes, the slicer is automatically updated. Both sparklines and slicers are created via the Insert tab on the Ribbon, as shown in Figure Q.
Figure Q
You create sparklines and slicers via the Insert tab on the Ribbon.
To see a demo of the new Excel features, check out the video at http://www.microsoft.com/office/2010/
9: OneNote improvements
OneNote has been a bit of a forgotten stepchild in previous editions of Office, perhaps because it only came with the “lowest” and “highest” editions of Office 2007 — Home and Student edition and Ultimate edition. Most Office users have the Standard, Small Business, or Professional edition. Microsoft obviously wants to get more exposure for OneNote. According to early reports, Office 2010 features will follow the same pattern as Windows 7; that is, each successively more expensive edition will contain all the applications of those editions “below” it, and more. That means OneNote will be included in all editions of Office 2010.
The most obvious change to OneNote, as with Outlook, is that now it sports the Ribbon interface, as shown in Figure R.
Figure R
OneNote, like its Office-mates, now sports the Ribbon interface.
In OneNote, however, the Ribbon is minimized by default. Just click the small arrow near the Help icon (blue question mark) in the upper-right part of the window to maximize it.
The new OneNote includes a number of improvements to simultaneous multiple-user editing of notebooks. New content that was added (or changed) by another user is now highlighted, so you immediately see what’s new. There is also color coding to indicate the author of content that was written by someone else. Searching has been enhanced, as well.
Another interesting feature is linked note taking. If you put OneNote in linked mode, it will automatically link your notes to whatever you’re viewing (Web page, selection in Word, a particular slide in a PowerPoint presentation, etc.). Then, when you hover over the link in OneNote, you see a thumbnail of the material to which it’s linked and you can click it to open the original.
For editing, OneNote now supports basic styles. You can also add math equations, and there is a miniature translator that provides a tooltip in your language if you hover over a foreign word. Finally, on Tablets and other touchscreen PCs, OneNote supports touch gestures, such as finger scrolling and panning and pinch zoom.
10: Simultaneous editing
Here is another favorite of mine. I often leave a document that I’m working on open on one computer, and then need to open and work on it from a different computer. I get the familiar “file in use” dialog box that gives me the option to open a read-only copy, create a local copy to merge later, or receive notification when the original is available. Office 2010 does away with that annoyance.
Now I can pick up where I left off, or two people can edit a document simultaneously. A notification in the status bar tells you who else is currently editing the document, and where they’re making changes. Very cool!
Word can also cache shared documents so you can edit them when you’re offline, and any changes you make will automatically be synchronized with the original on the server when you come back online. Now you don’t have to remember to merge your document when you get back.
Summary
Office 2010 still has to go through a public beta (expected later this year) before we see it in its final version, but what we’re seeing in the technical preview looks promising. Whether you’re using Office 2007 or you’re still using Office 2003, Office 2010 will offer enough new and improved features and functionality to make it worth considering the upgrade.
What's new with the Windows 7 taskbar?
In Windows 7, the taskbar has been completely redesigned to help you more easily manage and access your most important files and programs.
Taskbar buttons
Taskbar buttons have a new look and do more than just show you which programs are running.
In the default view, each program appears as a single, unlabeled button—even when multiple items for a program are open—for a clean and uncluttered look. You can customize the taskbar appearance to change how buttons appear and how they group together when you have multiple items open. You can also choose to see individual buttons for each open file.
You can also rearrange and organize buttons on the taskbar, including pinned programs and running programs that aren’t pinned, so they appear in the order you prefer. To rearrange the order of buttons on the taskbar, drag a button from its current position to a different position on the taskbar. You can rearrange buttons as often as you like.
Previewing open windows with Aero Peek
When you open multiple windows on the desktop, sometimes it can be a challenge to view separate windows and switch between them.
You can use Aero Peek to take a quick look at other open windows without clicking away from the window you are currently working on. Point your mouse at a taskbar button, and thumbnail previews of any open windows associated with that button appear above the taskbar. If you want to open a window you are previewing, just click its thumbnail.
Pinning items
Pinning programs to the taskbar complements pinning programs to the Start menu, like in earlier versions of Windows. When you pin a favorite program to the taskbar, you can always see it there and easily access it with a single click. Windows 7 also includes Jump Lists, so that in addition to launching a program from the taskbar, you can now launch favorite and recent items from that program, just by clicking the same button.
Jump Lists are lists of recently or frequently opened items, such as files, folders, tasks, or websites, organized by the program that you use to open them. In addition to being able to open recent items using a Jump List, you can also pin favorite items to a Jump List so you can quickly get to the items that you use every day.
On the taskbar, Jump Lists appear for programs that you've pinned to the taskbar and programs that are currently running. You can view the Jump List for a program by right-clicking the taskbar button, or by dragging the button toward the desktop. You open items from the Jump List by clicking them.
Notification area
A new way of managing the notification area on the end of the taskbar means you get fewer notifications, and the ones you get are collected in a single place in Windows.
In the past, the notification area could sometimes become cluttered with icons. Now, you can choose which icons appear visible at all times. And you can keep the rest of the icons on hand in an overflow area, where they’re accessible with a single mouse click.
Action Center is a single area that collects important notification messages about security and maintenance settings. You can review these messages later if you don’t want to be interrupted. When you click the Action Center icon 
and then click Open Action Center, you’ll see information about the things you need to take action on, and find helpful links to troubleshooters and other tools that can help fix problems.
Viewing the desktop
The Show desktop button has been moved to the opposite end of the taskbar from the Start button, making it easier to click or point at the button without accidentally opening the Start menu.
Show desktop button on the taskbar
In addition to clicking the Show desktop button to get to the desktop, you can temporarily view or peek at the desktop by just pointing your mouse at the Show desktop button, without clicking it. When you point at the Show desktop button at the end of the taskbar, any open windows fade from view, revealing the desktop. To make the windows reappear, move the mouse away from the Show desktop button.
This can be useful for quickly viewing desktop gadgets, or when you don’t want to minimize all open windows and then have to restore them.
A guide new features in SQL Server Management Studio 2008
When SQL Server Management Studio (SSMS) was first introduced with SQL Server 2005, Microsoft's main goal was to combine some of the existing tools and to integrate functionality provided by Query Analyzer and Enterprise Manager.
As expected, SSMS 2008 includes several new features and improvements – some of which have been requested for years. This article will introduce several new useful SQL Server Management Studio features, and later part 2 will show more advanced new features.
IntelliSense support. IntelliSense is a feature that monitors the context of what you are typing in the code editor and prompts you with syntax help or some likely choice to finish what you are typing. When implemented properly, it can be a huge time saver. It's one of those features that once you are used to it, you can never go back. If I recall correctly, this feature goes all the way back to Visual Basic 5.0 and has been included in every version of Visual Studio. But for some reason, the SQL Server team has been struggling for a long time to implement it.
IntelliSense appeared in the first beta version of SQL Server 2005 back in 2003, but it was removed by the RTM version. Again, IntelliSense was present in the CTPs of SQL Server 2005, but before the product was finished, Microsoft announced that the feature would only be supported when connected to a SQL Server 2008. So if you work a lot with SQL Server 2000 or 2005, you still have to rely on third-party tools such as Red Gate Software Ltd.'s SQL Prompt.
IntelliSense has syntax help, word completion, a list of available database objects and a list of system functions among other things. One feature I find very helpful is the listing of parameters for a stored procedure call. Once you type a stored procedure name and hit the space key, you should see the list of procedure parameters, including their data types. IntelliSense also identifies misspelled keywords, similar to the way a spell checker does.
Region outlining. This feature implements collapsible regions by grouping together a set of related statements and allowing you to quickly hide or expose entire sections of code. Just like IntelliSense, it has been in Visual Studio for a while. Now, finally, database developers and DBAs can improve their productivity by being able to better navigate within large code files. A region is automatically created for a batch, BEGIN-END, BEGIN TRY-END TRY and BEGIN CATCH-END CATCH blocks. The screenshot in Figure 1 shows region outlining. Both batches are the same, but the first batch is almost hidden in a collapsed region.
Figure 1: Region outlining in SQL Server Management Studio 2008 features collapsible regions. (Click on image for enlarged view).
Multi-server queries. With SSMS 2008, you can now execute the same script against multiple servers simultaneously. This can be very handy for multi-server administration or for easily comparing results from different servers. The feature works with the Registered Servers window.
First, you need to create a group of registered servers. Next, you right-click on the group and select New Query. The code in the query window executes against each server in the group that's currently running and is accessible from SSMS. The status bar in the lower left corner shows how many servers the query window was able to connect to, against the total number of servers in the group. You should see something like 5/5 or 4/5, if one server in the group is stopped at the moment. You can define whether you want to receive results in a combined result set or whether you want a separate result set for each server. However, results can only be merged into a single result set if all result sets have the same schema – that behavior is sort of similar to using the UNION clause. You can also configure whether the result set should include the server name. The screenshot in Figure 2 shows the configuration dialog for multi-server queries.
Figure 2: SQL Server Management Studio 2008 features a configuration dialog allowing multi-server queries. (Click on image for enlarged view).
It takes some time to get used to the results returned by a multi-server query. In my opinion, it is a good idea to include the server name in the results to easily identify where the data came from. I personally prefer to not merge results into a single result; if the query returns a lot of columns, you have to keep scrolling to the left to see where the data came from. You also have to examine any error messages very closely to identify which server they came from and to determine whether you need to take corrective action on some servers before you continue executing further multi-server queries.
Custom colors in connection properties. SSMS allows you to define a custom color when creating a connection to a SQL Server instance. Then when you connect to the instance, the status bar for the query window shows in the color you selected. This gives you a quick visual hint to help you figure out what server you are connected to without reading the details on the status bar – and perhaps prevent you from executing your script on the wrong server.
One idea floating in the SQL Server blogs is to configure all development servers with one color, QA servers in another color and then production servers with a color that really stands out to help you stay extra cautious when working directly with production data. But, you have to be really careful because this only works when you create a new connection but not when you change the existing connection and point to another server.
If you are connected to Server A and the status bar is green, the bar will stay green when you change the connection for the query window to point to Server B, even if Server B is registered to show in another color. This could be potentially dangerous since you actually could be connected to a different server than the custom color is indicating. Then you run the danger of accidentally executing scripts on the wrong server. If you are going to use this feature, I recommend that you train yourself to close the query window and start a new connection rather than reconnecting to another server.
T-SQL Debugger
If you have done some programming, you know how important it is to have a good debugging tool. Arguably, a large portion of T-SQL code is written for administrative and maintenance tasks, so debugging isn't as crucial as it is for application code. But many programmers still write complex application code in T-SQL, and benefit from a debugger. In the past, Query Analyzer featured a T-SQL debugger, but it posed reliability issues. In fact, many users were surprised to find that SSMS 2005 had no debugger. In the absence of a debugging tool, their only option was to debug in Microsoft Visual Studio. But with SSMS 2008, a debugging tool has finally been integrated into the SQL environment. But SSMS 2008 has a significant limitation: The debugger works only with SQL Server 2008. If you want to debug older versions, you have to install client tools for a prior version.
T-SQL Debugger includes all the core debugging features -- the Locals, Watch, QuickWatch and Output windows, as well as the ability to set breakpoints to stop execution.
One welcome feature enables you to step into stored procedures that are executed from the code. To use this feature, place a breakpoint or pause at the line that calls the procedure and press F11. You can also step into user-defined functions. Another useful feature is the ability to change values of local variables and parameters.
You start the debugger by clicking on the button with a green arrow. The decision to use this icon, and its placement right next to the Execute button, is controversial. The Execute button in Query Analyzer features the same icon, so users can get confused. I recommend removing this icon from the Standard toolbar to avoid this problem. To remove an icon from a toolbar in SSMS, click on the down arrow on the right side of the toolbar and select Add or Remove Buttons. Then, when you need to debug, add the Debug toolbar to the menu. As a result, the Debug button won't appear right next to the Execute button. Plus, the Debug toolbar contains additional icons for debugging.
If you work with a local instance of SQL Server, debugging should be easy. Debugging a remote server, however, is more complicated. You may need to configure Windows Firewall on both machines (for more details, consult SQL Server Books Online). In my experience, debugging a remote instance often fails. Rather than troubleshooting remote errors this way, it is almost always easier to log on to the remote server through Remote Desktop and debug locally. Finally, avoid debugging on production servers at all costs, because debugging can lock objects for a longer period than usual, especially when using workload-intensive transactions.
Enhanced Object Explorer Details window
The Object Explorer Details window now features several useful enhancements. First, by default, the window now includes more columns than the previous version. The columns that display by default also contain more detailed information. Furthermore, you can right-click the column headings and select additional columns to display. This window is dynamic: The display changes as you select different objects in the Object Explorer window. If you have not worked with this window yet, hit F7 as you browse database objects in the Object Explorer window. The Details window should open, providing additional information about the object currently selected in the Explorer window. As you move to other objects, this window automatically refreshes. SSMS saves selected columns for each object type, so when you return to viewing the same type of object (i.e., a database, a table, etc.), it will display the same level of detail. The following snapshot shows the Object Explorer Details window displaying info about the tables in the AdventureWorks database. I selected additional columns to display, such as data space used, index space used and the row count for each table:
Click on image for larger version
Another significant new feature in the Object Explorer Details window is object search. The toolbar now allows you to type in the name of an object and search for it. The search is context sensitive: It searches objects you select in the Explorer window. If, for example, you want to search for a table within a single database, you select the database in the Explorer window. If you want to search in all databases, select the Databases node. Unfortunately, in a search, the only way to select more than one database is to select all of them. The search displays the list of objects found, including the path to each object. You can jump directly to the object from the search by right-clicking the object and selecting Synchronize. SSMS will navigate down to the object while expanding and selecting the parent nodes in the Explorer window. Finally, the Object Explorer Details window now includes Back and Forward buttons, allowing easier navigation within a set of database objects.
Using SQL Server 2008's MERGE statement
SQL Server 2008's new MERGE statement allows you to insert, update, or delete data based on certain join conditions in the same statement.
In previous versions of SQL Server, you have to create separate statements if you need to insert, update, or delete data in one table based on certain conditions in another table. With MERGE, you can include the logic for these data modifications in one statement.
How MERGE works
The MERGE statement basically works as separate insert, update, and delete statements all within the same statement. You specify a "Source" record set and a "Target" table, and the join between the two. You then specify the type of data modification that is to occur when the records between the two data are matched or are not matched. MERGE is very useful, especially when it comes to loading data warehouse tables, which can be very large and require specific actions to be taken when rows are or are not present.
MERGE example
I will simulate sales feeds being received in the database and loaded to a reporting table that records daily sales statistics. In a typical scenario, the records would be loaded into a staging table (SalesFeed in this example), and then a series of transformations or DDL statements would be executed on the reporting table (SalesArchive in this example) to update the daily sales data. The MERGE statement allows you to use one statement to update the SalesArchive table rather than use several different DDL statements, which potentially could reduce the time it takes to make the updates occur, since only one lookup is done on the data rather than several.
The following script creates the SalesArchive and SalesFeed tables:
CREATE TABLE SalesArchive
(
CustomerID INT PRIMARY KEY,
SalesDate INT,
TotalSalesAmount MONEY,
TotalSalesCount SMALLINT,
CreationDate DATETIME CONSTRAINT df_CreationDate DEFAULT(GETDATE()),
UpdatedDate DATETIME CONSTRAINT df_UpdatedDate DEFAULT(GETDATE())
)
CREATE TABLE SalesFeed
(
CustomerID INT,
Product VARCHAR(10),
SaleAmount MONEY
)
The script below loads some data into the SalesFeed table. The way in which I am inserting data into this table is new to SQL Server 2008; it allows you to specify many values to be inserted using the VALUES clause of the INSERT statement.
INSERT INTO SalesFeed
(CustomerID, Product, SaleAmount)
VALUES
(1,'PoolTable', 1000),
(2,'BigScreen', 955),
(3,'Computer', 590),
(4,'BigScreen', 880),
(5,'Computer', 700)
I have a few rows of data in my SalesFeed table and no data in my SalesArchive table. Now it is time for me to create my MERGE statement to add data to this table. Below is the MERGE script.
MERGE SalesArchive AS SA
USING (
SELECT
CustomerID,
LoadDate = MIN(CONVERT(VARCHAR(8), GETDATE(), 112)),
TotalSalesAmount = SUM(SaleAmount),
TotalSalesCount = COUNT(*)
FROM SalesFeed
GROUP BY CustomerID
) AS SalesFeedCTE (CustomerID, LoadDate, TotalSalesAmount, TotalSalesCount)
ON
(
SA.CustomerID = SalesFeedCTE.CustomerID AND SA.SalesDate = SalesFeedCTE.LoadDate
)
WHEN NOT MATCHED THEN
INSERT (CustomerID, SalesDate, TotalSalesAmount, TotalSalesCount, CreationDate, UpdatedDate)
VALUES( SalesFeedCTE.CustomerID, SalesFeedCTE.LoadDate, SalesFeedCTE.TotalSalesAmount, SalesFeedCTE.TotalSalesCount, GETDATE(), GETDATE())
WHEN MATCHED THEN
UPDATE
SET SA.TotalSalesAmount = SA.TotalSalesAmount + SalesFeedCTE.TotalSalesAmount,
SA.TotalSalesCount = SA.TotalSalesCount + SalesFeedCTE.TotalSalesCount,
SA.UpdatedDate = GETDATE();
At first glance, it looks reasonably complicated, but it's not too bad once you get used to it. The table immediately following the MERGE statement is the table that will be modified; this is known as the TARGET table. In the USING statement, data from the SalesFeed table is being aggregated inside of a subquery based on the CustomerID; this portion is known as the SOURCE. This aggregation allows me to guarantee that there will be only one record per customer to update my SalesArchive table.
The ON clause of the MERGE statement is where I specify: the joining between the SOURCE, the aggregated data from the subquery, and the TARGET, the SalesArchive table.
The WHEN NOT MATCHED clause is where I specify what action I want to occur when the records from the SOURCE are not found in the TARGET. In this scenario, I want to insert those records into the SalesArchive table.
The WHEN MATCHED clause is where I specify what I need to occur when the records from the SalesArchive table and the subquery of the SalesFeed table are found. In this scenario, I want to update what is currently in the table for that day, such as the TotalSalesAmount, the TotalSalesCount, and the UpdatedDate.
With this scenario, if another sales feed comes into the database, only one statement will need to be run for that feed. Any new customer sales will be added to the database, and any existing sales will be updated with the new sales information.