SQL Server Express Update--Upgrading from MSDE--January 9, 2006

==== This Issue Sponsored By ====

This email newsletter comes to you free and is supported by the following advertisers, who offer products and services that might interest you. Please take a moment to visit these advertisers' Web sites and show your support for SQL Server Magazine UPDATE.





January 9, 2006

1. Commentary
- Upgrading from MSDE

2. SQL Server Express Jump Start
- Attaching and Detaching Databases

3. Check It Out
- Microsoft's E-Learning Center

4. From the Community
- Reader Discoveries

5. Events and Resources
- SQL Server 2005: Up & Running Roadshows Coming to Europe!
- Identify and Troubleshoot Common SMTP Problems
- Validate Your Disaster Recovery Data

6. Featured White Papers
- Protect Your Data: How to Ensure Data Integrity
- Optimize Your Existing Windows Server Infrastructure

7. New Products
- Layton's Database Manager

8. Announcements
- Get Full Online Access to SQL Server Magazine
- Celebrate the New Year with SQL Server Magazine


==== Sponsor: Microsoft ====

Get the tips and tricks you'll need to upgrade to Microsoft SQL Server 2005 Analysis Services, including possible upgrade and migration scenarios, pre-planning steps, running the new Analysis Services Migration Wizard, and more.

1. ==== Commentary ====

Upgrading from MSDE
by Michael Otey, [email protected]

If you're currently running Microsoft SQL Server Desktop Engine (MSDE), then upgrading to SQL Server 2005 Express Edition is probably one of the high-priority items on your New Year's resolutions list. However, there are a few things you need to know about your MSDE installation before jumping headfirst into the upgrade. Generally, upgrading from MSDE to SQL Server Express is as simple as running the SQL Server Express setup program. However, that’s not always the case, and there are a couple of key considerations that you should be aware of before you begin the process. MSDE can be installed in one of two ways: by using Microsoft’s setup program or by using merge modules. The MSDE installation method you use determines how you should proceed along the upgrade path.

If you installed MSDE by using Microsoft's standalone setup program, the upgrade process is straightforward; you run the SQL Server Express setup program and perform what is called an in-place upgrade. (In-place means the existing copy of the MSDE database files is upgraded in its current location--or "in-place.") In this scenario, your primary consideration is the default instance name that SQL Server Express uses. MSDE and SQL Server use the name of the local computer as the default instance name, but SQL Server Express uses SQLEXPRESS as the default instance name to help differentiate between a SQL Server Express database instance and a standard SQL Server database instance. This scheme makes it easy for developers--they know that applications developed specifically for SQL Server Express will always connect to an instance named SQLEXPRESS. The naming scheme also makes it easier for applications to share the same database instance. The instance name you choose depends primarily on how you use the database and on your access to the application source code. If you use the database as a Microsoft Office back end or you have access to the application’s source code, then you'll probably want to use the SQLEXPRESS default instance name. On the other hand, if you’re using a database application that's locked into a previously defined instance name, you should select the existing named instance during the upgrade process.

If you installed MSDE by using the old merge module technology (which Microsoft removed from SQL Server Express), the upgrade process is very different. In this case, the SQL Server Express setup program can't do an in-place upgrade to the MSDE instance. Instead, you should do the upgrade using what is termed a side-by-side installation (see Jump Start: "Attaching and Detaching Databases" in this edition for more details). This means that during the installation process, you should detach the databases from the old copy of MSDE, install a new copy of SQL Server Express, then reattach the databases to the new SQL Server Express instance. In this scenario, you typically don’t have access to the application’s source code. Make sure you use a named instance instead of the SQLEXPRESS default instance name when you install SQL Server Express; otherwise, an application might not work because the default instance name most likely won't match the instance name that the existing application expects.

**** Sponsor: ProClarity ****

With the proliferation of different types of business intelligence (BI) tools, what constitutes a portal, a dashboard, and a scorecard can be confusing. Learn how to differentiate between them with this free guide!


2. ==== SQL Server Express Jump Start ====

Attaching and Detaching Databases
by Michael Otey, [email protected]

If you’re upgrading from Microsoft SQL Server Desktop Engine (MSDE) to SQL Server 2005 Express Edition by using the side-by-side upgrade process (see the Commentary: "Upgrading from MSDE" in this edition for more information), you'll need to detach the databases from your old MSDE instance, install SQL Server Express, then reattach the databases to your new SQL Server Express instance. When you detach a database, you're essentially taking it offline so the database engine can't use it, but all of its data remains intact. When you reattach the database, it comes back online.

Detaching and reattaching a database is also a great way to move and copy databases between different SQL Server systems. For example, you can detach a database, copy the data (.mdf) and log (.ldf) files to another system, then reattach the database to make it available on the target system.

