Author: Steve M

SSIS 2008 R2 to SSIS 2014

Notes:

After performing an in-place upgrade on a SQL 2008 R2 server to SQL 2014, l had access issue to SSIS 2014.

I also noticed [rom SSCM SQL Server Configuration Mananger] that SSIS 2008 R2 ie SSIS version 10.0 was still running, using the NT Autohoritynetworkservice account while SSIS 2014 ie version 12.0 was also running – but using the nt servicemsdtsserver120 local account.

From a remote SSMS 2008 R2 window, l could access the target SSIS instance and it shows up with version 10.50.4000 [the number for SSIS 2008 R2]

From the local upgraded server’s SSMS 2014 , connecting to the target SSIS 2014 shows a connection with version number 12.0.2000

Steps taken to resolve the initial access issue.

 

ssis_12_issue

In-place upgrade to Microsoft SQL Server 2014

In some situations you might want to perform an in-place upgrade from say SQL 2008 R2 to SQL 2014, rather than a fresh installation.

First thing to do is to run the Install upgrade advisor, this can be sound by running the setup.exe in the SQL 2014 installation disk, selecting Install Upgrade Advisor from the planning menu.

sql5_prep2

You might get an error as shown below. To solve this, run/install components for SQL 2014 which can be found by clicking here.

sql5_upgrade_2014

After a restart, you should be able to run the install upgrade advisor, which will be accessible from Start/PRograms/SQL 2014/SQL Server 2014 Upgrade Advisor

sql5_upgrade_2014b

SQL Server Mirroring

This post will describe steps for setting up SQL Server 2012 mirroring.

Note: Microsoft recommends focusing on Always On for high availability as database mirroring will most likely be removed from MS SQL Server in future.

Setup – on my laptop [called K9] with 3 SQL instances, default [primary], MIR instance and the secondary/target is called SVR12:

m1

Backup on primary instance – database been used for this: AdventureWorks2012

BACKUP DATABASE [AdventureWorks2012]
TO DISK = N’d:AW2012.bak’ WITH NOFORMAT, NOINIT,
NAME = N’AdventureWorks2012-Full Database Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Create same login on all 3 instances:

USE [master]
GO
CREATE LOGIN [k9sqluser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

Restore the backup on SVR12 instance – the secondary/target instance:

USE [master]
RESTORE DATABASE [AdventureWorks2012]
FROM DISK = N’D:AW2012.bak’ WITH FILE = 1,
MOVE N’AdventureWorks2012_Data’
TO N’C:Program FilesMicrosoft SQL ServerMSSQL11.SVR12MSSQLDATAAW2012mir.mdf’,
MOVE N’AdventureWorks2012_Log’ TO N’d:Program FilesMicrosoft SQL ServerMSSQL11.SVR12MSSQLDATAAW2012mir.ldf’,
NORECOVERY, NOUNLOAD, STATS = 5

GO

on Primary, right click on the database, tasks, mirroring

below shapshots show the next steps.

m3

m2

m4 m5 m6 m7 m8 m9 m10 m11 m12

 

 

 

 

Powershell – Stopping a Microsoft Windows Service

In this example, l will attempt to get and stop a windows service for say, SQLdefragmanagementservice.
First open a windows powershell console, using the run as administrator below.
Use the get-process to get the process id.
Use the stop-process to kill the windows process/service.
Then run the code shown in the screenshot below, modify as applicable.

getstopprocess

The database owner SID recorded

Issue:

A restored database from one instance to the other keeps showing below when attempting to say, alter a stored procedure.

The database owner SID recorded in the master database differs from the database owner SID recorded in database

Solution applied: Assuming XYZ is the name of the database and contosoadmin is the name of the sa or sysadmin domain account usually assigned as owner in the destination SQL instance. contoso here represents the domain name.

Run:

ALTER DATABASE XYZ SET TRUSTWORTHY ON

ALTER AUTHORIZATION ON Database::archive TO [contosoadmin]

Microsoft SQL Server Merge Replication Client Status Information

Below is a select statement which can be placed in a view, for providing information on core merge replication status information usually seen using Microsoft SQL Server Replication Monitor GUI tool.

SELECT TOP 1000 [subscriber_server]
,[db_name]
,SubStatus =
CASE [status]
WHEN 0 THEN ‘Inactive’
WHEN 1 THEN ‘Active’
WHEN 2 THEN ‘Deleted’
ELSE ‘N/A’
END
,convert(nvarchar(100), [last_sync_date], 100) as LastSyncedOn
,LastSyncStatus =
CASE [last_sync_status]
WHEN 0 THEN ‘All jobs are waiting to start’
WHEN 1 THEN ‘One or more jobs are starting’
WHEN 2 THEN ‘All jobs have executed successfully’
WHEN 3 THEN ‘At least one job is executing’
WHEN 4 THEN ‘All jobs are scheduled and idle’
WHEN 5 THEN ‘At least one job is attempting to execute after a previous failure’
WHEN 6 THEN ‘At least one job has failed to execute successfully’
ELSE ‘N/A’
END
,[last_sync_summary]
,ReplicationVersion =
CASE [replica_version]
WHEN 90 THEN ‘SQL Server 2005’
WHEN 100 THEN ‘SQL Server 2008’
ELSE ‘N/A’
END

FROM [MyDatabase].[dbo].[sysmergesubscriptions]
where subscriber_type=2
order by [last_sync_date] asc

Below might be needed as well for non-sysadmins

grant select on [sysmergesubscriptions] to public

More details on the system table used can be found by clicking here.