Package configuration option missing in SSDT while working SSIS Package

I am creating a SSIS package to create dynamic connection. There is a step to enable Configuration files for your package. But I didn't find the Package configuration option,

I tried in Menu --> SSIS  & Control Flow designer for my package expecting to see the Package Configurations option on the menu.  The option was not there

There is a work around in for this

 In Package Properties window there is an option for Configurations in Misc category

click on ellipsis to see package configuration organiser 

Click on enable package configuration Check box to enable Package Configuration 

TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

2015-02-17 12:27:38.19 spid15s     Error: 26014, Severity: 16, State: 1.

2015-02-17 12:27:38.19 spid15s     Unable to load user-specified certificate [Cert Hash(sha1) "5B4FF9FFF4E6752A3AD51489E1A9C455E580BCC3"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.

2015-02-17 12:27:38.19 spid15s     Error: 17182, Severity: 16, State: 1.

2015-02-17 12:27:38.19 spid15s     TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property. 

2015-02-17 12:27:38.19 spid15s     Error: 17182, Severity: 16, State: 1.

2015-02-17 12:27:38.19 spid15s     TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property. 

2015-02-17 12:27:38.19 spid15s     Error: 17826, Severity: 18, State: 3.

2015-02-17 12:27:38.19 spid15s     Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2015-02-17 12:27:38.19 spid15s     Error: 17120, Severity: 16, State: 1.

2015-02-17 12:27:38.19 spid15s     SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Event ID 17182: TDSSNIClient initialization failed with error 0x7e, status code 0x3a.

Event ID 17826: Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

Event ID 17120: SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Solution :

1. Check 'Forced Encryption' (Turned False) and 'Certificate' value is cleared under SQL Server Configuration Manager.
2> Check the following registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\SuperSocketNetLib\Certificate
and clear the Certificate values.

Error messages when restoring from different versions of SQL Server.

Error messages when restoring from different versions of SQL Server.

We can't restore the backups taken in Newer version of SQL Server instance on older version of SQL Server. 

SQL Server 2008 R2 to SQL Server 2008

Msg 3169, Level 16, State 1, Line 1

The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.1600. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

SQL Server 2008 R2 to SQL Server 2005

Server: Msg 3241, Level 16, State 7, Line 1

The media family on device 'f:\temp\test001_sql2008r2.bak' is incorrectly formed. SQL Server cannot process this media family.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

SQL Server 2008 R2 to SQL Server 2000

Server: Msg 3169, Level 16, State 1, Line 1

The backed-up database has on-disk structure version 661. The server supports version 539 and cannot restore or upgrade this database.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

SQL Server 2008 to SQL Server 2005

Server: Msg 3241, Level 16, State 7, Line 1

The media family on device 'f:\temp\test001_sql2008.bak' is incorrectly formed. SQL Server cannot process this media family.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

SQL Server 2008 to SQL Server 2000

Server: Msg 3169, Level 16, State 1, Line 1

The backed-up database has on-disk structure version 655. The server supports version 539 and cannot restore or upgrade this database.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

SQL Server 2005 to SQL Server 2000

Server: Msg 3169, Level 16, State 1, Line 1

The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.

Server: Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

SQL Server installation methods

There are three types of Installation methods 

1. Local/Standard installation

2. Unattended installation 

3. Remote Installation 

Local/Standard installation

To perform standard installation you need to login into the machine and the login is part of local administrator group. 

Unattended installation 

It is also known as silent installation. Install multiple SQL Servers with identical configurations without using the interactive SQL Server setup is known as Unattended installation. 

Installation initiates from command prompt ( batch file will call setup file).

All the parameters are saved in configuration file (setup.exe) 

An unattended installation using a configuration file using the following command line at a new command prompt.

D:\SQLFULL_x86_ENU\Setup.exe /ConfigurationFile=D:\SQLFULL_x86_ENU\SQLConfigurationFile.ini

 

Remote Installation

You can install SQL Server from remote machine also. 

To perform remote installation the user account should have following permissions 

1. Have administrative rights on the remote computer. 

2. Have read access to the setup source files folder.

Rename the sql server database

There are couple of options are available to rename the database. 

Option 1 : 

 Rename the "MyDATABASE" to "MyDATABASE_NEW" using sp_renamedb command

 EXEC sp_renamedb 'MyDATABASE', 'MyDATABASE_NEW' 

Option 2:

 Rename the "MyDATABASE" to "MyDATABASE_NEW" using ALTER DATABASE Commnad

USE [master]

GO

ALTER DATABASE [MyDATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE [MyDATABASE] MODIFY NAME = MyDATABASE_NEW

GO

ALTER DATABASE [MyDATABASE_NEW] SET MULTI_USER

GO

SQL Script to Change the database to read only and read write

Below script is used to change the database into single user mode. To kill the current connections change the database to single user mode then change it to single user mode.Replace the database name from MyDATABASE to the database name need to change to READ ONLY

USE [master]

GO

ALTER DATABASE [MyDATABASE]  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

 ALTER DATABASE [MyDATABASE] SET READ_ONLY WITH NO_WAIT

GO

ALTER DATABASE [MyDATABASE] SET MULTI_USER

GO

Below script is used to change the database into read/write user mode. 

 ALTER DATABASE [MyDATABASE] SET READ_WRITE WITH NO_WAIT

SQL Server Job history

select job_name, run_datetime, run_duration

from

(

    select job_name, run_datetime,

        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +

        SUBSTRING(run_duration, 5, 2) AS run_duration

    from

    (

        select DISTINCT

            j.name as job_name, 

            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  

                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,

            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)

        from msdb..sysjobhistory h

        inner join msdb..sysjobs j

        on h.job_id = j.job_id

    ) t

) t

where job_name = 'Job name '

order by job_name, run_datetime

--- Query results are like below