Delete Statement running forever/slow in SQL Server

Things that can cause a delete to be slow:

• cascade delete (those ten parent records you are deleting could mean millions of child records getting deleted)
• Transaction log needing to grow
• Many Foreign keys to check
• deleting a lot of records
• many indexes
• deadlocks and blocking
• triggers

Move and Rename File with File System Task in SSIS

Move and Rename File with File System Task

All Credit goes to Katie & Emil Youtube Channel

Script to find SQL Server Engine Edition

Finding SQL Server EngineEdition:

SELECT SERVERPROPERTY('EngineEdition');

Database Engine edition of the instance of SQL Server installed on the server.
1 = Personal or Desktop Engine (Not available in SQL Server 2005 and later versions.)
2 = Standard (This is returned for Standard, Web, and Business Intelligence.)
3 = Enterprise (This is returned for Evaluation, Developer, and both Enterprise editions.)
4 = Express (This is returned for Express, Express with Tools and Express with Advanced Services)
5 = SQL Database

Description:

The EngineEdition property returns a value of 2 through 5

Value 1: 1 isn’t a valid value in versions after SQL Server 2000,

Value 2: If value is 2, edition is either Standard, Web, or Business Intelligence, and fewer features are available. The features in Enterprise edition (as well as in Developer and Enterprise Evaluation editions) that aren’t in Standard edition generally relate to scalability and high-availability features, but other Enterprise-only features are available

Value 3: A value of 3 indicates that SQL Server edition is either Enterprise, Enterprise Evaluation, or Developer. These three editions have exactly the same features and functionality.

Value 4: A value of 4 for EngineEdition indicates that your SQL Server edition is Express, which includes SQL Server Express, SQL Server Express with Advanced Services, and SQL Server Express with Tools.

Value 5: Value of 5 for EngineEdition indicates that SQL Azure, a version of SQL Server that runs as a cloud-based service. Although many SQL Server applications can access SQL Azure with only minimum modifications because the language features are very similar between SQL Azure and a locally installed SQL Server.

How to find percentage of a database backup job done in SQL Server?

Script to find percentage of a database backup job is done

SELECT percent_complete
 ,*
FROM sys.dm_exec_requests
WHERE command IN (
  'RESTORE DATABASE'
  ,'BACKUP DATABASE'
  )

Elapsed time in Hours to complete the Job

SELECT command
 ,percent_complete
 ,'elapsed' = total_elapsed_time / 3600000.0
 ,'remaining' = estimated_completion_time / 3600000.0
FROM sys.dm_exec_requests
WHERE command LIKE 'BACKUP%'

DBCC CHECKDB :A database snapshot cannot be created because it failed to start.

Disk space issues during DBCC CHECKDB

Sometimes an issue arises when the hidden database snapshot runs out of space. Because it’s implemented using alternate streams of the existing data files, the database snapshot consumes space from the same location as the existing data files. If the database being checked has a heavy update workload, more and more pages are pushed into the database snapshot, causing it to grow. In a situation where the volumes hosting the database don’t have much space, this can mean the hidden database snapshot runs out of space and DBCC CHECKDB stops with an error. An example of this is shown here (the errors can vary depending on the exact point at which the database snapshot runs out of space):

DBCC CHECKDB ('SalesDB2') WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
Msg 1823, Level 16, State 1, Line 5
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5128, Level 17, State 2, Line 1
Write to sparse file 'C:\SQLskills\SalesDBData.mdf:MSSQL_DBCC20' failed due to lack of disk space.
Msg 3313, Level 21, State 2, Line 1
During redoing of a logged operation in database 'SalesDB2', an error occurred at log record ID (1628:252:1). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

In this case, the solution is to create your own database snapshot, placing the snapshot files on a volume with more disk space, and then to run DBCC CHECKDB on that. DBCC CHECKDB recognizes that it’s already running on a database snapshot and doesn’t attempt to create another one. If a database snapshot was created by DBCC CHECKDB, it’s discarded automatically after the consistency-checking algorithms are complete.
While it runs, DBCC CHECKDB creates a database snapshot (if needed) and suspends the FILESTREAM garbage collection process. You might notice garbage collection run at the start of the consistency checking process; the creation of the hidden database snapshot starts with a checkpoint, which is what triggers FILESTREAM garbage collection. The suspension of garbage collection activity allows the consistency-checking algorithms to see a transactionally consistent view of the FILESTREAM data on any FILESTREAM data containers

