Move Report Server database to different server

I have been running into many issues while moving ReportServer database between servers and all the necessary information on the issues I face is not available at a single place. So I thought of putting all together so people can find all the data to move ResportServer database at a single source (my blog :) ). I am going to put the regular simple tasks simple.

1.      Backup the encryption key of the reporting services using Report Server Configuration Manager on the source.

2.      Detach the ReportServer database on source and copy the files to the destination server.

3.      Attach the ReportServer database on the destination SQL Server.

4.      You can also backup and restore the database from source to destination.

5.      While configuring the reporting services select the restore option on the encryption key tab in the Reporting Services Configuration Services.

6.      When you configure the reporting services on Standard edition you get the below message.

The feature: "Scale-out deployment" is not supported in this edition of Reporting Services. (rsOperationNotSupported) (rsRPCError)

7.      Run the below commands to fix the scale-out deployment issue.

1.      Open the cmd prompt window and run below command to determine the GUID referring the server you want to remove.

rskeymgmt –l

2.      Remove the GUID by running the below command.

rskeymgmt -r "src_id"

8.      You may want to check this option even if you are on Enterprise edition as you do not want your Reporting services to be a scale-out deployment until it is really the way you designed your environment.

 Happy Troubleshooting,

-Manu

The Instance ID 'MSSQLSERVER' is already in use by SQL Server instance 'MSSQLSERVER.INACTIVE'.

This content has been moved to http://www.sqlservergeeks.com/blogs/manu0417/personal/785/upgrade-fails-the-instance-id-mssqlserver-is-already-in-use-by-sql-server-instance-mssqlserver-inactive

Error 1706. Setup cannot find the required files. Check your connection to the network, or CD-ROM drive. For other potential solutions to this problem, see c:\Program Files (x86)\Microsoft Office\OFFICE11\1033\SETUP.CHM.

I was sitting in the class and my friend was trying to install SQL server on his laptop but it was continuously failing. He has a windows laptop which came with small business server installed with it. I started the setup with my magic hands and it failed with the below error.

Error 1706. Setup cannot find the required files.  Check your connection to the network, or CD-ROM drive.  For other potential solutions to this problem, see c:\Program Files (x86)\Microsoft Office\OFFICE11\1033\SETUP.CHM.

Ok, even with magic hands we cannot do magic with SQL setup. So I started with the first step of checking the setup logs. Started with SQLSetup003_SystemName_OWC11_1.log. The setup was failing as it was not able to validate the MSI file as per the below message in the log file.

MSI (s) (64:3C) [14:22:07:275]: SOURCEMGMT: Looking for sourcelist for product {90A40409-6000-11D3-8CFE-0150048383C9}

MSI (s) (64:3C) [14:22:07:275]: SOURCEMGMT: Adding {90A40409-6000-11D3-8CFE-0150048383C9}; to potential sourcelist list (pcode;disk;relpath).

MSI (s) (64:3C) [14:22:07:275]: SOURCEMGMT: Now checking product {90A40409-6000-11D3-8CFE-0150048383C9}

MSI (s) (64:3C) [14:22:07:275]: SOURCEMGMT: Attempting to use LastUsedSource from source list.

MSI (s) (64:3C) [14:22:07:275]: SOURCEMGMT: Trying source c:\windows\temp\mount\204836\OWC11\.

MSI (s) (64:3C) [14:22:07:291]: Note: 1: 2203 2: c:\windows\temp\mount\204836\OWC11\OWC11.MSI 3: -2147287037

MSI (s) (64:3C) [14:22:07:291]: SOURCEMGMT: Source is invalid due to missing/inaccessible package.

MSI (s) (64:3C) [14:22:07:291]: Note: 1: 1706 2: -2147483647 3: OWC11.MSI

MSI (s) (64:3C) [14:22:07:291]: SOURCEMGMT: Processing net source list.

MSI (s) (64:3C) [14:22:07:291]: Note: 1: 1706 2: -2147483647 3: OWC11.MSI

MSI (s) (64:3C) [14:22:07:291]: SOURCEMGMT: Processing media source list.

MSI (s) (64:3C) [14:22:07:322]: Note: 1: 2203 2:  3: -2147287037

MSI (s) (64:3C) [14:22:07:322]: SOURCEMGMT: Source is invalid due to missing/inaccessible package.

MSI (s) (64:3C) [14:22:07:322]: Note: 1: 1706 2: -2147483647 3: OWC11.MSI

MSI (s) (64:3C) [14:22:07:322]: SOURCEMGMT: Processing URL source list.

MSI (s) (64:3C) [14:22:07:322]: Note: 1: 1402 2: UNKNOWN\URL 3: 2

MSI (s) (64:3C) [14:22:07:322]: Note: 1: 1706 2: -2147483647 3: OWC11.MSI

MSI (s) (64:3C) [14:22:07:322]: Note: 1: 1706 2:  3: OWC11.MSI

MSI (s) (64:3C) [14:22:07:322]: SOURCEMGMT: Failed to resolve source

