Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday 20 July 2017

Step by Step Demo of In Place Upgrade from SQL 2008 Express Edition to SQL 2014

Step by Step Demo of In Place Upgrade from SQL 2008 Express Edition to SQL 2014
Before going to see demo below  kindly check this

 http://sqltechi.blogspot.in/2017/07/how-to-run-upgrade-advisor-for-in-place.html
  • Before upgrading from one edition of SQL Server 2014 to another, verify that the functionality you are currently using is supported in the edition to which you are moving.
  • Before upgrading SQL Server, enable Windows Authentication for SQL Server Agent and verify the default configuration: that the SQL Server Agent service account is a member of the SQL Server sysadmin group.
  • To upgrade to SQL Server 2014, you must be running a supported operating system. For more information, see Hardware and Software Requirements for Installing SQL Server 2014.
  • Upgrade will be blocked if there is a pending restart.
  • Upgrade will be blocked if the Windows Installer service is not running.
  • Cross-version instances of SQL Server 2014 are not supported. Version numbers of the Database Engine, Analysis Services, and Reporting Services components must be the same in an instance of SQL Server 2014.
  • Cross-platform upgrade is not supported. You cannot upgrade a 32-bit instance of SQL Server to native 64-bit using SQL Server Setup. However, you can back up or detach databases from a 32-bit instance of SQL Server, and then restore or attach them to a new instance of SQL Server (64-bit) if the databases are not published in replication. You must re-create any logins and other user objects in master, msdb, and model system databases.
  • You cannot add new features during the upgrade of your existing instance of SQL Server. After you upgrade an instance of SQL Server to SQL Server 2014, you can add features by using the SQL Server 2014 Setup. For more information, see Add Features to an Instance of SQL Server 2014 (Setup).
  • Failover Clusters are not supported in WOW mode.
  • Upgrade from an Evaluation edition of a previous SQL Server version is not supported.
In-place facts:

 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. 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.
Following are the advantages of an in-place upgrade:
  • Fast, easy, and automated (best for small systems).
  • No additional hardware required.
  • Applications retain same instance name.
  • Preserves SQL Server 2008 (or 2005) functionality automatically.
The disadvantages of an in-place upgrade are as follows:

  • Downtime incurred because the entire SQL Server instance is offline during upgrade.
  • No support for component-level upgrades.
  • Complex rollback strategy.
  • Backward-compatibility issues must be addressed for that SQL instance.
  • In-place upgrade is not supported for all SQL Server components.
  • Large databases require substantial rollback time.

SQL Server 2014 supports upgrade from the following versions of SQL Server:
  • SQL Server 2005 SP4 or later
  • SQL Server 2008 SP3 or later
  • SQL Server 2008 R2 SP2 or later
  • SQL Server 2012 SP1 or later
The table below lists the supported upgrade scenarios from earlier versions of SQL Server to SQL Server 2014.
Upgrade fromSupported upgrade path
SQL Server 2005 SP4 EnterpriseSQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence
SQL Server 2005 SP4 DeveloperSQL Server 2014 Developer
SQL Server 2005 SP4 StandardSQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence

 SQL Server 2014 Standard
SQL Server 2005 SP4 WorkgroupSQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence

 SQL Server 2014 Standard
SQL Server 2005 SP4 Express,

 SQL Server 2005 SP4 Express with Tools, and

 SQL Server 2005 SP4 Express with Advanced Services		SQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence

 SQL Server 2014 Standard

 SQL Server 2014 Web

 SQL Server 2014 Express
SQL Server 2008 SP3 EnterpriseSQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence
SQL Server 2008 SP3 DeveloperSQL Server 2014 Developer
SQL Server 2008 SP3 StandardSQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence

 SQL Server 2014 Standard
SQL Server 2008 SP3 Small BusinessSQL Server 2014 Standard
SQL Server 2008 SP3 WebSQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence

 SQL Server 2014 Standard

 SQL Server 2014 Web
SQL Server 2008 SP3 WorkgroupSQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence

 SQL Server 2014 Standard
SQL Server 2008 SP3 Express,

 SQL Server 2008 SP3 Express with Tools, and

 SQL Server 2008 SP3 Express with Advanced Services		SQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence

 SQL Server 2014 Standard

 SQL Server 2014 Web

 SQL Server 2014 Express