Alternatives to using a database snapshot

A database snapshot isn’t required under the following conditions.

• The specified database is a database snapshot itself.
• The specified database is read-only, in single-user mode, or in emergency mode.
• The server was started in single-user mode with the –m command-line option.

In these cases, the database is already essentially consistent because no other active connections can be making changes that would break the consistency checks.
A database snapshot can’t be created under the following conditions.

• The specified database is stored on a non–NTFS file system, in which case a database snapshot can’t be created because it relies on NTFS sparse-file technology.
• The specified database is tempdb, because a database snapshot can’t be created on tempdb.
• The TABLOCK option was specified.

If a database snapshot can’t be created for any reason, DBCC CHECKDB attempts to use locks to obtain a transactionally consistent view of the database. First, it obtains a database-level exclusive lock so that it can perform the allocation consistency checks without any changes taking place. Offline consistency checks can’t be run on master or on tempdb because these databases can’t be exclusively locked. This means that allocation consistency checks are always skipped for tempdb (as was usually the case with SQL Server 2000, too). This also isn’t possible if the database is an Availability Group replica, in which case error 7934 is reported if the database snapshot creation fails.
Rather than wait for the exclusive lock indefinitely (or whatever the server lock timeout period has been set to), DBCC CHECKDB waits for 20 seconds (or the configured lock timeout value for the session) and then exits with the following error:

DBCC CHECKDB ('msdb') WITH TABLOCK;
GO
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

If the lock was acquired after the allocation checks are completed, the exclusive lock is dropped and table-level share locks are acquired while the table-level logical consistency checks are performed. The same time-out applies to these table-level locks.
One way or another, DBCC CHECKDB obtains a transactionally consistent view of the database that it’s checking. After that, it can start processing the database.





Reference:Microsoft SQL Server 2012 Internals

Connect to SQL Server with Windows Authentication from others Windows desktop

If you want to use your windows credentials to connect to server from your colleagues desktop in process of fixing/debugging a issue.

Try this Steps

• Press SHIFT button 
• Right CLICK on the SQL Server Management Studio button
• Select "Run as different user" and give your windows credential to connect to the server.

Cannot specify a log file in a CREATE DATABASE statement without also specifying at least one data file.

Error Message:Msg 188, Level 15, State 1, Line 2Cannot specify a log file in a CREATE DATABASE statement without also specifying at least one data file.

Try this Solution:

Error of the Severity level 15 are generated by the user and can be resolved by the users. Just remove either the log file(.ldf) specification from Create database Script or specify at least one data file(.mdf).

Script level upgrade for database ‘master’ failed because upgrade step ‘u_tables.sql’ encountered error 25641, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Script level upgrade for database ‘master’ failed because upgrade step ‘u_tables.sql’ encountered error 25641, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Try this Solution : 

Nothing could be found in SQL Error logs.Check the service account of SQL Server, find out whether it has enough permissions to run SQLServer. If you are not able to change the Service acccounts permissions immediately and wants to come out of this Service Pack Installtion errors.  Change Service account for SQL Server to Local System. SQL Server DB Engine Service will be started and the build number will be updated too. Can revert your SQL Servers Service account back to Original from Local System.

Tempdb is skipped. You cannot run a query that requires Tempdb

tempdb is skipped. You cannot run a query that requires tempdb

You Might have applied a patch that required the database server to be rebooted during a scheduled outage. The application on the web server might have connections cached to the database, which were invalid at that point. In the .Net implementation of connections, they only support the statuses Open and Closed, and Broken . So it boils down to the connection trying to use resources that are no longer there, but that doesn't explain this specific message. 

Solution: At this point the solution is to restart IIS/Reboot the box. 

Procedures

To restart IIS using IIS Manager

