You are here: Home / Archives for Upgrades

Installing SQL Server 2008 on Windows 2008 R2

03/01/2010 By

Today I was performing a new SQL Server 2008 install on a Windows 2008 R2 server.  I got 3 errors while performing the install and thought I would save everyone the trouble of searching the internet for all the workarounds so I included them all here.  The first error came when I tried installing the .Net framework via the SQL Setup.  It was not letting me install via the SQL install and I had to install the .Net framework in the features of the server management console.  You can do that by doing the following steps from this article

Filed Under: Database Administration, Tips, Upgrades

#TSQL2sDay Article Submission – Join syntax

01/11/2010 By

I heard about Adam Machanic’s #TSQL2sday initiative and I thought it was a fantastic idea.  For those of you not familiar with this initiative read up about TSQL2sday  Here’s a brief synopsis for this month’s topic.

Theme

Have you ever found yourself unable to figure out the intricacies of how some piece of code works? Ever been confused by the results you’ve gotten back from a query, only to find out that something totally unrelated was going on? Or have you ever been compelled to wile away your spare time working on a “challenge” posted by some blogger?

For this month’s T-SQL Tuesday, I’m asking participants to write a blog post on a “puzzling” topic, along the lines of some of the following ideas:

  • Describe a confusing situation you encountered, and explain how you debugged the problem and what the resolution was
  • Show a piece of code that doesn’t behave as most people might expect, and illustrate the reasoning behind the discrepancy
  • Create a challenge for your readers to solve

As always, even given the event’s name the posts are not limited to T-SQL! Any component of, or software product related to SQL Server, is fair game. MDX, SSIS, LINQ to SQL, Entity Data Model, NHibernate, and any other software product that deals with SQL Server data can be featured in your post. Be creative!

Rules

As before, any blogger can–and should–feel free to participate in this event. In order to make the event slightly more international, I’m changing the time range from PST-based to UTC-based. So the rules are:

  • Your post must go live between 00:00:00 UTC and 23:59:00 UTC on Tuesday, January 12, 2010
  • Your post must link back to this one, and it’s recommended that you clearly identify the post as a T-SQL Tuesday post
  • You are responsible for ensuring that a trackback or comment appears here so that I can find the posts

Follow the rules, and your post will be included in the roundup to be posted on January 13 or 14. Don’t follow the rules, and it won’t show up there. Simple as that!

My Submission

Not every SQL Server upgrade is equal.  Some are very straight forward and simple.  Yet others are just a complete bear, and have many things that have to be very carefully thought out and tested many times in order to insure things go smoothly.   The upgrade from SQL Server 2000 to SQL Server 2005 or 2008 is no exception and often times it will yield a few obstacles in your path that may slow you down some and at times it may get quite confusing in places.   To start out we may have to convert a lot of our DTS packages to SSIS packages.  This is often a chore and it is confusing in itself sometimes, but that’s not what I wanted to write about.  What I wanted to write about today are the obstacles that I often face during the conversion of the old TSQL join syntax in queries to the SQL-92 standard syntax.   With the old TSQL standard (pre-SQL Server 2005), left and right outer join conditions were able to be specified in the WHERE clause using the *= and =* operators.   In some cases,  this results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins on the other hand are specified in the FROM clause and therefore do not result in this same ambiguity.   In order to Upgrade to SQL Server 2005 or SQL Server 2008 we are forced to convert to these types of joins or we have to run our databases in a lower capability mode.  We can run the lower capability in some environments to ease the transition for a little while but eventually we will have to convert them all to the new standard.  So let’s now look at an example of how we can convert some queries to the SQL-92 standard from the old TSQL syntax.  Review the following lines of code:

select SubCategory=psc.Name, ProductName=p.Name, p.ListPrice, p.Color

from Production.Product p, Production.ProductSubcategory psc

where p.ProductSubcategoryID *= psc.ProductSubcategoryID

and Color is not null

--(256 row(s) affected)

 This query can be really simple to convert.  We just to remove the *= and move up the two fields referenced into the ON clause of the LEFT OUTER JOIN statement.  The result sets match and everything seems to be fine.

SELECT psc.Name AS SubCategory, p.Name AS ProductName, p.ListPrice, p.Color

