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!!!!!!!!!!!!!!!!!!

Wednesday, 19 July 2017

Solving Issue Error 1053: The service did not respond to the start or control request in a timely fashion in SQL Server


Solving Issue Error 1053: The service did not respond to the start or control request in a timely fashion in SQL Server

After you have installed SQL Server we have received following error








Solution:

Services that use the local system account to log on to a Windows Server 2003-based computer start if the Allow service to interact with desktop option is turned on. To turn this option on, follow these steps:
  1. In the Services tool, click the service that you want to start, and then click Properties.
  2. Right-click the Log On tab, and then click to select the Allow service to interact with desktop check box.
  3. Click OK to exit the Properties dialog box.




After we have choose we need start services on SQL Services



Tuesday, 18 July 2017

How to Restore TDE (Transparent Data Encryption) Enabled Database from one SQL Server to Other SQL Server 2012 & Demo of Restore TDE enabled database in SQL Server

How to Restore TDE (Transparent Data Encryption) Enabled Database from one SQL Server to Other SQL Server 2012

Transparent Data Encryption (TDE) is a feature introduced in SQL Server 2008 and available in later versions for bulk encryption at the database file level (data file, log file and backup file) i.e. the entire database at rest. Once enabled for a database, this feature encrypts data into pages before it is written to the disk and decrypts when read from the disk. The best part of this feature is, as its name implies, it’s completely transparent to your application. This means literally no application code changes (only administrative change to enable it for a database) are required and hence no impact on the application code\functionalities when enabling TDE on a database being referenced by that application.

available in the Enterprise edition of SQL Server 2008 and later


Getting Started with Transparent Data Encryption (TDE)

These are the steps you need to perform to enable TDE for a database, assuming you have the required permissions for creating a database master key and certificates in the master database and CONTROL permissions on the user database.
  • Create a master key – A master key is a symmetric key that is used to create certificates and asymmetric keys.
  • Create or obtain a certificate protected by the master key – Certificates can be used to create symmetric keys for data encryption or to encrypt the data directly.
  • Create a database encryption key and protect it by the certificate .
  • Set the database to use encryption – When you enable TDE on any user database, encryption is also automatically enabled for the tempdb database. This prevents temporary objects that are used by the user database from leaking to disk unencrypted via tempdb database. System databases other than tempdb cannot currently be encrypted by using TDE.

TDE Eligible SQL Server Editions

First we must determine the correct version of SQL Server that allows Transparent Data Encryption. I like to call it an expensive feature as it requires Enterprise Editions. It also works with Developer Edition, but of course, this is just for testing and development purposes. When implementing this in a production environment you must have the correct version of SQL Server. I’ve listed the eligible editions below.
  • SQL 2016 Evaluation, Developer, Enterprise
  • SQL 2014 Evaluation, Developer, Enterprise
  • SQL Server 2012 Evaluation, Developer, Enterprise
  • SQL Server 2008 R2 Datacenter, Evaluation, Developer, Enterprise, Datacenter
  • SQL Server 2008 Evaluation, Developer, Enterprise
SQL Server TDE is a light encryption method that will not affect the queries performance, as the encryption is at the database files level. On the other hand, using the TDE, the database backup files will not take benefit from the backup compression feature completely.

Demo

use master
select getdate() 'Today Date and Time'

Select @@version as Version
SELECT
            @@SERVERNAME AS ServerName,SERVERPROPERTY('ProductVersion') AS ProductVersion,
            SERVERPROPERTY('ProductLevel') AS ProductLevel,
            SERVERPROPERTY('Edition') AS Edition,
            SERVERPROPERTY('ResourceLastUpdateDateTime') AS 'ResourceLastUpdateDateTime', 

            SERVERPROPERTY('ResourceVersion') AS 'ResourceVersion'

Here Source database is MS SQL 2012 Enterprise RTM Version


Here Destination database is MS SQL 2012 Enterprise SP3 Version


Now we need to create Master Key by encryption and decryption and certificate in master database

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Ws@2012';
GO
Use Master

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012'

USE Master

CREATE CERTIFICATE TDE_CertTestTDE
WITH 
SUBJECT='Database_Encryption';
GO


after that we need to create Database encryption  on user database and we can see in DMV

USE [TestTDE]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_CertTestTDE;

GO

  --To monitor encryption progress you can use this query
SELECT db_name(database_id), encryption_state,   percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys


now we need to enable encryption on database level

after that we need to backup certificate on master database

ALTER DATABASE [TestTDE]
SET ENCRYPTION ON;
GO

USE Master

BACKUP CERTIFICATE TDE_CertTestTDE
TO FILE = 'C:\SQL activity\TDE\Certificate\TDE_CertTestTDE'
WITH PRIVATE KEY (file='C:\SQL activity\TDE\Certificate\TDE_CertTestTDEKey.pvk',

ENCRYPTION BY PASSWORD='Pass@w0rd') 


We now need to go for destination server and create master key and certificate as we gave in source

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Ws@2012';
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012'

use master

CREATE CERTIFICATE TDE_CertTestTDE
FROM FILE = 'C:\SQL activity\TDE\Certificate\TDE_CertTestTDE'
WITH PRIVATE KEY (file='C:\SQL activity\TDE\Certificate\TDE_CertTestTDEKey.pvk',
DECRYPTION BY PASSWORD='Pass@w0rd') 



Restore process:


we need to take backup of source database


now we are going to restore in Destination database



Be aware if you not create certificate on destination from backedup Certificate you might get Error as see below

Once the certificate is restored to the secondary server you may restore a copy of the encrypted database other wise we will get below error



we are pointing to backup location from destination (our case we already created certificate from backup on destination )


we need to tick relocate and file name on restore


You can see we are successfully restore database of TDE enabled database


Here are below complete script on source and destination

Script:(source server)

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Ws@2012';
GO
Use Master

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012'

USE Master

CREATE CERTIFICATE TDE_CertTestTDE
WITH 
SUBJECT='Database_Encryption';
GO

USE [TestTDE]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_CertTestTDE;

GO

  --To monitor encryption progress you can use this query
SELECT db_name(database_id), encryption_state,   percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys

ALTER DATABASE [TestTDE]
SET ENCRYPTION ON;
GO

USE Master

BACKUP CERTIFICATE TDE_CertTestTDE
TO FILE = 'C:\SQL activity\TDE\Certificate\TDE_CertTestTDE'
WITH PRIVATE KEY (file='C:\SQL activity\TDE\Certificate\TDE_CertTestTDEKey.pvk',
ENCRYPTION BY PASSWORD='Pass@w0rd') 

Script:(destination server)

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Ws@2012';
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012'


use master

CREATE CERTIFICATE TDE_CertTestTDE
FROM FILE = 'C:\SQL activity\TDE\Certificate\TDE_CertTestTDE'
WITH PRIVATE KEY (file='C:\SQL activity\TDE\Certificate\TDE_CertTestTDEKey.pvk',
DECRYPTION BY PASSWORD='Pass@w0rd') 




Ref:
http://www.databasejournal.com/features/mssql/transparent-data-encryption-tde-in-sql-server.html

https://www.sqlshack.com/how-to-configure-transparent-data-encryption-tde-in-sql-server/

https://www.sqlshack.com/how-to-configure-sql-server-mirroring-on-a-tde-encrypted-database/



Happy Blogging!!!!!!!!!!!!!!!!!!