1. In IIS Manager, right click the local computer, point to All Tasks, then click Restart IIS.

2. In the What do you want IIS to do list, click Restart Internet Services on computer name.

3. IIS attempts to stop all services before restarting. IIS waits up to five minutes for all services to stop. If the services cannotbe stopped within five minutes, all IIS services are terminated, and IIS restarts. In addition, clicking End now forces all IIS services to stop immediately, and IIS is restarted.

What is IIS?

Definition - What does Internet Information Services (IIS) mean?

Internet Information Services (IIS), formerly known as Internet Information Server, is a web server producted by Microsoft. IIS is used with Microsoft Windows OSs and is the Microsoft-centric competition to Apache, the most popular webserver used with Unix/Linux-based systems.
Techopedia explains Internet Information Services (IIS)

IIS was initially released for Windows NT and, along with ASP (Active-Server Pages), finally made a Windows-box a usable alternative for web-hosting. That being said, it was also noted for being completely wide-open out of the box and required significant configuration to be made secure. 

This changed with later releases, and IIS is now generally considered by many to be a stable and usable product. As of 2011, the most current version is IIS 7, which includes pretty much all modern features you'd expect to see in a webserver, including tight integration to ASP.NET. Though, as with any Microsoft vs Linux debate, some would argue that Apache is the only way to go.

Exception deserializing the package "Access to the path is denied

ERROR:

TITLE: Microsoft Visual Studio
------------------------------

Failed to start project

------------------------------
ADDITIONAL INFORMATION:

Exception deserializing the package "Access to the path 'G:\VisualStudio\bin\Development\MySSISPackage.ispac' is denied.". (Microsoft.DataTransformationServices.VsIntegration)

------------------------------

Access to the path 'G:\VisualStudio\bin\Development\MySSISPackage.ispac' is denied. (mscorlib)

------------------------------
BUTTONS:

OK
------------------------------ 

Try this :

1. ExitData Tools
2. Open Task Manager and end any processes of DTSDebugHost
3. Delete the ISPAC file
4. Open Data Tools and rebuild the package

Is it is possible to Limit the number of ErrorLog Files in SQL Server

Identify SQL Server Error Log File used by SQL Server Database Engine by Reading SQL Server Error Logs

SELECT SERVERPROPERTY('ErrorLogFileName')

Is it possible to limit the number of errorlog files less then 6 ?

Nope. Minimum number to configure is 6. We can delete all the archived errorlog files from server if we want but  ensure they are not required for auditing purposes and such.

we can set it to 6 and then create a scheduled job that simply calls

EXEC sp_cycle_errorlog

The more often we run this, the smaller error log files will have and will keep it to 6 error log files.

 clean up message by reviewing what messages are appearing. For example if it is "successful backup" messages you can enable trace flag 3226 globally for that instance and this will suppress those messages from being written all the time. Outside of that it is up to us to determine what is writing the message and figure out how to clean that up. An example of this that is most common is "failed login" messages for rogue service or application on a remote server.pplication on a remote server.

GRANT ALL in SQL Server

A GRANT ALL syntax also exists, granting supposedly all the permissions
on a securable. But it is better not to use it, because it does not in fact
grant all permissions, only the ones defined in the SQL-92 ANSI standard.
More permissions are available for SQL Server objects than the permissions
defined in the ANSI standard. The GRANT ALL syntax is now deprecated.

• If the securable is a database, "ALL" means BACKUP DATABASE, BACKUP LOG, CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, and CREATE VIEW.
• If the securable is a scalar function, "ALL" means EXECUTE and REFERENCES.
• If the securable is a table-valued function, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
• If the securable is a stored procedure, "ALL" means EXECUTE.
• If the securable is a table, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
• If the securable is a view, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

List of Permissions and their Description in SQL Server