SQL Server 2008 R2 SP2 DatacenterSQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence
SQL Server 2008 R2 SP2 EnterpriseSQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence
SQL Server 2008 R2 SP2 DeveloperSQL Server 2014 Developer
SQL Server 2008 R2 SP2 Small BusinessSQL Server 2014 Standard
SQL Server 2008 R2 SP2 StandardSQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence

 SQL Server 2014 Standard
SQL Server 2008 R2 SP2 WebSQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence

 SQL Server 2014 Standard

 SQL Server 2014 Web
SQL Server 2008 R2 SP2 WorkgroupSQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence

 SQL Server 2014 Standard
SQL Server 2008 R2 SP2 Express,

 SQL Server 2008 R2 SP2 Express with Tools, and

 SQL Server 2008 R2 SP2 Express with Advanced Services		SQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence

 SQL Server 2014 Standard

 SQL Server 2014 Web

 SQL Server 2014 Express
SQL Server 2012 SP1 EnterpriseSQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence
SQL Server 2012 SP1 DeveloperSQL Server 2014 Developer
SQL Server 2012 SP1 StandardSQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence

 SQL Server 2014 Standard
SQL Server 2012 SP1 WebSQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence

 SQL Server 2014 Standard

 SQL Server 2014 Web
SQL Server 2012 SP1 Express,

 SQL Server 2012 SP1 Express with Tools, and

 SQL Server 2012 SP1 Express Management Studio, and

 SQL Server 2012 SP1 Express with Advanced Services		SQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence

 SQL Server 2014 Standard

 SQL Server 2014 Web

 SQL Server 2014 Express
SQL Server 2012 SP1 Business IntelligenceSQL Server 2014 Enterprise

 SQL Server 2014 Business Intelligence
However, SQL Server 2005 and 2008 Express Editions may only be upgraded to SQL Server 2012 Express Edition.

Demo:

SQL 2008 RTM Version as source  we can not upgrade directly we should need to apply service pack(latest)


we applied service pack 4 latest on SQL 2008 Express Edition

SQL 2008 Express Edition we have test connection succeeded



SQL 2008 Express Edition we have db info and linked server and view and SPs info


We need to stop SQL Services



Before that we need to install/run upgrade adviser to know issues for this upgrade









We need to stop SQL Services on SQL 2008 Express Edition



Now we should need to run SQL 2014 Enterprise Edition setup file


We have to click upgrade from SQL Version link as see below


It is analyzing click Next


click Next


product updates click Next


Upgrade Rule click Next


Specify Product Key click Next


Accept Licenses click Next


Choose Instance to upgrade our case SQL 2008 Exp SP4 (Note we can not see Instance If not met pre requisite  like Latest Service pack like less than SQL Server 2008 SP3 or later) Database service not showing see below (for example Below I Tried to upgrade SQL 2008 from SQL 2005 RTM)


but our case we have latest SP4 in SQL 2008 Exp so it should be shown as see below


Features not possible to change


Mention Instance Click Next


Checking Rules with some warnings  Click Next


Ready to upgrade Click Next


Upgrade is in progress


Now successfully upgraded SQL 2008 Express Edition SP4 to SQL 2014 SP4


You can see the info


See the Edition info of SQL 2014 Enterprise


Also Now we can sign in SQL 2008 SSMS with new upgraded instance


Now we can sign in SQL 2014 SSMS with new upgraded instance


Testing the Linked server connectivity after upgraded SQL 2014 from SQL 2008 





To check compatibility level check:

SELECT @@servername as ServerName,NAME,COMPATIBILITY_LEVEL,version_name =
CASE compatibility_level
    WHEN 65  THEN 'SQL Server 6.5'
    WHEN 70  THEN 'SQL Server 7.0'
    WHEN 80  THEN 'SQL Server 2000'
    WHEN 90  THEN 'SQL Server 2005'
    WHEN 100 THEN 'SQL Server 2008/R2'
    WHEN 110 THEN 'SQL Server 2012'
    WHEN 120 THEN 'SQL Server 2014'
END,LOG_REUSE_WAIT_DESC,recovery_model_desc,state_desc,* from sys.databases
--where name='dbname'



If we need we can change compatibility level from Query as see below

Compatibility level change Script:

ALTER DATABASE DBname SET SINGLE_USER

 ALTER DATABASE DBname
SET COMPATIBILITY_LEVEL = 100

 ALTER DATABASE DBname SET MULTI_USER


Compatibility level upgraded to SQL 2014 from SQL 2008