FROM Production.Product AS p LEFT OUTER JOIN

Production.ProductSubcategory AS psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID

Where (p.Color IS NOT NULL)

--(256 row(s) affected)

That wasn’t so bad, right?  Let’s now look at a little more complex example.   Concider the next few lines of code:

SELECT DISTINCT branch.branch_no,branch.branch_name,site.site_no
FROM site,
customer_site,
branch
WHERE site.site_no *= customer_site.site_no
and getdate() between customer_site.start_date and customer_site.end_date
and branch.branch_no = site.branch_no
and ( site.co_no = 1 )
ORDER BY branch.branch_no ASC, site.site_no ASC

If we did the same thing as before, everything would look to be just fine but when we actually run the query we will discover that the result sets would return different counts of records.  This is the ambiguity I was refering to earlier.  So what seems to be the problem?

SELECT DISTINCT branch.branch_no,branch.branch_name,site.site_no
FROM site
    left join customer_site
        on (site.site_no = customer_site.site_no
            and site.co_no = 1)
    inner join branch
        on (branch.branch_no = site.branch_no )
WHERE
    getdate() between customer_site.start_date and customer_site.end_date
ORDER BY
    branch.branch_no ASC,
    site.site_no ASC

The discrepancy in this example comes down to how the relations are really joined.  You see, there are two main things we always have to be mindful of when we are converting queries to the new form and they are. 

1) We need to first determine what the correct elements of the join itself are. 

2) We need to further identify the supplemental conditions have been applied to limit or filter the data returned after the initial join has taken place and decide if they are really part of the join or are applied afterwards.

What I mean by these two things is that there are certain criteria that is used to join the two relations and those are the things that need to be moved to the ON phrase of your join.  After the relations are joined there are often some additional filters applied to the result set as well that can widdle down the result set further but these may need to stay in the WHERE clause or be added to the join statement as well.  This is the confusing part.  So, for this conversion above the following would be a workable solution. 

SELECT DISTINCT branch.branch_no,branch.branch_name,site.site_no
FROM site
    left join customer_site
        on (site.site_no = customer_site.site_no
            and site.co_no = 1
            and getdate() between customer_site.start_date and customer_site.end_date)
    inner join branch
        on (branch.branch_no = site.branch_no )
ORDER BY
    branch.branch_no ASC,
    site.site_no ASC

As you can see, the SQL-92 standard really make a lot more sense when looking at the queries because if you are joining on something you do exactly that, we JOIN on it!  If you are doing additional filtering we keep that stuff in the where clause.  The real problem though is that depending on how the relations are joined in the first place you can get different result sets as in our second example above.   For inner joins this is not so much a problem but for outer joins it really comes into play.  In our example above the additional filters are actually needed in the join condition itself. Have you ever had any issues converting some of your joins to the new syntax like these?  Please let me know what they were and how you ended up solving them. 

TIP

One little trick that I have used in the past to speed up some of my conversions is to write the queries as a view in the view designer first.  By opening the view designer in SSMS it will automatically convert the queries for you.  It is not always 100% correct as the filters may need to be rearranged but it does get you pretty darn close and it makes it a bit easier when dealing with complex statements.  Try it out and let me know what you think.

Filed Under: TSQL2sDAY, Upgrades

Upgrading your SQL Servers

12/16/2009 By

 Do you want to go from 2000-2005 or 2000 -2008 or do you want to by-pass 2005 and just go from 2000-2008.  The decision is based mainly on your comfort level and your cost benefit analysis.  Regardless of your choice I am writing this article based on a 2000-2008 upgrade. 

Microsoft has two distinct ways to Update SQL Server : a side-by-side upgrade and an in-place upgrade. Each employs a unique methodology with regard to the upgrade process.  There are advantages and disadvantages associated with each. Some factors to include when determining which of the upgrade solutions to choose include scheduled downtime, cost, complexity, and other factors.  Below will be an overview of the steps involved with each of the methods, as well as the strengths and weaknesses associated with each method.

Side-By-Side Upgrade

The side-by-side upgrade process is a manual process that utilizes two distinct servers – one running SQL Server 2000 (SQL 2k) and the other running SQL Server 2008 (SQL 2k8).  Each of these servers is configured as distinct entities with distinct server names and properties. 