SQL Server 2005 provides two stored procedures that you can use to detach and reattach databases: sp_detach_db and sp_attach_db. You can run both procedures from the command-line osql or sqlcmd tools or by using Microsoft SQL Server Management Studio Express.

To detach a database, enter the following command, substituting the name of your database for ‘database_name’:

exec sp_detach_db 'database_name'

To reattach to the database, enter the following command, substituting the name of your database for ‘database_name’:

exec sp_attach_db 'database_name', 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\database_name.mdf', 'C:\Program Files\Microsoft SQL

When you attach to a database, you need to tell SQL Server the name and location of all the data (.mdf) and log (.ldf) files.

==== Sponsor: FaxBack ====

Business Communications Trends: Integrating Fax Servers in MFP Environments
Did you know that wasteful processes can drive the cost of document management and output to as high as 10-15% of your company's annual revenues? Download this free white paper today and find out how you can use fax solutions to achieve cost control, security and compliance, increased workflow, and more.

3. ==== Check It Out ====

Product Highlight: Microsoft’s E-Learning Center

Education is the key to learning how to take full advantage of SQL Server 2005 (or SQL Server 2005 Express Edition, for that matter), and Microsoft’s E-Learning center is a great place to start. Better yet, all of the SQL Server 2005 online courses are free until November 1, 2006. When you get a chance, check out the available courses for SQL Server 2005 at:

4. ==== From the Community ====

Reader Discoveries: Share your SQL Server discoveries, comments, problems, solutions, and experiences with products, and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to [email protected] Please include you phone number. We edit submissions for style, grammar, and length. If we print your submission, you'll get $50!

5. ==== Events and Resources ====

SQL Server 2005: Up & Running Roadshows Coming to Europe!
SQL Server experts will present real-world information about administration, development, and business intelligence to help you put SQL Server 2005 into practice and learn how to use its new capabilities. Includes one-year PASS membership and subscription to SQL Server Magazine. Register now for London and Stockholm, Sweden.

WEB SEMINAR: Identify and troubleshoot common SMTP problems and learn about each component of Exchange that touches inbound and outbound messages. Live seminar: February 14, 2006.

WEB SEMINAR: Validate your disaster recovery data and learn if your backup and restore data is worth staking your career on.


6. ==== Featured White Papers ====

Learn ways to ensure the integrity of your application's data with minimal impact on network and system performance.

Optimize your existing Windows Server infrastructure with the addition of server and storage consolidation software and techniques.

7. ==== New Product ====

Simplify Database Management and Development
by Blake Eno, [email protected]

Layton Technology released Layton Database Manager, a management tool used to simplify database administration and development for SQL Server 2005 Express Edition and Microsoft SQL Server Desktop Engine (MSDE). All database functions are performed in the GUI of the product. You can add, edit, or delete tables, views, users, roles, stored procedures, rules, and user-defined data types. The GUI also lets you build SQL queries, set primary keys, change column properties, backup and restore, and change authentication mode. Pricing for Layton Technology Layton Database Manager starts at $795 for a site license. For more information, contact Layton Technology at [email protected] or 813-319-1390.


8. ==== Announcements ====

Get Full Online Access to SQL Server Magazine
Order a Monthly Online Pass now and get INSTANT access to all articles, tools, and helpful resources published on SQLMag.com, including exclusive Web content. You'll have 24/7 online access to the SQL Server Magazine article database (includes more than 2300 articles) and get the latest digital issue of SQL Server Magazine delivered to your inbox. Order now for just $5.95 per month:

Celebrate the New Year with SQL Server Magazine
You won't want to miss any of SQL Server Magazine's upcoming 2006 issues. Subscribe now and discover the best ways to plan for a successful SQL Server 2005 upgrade, the value of integrating Visual Studio 2005, ways ADO.NET 2.0 solves your problems, the annual Top SQL Server Tips issue, and much more! You'll also gain exclusive access to the entire SQL Server Magazine online article database FREE and SAVE up to $40 off the full cover price. Subscribe today.

==== Contact Us ====

About the [email protected]
About the [email protected]
About technical questions-- http://sqlforums.windowsitpro.com/web/forum/default.aspx?forumid=10
About product [email protected]
About your [email protected]

About sponsoring an issue of SQL Server Express UPDATE--Richard Resnick, [email protected] or Lisa Kling, [email protected]

SQL Server Express UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.

Manage Your Account

You are subscribed as %%$email%%. To unsubscribe from this email newsletter, click here

To manage your email account, simply log on to our Email Preference Center.

View the SQL Server Magazine Privacy Policy.

SQL Server Magazine is a division of Penton Media, Inc.
221 East 29th Street
Loveland, CO 80538
Attention: Customer Service Department

Copyright 2006, Penton Media, Inc. All Rights Reserved.



TAGS: SQL Server
Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.