Complete script:

SELECT @@servername as ServerName,NAME,COMPATIBILITY_LEVEL,version_name = 
CASE compatibility_level
    WHEN 65  THEN 'SQL Server 6.5'
    WHEN 70  THEN 'SQL Server 7.0'
    WHEN 80  THEN 'SQL Server 2000'
    WHEN 90  THEN 'SQL Server 2005'
    WHEN 100 THEN 'SQL Server 2008/R2'
    WHEN 110 THEN 'SQL Server 2012'
    WHEN 120 THEN 'SQL Server 2014'
END,LOG_REUSE_WAIT_DESC,recovery_model_desc,state_desc,* from sys.databases 
--where name='dbname'

 ALTER DATABASE  dbname SET SINGLE_USER

 ALTER DATABASE dbname
SET COMPATIBILITY_LEVEL = 100

 ALTER DATABASE dbname SET MULTI_USER

 select name, compatibility_level 
from sys.databases

 To check object info:

 select * from sys.triggers
select  * from sys.views
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'SET' + '%'
and OBJECT_NAME(OBJECT_ID) not like '%sp_%'
AND OBJECT_NAME(OBJECT_ID) not like '%dt_%'
GO

SELECT 
    CASE TYPE 
        WHEN 'U' 
            THEN 'User Defined Tables' 
                        WHEN 'P'
            THEN 'Stored Procedures'
        WHEN 'PC'
            THEN 'CLR Stored Procedures'
        WHEN 'X'
            THEN 'Extended Stored Procedures'
WHEN 'V'
            THEN 'Views'
WHEN 'TF'
            THEN 'SQL_TABLE_VALUED_FUNCTION'
WHEN 'IF'
            THEN 'INLINE_TABLE_VALUED_FUNCTION'
WHEN 'FN'
            THEN 'SQL_SCALAR_FUNCTION'
    
END as 'Objects',  
    COUNT(*) [Objects Count]    
FROM SYS.OBJECTS
WHERE TYPE IN ('U','P', 'PC','V','TF','IF','FN')
GROUP BY TYPE
order by Objects
compute sum(count(*))

To check dynamic SP Used or not

SELECT DISTINCT o.name ,o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%exec%' OR c.TEXT LIKE '%execute%' or c.text LIKE '%sp_executesql %' 


To Detect Compatibility level issue:


 DECLARE @sql VARCHAR(max),
@Text VARCHAR(max),
@ProcName VARCHAR(200),
@ProcName1 VARCHAR(200)

 DECLARE @T TABLE (ProcName VARCHAR(200), sql VARCHAR(max), ErrorMessage VARCHAR(4000))

 DECLARE c Cursor FOR
SELECT O.Name, C.Text
FROM sysobjects O
JOIN syscomments C ON o.ID=C.ID
WHERE O.XType IN ('P','TF','FN')
and C.text IS NOT NULL
ORDER BY O.Name, C.colid

 Open C
FETCH NEXT FROM c INTO @ProcName, @Text 
SET @sql=@Text
SET @ProcName1=@ProcName

 WHILE @@FETCH_STATUS = 0 BEGIN

 FETCH NEXT FROM c INTO @ProcName, @Text 
IF @@FETCH_STATUS = 0 AND @ProcName1=@ProcName BEGIN
SET @sql=@sql+@Text 
END ELSE BEGIN
SET @sql = REPLACE(@sql, @ProcName1, 'Temp_TestProc_DeleteMe') -- change proc name
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType='P') EXEC('DROP PROC Temp_TestProc_DeleteMe')
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType IN ('TF','FN')) EXEC('DROP FUNCTION Temp_TestProc_DeleteMe')

 BEGIN TRY
EXEC(@sql) -- try to create the proc
END TRY

 BEGIN CATCH
INSERT @T values (@ProcName1, @sql, ERROR_MESSAGE()) -- record procs that couldn't be created
END CATCH

 print @ProcName1
SET @sql=@Text
SET @ProcName1=@ProcName

 END
END

 CLOSE c
DEALLOCATE c

 IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType='P') EXEC('DROP PROC Temp_TestProc_DeleteMe')
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType IN ('TF','FN')) EXEC('DROP FUNCTION Temp_TestProc_DeleteMe')
SELECT * FROM @T
where ErrorMessage not like '%There is already an object named%'


We need to fix it one by one manually

That is it

Keep blogging!!!!!!!!!!!!!!!!!!
Posted by at 00:48

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)