MSI (s) (64:3C) [14:23:01:042]: Product: Microsoft Office 2003 Web Components -- Error 1706. Setup cannot find the required files.  Check your connection to the network, or CD-ROM drive.  For other potential solutions to this problem, see c:\Program Files (x86)\Microsoft Office\OFFICE11\1033\SETUP.CHM.

Error 1706. Setup cannot find the required files.  Check your connection to the network, or CD-ROM drive.  For other potential solutions to this problem, see c:\Program Files (x86)\Microsoft Office\OFFICE11\1033\SETUP.CHM.

And the reason it was not able to validate the file is because my good friend has saved his laptop previously by restoring it from the recovery disc. This has installed all the components from the mount point created in the Temp drive which is no longer valid.

Having dealt with this kind of issues previously I had explained my frined that we can do it in two ways.

1.       Download the get the required file.

a.       Download the express edition of SQL Server 2005 which installs with small business server and extract the exe file.

b.      Copy the location of OWC11.msi file in the extracted location.

c.       Open the registry location “HKEY_CLASSES_ROOT\Installer\Products” and search for the GUID as per the log above.

d.      In this case the product GUID is 90A40409-6000-11D3-8CFE-0150048383C9). So search by reversing the first part of the above GUID 90404A09………

e.      Under the key in registry select SourceList. Open the key LastUsedSource and paste the location of new OWC11.msi and click ok.

f.        Expand SourceList and select Net. Open the key 1 and paste the same location and click ok.

Rerun the setup and it should complete successfully.

2.       Uninstall the Microsoft Office Web Components from add and remove programs. Rerun the setup of SQL Server and it will reinstall the Office Web Components successfully.

My friend was intelligent and chooses the second option which saved the pain I go through every day with many others.

Happy Troubleshooting,

Manu

SQL Server Security Audit

This content is moved to http://www.sqlservergeeks.com/blogs/manu0417/personal/730/sql-server-security-audit-a-time-consuming-task-for-dba-simplified

-Manu

Transparent Data Encryption

This content has been moved to http://www.sqlservergeeks.com/blogs/manu0417/personal/765/transparent-data-encryption

Msg 3180 This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.

When you try to implement Log shipping from SQL Server 2008 to SQL Server 2008 R2 we cannot implement it in standby mode. The reason for this does not have anything to do with log shipping. When you try to restore the database during initialization with standby option the restore fails with the below error.

RESTORE DATABASE test FROM DISK = 'backup drive\test_full.bak' WITH STANDBY

Msg 3180, Level 16, State 1, Line 1
This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.

Both the versions of databases are 100. Now what is the upgrade the restore is looking for?

The answer is database internal version, DBI_Version. Each page has a version number of SQL called major version in the page header. For example the version number for SQL 2008 is DBI 655 and for SQL 2008 R2 is DBI 661.

To upgrade a database you can restore the database with no recovery.

So you will not be able to implement log shipping between 2008 and 2008 R2 versions of SQL Server with standby option. Even mirroring with standby or snapshot options does not work. The reason being, one version of SQL trying to read another version of data. For the SQL to read the DBI number must be upgraded.

Happy Troubleshooting,
-Manu

Cannot access database in single user mode

SQL Server database can be changed to single user mode by the below command.

ALTER DATABASE SET SINGLE_USER
GO

Keep the session from where you ran the ALTER DATABASE alive and open a new session.
Try to use the database from the new session and it throws the below error.

Error 924 - The database is already open and can only have one user at a time.

When you run the below query to find out any session using this database context it will return zero results.

SELECT spid, DB_NAME(dbid) FROM sys.sysprocesses WHERE DB_NAME = 'mydb'
GO

But when you run the below query you will see the locks on the database from the session which altered the database to single user mode.

SELECT * FROM sys.dm_tran_locks WHERE DB_ID('mydb') = resource_database_id
GO

So you will be able to use the database in single user mode from the session where you ran the ALTER statement or close that session and then try to use it from other session.

This is by design of SQL Server. The whole intention of holding locks on the database after altering the database to single user mode even though the database is not associated with any session is very simple. The session which changed the database to single user mode should have the capability to change it back to multi user. No other session should change back the database to multi user while this session is doing some operations on the database in single user mode.

So now you know, use a single session while you are working with single user database. If the session is orphaned the locks remain and you will never be able to get back the database to multi-user. The only solution then is to stop the services, delete the log file and start the services. Drop the database and restore from the most recent backup available which is not at all recommended as a DBA. Prevention is better than data loss and downtime.

Happy Troubleshooting,
Manu

SQL Server 2008 and 2008 R2 installation fails on Windows 7 with the error "Could not load file or assembly '0 bytes loaded from System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. An attempt was made to load a program with an incorrect format. Error code 0x84B10001."

On Windows 7, SQL Server 2008 or SQL Server 2008 R2 installation fails to launch the Landing Page and throws the below error message.

 

"Could not load file or assembly '0 bytes loaded from System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. An attempt was made to load a program with an incorrect format. Error code 0x84B10001."

 