Upgrade Steps

Below are the complete steps associated with a Side-By-Side Upgrade.

  1. Install a separate instance of SQL Server 2008 on a separate server. The legacy instance continues to be available.
  2. Run the SQL Server 2008 Upgrade Advisor (will be explained in detail later in this document) against the legacy instance and remove any upgrade blocker issues.
  3. Stop all update activity to the legacy instance. This may involve disconnecting all users or forcing applications to read-only activity.
  4. Transfer data, packages, or other objects from the legacy instance to the SQL Server 2008 instance.   This is an entirely manual process which introduces the possibility of human error.
  5. Apply supporting objects such as SQL Server Agent jobs, security settings, configuration settings, and DTS packages to the new SQL Server 2008 instance.
  6. Verify that the new instance supports the required applications, using validation scripts and user-acceptance tests.
  7. If the new instance passes validations and acceptance tests, redirect applications and users to the new instance. At this point, the new instance is available and databases are online.
  8. If the new instance does not pass validation or acceptance tests, direct users and applications to the legacy database server.

Advantages of a Side-By-Side Update

Below are some advantages of proceeding with a Side-By-Side Update.

  1. You gain more granular control over what database objects you want to upgrade.  You have the capability of choosing individual databases from a server and upgrading them while leaving the rest intact.
  2. You can run the legacy database server alongside the new server, perform a test migration, and research compatibility issues without disturbing the production system.
  3. The legacy database server remains available during the migration, although it cannot be updated for at least the time it takes to transfer data.
  4. You can roll back to the legacy system if the migration does not pass validation and acceptance tests.

Disadvantages of a Side-By-Side Upgrade

Below are some disadvantages of proceeding with a Side-By-Side upgrade:

  1. A side-by-side upgrade will require new or additional hardware resources.  In production environments, this requirement may make this upgrade method cost prohibitive.
  2. You must redirect applications and users to a new instance and possibly a new server. This will require some recoding in the application and configuration settings.
  3. You must manually transfer data, security, configuration settings, and other supporting objects to the new instance.
  1. The downtime might be longer in some cases because large amounts of data must be transferred from one server to another.

In-Place Upgrade

            The in-place upgrade is an automated process that is handled through Microsoft’s Database Server Upgrade executable.  This executable is a wizard driven application that moves an entire instance of SQL 2k to SQL 2k8.  At the completion of the in-place upgrade, the legacy SQL 2k instance is no longer available, as it is replaced with the new SQL 2k8 instance.

Upgrade Steps

Below are the complete steps associated with an In-Place upgrade.

  1. The SQL Server 2008 Setup prerequisites are installed (Microsoft .NET Framework, SQL Server Native Client, and so on). The legacy instance databases continue to be available.
  2. Setup checks for upgrade blockers, a small set of issues that will completely block an upgrade. If any are found, Setup will warn and exit.  These blockers, if any, are usually identified during the testing phase with the SQL Server 2008 Upgrade Advisor.
  3. Setup installs the required SQL Server 2008 executables and support files
  4. Setup stops the legacy SQL Server service. At this point, the legacy instance is no longer available.
  5. SQL Server 2008 updates the selected component data and objects.
  6. Setup removes the legacy executables and support files as well as the legacy tools. The new SQL Server 2008 instance is now fully available.
  7. An in-place upgrade is easier and faster than a side-by-side upgrade.
  8. It is mostly an automated process.
  9. The instance will be offline for only the minimum amount of time.
  10. The resulting upgraded instance has the same server name and configuration settings as the original.
  11. Applications continue to connect to the same instance name without any modification to the code base.
  12. No additional hardware is required because only the one instance is involved.
  13. You must upgrade an entire instance or a major SQL Server component. 
  14. You cannot directly upgrade a single database.
  15. There will be some disk use growth during the metadata upgrade process.  This would require the disk to have room for growth on each database log file,  as well as the tempdb log and data files.
  16. You must inspect the entire instance for backward compatibility issues.  This is to ensure that little used functionality continues to behave as expected.
  17. You cannot run the newly upgraded version alongside the legacy version for comparison.
  18. Rollback of upgraded data and the upgraded instance is complex.  Because the legacy version of the system is unavailable, the only way to rollback from an in-place upgrade is to re-install the legacy SQL Server and then manually restore each of the objects.

