SSMSTools

This is a natty plugin in for SSMS that is totally free and has some interesting functionality including:

• Restoring the last tab group of query windows (great for reopening queries after a restart/shutdown) 
• Colour coding by server connection which may help reduce accidents
• Searching for table/view/database data
• Better Undo functionality
• Better control over snippets
• Search results in a grid
• ...lots more...

Restoring Master Data Services databases to different domains

Overview

• Before performing the restore on the desired DEV/TEST environment, you'll need the SSIDs for the user (who will be admin on DEV and TEST as admin on Production). There are ways to access this data using Active Directory but the easiest way is to log onto the existing mdm_hub and query the information out of the [mdm.tblUser] table (first row).
• Make a note of the MUID, SID, UserName and DisplayName. 

Restore the mdm database

You may need to run the following SQL to clear connections to the database to allow the restore to run:

alter database mdm_db set single_user with rollback immediate;
go

alter database mdm_db set multi_user;
go

• Restore the mdm_db database from the backup file over the top of the existing database

Correct user SID values in mdm.tblUser

• Update the admin user in mdm.tblUser to reflect the TEST admin details

Delete any mdm_db snapshots from the target server

See below for more information

Update the SID for the service account

This is a problem found when restoring MDS from TEST where the SID for the admin user (which is also the service account) didn't match the SID in the master database.
Run this to fix:

USE master;
ALTER AUTHORIZATION ON DATABASE:: mdm_db TO admin;

Check IIS website authentication settings

• Open up the IIS manager using the object explorer to browse to the MDS website.
  • Check that the Authentication at the website level has everything disabled apart from Windows Authentication
• Expand the MDS website to click once on the Service folder
  • Check that Anonymous Authentication is enabled here
• Restart the web services using the "iisreset" command in Start->Run
• Open IE on the mds web server and navigate to http://mds and the website should open successfully
• From here you can add in the TEST users as required

If you find that business rules or the staging batch process don't run properly run the following code to get the background processes running (not required yet but could be depending on when the backup was taken):

ALTER DATABASE mdm_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE mdm_db SET DISABLE_BROKER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE mdm_db SET ENABLE_BROKER;
ALTER DATABASE mdm_db SET MULTI_USER;
ALTER DATABASE mdm_db SET TRUSTWORTHY ON;


--Script to start the member security message queue
DECLARE @memberHandle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @memberHandle
      FROM SERVICE [microsoft/mdm/service/system]
      TO SERVICE N'microsoft/mdm/service/securitymember'
      ON CONTRACT [microsoft/mdm/contract/securitymember]
      WITH ENCRYPTION=OFF; --is by default

--Script to Start the member security timer queue
DECLARE @memberSecHandle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @memberSecHandle
      FROM SERVICE [microsoft/mdm/service/securitymembertimer]
      TO SERVICE N'microsoft/mdm/service/system'
      --WITH RELATED_CONVERSATION_GROUP=0x1;
      WITH ENCRYPTION=OFF --is by default
BEGIN CONVERSATION TIMER (@memberSecHandle) TIMEOUT = 30;
GO
--Script to Start the StagingBatch Queue
DECLARE @stagingHandle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @stagingHandle
      FROM SERVICE [microsoft/mdm/service/stagingbatch]
      TO SERVICE N'microsoft/mdm/service/system'
      WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER (@stagingHandle) TIMEOUT = 30;
GO
--Script to Start the Notification Queue
DECLARE @notifyHandle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @notifyHandle
      FROM SERVICE [microsoft/mdm/service/notification]
      TO SERVICE N'microsoft/mdm/service/system'
      WITH ENCRYPTION = OFF;
BEGIN CONVERSATION TIMER (@notifyHandle) TIMEOUT = 30;
GO

EXEC sp_configure 'clr_enable', 1;
RECONFIGURE WITH OVERRIDE;
GO

Orphaned Database Snapshots prevents configuration manager database connection

Problem:

Orphaned database snapshots in dev and test environments can stop you being able to use the MDS Configuration Manager to connect to the database. This occurs if you restore the mdm_db database when a database snapshot exists on the target server. Because the snapshot is orphaned it gets left in SUSPECT mode.

SELECT name,state_desc 
FROM sys.databases

Solution:

Delete the database snapshot and make sure the database is in ONLINE mode.

More Info:

To check for the error you can run a profiler trace which will bring up an Exception 926 when trying to connect with configuration manager.
Books online error 926

Organising tabs in SQL Server Management Studio

It's really annoying when you can't see all of the SQL query window tabs you have more than 3-4 open (which is pretty much all of the time). It'd be nice to have them all on screen (a bit like Mozilla and Chrome tabbing) rather than having to select through each one to find the right window.