The above error message is thrown if the.NET installation is corrupt or there is issues loading the file csc.exe.

 

If there is issue with .NET Installation you can try reinstalling the .NET and installation should work. But even after reinstalling .NET the setup doesn't launch then follow the below steps.

 

• Run the process monitor to collect the data while trying to run the setup.
• Check if the process csc.exe does start.
• If the process does not start check for the below entries in the process monitor. 

• Check in the registry path HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\ and see for csc.exe and any entry for the debugger key. 
• If it exists and if the debugger doesn't launch csc.exe successfully, it will fail the installation and throws the error. 
• To fix this issue rename or delete the entry csc.exe in the below path HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\csc.exe
• Re-launching the setup will run successfully.

 Happy Troubleshooting,

Manu

Cannot upgrade SQL Server 2005 Cluster as Domain groups are unavailable

This blog has been moved to 
http://www.sqlservergeeks.com/blogs/manu0417/personal/747/cannot-upgrade-sql-server-cluster-as-domain-groups-are-unavailable

Log shipping on SQL Server 2000 Standard edition

Log shipping is not supported on SQL Server 2000 Standard edition. But there are undocumented system stored procedures to create maintenance plans and setup log shipping on the standard edition.

First create the two stored procedures below. (The SPs are designed to keep backups on Primary for 4 days and on Secondary for 72 Hours. The job frequency is 15min. If needed change the SPs as commented.)

SP - 1 on PRIMARY SERVER:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[CreateLogJob] Script Date: 12/09/2009 09:55:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[CreateLogJob] @ServerNm varchar(255), @DBNm varchar(255)
as

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
declare @JobName varchar(255)
declare @JobCommand varchar(255)
declare @NameStr varchar(255)

SELECT @NameStr = @DBNm + ' Log Ship Trans Log Backup'
SELECT @JobName = N'' + @NameStr
SELECT @JobCommand = N'EXECUTE master.dbo.xp_sqlmaint ''-D ' + @DBNm + ' -WriteHistory -BkUpMedia DISK -BkUpLog \\' + @ServerNm + '\Logship\ -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"''' -- RETENTION ON PRIMARY IS 4 DAYS.

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = @JobName)
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = @JobName
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
@job_name = @JobName,
@owner_login_name = N'sa',
@description = N'Backup Transaction Log for Log Shipping',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 0,
@delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Step 1',
@command = @JobCommand,

@database_name = N'master',
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 4,
@retry_attempts = 0,
@retry_interval = 0,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID,
@start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID,
@name = N'Schedule 1',
@enabled = 1,
@freq_type = 4,
@active_start_date = 20000801,
@active_start_time = 0,
@freq_interval = 15, -- Every 15 mins
@freq_subday_type = 4,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_end_date = 99991231,
@active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID,
@server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

SP - 2 on SECONDARY SERVER:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[CreateLogFilePlans] Script Date: 12/09/2009 09:55:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[CreateLogFilePlans] @ServerNm varchar(255), @BackupServerNm varchar(255), @DBNm varchar(255)
as

declare @SourceDirStr varchar(255)
declare @DestDirStr varchar(255)
declare @NameStr varchar(255)
declare @cmd varchar(255)

set @SourceDirStr = '\\' + @ServerNm + '\Logship\' + @DBNm + ''
set @DestDirStr = '\\' + @BackupServerNm + '\Logship\' + @DBNm + ''
set @NameStr = @DBNm + ' Log_Shipping'
set @cmd = 'mkdir \\' + @BackupServerNm + '\Logship\' + @DBNm + ''

BEGIN
-- Create directory for log files
exec xp_cmdshell @cmd

PRINT 'Creating log file plans...'

exec msdb.dbo.sp_create_backup_movement_plan
@name = @NameStr, -- Log shipping plan name
@source_dir = @SourceDirStr, -- path to P1 tran logs
@dest_dir = @DestDirStr, -- location to copy P1 logs to
@sub_dir = 0, -- 0 = dest_dir is absolute path
@load_job_freq = 15, -- Load job frequency in minutes
@copy_job_freq = 15 -- copy job frequency in minutes

exec msdb.dbo.sp_add_db_to_backup_movement_plan
@plan_id = NULL, -- Plan ID, optional param
@plan_name = @NameStr, -- Log shipping plan name,equal to @name above
@source_db = @DBNm, -- Primary db name
@dest_db = @DBNm, -- Secondary db name
@load_delay = 0, -- load delay for load job in minutes
@load_all = 1, -- set to 1
@source_server = @ServerNm, -- source server
@retention_period = 72 -- time before deleting t-logs from secondary server

exec ('use msdb grant insert on backup_movement_plan_history to public')
END

After creating the SPs run them in the below syntax.

On PRIMARY:
EXEC [dbo].[CreateLogJob] *PRIMARY_SERVER_NAME*, *DBNAME*

On SECONDARY:
EXEC [dbo].[CreateLogFilePlans] *PRIMARY_SERVER_NAME*, *SECONDARY_SERVER_NAME*, *DBNAME*

-Manu