Permission name	Description
ALTER	Permission to modify the object's definition
CONNECT	Permission to access the database or connect to the endpoint
DELETE	Permission to delete the object
EXECUTE	Permission to execute the stored procedure or the function
IMPERSONATE	Permission to take the identity of a principal, by the means of an EXECUTE AS command
INSERT	Permission to insert data into the table or view
REFERENCES	Permission to reference the object in a foreign key definition, or to declare a view or function WITH SCHEMABINDING referencing the object
SELECT	Permission to issue a SELECT command against the object or column
TAKE OWNERSHIP	Permission to become the owner of the object
UPDATE	Permission to update the data
VIEW DEFINITION	Permission to view the definition (structure) of the object

Script to find what Permissions apply to what class of securables in SQL Server

SELECT *
FROM sys.fn_builtin_permissions(DEFAULT)
ORDER BY class_desc

Protect SQL Server against brute-force attacks

Protect SQL Server from Brute Force attacks

A brute force attack is a trial-and-error method used to obtain information such as a user password or personal identification number (PIN). In a brute force attack, automated software is used to generate a large number of consecutive guesses as to the value of the desired data. Brute force attacks may be used by criminals to crack encrypted data, or by security analysts to test an organization's network security. 

A brute force attack may also be referred to as brute force cracking.



In SQL Server brute force is a way to crack SQL login passwords by trying every possible letter combination of letters without knowing the password

• Identify the SQL passwords that are not enforced by policies

SELECT NAME
 ,is_disabled
FROM sys.sql_logins
WHERE is_policy_checked = 0
ORDER BY NAME;

All the logins listed in the output  might have a weak password.

• Enforce policy to those logins

ALTER LOGIN MyLogin
 WITH CHECK_POLICY = ON
  ,CHECK_EXPIRATION = ON;

This code modifies the MyLogin login to enforce password policy and expiration.
Check when the password is set to expire with the following script :

SELECT LOGINPROPERTY('MyLogin', 'DaysUntilExpiration');

• Force the login to change password with the MUST_CHANGE option

If you want to enforce it immediately change the password and communicate the password to
corresponding user:

ALTER LOGIN MyLogin
 WITH PASSWORD = 'ch@nge y0ur Pa$$word !' MUST_ CHANGE
  ,CHECK_POLICY = ON
  ,CHECK_EXPIRATION = ON;

• Generate the script for all the necessary logins:

SELECT 'ALTER LOGIN ' + QUOTENAME(NAME) + ' WITH PASSWORD = ''ch@nge y0ur Pa$$word !'' MUST_CHANGE, CHECK_POLICY = ON, CHECK_
EXPIRATION = ON;
'
FROM sys.sql_logins
WHERE is_policy_checked = 0
ORDER BY NAME;

Note:The best way to protect passwords against brute-force attacks is to enforce Windows
password policies and expiration, because this will guarantee passwords are strong enough from being guessed. This attacks leaves the traces in SQL Server Error log.

Script to convert a database to contained database in SQL Server

