Header

Wednesday, February 26, 2014

Which databases is this login used in ?

When moving databases and logins, or just cleaning the server of unused logins this script can help you test if a single login is mapped to any databases.

-- =============================================
-- Author: Rasmus Glibstrup, SQLGuyDK
-- Company: it-Craft
-- Create date: 26. Feb. 2014
--
-- Description:   
-- Which databases are a single login mapped to ?
--
-- When moving databases and logins, or just cleaning
-- the logins of the server this script can show you
-- which databases a specific login is mapped to.
--
--
-- DISCLAIMER !
-- THIS CODE-SAMPLE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED  
-- OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR  
-- FITNESS FOR A PARTICULAR PURPOSE. 
-- =============================================

-- Change SQLGuy to the login you need to find
DECLARE @LoginName nvarchar(1000) = 'SQLGuy'


DECLARE @SqlStm nvarchar(1000)
Create table #LoginTable(LoginMappedInDatabase nvarchar(1000),LoginName nvarchar(1000),DatabaseUser nvarchar(1000))

DECLARE @DBName nvarchar(1000)
DECLARE dbCursor CURSOR 
FOR SELECT name from sys.databases
Where state_desc = 'online'

OPEN dbCursor

FETCH NEXT FROM dbCursor into @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @SqlStm ='INSERT INTO #LoginTable Select ''' + @DBName + ''' as LoginMappedInDatabase,SP.name as LoginName, DP.name as DatabaseUser from sys.server_principals SP inner join [' + @DBName + '].sys.database_principals DP ON SP.sid = DP.sid Where SP.Name=''' + @LoginName + ''''
 
 exec (@SqlStm)
 FETCH NEXT FROM dbCursor into @DBName
END

CLOSE dbCursor
DEALLOCATE dbCursor

Select *
FROM #LoginTable
Drop table #LoginTable

Thursday, February 20, 2014

Monitoring if my databases are online

Usually my customers are monitoring if their servers are online, but in the case of SQL Server, monitoring if the databases are online is just as vital.

This little script checks if the databases are not online and alert you via email.



IF EXISTS (
   Select Name,state_desc 
   From Sys.databases 
   where state_desc <> 'ONLINE'
    )
BEGIN
 DECLARE @tableHTML  NVARCHAR(MAX) ;

 SET @tableHTML =
  N'<H1>OFFLINE Databases</H1>' +
  N'<table border="1">' +
  N'<tr><th>Database Name</th><th>State</th></tr>' +
  CAST ( ( SELECT td = Name, ' ',
      td = state_desc, ' '
      FROM Sys.databases 
      Where state_desc <> 'ONLINE'
      FOR XML PATH('tr'), TYPE 
  ) AS NVARCHAR(MAX) ) +
  N'</table>' ;

 EXEC msdb.dbo.sp_send_dbmail @recipients='you@yourcompany.com',
  @subject = 'OFFLINE Databases',
  @body = @tableHTML,
  @body_format = 'HTML' ;
END



Create a simple job that runs the above script a often as you want - eg. every 10 minutes.

Wednesday, February 19, 2014

Who is using my databases

Due to Microsofts change in licensing with SQL Server 2012, alot of our customers is currently doing consolidation projects or migrating databases from older servers so these can be closed.

This also brings up that simple question: "Are these databases being used - and who are using them?"

To solve this we have made this simple script that tracks the activity of the server, using the same basic views as the activity monitor does.


 
-- =============================================
-- Author: Rasmus Glibstrup, SQLGuyDK
-- Company: it-Craft
-- Create date: 03-Jun-2013
--
-- PART 01 - Create table

-- Description:   
/*
SQL Access Monitor script
Script that captures activity on the SQL server
and saves it to a table.

The Script is based on the Activity Monitor
queries is SSMS
*/
-- =============================================
Create Database [SQLGuyDK_SQLAccessMonitor]
GO

USE [SQLGuyDK_SQLAccessMonitor]
GO

IF EXISTS(Select object_id  from sys.tables where name ='SQLGuyDK_SQLAccessMonitor')
BEGIN
    DROP TABLE [dbo].[SQLGuyDK_SQLAccessMonitor]
END
GO

CREATE TABLE [dbo].[SQLGuyDK_SQLAccessMonitor](
    [SQLSERVER] [varchar](128) NOT NULL,
    [Login] [varchar](128) NOT NULL,
    [Database] [varchar](128) NOT NULL,
    [Application] [varchar](255) NULL,
    [LoginTime] [datetime] NULL,
    [LastRequest] [datetime] NULL,
    [HostName] [varchar](128) NOT NULL,
    [NetAddress] [varchar](48) NULL
) ON [PRIMARY]
GO

-- =============================================
-- PART 02 - Script to capture activity
-- =============================================
Use SQLGuyDK_SQLAccessMonitor

GO
Create procedure dbo.[usp_SQLGuyDK_SQLAccessMonitor]
@RefreshIntervalInSeconds int = 10
as
SET NOCOUNT ON

Declare @DelayTime varchar(10)

SET @Delaytime = SUBSTRING(CONVERT(varchar, DATEADD(ms, @RefreshIntervalInSeconds * 1000, 0), 114),1,8)

WHILE 1=1
BEGIN
    WAITFOR DELAY @Delaytime

    MERGE [dbo].[SQLGuyDK_SQLAccessMonitor] as TARGET
    USING (
            SELECT
               [SQLServer]        = @@Servername              
               ,[Login]         = s.login_name  
               ,[Database]      = ISNULL(db_name(sp.dbid), N'')
               ,[Last Request Start Time] = MAX(s.last_request_start_time)
               ,[Host Name]     = ISNULL(s.host_name, N'')
               ,[Net Address]   = ISNULL(c.client_net_address, N'')
            FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
            LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
            LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
            LEFT OUTER JOIN
            (
                SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
                FROM sys.dm_os_waiting_tasks
            ) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
            LEFT OUTER JOIN sys.dm_exec_requests r2 ON (r.session_id = r2.blocking_session_id)
            LEFT OUTER JOIN sys.sysprocesses sp ON (s.session_id = sp.spid)
            --Filter out only user processes
            Where s.is_user_process = 1
            -- do not include the query we are running
            AND s.session_Id NOT IN (@@SPID) 
       
            Group by  s.login_name,sp.dbid,s.host_name,c.client_net_address

        ) AS SOURCE ([SQLServer],[Login],[Database],[Last Request Start Time],[Host Name],[Net Address])
        ON SOURCE.[Login] = TARGET.[Login]
        AND SOURCE.[SQLServer] = TARGET.SQLSERVER
        AND SOURCE.[Database]  = TARGET.[Database]
        AND SOURCE.[Host Name] = TARGET.[HostName]

    WHEN MATCHED THEN
        UPDATE SET TARGET.[LastRequest]=SOURCE.[Last Request Start Time]

    WHEN NOT MATCHED THEN
        INSERT ([SQLSERVER],[Login],[Database],[LastRequest],[HostName],[NetAddress])
        VALUES ([SQLSERVER],[Login],[Database],[Last Request Start Time],[Host Name],[Net Address])
    ;

END

SET NOCOUNT OFF
GO

-- =============================================
-- PART 03 - Job to run script
-- =============================================

-- Create a job that is scheduled to run on sql server agent startup, and make it run this one command 

exec SQLGuyDK_SQLAccessMonitor.dbo.[usp_SQLGuyDK_SQLAccessMonitor]


-- =============================================
-- PART 04 - Which databases are not being used?
-- =============================================

Select name
From Sys.databases 
where name not in (
Select [Database]
from [dbo].[SQLGuyDK_SQLAccessMonitor])
And database_id > 4

Tuesday, February 18, 2014

Gothas on changing the Recovery model from Simple to Full

While teaching traditional Backup and Recovery of SQL Server databases this week I made a simple example to prove a point on changing the Recovery model.

When changing the database from Full to Simple mode, no problems, except that you cannot do log backup.

BUT when changing back from Simple to Full recovery mode - BE AWARE the database does not change the recovery model until you make a Full backup after the change.

You can reproduce using the code below:  
 
--Create the test database
Create database TestRecoveryModelChange
GO

--Check the recovery model of the test database
Select name,recovery_model_desc
from sys.databases
Where Name = 'TestRecoveryModelChange'
GO

--Create a Full backup of the test database
Backup database [TestRecoveryModelChange] to disk='E:\Backup\TestRecoveryModelChange.bak'
GO

--Change Recovery model to Simple
USE [master]
GO
ALTER DATABASE [TestRecoveryModelChange] SET RECOVERY SIMPLE WITH NO_WAIT
GO


--Create a change
USE [TestRecoveryModelChange]
Create table Test(Col1 int)
Insert into Test Values(1)
GO

--Change the recovery model back to FULL
USE [master]
GO
ALTER DATABASE [TestRecoveryModelChange] SET RECOVERY FULL WITH NO_WAIT
GO

--Try doing a log backup
Backup Log [TestRecoveryModelChange] to disk='E:\Backup\TestRecoveryModelChange_Log.trn'


--Uupps !
Msg 4214, Level 16, State 1, Line 3
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.

Monday, February 17, 2014

Using DNS to virtualize SQL Server naming

One very common problem is when you want to move you databases to a new SQL Server or create a new SQL Server for your datatabases: All the current connection strings, .ini files, ODBC links and so on, in the often many applications scattered throughout the organization needs to be updated with the new SQL Server name. Eg. Going from SQLSERVEROLD1 to SQLSERVERNEW1.

How to change SQL Server connections centrally

The simple solution to this problem is using DNS. Create a CNAME record in your DNS for each application that uses SQL Server. Point the CNAME record to the OLD server and start changing your connection strings, .ini files and so on for the last time.

Eg. your current SQL Server is named SQLSERVEROLD1. You have a CRM application that have one or more databases on the server.

You then create a CNAME record in DNS for the CRM application. A proposed naming could be: SQL-CONN-CRM, that points to SQLSERVEROLD1.

You then change the connection information in the CRM application from SQLSERVEROLD1 to SQL-CONN-CRM - and the next time you move the CRM databases, or upgrade the server - you just change the CNAME record to point at the new SQL Server - no application change needed.

What about named instances?
The CNAME trick will only work for the server part of a named instance - Eg. SQLSERVEROLD1\INSTANCE =>  SQL-CONN-CRM\INSTANCE.

If you would like the SQL-CONN-CRM\INSTANCE to virtualize instance naming as well, you will need to use SQL Server Aliases. (Another blog post to come)