Upgrade Sql Server 2008 To 2012 Step By Step Pdf
.Enterprises partner with Datavail to plan, design, build and deploy intelligent enterprise solutions, leverage data for insight, and manage their data and systems. See what Datavail can do for you.Solutions.Explore This Solution.Services We Offer.Explore This Solution.Services We Offer.Explore This Solution.Platforms We Service.Explore This Solution.Platform Overview.Explore This Solution.Services We Offer.Explore This Solution.Services We Offer.No matter what the scope of an engagement covers, no matter what technology we’re asked to support, Datavail helps organizations leverage data for business value. Transform your firm’s performance, processes, decision making and more with tour technology support.Technology Areas.Analytics & Business Intelligence.We Support.Cloud & Infrastructure.We Support.Custom Applications.We Support.Databases.We Support.Packaged Applications.We Support. It’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans. It’s true – SQL 2005 is 10 years old and if you’ve been working with SQL Server that long it’s likely you’re a seasoned pro. However unlike DBAs, a given SQL version does not get better with age (well not after SP4 anyway.)Take SQL 2008 for instance, mainstream support for this version in 2014 and its technology has been superseded by the new features and valuable improvements of recent SQL versions.
Sure, some SQL 2008 instances will be around – and DBAs will be supporting them for many more years. But over the next few years many other SQL 2008 (and earlier) instances and databases will be making the migration to SQL 2012 or SQL 2014. Here’s part one of a post on bringing these older versions up to date as smoothly and succinctly as possible. Make sure to stay tuned for part two. Read our latest white paper on ways to optimize performance and learn about new features to help improve security.Upgrade TechniquesOf course we want the upgrade project to be as streamlined and efficient as possible.
An in-place upgrade provides this, since it simply versions-up the binaries of the existing install. But a version upgrade can be an opportunity to make some potentially important changes. Should the instance also be migrating to newer OS versions or moving to improved hardware? Can several instances be consolidated? A side-by-side migration upgrade allows these kinds of tasks to be included in the upgrade project.You Can’t Get There From HereTo begin, let’s identify what we cannot do. Some of these show stoppers may apply only to in-place upgrades but let’s get them all out on the table:. The OS must be at least 2008.
Upgrade Sql Server 2008 To 2012 Step By Step Pdf Download
SQL 2014 cannot be installed on Windows 2003. The platform must be the same (in-place only). An instance cannot go from 32 to 64 nor 64 to 32. Editions cannot downgrade (in-place only). An instance cannot go from EE to SE. You can only migrate from 2008.
Upgrading SQL 2005 will be a 2 step process since it must first be upgraded to 2008.The Magician’s TableIn-place upgrades always make me think of the old Magician’s Table trick that everyone tried when they were kids. For SQL upgrades it’s like this: The older version, SQL 2008, is the table cloth. The SQL Install Executable sets the table with all the SQL 2014 china plates and crystal glasses. Then in one smooth and quick motion it pulls the table cloth out from under the dishes leaving a SQL 2014 instance where SQL 2008 used to be. It is like magic and it almost always works fine. But if things do go, picking up the pieces can really ruin your chances for a lunch break (to say the least). Here are some pros and cons.Pros.
One and done! It’s very easy and fairly quick to click through the installer and get the job done.Cons. No rolling back (not practically anyway.). No opportunity for additional changes or optimizations.
For example make a change to the OS version or the SQL edition.Stepping Stones To The Next VersionSide-by-side migrations are more like stepping stones that allow us to get from one version to the next. The original 2008 version is stone A, and well ahead of the actual migration date we can set stone B, the SQL 2014 instance, in place. What is great about side-by-side migration is that if necessary, we can easily step back to the original instance. What’s more, we have the opportunity to pick and choose many aspects of the ‘new’ stone. Our migration can go down edition from enterprise to standard, cross platform from 32bit to 64bit, or to more optimal hardware, like faster CPUs.
There is even an opportunity to choose a new hosting solution for the instance; a physical server, a virtual machine, or the cloud. This flexibility really stands out as an advantage over in-place upgrades.
Here are the pros and cons:Pros. Advance preparation – its easy to set up the new target server and instance ahead of time and pre-configure, allow a burn in period, do testing, etc. Rollback options – if an upgrade migration does not go to plan, the original instance and data are intact and exist as a fail back option. Opportunity to upgrade hardware – this may be especially relevant considering SQL Server is now licensed per core rather than socket.
Upgrade and modernization projects for SQL Server should not be taken lightly. You might want to take advantage of the latest versions of. You might need to take your aging solutions running on older hardware and operating systems and get them onto a supported version of SQL Server. In either case, having a programmatic approach to upgrading your applications and databases is critical to keeping your business running. At DB Best Technologies, we have a 12-step approach for upgrade projects based on our proven.
These steps include thorough assessment, conversion of database schemas, application, and scripts, performance testing, and may even require SQL Server data migration. This blog post provides you an overview of the DB Best 12 SQL Server upgrade steps to help you prepare for your upgrade.
Let’s get started. 1 Envisioning and assessmentThe envisioning and assessment phase is the most important phase of an upgrade project. For us, it establishes the business case for upgrading and defines the approach that you will take. DB Best uses an internal tool called DBMSys that captures system metrics that helps you decide on what the target platform should be. For example, can you consolidate databases on a virtualized environment? Is it a candidate for moving to the cloud? DB Best creates a financial model that can help you decide on the future state for the deployment.
You also need to look at other aspects of the application and database.Examples include: Does your third-party vendor support the newer version of SQL Server? What is your downtime tolerance? What are the times that you can’t touch the system for maintenance? Are there other projects that might interfere with the upgrade? What level of security access do you need? Do you have an existing test plan for the application?
What technologies are you using for high availability and disaster recovery? Factors like these help in defining the overall project and deciding on the upgrade approach. Different approaches include: in-place, side-by-side, upgrade to a new server, upgrade to the cloud using, or managed services like. You might also want to look at how you can move your applications, like, to the cloud as well. Using specific toolsIn the assessment phase, we look for potential incompatibilities with the database using tools like the. Please note that Microsoft has replaced the SQL Server 2016 Upgrade Advisor with the tool. Although the Upgrade Advisor helps you to analyze the database, you need a way to collect SQL Server traffic between your applications and your older servers to look for deprecated syntax.DB Best uses an internal tool called SQL Sniffer and Replayer to analyze the network traffic without the overhead of using a profiler trace to identify incompatibilities.
You will also need to understand what data transformation services (DTS) packages or other extract, transform, and load (ETL) services, like SQL Server Integration Services (SSIS) packages, need upgrading. At DB Best, we have an internal tool that scans existing databases for DTS and SSIS packages to help scope the upgrade effort. For complex systems, it helps to create an architecture diagram to show all the components that connect to the system to look for other incompatibilities such as network connectivity that may not work with newer versions of SQL Server.With all of this information, you’re on a great start to your upgrade. You also have insight as to how much effort you need to spend on the remaining 11 SQL Server upgrade steps. 2 Database schema conversionFor the database schema conversion step, the first thing you do is to fix and store procedures. From there, views, triggers, and other database objects that are using deprecated syntax will be identified using the. Typically, you will want to back up your application database and then restore.
We suggest to do this on a development/test environment running a newer version of SQL Server before making your corrections.You then want to look at to see how they might help you improve security, performance, and high availability. For example, encrypts data files at rest. SQL Server 2016 includes additional features like for encrypting column data from applications that don’t have the proper security credentials, to limit access to data based on a security policy, and to redact data based on role permissions. You will also want to look at to replace for failover protection. For performance, you might want to look at how you can incorporate for improved data warehouse and query reporting performance.
3 Application conversion and remediationIn order to convert and remediate your application, you first need to understand your syntax. The first step is to identify where the application is sending any or syntax along with and update the application using the supported syntax.
In addition, you need to see how the application connects to the database for the switch over to the newer versions of SQL Server. This is where tools like the SQL Sniffer can help identify the application code to expedite the process.
4 Scripts conversionThe script conversion step looks at the batch scripts that are used for ETL processes, database maintenance, disaster recovery, and other scripts. These scrips, that are not related directly to the applications using the database, need to be analyzed to make sure they will work with the new versions of SQL Server. In addition, you will want to look at how newer scripting languages like can improve automation processes. 5 Integration with third-party applicationsWhat about your applications like third-party business intelligence and ETL tools? Do your utilities continue to work after the upgrade process? In this step, you need to identify them and make sure they continue to work with the newer versions of SQL Server. You may need to explore upgrading the third-party tools as well.
6 Data migrationNext, comes SQL Server data migration. It is crucial that you are using tools to backup and restore full and transaction logs. You may have a need to apply some type of data replication scheme leading. This is done to keep your pre-deployment version of the database in sync with the current production version. Tools like the DB Best SQL Server Central Log Shipping scripts provide a way to automate the process.
In addition, you can use tools like DB Best’s to look for data changes and look up tables. We do this to ensure they are in sync with the production database before cutting over. 7 Functional testing of the entire systemFunction testing is often the most time-consuming process. Time varies depending on the changes needed to the database and applications. Having an existing test plan will certainly help in the process. If not, the upgrade team will need to work closely with the application development team. The goal will be to develop test plans and execute them in a pre-production environment.
8 Performance testingAlmost instantly you should see overall performance improve after your upgrade. Thanks to the performance enhancements with newer versions of your server will be far snappier. In addition, you can expect performance improvements with newer hardware configurations. Ideally, you will want to measure performance for critical areas of the application and then compare them against the new environment. In this step, you will also want to look at how you can take advantage of the new SQL Server capabilities such as. 9 Integration and deploymentOnce you are ready to switch over to production, it’s important to have a plan for what to do in the case you need to roll back changes.
You may need to do this so that you can meet your business continuity requirements. You will want to test this plan in your pre-production environment as well so that the team is ready for any problems than can occur during the rollout. 10 Training and knowledge transferThere are two types of training and knowledge transfer you want to consider during this phase.
The first is focused on the operational changes to the upgraded system. The second has to do with taking advantages of the latest SQL Server features such as.
11 Documentation and version controlIn this phase, you need to make sure that you have the necessary documentation in place to support the upgraded system. In addition, you might want to consider using a version control system moving forward to control changes as part of a solid development practice. At DB Best Technologies, we use tools like to maintain all source scripts and tools used for the upgrade project and share them with our clients at all phases of the project as needed. 12 Post-production supportIn this final SQL Server upgrade step, you want to make sure you have the plan and the resources to manage your upgraded system. If you happen to lack the resources to maintain the upgraded system, DB Best can help you reach your goals, even on the most demanding applications. DB Best can helpHaving your infrastructure and data environment secure and up-to-date is crucial for any organization.