This is a handy piece of software: http://www.tabsstudio.com/download.html

Just a shame it's not free...

Finding text in a Stored Procedure SP

Discovered this handy bit of code on the web.


SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%foobar%' 
    AND ROUTINE_TYPE='PROCEDURE'

Viewing SQL Job history via TSQL

This SQL Server script will display job history. The benefit of this script over displaying it from the GUI is that you get to see the job durations quickly. You will need to replace with the job you want to see history for.

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 = ''
order by job_name, run_datetime

Replication errors we have encountered
SQL Server Replication requires the actual Server name
This is can arise in our dev and test environment when trying to configure replication on a cloned server. The problem is that the servername becomes unregistered at some point in the process.

To check whether this is the problem run

SELECT @@SERVERNAME
This should be the name of the SQL Server Instance e.g. MSSQLSERVER\PROD1

Resolution:
Run the following SQL

-- This first bit will not be needed if @@SERVERNAME IS NULL
Use Master
go
Sp_DropServer '[wrongname]'
GO

-- This is the important bit
Use Master
go
Sp_Addserver 'MSSQLSERVER\PROD1', 'local'
GO
Then restart the SQL Services

Profile not valid
Troubleshooting Database Mail: Profile not valid

Caused due to DDL Trigger on stored procedures on the Hub database which attempts to send a notification email if it is triggered.

Resolution: ensure that the database mail profile specified in the DDL trigger exists on the server and works.

INSERT failed because the following SET options have incorrect settings: 'ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. (Source: MSSQLServer, Error number: 1934)
Caused due to DDL Trigger on stored procedures on the Hub database - only affected one publication for some reason.

Resolution: only way round this found so far is to drop the DDL trigger on stored procedures.

Server: Msg 14117, Level 16, State 1, Procedure sp_dropdistributiondb, Line 39 'Distribution' is not configured as a distribution database
MSSQL_ENG014117

Related stuff
Add links to related pages or external web sites here

Msg 20581, Level 16, State 1, Procedure sp_MSrepl_check_server, Line 22
Cannot drop server 'repl_distributor' because it is used as a Distributor in replication.

Run the following:

EXEC master.dbo.sp_serveroption @server=N'MSSQLSERVER\DEV1', @optname=N'dist', @optvalue=N'false'
GO
Could not execute 'sp_replcmds'
Error messages:
The process could not execute 'sp_replcmds' on 'GP10'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Source: MSSQLServer, Error number: 15517)
Get help: http://help/15517

etc.

Run the following on the database being published:

ALTER AUTHORIZATION ON DATABASE::Hub_staging to sql_deploy
Where 'sql_deploy' is the database owner

Error: The process could not execute 'sp_repldone/sp_replcounters' on 'GP10'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Run the following job on the publication server.

sp_replrestart

The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated. (Source: MSSQLServer, Error number: 21074)
Run the following script on the publisher database:


EXEC sp_reinitsubscription @publication =  'Pub Master.Product', @subscriber = 'MSSQLSERVER'


Cannot delete the publication because there is a subscription
I had a problem where there was no subscrition to the publication and i still could not delete it. The distributor database was stopping the deletion, i managed to delete it and recreate it in the end but to delete it i used this command to ignore the distributor

exec sp_droppublication @publication = N'Pub uvwProduct',@ignore_distributor = 1
Unable to shrink transaction log
Run the following:

SELECT name, log_reuse_wait_desc
FROM sys.databases
If log_resuse_wait_desc says REPLICATION then there could be some problems with replication not being marked as done so run the following:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1

Dealing with orphaned users in SQL

SQL Server logins access individual databases using a database user that is mapped to the SQL Server login. There are two exceptions to this rule:

• The guest account. This is an account that, when enabled in the database, enables SQL Server logins that are not mapped to a database user to enter the database as the guest user.

• Microsoft Windows group memberships. A SQL Server login created from a Windows user can enter a database if the Windows user is a member of a Windows group that is also a user in the database.

Information about the mapping of a SQL Server login to a database user is stored within the database. It includes the name of the database user and the SID of the corresponding SQL Server login. The permissions of this database user are used for authorization in the database.
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.

Finding Orphaned Users

USE <database_name>;
GO
sp_change_users_login @Action='Report';

Outputs a list of users in the selected database that are not linked to any SQL Server login.

N.B.	sp_change_users_login cannot be used with SQL Server logins that are created from Windows.

Fixing orphaned users

The following command relinks the server login account specified by <login_name> with the database user specified by <database_user>.

USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';

• See books online Troubleshooting Orphaned Users which is where the above information was copied from.