Advantages of an In-Place Upgrade

  1. An in-place upgrade is easier and faster than a side-by-side upgrade.
  2. It is mostly an automated process.
  3. The instance will be offline for only the minimum amount of time.
  4. The resulting upgraded instance has the same server name and configuration settings as the original.
  5. Applications continue to connect to the same instance name without any modification to the code base.
  6. No additional hardware is required because only the one instance is involved.

Disadvantages of an In-Place Upgrade

  1. You must upgrade an entire instance or a major SQL Server component. 
  2. You cannot directly upgrade a single database.
  3. There will be some disk use growth during the metadata upgrade process.  This would require the disk to have room for growth on each database log file,  as well as the tempdb log and data files.
  4. You must inspect the entire instance for backward compatibility issues.  This is to ensure that little used functionality continues to behave as expected.
  5. You cannot run the newly upgraded version alongside the legacy version for comparison.
  6. Rollback of upgraded data and the upgraded instance is complex.  Because the legacy version of the system is unavailable, the only way to rollback from an in-place upgrade is to re-install the legacy SQL Server and then manually restore each of the objects.

Upgrade Tools

            There are three primary tools that are used to plan, test, and verify the upgrade.  The tools that will be mentioned and described in this section are Microsoft’s SQL Server 2008 Upgrade Advisor,  Scalability Expert’s SQL Server 2008 Application Upgrade Advisor and DTS xChange.  Below is a description of each of the tools to be used.  Later, it will be explained how each of these tools will be used for planning and verifying the upgrade.

SQL Server 2008 Upgrade Advisor

            SQL Server 2008 Upgrade Advisor is a tool developed by Microsoft to help identify any issues that may arise during the process of upgrading from SQL 2k to SQL 2k8.  The Upgrade Advisor will highlight issues that might prevent a successful upgrade, as well as issues that might introduce backwards compatibility issues.  Upgrade Advisor analyzes your legacy instances and produces reports detailing upgrade issues by SQL Server component. The resulting reports show detected issues and also guidance on how to fix the issues or work around them.  These reports are stored on disk, so they can be retrieved and reviewed multiple times.  Upgrade Advisor executes read only operations against the server and is CPU intensive, so it is best to run off of a remote machine.  When you perform the analysis with Upgrade Advisor, you have the option to analyze any or all of the SQL Server components: SQL Server Database Engine, Analysis Services, Reporting Services, Notification Services, and Data Transformation Services.  When analyzing the Database Engine, you also have the option to select which of the databases you wish to include in the analysis.

            Once the analysis is performed on each of these components, the Upgrade Advisor prepares a report of its findings.  The report is divided by component, and highlights issues that need to be addressed.  The report classifies these issues into issues that need to be addressed prior to the upgrade, issues that should be addressed after the upgrade, and issues that should be addressed at any time.  Clicking on any issues that are highlighted will bring up suggestions by Microsoft regarding what steps may be taken to correct the issues.  Because the analysis can be run multiple times, we would run the analysis until the report only shows issues to be addressed after the upgrade.  We will not upgrade until all of the issues identified as needing to be addressed prior to the upgrade, or addressed at anytime are completed.

SQL Server Application Upgrade Advisor

            The Application Upgrade Advisor is a third party application that was developed by Scalability Experts.  Like the Database Upgrade Advisor, the Application Upgrade Advisor performs an analysis and generates reports based on that analysis.  While the Database Upgrade Advisor performs a validation to see if the database would successfully update, the Application Upgrade Advisor is used to validate whether the applications would be able to successfully operate under SQL Server 2008.  To successfully utilize the Application Upgrade Advisor, you have to incorporate results generated from the SQL Server Database Upgrade Advisor.  Listed below are the steps to run the Application Upgrade Advisor.