What is Contained Database in SQL Server 2012?

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server 2014 helps user to isolate their database from the instance in 4 ways.
Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)
All metadata are defined using the same collation.
User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.
The SQL Server environment (DMV's, XEvents, etc.) reports and can act upon containment information.


To Set-up  contained databases  is a simple process which involves the following being carried out within SSMS:-

sp_configure 'contained database authentication'
 ,1
GO

RECONFIGURE

We can convert a non contained database to a contained database simply by setting its CONTAINMENT property,

USE [master]
GO

ALTER DATABASE [marketing]

SET CONTAINMENT = PARTIAL;

The users mapped to SQL logins can be converted to contained database users, using the sp_migrate_user_to_contained system
procedure 

SELECT 'EXEC sp_migrate_user_to_contained @username = N''' + dp.NAME + ''',
@rename = N''keep_name'',
@disablelogin = N''do_not_disable_login'' ;'
FROM sys.database_principals AS dp
INNER JOIN sys.server_principals AS sp ON dp.sid = sp.sid
WHERE dp.authentication_type = 1
 AND sp.is_disabled = 0;

This code returns execute statements copy it and execute

A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.


If you move a non-contained database from one server to another, by means of backup/
restore or detach/attach, then there is a chance that your SQL users will become
orphaned, meaning that they will have no corresponding login. As the mapping between logins
and users is done by the SID, if a login is present on the destination instance with the same
name but another SID, then the user will not recognize it and will be orphaned.
If you are moving the database to another server in the same domain, the
user to login mapping problem occurs only with SQL logins, because the SID
used for Windows logins is the same as the domain SID set in Active Directory.
Thus it is the same on every instance where this login is created.

Preventing logins and users to see metadata in SQL Server

To hide databases to all LOGINS, remove/revoke "VIEW ANY DATABASE" permission from the public server role

USE master;
GO

REVOKE VIEW ANY DATABASE
 TO PUBLIC;

To allow only some logins to view all databases, Jus create a user-defined server role

USE master;

CREATE SERVER ROLE [DBViewer];
GO

GRANT VIEW ANY DATABASE
 TO [DBViewer];

ALTER SERVER ROLE [DBViewer] ADD MEMBER [MyLogin];

This code creates a server role named DBViewer and grants the
VIEW ANY DATABASE permission to it. It then adds the login MyLogin to it.

Note: MASTER and TEMPDB will always be visible to all logins,We cannot make them invisible.

In Database Mirroring Configuration you might encounter with following error:
Msg 1418, Level 16, State 1, Line 1
The server network address "%.*ls" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.


Run the command:
ALTER DATABASE TestDBMirror SET PARTNER = 'TCP://HostName:5050'

You might notice that server network endpoint will not be responding because the specified server network address cannot be reached or does not exist.

Solution.

1. Verify Status of ENDPOINTS. ENDPOINTS should have the status started.
      select state_desc from sys.database_mirroring_endpoints

2. Accounts running  SQL Server services on both machine should be same
       Verify it thru SQL Server Configuration Manager

3. Enable Named Pipe protocols
    Verify it thru SQL Server Configuration Manager  

4. The server should not be behind a firewall.
            ping servername in cmd

5.  The principal server instance and mirror instance should be listening on the same ports and must be available.

6. Enable Named Pipe protocols
    Verify it thru SQL Server Configuration Manager

7. The usernames to run SQL Server services have NT Auth Access between both machines.

CREATE FILE encountered operating system error 5 (Access is denied.)

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file... (Microsoft SQL Server, Error 5123)


Try to open SSMS (Sql Server Management Studio) with Run as ADMINISTRATOR, and then try again to then try again to attach

For More info:  http://www.mssqltips.com/sqlservertip/2528/database-attach-failure-in-sql-server-2008-r2/

What is dbo in SQL Server?

dbo stands for DataBase Owner. It is a special database user that maps to the owner of the
database. When created, a database is assigned an owner, which is the login that created it.
You can query the database owner using one of the following queries:

SELECT SUSER_SNAME(owner_sid)
 ,NAME
FROM sys.databases;

-- or :
SELECT SUSER_SNAME(sid)
FROM sys.database_principals
WHERE principal_id = USER_ID('dbo');

This login is automatically mapped to the dbo special user, and thus is granted all
permissions in the database. You can change the owner of a database by using the
following command:

ALTER AUTHORIZATION ON DATABASE::marketing TO sa;

This will change the owner of the marketing database to the sa login.

SQl Server "How is My Memory Being used" Performance Counters"

Reference: http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf

Script to obtain SQL feature list from the command line

The components for SQL Server 2012 are divided by server and then management tools. You can get the server level components by using a PowerShell command like:

Get - Service * SQL 

To get the management tools would require either registry search for the uninstall list or you can query the WMI class win32_product:

get - wmiobject win32_product |
WHERE {$_.NAME - match "SQL" -
 AND $_. vendor - eq "Microsoft Corporation" } |

SELECT NAME
 ,version

Reference:  http://dba.stackexchange.com/questions/77016/how-to-obtain-sql-feature-list-from-the-command-line

Upgrading the Integration Services in SQL Server from one edition to other

Upgrade Integration Services

SQL Server 2014

If SQL Server 2005 Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS) is currently installed on your computer, you can upgrade to SQL Server 2014 Integration Services (SSIS).

When you upgrade to SQL Server 2014 Integration Services (SSIS) on a machine that has one of these earlier versions of Integration Services installed, SQL Server 2014 Integration Services (SSIS) is installed side-by-side with the earlier version.

With this side-by-side install, multiple versions of dtexec utility are installed. To ensure that you run the correct version of the utility, at the command prompt run the utility by entering the full path (<drive>:\Program Files\Microsoft SQL Server\<version>\DTS\Binn). For more information about dtexec, see dtexec Utility.

Before Upgrading Integration Services

We recommended that you run Upgrade Advisor before you upgrade to SQL Server 2014. Upgrade Advisor reports issues that you might encounter if you migrate existing Integration Services packages to the new package format that SQL Server 2014 uses. For more information, see Use Upgrade Advisor to Prepare for Upgrades.

Upgrading Integration Services

You can upgrade by using one of the following methods:

• Run SQL Server 2014 Setup and select the option to Upgrade from SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2, or SQL Server 2012.
• Run setup.exe at the command prompt and specify the /ACTION=upgrade option. For more information, see the section, "Installation Scripts for Integration Services," in Install SQL Server 2014 from the Command Prompt.

You cannot use upgrade to perform the following actions:

• Reconfigure an existing installation of Integration Services.
• Move from a 32-bit to a 64-bit version of SQL Server or from a 64-bit version to a 32-bit version.
• Move from one localized version of SQL Server to another localized version.

When you upgrade, you can upgrade both Integration Services and the Database Engine, or just upgrade the Database Engine, or just upgrade Integration Services. If you upgrade only the Database Engine, SQL Server 2005 Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS) remains functional, but you do not have the functionality of SQL Server 2014 Integration Services (SSIS). If you upgrade only Integration Services, SQL Server 2014 Integration Services (SSIS) is fully functional, but can only store packages in the file system, unless an instance of the SQL Server 2014 Database Engine is available on another computer.

Upgrading Both Integration Services and the Database Engine to SQL Server 2014

This section describes the effects of performing an upgrade that has the following criteria:

• You upgrade both Integration Services and an instance of the Database Engine to SQL Server 2014.
• Both Integration Services and the instance of the Database Engine are on the same computer.

What the Upgrade Process Does

The upgrade process does the following tasks:

• Installs the SQL Server 2014 Integration Services (SSIS) files, service, and tools (Management Studio and SQL Server Data Tools). When there are multiple instances of SQL Server 2005 or SQL Server 2008 on the same computer, the first time you upgrade any of the instances to SQL Server 2014, the SQL Server 2014 Integration Services (SSIS) files, service, and tools are installed.
• Upgrades the instance of the SQL Server 2005 or the SQL Server 2008 Database Engine to the SQL Server 2014 version.
• Moves data from the SQL Server 2005 Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS) system tables to the SQL Server 2014 Integration Services (SSIS) system tables, as follows:
• Moves packages without change from the msdb.dbo.sysdtspackages90 system table to the msdb.dbo.sysssispackages system table.
• Moves folder metadata from the msdb.sysdtsfolders90 system table to the msdb.sysssisfolders system table.
• Moves log data from the msdb.sysdtslog90 system table to the msdb.sysssislog system table.
• Removes the msdb.sysdts*90 system tables and the stored procedures that are used to access them after moving the data to the new msdb.sysssis* tables. However, upgrade replaces the sysdtslog90 table with a view that is also named sysdtslog90. This new sysdtslog90 view exposes the new msdb.sysssislog system table. This ensures that reports based on the log table continue to run without interruption.
• To control access to packages, creates three new fixed database-level roles: db_ssisadmin, db_ssisltduser, and db_ssisoperator. The SQL Server 2005 Integration Services roles of db_dtsadmin, db_dtsltduser, and db_dtsoperator are not removed, but are made members of the corresponding new roles.
• If the SSIS package store (that is, the file system location managed by the Integration Services service) is the default location under \SQL Server\90, \SQL Server\100, or \SQL Server\110 moves those packages to the new default location under \SQL Server\120.
• Updates the Integration Services service configuration file to point to the upgraded instance of the Database Engine.

What the Upgrade Process Does Not Do

The upgrade process does not do the following tasks:

• Does not remove the SQL Server 2005 Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS) service.
• Does not migrate existing Integration Services packages to the new package format that SQL Server 2014 uses. For information about how to migrate packages, see Upgrade Integration Services Packages.
• Does not move packages from file system locations, other than the default location, that have been added to the service configuration file. If you have previously edited the service configuration file to add more file system folders, packages that are stored in those folders will not be moved to a new location.
• In SQL Server Agent job steps that call the dtexec utility (dtexec.exe) directly, does not update the file system path for the dtexec utility. You have to edit these job steps manually to update the file system path to specify the SQL Server 2014 location for the dtexec utility.

What You Can Do After Upgrading

After the upgrade process finishes, you can do the following tasks:

• Run SQL Server Agent jobs that run packages.
• Use Management Studio to manage Integration Services packages that are stored in an instance of SQL Server 2008 or SQL Server 2014. You need to modify the service configuration file to add the instance of SQL Server 2008 to the list of locations managed by the service.
• Identify the version of packages in the msdb.dbo.sysssispackages system table by checking the value in the packageformat column. The table has a packageformat column that identifies the version of each package. A value of 2 in the packageformat column indicates a SQL Server 2005 Integration Services (SSIS) package; a value of 3 indicates a SQL Server 2008 Integration Services (SSIS) package. Until you migrate packages to the new package format, the value in the packageformat column does not change.
• You cannot use the SQL Server 2005 or SQL Server 2008 tools to design, run, or manage Integration Services packages. The SQL Server 2005 and SQL Server 2008 tools include the respective versions of SQL Server Data Tools (SSDT), the SQL Server Import and Export Wizard, and the Package Execution Utility (dtexecui.exe). The upgrade process does not remove the SQL Server 2005 or SQL Server 2008 tools. However, you will not able to use these tools to continue to work with SQL Server 2005 Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS) packages on a server that has been upgraded.
• By default, in an upgrade installation, Integration Services is configured to log events that are related to the running of packages to the Application event log. This setting might generate too many event log entries when you use the Data Collector feature of SQL Server 2014. The events that are logged include EventID 12288, "Package started," and EventID 12289, "Package finished successfully." To stop logging these two events to the Application event log, open the registry for editing. Then in the registry, locate the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\120\SSIS node, and change the DWORD value of the LogPackageExecutionToEventLog setting from 1 to 0.

Upgrading only the Database Engine to SQL Server 2014

This section describes the effects of performing an upgrade that has the following criteria:

• You upgrade only an instance of the Database Engine. That is, the instance of the Database Engine is now an instance of SQL Server 2014, but the instance of Integration Services and the client tools are from SQL Server 2005 or SQL Server 2008.
• The instance of the Database Engine is on one computer, and Integration Services and the client tools are on another computer.

What You Can Do After Upgrading

The system tables that store packages in the upgraded instance of the Database Engine are not the same as those used in SQL Server 2005 or SQL Server 2008. Therefore, the SQL Server 2005 or SQL Server 2008 versions of Management Studio and SQL Server Data Tools cannot discover the packages in the system tables on the upgraded instance of the Database Engine. Because these packages cannot be discovered, there are limitations on what you can do with those packages:

• You cannot use the SQL Server 2005 or SQL Server 2008 tools, Management Studio and SQL Server Data Tools, on other computers to load or manage packages from the upgraded instance of the Database Engine. 
• You cannot use SQL Server 2005 Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS) on other computers to run packages that are stored in msdb on the upgraded instance of the Database Engine.
• You cannot use SQL Server Agent jobs on SQL Server 2005 or SQL Server 2008 computers to run SQL Server 2005 Integration Services (SSIS) or SQL Server 2008 Integration Services (SSIS) packages that are stored in the upgraded instance of the Database Engine.

Reference :  http://msdn.microsoft.com/en-us/library/cc879336.aspx

Script to compare settings of two DB servers in SQL Server

USE master;
GO

EXEC sp_configure 'show advanced option'
 ,'1';

RECONFIGURE;

EXEC sp_configure;