By: Jeremy Kadlec | Comments | Related: > Upgrades and Migrations
Problem
At our organization we have had limited exposure to SQL Server 2005 because SQL Server 2000 just works. We have worked with SQL Server 2005 in some particular areas, but we do not have the enterprise experience we need to move forward. We know support will be lifted for SQL Server 2000 and know we need to jump on SQL Server 2005 or 2008 in short order. How can I get some first hand experience and what sorts of things should I try to do?
Solution
In an earlier tip (Access to SQL Server 2008 Virtual Environment), we outlined an opportunity to begin to work with SQL Server 2008. In this tip we will outlined some of the first impressions after taking a look at how to complete some of the common tasks in Management Studio and the Business Intelligence Development Studio. Let's jump in...
SQL Server history
When I think about new versions of SQL Server, I think about upgrades. When I started to look at SQL Server 2008 and see how Microsoft is handling the upgrade process, I think history will repeat itself. Here is what I mean. I can remember working with SQL Server 6.5 and upgrading to SQL Server 7.0. As SQL Server 2000 was released that company upgraded. But many other companies upgraded directly from SQL Server 6.5 to SQL Server 2000. I also worked with a company that chose this upgrade approach, so I saw both sides of the coin.
When I think about the interfaces (client tools) between those three versions (6.5, 7.0, 2000) of SQL Server, I see many parallels to our current state of affairs (2000, 2005, 2008). The upgrade from SQL Server 6.5 to 7.0 was very significant in a number of ways, similar to the upgrade from SQL Server 2000 to 2005. In both of these scenarios the interfaces were very different, although intuitive to DBAs and Developers. For the upgrades between SQL Server 7.0 and 2000 as well as SQL Server 2005 and 2008, the upgrades incurred much less of a learning curve although new functionality was released in both versions.
Another upgrade scenario back in the SQL Server 6.5, 7.0, 2000 days was the upgrade from SQL Server 6.5 to SQL Server 2000. This seems like this will be another popular option for many organizations as the SQL Server 2000 to 2005 or 2008 option presents itself. Hopefully skipping versions of SQL Server will not be the norm for a large percentage of SQL Server customers, but based on my career, it seems like an alternative many companies are considering.
SQL Server Interfaces
Since we mentioned the SQL Server interfaces and taking a look at them is where I started with SQL Server 2008, it only makes sense to point out how the similar the SQL Server 2008 interfaces are to the SQL Server 2005 versions. As I look at these two interfaces unless I really dig for differences between SQL Server 2005 and 2008, I really cannot find any. This should be a big help as far as the learning curve associated with the upgrade is concerned. As SQL Server DBAs and Developers, we can hit the ground running if we know SQL Server 2005 and just focus on learning the new functionality.
|
SQL Server 2008 Management Studio |
|
|
|
SQL Server 2008 BIDS - Integration Services Project |
|
|
T-SQL Commands
After I started looking at the interfaces, I pulled up a few quick commands that I use from an administrative and troubleshooting perspective. All of the commands\scripts worked well. So as far as my code is concerned, I am not too concerned about deprecated code. Although, I need to check out my code with respect to:
Change Tracking
As I was browsing through Management Studio, another feature that caught my eye was Change Tracking. After a little bit digging I was impressed to learn about Change Tracking. It is actually a new form of data synchronization different from replication, Service Broker, Database Mirroring, Log Shipping, etc. Change Tracking can be configured for customizable scenarios with a two way relationship. Change Tracking can be enabled by reviewing the properties for a database. From that point forward a few different functions, views and tables are needed to complete the process. Although I was unable to work through an entire scenario, I am interested to see how this technology can help in new and different ways from the current set of options. Stay tuned for some future tips on SQL Server 2008 Change Tracking.
|
Database Property - Change Tracking |
|
|
Plan Guides
Another new feature that caught my eye as an improvement over SQL Server 2005 is the New Plan Guide interface. This provides a simple and intuitive GUI to setup the plan guides for specific queries. This new interface can be accessed by navigating to root | Databases | Database Name | Programmability | Plan Guides. I can see this offering a great deal of functionality for those pesky queries when one little parameter throws off the entire query plan.
|
SQL Server 2008 - New Plan Guide Interface |
 |
SQL Server Data Collector
When browsing through BIDS on a Integration Services project, I noticed a few new options in the toolbox for the Control Flow tab. These included the File Access Task, Reset Row Counts and Trace Start Script. The common denominator for all of these SQL Server Data Collector. The SQL Server Data Collector is a new set of functionality in SQL Server 2008 to query results, traces, and Performance Monitor counters in an automated manner. Once the data is collected it is then available for reporting, analysis and resolution. I am going to be on the lookout for more information related to the SQL Server Data Collector since my first exposure in BIDS.
|
SQL Server 2008 BIDS - Control Flow Tool Box |
|
|
Next Steps
- SQL Server 2008 will be here and in production environments before we all know it, so start to get up to speed on the latest version of SQL Server before it passes you bye.
- To gain access to a SQL Server 2008 sandbox, check out - http://www.sqlserverbeta.com/.
- The information provided in this tip is just the tip of the iceberg when it comes to SQL Server 2008. Stay tuned for many more tips on the topic in the weeks and months ahead.
- Check out these related items:
- Feel free to share your first hand experience as well with the forum below.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