Running the Application Upgrade Advisor

  1. Create a Test Environment – Ensure that the environment used for testing is configured as closely as possible to the specifications of the production environment.
  2. Capture a Playback – A playback consists of a backup of all system and user databases and a trace that can be replayed.  To capture the trace, the front end GUI that utilizes the database should be used.  If there is an automated script that is used for regression testing, this should be used to guarantee that a comprehensive test is performed.
  3. Setup Playback Baseline System – Restore the backups of all of the databases that were taken in Step 2.
  4. Run SQL Server Database Upgrade Advisor – Run the Upgrade Advisor as described above and fix any issues that are identified to be fixed.
  5. Replay Trace on SQL Server 2000 – After running the Database Upgrade Advisor, replay the trace created in step 2.  The replayed trace result becomes the baseline from SQL 2k.  This baseline will be compared with the same trace, replayed on SQL 2k8 to help us understand any changes to expect after upgrading the production servers.
  6. Setup Playback Test System – During this step, the backups from step 2 are restored.
  7. Upgrade to SQL Server 2k8 – With the databases restored, upgrade the test server to the latest version of SQL Server.
  8. Replace Trace on SQL Server 2k8 – To evaluate the success of the upgrade, replay the trace against the SQL Server 2k8 database.
  9. Compare Trace Files – At this stage, there will be results of the trace from SQL 2k and the results of the trace from SQL 2k8.  Application Upgrade Advisor will evaluate both trace file results and generate a report comparing the two.
  10. Analyze Results – Use the Application Upgrade viewer to evaluate the differences between the results of the two traces.

DTS xChange

Although SQL Server 2008 Integration Services may be considered the fourth iteration of an ETL tool in SQL Server, it is dramatically different from SQL Server 2000 Data Transformation Services (DTS). The architecture of SSIS has changed drastically to support an in-memory ETL method that can efficiently support a load of millions of rows. To take advantage of this new architecture, organizations must migrate their DTS packages into SSIS and apply new best practices to the migrated packages.

SQL Server 2008 comes with the DTS Package Migration Wizard, a built-in means of migrating simple DTS packages to SSIS. However, this method will not meet the needs of all organizations. For organizations in need of a more robust solution that can handle thousands of packages with little administrator intervention, DTS xChange is an enterprise solution, offered by a Microsoft partner Pragmatic Works, that migrates DTS packages to Integration Services while applying a series of best practices rules to the packages.

DTS xChange is broken into three components:

  1. Profile: DTS xChange Profiler helps organizations estimate their migration project in hours and dollar cost whether they choose to use an automation tool or not.
  2. Convert: DTS xChange migrates packages, applying rules to each DTS package as it migrates them to enforce best practices.
  3. Monitor: The SSIS Performance Warehouse is a software development kit (SDK) to help organizations get the most out of their new Integration Services environment. It contains a series of reports and a data warehouse to monitor administrators’ Integration Services package execution.

Testing Process

            We will validate that our applications in their current state will continue to operate under SQL Server 2k8.  Below will be a description of the requirements of the testing process, as well as a detailed description of each of the phases.

Testing Requirements

            During testing, we will validate our applications ability to run successfully against SQL Server 2008.  This will be done primarily using SQL Server Application Upgrade Advisor.

  1. Restore backup of legacy databases onto server to be upgraded.
  2. I will ensure that restore operations are successful and connectivity has been established.
  3. QA group verifies that restored databases behave as expected.
  4. Proceed with detailed steps that were highlighted under description of SQL Server Application Upgrade Advisor.  This description contains instructions for capturing trace files, running the Database Upgrade Advisor, upgrading the database, and re-running the trace files.
    1. During the running of the Database Upgrade Advisor, a list of issues that need to be addressed will be created.  It will be the responsibility of the DBA group and the Developer groups to address these issues.  Note that this may potentially be a lengthy process as there may be a number of server and/or database related issues.  The Database Upgrade Advisor is designed to be run multiple times, and each time should theoretically produce less issues.  These issues and their resolutions would be saved.  If possible, the solutions to the issues would be developed in a repeatable format so that the solutions may be reused when they are encountered in the production environment.
  5. Compare the results of the trace files that were run against the SQL 2k and SQL 2k8 versions of the database.
  6. DBA and QA groups document any known issues.
  7. DBA and Developer groups address any discrepancies between the results of the two trace files.  Once the discrepancies are corrected, the process is repeated until the results of the two trace files are identical.
  8. When the results of the trace files are identical, each group signs off that the application is verified for SQL Server 2008.
Filed Under: Upgrades