Find Useful information about your SQL Server

The below will find various useful chunks of information  about SQL Including licence information, version and cluster.  All using the SERVERPROPERTY command.

Run this:-


SELECT
@@SERVERNAME as InstanceName,


SERVERPROPERTY('BuildClrVersion')
,
 SERVERPROPERTY('Collation')
,
 SERVERPROPERTY('CollationID')
,
 SERVERPROPERTY('ComparisonStyle')
,
 SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
,
 SERVERPROPERTY('Edition')
,


 SERVERPROPERTY('InstanceName')
,
 SERVERPROPERTY('IsClustered')
,
 SERVERPROPERTY('IsFullTextInstalled')
,
 SERVERPROPERTY('IsIntegratedSecurityOnly')
,
 SERVERPROPERTY('IsSingleUser')
,
 SERVERPROPERTY('LCID')
,
 SERVERPROPERTY('LicenseType')
,
 SERVERPROPERTY('MachineName')
,
 SERVERPROPERTY('NumLicenses')
,
 SERVERPROPERTY('ProcessID')
,
 SERVERPROPERTY('ProductVersion')
,
 SERVERPROPERTY('ProductLevel')
,
 SERVERPROPERTY('ResourceLastUpdateDateTime')
,
 SERVERPROPERTY('ResourceVersion')
,
 SERVERPROPERTY('ServerName')
,
 SERVERPROPERTY('SqlCharSet')
,
 SERVERPROPERTY('SqlCharSetName')
,
 SERVERPROPERTY('SqlSortOrder')
,
 SERVERPROPERTY('SqlSortOrderName')
,
 SERVERPROPERTY('FilestreamShareName')
,
 SERVERPROPERTY('FilestreamConfiguredLevel')
,
 SERVERPROPERTY('FilestreamEffectiveLevel')

Find where Password and Username are the same

Weak security is always going to bite you.  The first thing any Hacker, Auditor, Penetration Tester or RAS is going to try is passwords that are the same as the user.

So how can we check this?  well a handy little script that uses PwdCompare, to checked the hashed password with the user name.

here it is:-

use master

select
cast(@@SERVERNAME as varchar(150)) as SQLInstanceName
,name as [LoginName]
,'Password is same as Login Name' [Description]
from syslogins
WHERE PWDCOMPARE (name,password) = 1

Powershell to do almost any thing

Below is a powershell script that will let you read servers from a table:-



CREATE TABLE [Core].[InstanceDatabases](
[DatabaseID] [bigint] IDENTITY(1,1) NOT NULL,
[InstanceID] [bigint] NOT NULL,
[DatabaseName] [varchar](50) NULL,
[RemoteDBID] [int] NULL,
[sysEffectiveFromDate] [datetime] NULL,
[sysEffectiveToDate] [datetime] NULL,
[Active] [bit] NULL,
 CONSTRAINT [PK_Instance_Databases] PRIMARY KEY CLUSTERED
(
[DatabaseID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [Core].[InstanceDatabases]  WITH CHECK ADD  CONSTRAINT [FK_InstanceDatabases_InstanceDetails] FOREIGN KEY([InstanceID])
REFERENCES [Core].[InstanceDetails] ([InstanceID])
GO

ALTER TABLE [Core].[InstanceDatabases] CHECK CONSTRAINT [FK_InstanceDatabases_InstanceDetails]
GO

ALTER TABLE [Core].[InstanceDatabases] ADD  DEFAULT (getutcdate()) FOR [sysEffectiveFromDate]
GO

ALTER TABLE [Core].[InstanceDatabases] ADD  DEFAULT ('2099-01-01') FOR [sysEffectiveToDate]
GO

ALTER TABLE [Core].[InstanceDatabases] ADD  DEFAULT ((0)) FOR [Active]
GO

In here can be stored all the servers you would like to connect to.

You will also require a few external scripts from other devs:-

http://poshcode.org/4967

http://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd

You can then run any SQL on all boxes in your estate and place the answer back to a table.

Code:-


##########
Dave Dunckley
Version 1.1
Please fell free to use as your like, please post back any modifications.
############

. D:\Powershell\TSM\Plugins\invoke-sqlcmd2.ps1

. D:\Powershell\TSM\Plugins\Out-DataTable

cls

#Add-PSSnapin SqlServerCmdletSnapin100
#Add-PSSnapin SqlServerProviderSnapin100

$wmiquery = "select SystemName, Caption, DeviceID, DriveType, FileSystem, FreeSpace, Size from win32_LogicalDisk where DriveType = 3"


########################################
# Set Config Settings for this session #
########################################
#add-pssnapin sqlserverprovidersnapin100
#add-pssnapin sqlservercmdletsnapin100

################################
# Setup and populate variables #
################################

# TSM Monitoring Server
$ManagementServer="servrename"

# DBA Monitoring DB
$ManagementDB="Monitor"

# OutCSV file
$CSVOUT = 'D:\Powershell\TSM\Temp\DBLastUsed.csv'

# Query String for servers to Collect Info from
$q = "SELECT distinct upper(InstanceName) FROM $ManagementDB.Core.InstanceDetails WHERE [InstanceScan] = 1 ORDER BY 1;"

# Query Conection String
$sconn = new-object System.Data.SqlClient.SqlConnection("server=$ManagementServer;Initial Catalog=$MonitoringDB; Trusted_Connection=true");

# Open Connection
$sconn.Open()

# Open Data Adapter and create data table
$da = new-object System.Data.SqlClient.SqlDataAdapter ($q, $sconn)
$dt = new-object System.Data.DataTable

#Fill Data table
$da.fill($dt)

# Set Stage table name for Truncate
$StageTable = "DBLastUsed"

# Truncate Stage Table
Invoke-Sqlcmd –ServerInstance $ManagementServer –Database $ManagementDB –Query "truncate table stage.$StageTable"

#Loop though each server


foreach ($row.host_connection in $dt.Rows)
{
$Server = $($Row[0])
   
 
#run SQL
      invoke-sqlcmd2 -ServerInstance $Server -Database "master" -Query "
   
SELECT @@servername as servername, DatabaseName, MAX(LastAccessDate) LastAccessDate
FROM
    (SELECT
        DB_NAME(database_id) DatabaseName
        , last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update
    FROM sys.dm_db_index_usage_stats) AS PivotTable
UNPIVOT
    (LastAccessDate FOR last_user_access IN
        (last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update)
    ) AS UnpivotTable
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY 2"   |Export-CSV -NoTypeInformation $CSVOUT


#Output to temp CSV

$csvDataTable = Import-CSV -Path $CSVOUT | Out-DataTable


#Create conection

$ConnectionString = "Data Source=$ManagementServer; Database=$ManagementDB; Trusted_Connection=True;";

#Import from temp CSV directly to SQL

$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $ConnectionString
$bulkCopy.DestinationTableName = "stage.$StageTable"
$bulkCopy.WriteToServer($csvDataTable)
   



#close connections

$connSQLOUT.Open()


$cmd = $connSQLOUT.CreateCommand()
   
   
 
   
   $connSQLOUT.Close()
   
   
   


}

#enjoy!

Find when a database was last used in SQL

Here is some nice SQL that will tell us when each database was last accessed.

SELECT @@servername , DatabaseName, MAX(LastAccessDate) LastAccessDate
FROM
    (SELECT
        DB_NAME(database_id) DatabaseName
        , last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update
    FROM sys.dm_db_index_usage_stats) AS PivotTable
UNPIVOT
    (LastAccessDate FOR last_user_access IN
        (last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update)
    ) AS UnpivotTable
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY 2

Get useful information on your database

Its sometimes useful to know a little more about your database, the below will give you information on Database_name, File_Name, Location, FileSizeMB, SpaceUsed, SpaceFree, database Owner, Recovery Model, Compatibility and Collation:-

set nocount on
create table #dbfileInfo(
Database_name varchar(300),
File_name varchar(300),
location varchar(300),
filesizeMB decimal(9,2),
spaceUsedMB decimal(9,2),
FreespaceMB decimal(9,2),
DB_Owner varchar(max),
Recovery_Model varchar(max),
Compatibility varchar(max),
Collation varchar(max))

declare @mySQL nvarchar(2000)
--DECLARE @dbName varchar(MAX)
DECLARE @cur_DBName CURSOR
DECLARE @DBO varchar(max)
DECLARE @Rec_Model varchar(max)
DECLARE @compt_lvl varchar(max)
Declare @collation varchar(max)


set nocount on
declare @SQLText nvarchar(4000), @DBName sysname


select @DBName = min(name) from sysdatabases
while @DBName is not null
begin



select @mySQL =
    'use ' + @dbname + '
        INSERT INTO #dbfileInfo
        select db_name(),
      sf.name
    , filename
    , convert(decimal(18,2),round(sf.size/128.000,2)) as FileSizeMB
    , convert(decimal(18,2),round(fileproperty
(sf.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB
    , convert(decimal(18,2),round((sf.size-fileproperty
(sf.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB
, SUSER_SNAME(owner_sid) as [DB_Owner]
    ,recovery_model_desc
    ,compatibility_level
    ,Collation_name
    from master.dbo.sysaltfiles sf
 INNER join master.sys.databases sd
 ON sf.dbid = sd.database_id where sd.name = db_name()
    '
    exec sp_executesql @mySQL
      -- select  @mySQL

--select @DBName
select @DBName = min(name) from sysdatabases where name > @DBName
end

 select * from #dbfileInfo
drop table #dbfileInfo

Get disk space info

EXEC master..xp_fixeddrives

Simple as that!

Check your databases are in the correct version

DECLARE @ver nvarchar(128)
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)

SELECT
[name] AS DatabaseName ,
200 AS Priority ,
'Performance' AS FindingsGroup ,
'Old Compatibility Level' AS Finding ,

( 'Database ' + [name]
 + ' is compatibility level '
 + CAST(compatibility_level AS VARCHAR(20))






 + ', which may cause unwanted results when trying to run queries that have newer T-SQL features.' ) AS Details
FROM    sys.databases


where
   CASE compatibility_level
    WHEN 65  THEN '6'
    WHEN 70  THEN '7'
    WHEN 80  THEN '8'
    WHEN 90  THEN '9'
    WHEN 100 THEN '10'
    WHEN 110 THEN '11'
    WHEN 120 THEN '12'
    end

    <>   @ver

Finding the Active SQL Node

Ever needed to know what node of your SQL cluster is the alive one.  Wonder no more.

SQL:-

Select ServerProperty('ComputerNamePhysicalNetBIOS')

Will show all nodes and there current state.

Or in Powershell:-

# Set cluster name 
$cluster_name = "ClusterName";
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null;
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $cluster_name;
# Get server properties
$properties = $srv.Properties
$owner_node = $properties.Item("ComputerNamePhysicalNetBIOS").Value;
$is_clustered = $properties.Item("IsClustered").Value
if($is_clustered)
{
 Write-Host "The current active node of $cluster_name is $owner_node.";
}
else
{
 Write-Host "$cluster_name is not a clustered instance of SQL Server.";
}

SLAs

Finding SLA's can be one of the hardest things to do as a DBA.  As it totally depends from company to company.

Some company's will have a list of very tight SLA's that are very hard to keep to.  And others will have none.

That's why I have always found its best to come at this from a DBA angle.  Get to know your databases, what application do they affect, what do them applications do, and who uses them are they client facing?

Understanding the databases and businesses importance will mark you out from other DBA's and ensure you fully know why each database has/Or should have the SLA's assigned to them. 

DFS

Just been reading up on what DFS is. I have known of it for some time.  But never the basics of how it works

HA / DR / Replication relationships

Working out the companies existing HA and DR procedures is vital.

I find the best way to do this is to map them out in something like visio, Its important to look at this on a database level not server, as no all will go to the same server.

First thing is to get an idea of any Mirrors, AG's or Log shipping.

I recommend that there are mapped in Visio with a different colour line per HA type.

The same can be done with Replication.

SP_whoisactive

This is bloody great.  Gives you a lot move info on whats doing on point in time.

SP_WHOISACTIVE

DNS are a DBA's best friend

Pah who needs diamonds.  A few DNS address can make you life much more simple.

SQL 2012 + Method 

In SQL 2012 high availability groups Microsoft brought in the ability to use a single Arecord.  This means that you can choose a set of databases that are related to a single product and have them six in a box with a virtual server name, that can fail over to DR with them.  an example set up below.

SERVER1 - Primary sever

DRSERVER2 - Your DR server

You could  have all databases related to say communications sitting in an AG.

Lync
EAS
Quest

These would all now have a virtual server name of say SQLComms

So no matter what server your databases now end up on, any application pointing to the arecord of SQLComms will see the right server.

All very useful as you can ensure that you have seamless DR, and also allows you to move databases about with ease.

NOTE You will still need to ensure that correct users sit on both servers.

 Pre SQL 2012 Method

The same can be achieved in older versions of SQL, however it does require a bit of manual intervention.

Set up a Cname (alias) for the same set of databases, you can then re-point this name to the failed over DR server once your at DR.

Another good use for this, is to stop hard coded server names.  If as far as every one else is concerned the server name is that alias, you can always just pick up and move the databases.

List of Instances \ environments \ Clusters

So in the the previous post we went over the importance of collecting server information and how to do so (well the basics)

So now that we have that, we need to look at the servers in a little more depth.

My first port of call would always be to check what environments these all sit in.  This gives you an idea of where to start when you begin checks and balances.

This is also requires you talking to your team/devs/the business so can prove to be a very beneficial exercise to get to know the people you will be working with, and there understanding of the systems.

Once you have this its a good idea work out if there are clusters, and if so find the node names, and the normally active node.  Also find out if its active/active or active/passive.

Now you have this it will help you get a better idea of what kind of HA, DR set up you have.

List of databases / Servers - Scanning

Its important that You are provided a full list of SQL servers and databases on day one.

However once you have these I would always recommend that you go off and check them further.

First compile a list of all the servers given, from here you should check you have access.  In a large organisation this could prove to be a hard job.  But one you should start as soon as you can.

Now from here you can use some lovely code - post coming - to check though each and see if you can discover any extra servers.  Almost all organisations when doing this exercise find that they have servers they did not know about.  - More detailed post on this to follow  - This is maybe due to the fact that they are never used, or are on peoples desk tops, or out of normal servers naming convention.  But they all need discovering and reporting on.  One of these could be really important to someone.

Go to each one and use OSQL - L.  This will find all servers on that subnet.  I will post later on a more straight forward way to do this.

Once you have got all your servers, now you need to list all databases on them, from here you can start to work out the environments and SLAs.  More on that Here

New DBA Manager

Its never easy to walk into a role where there has been an existing team, and come in and manage it.

They have there systems and ways of doing things and you have yours.  However its important to ensure that you don't step on any feet but also keep your own integrity.

So how can you go about building this.  I decided the best way would be to create a check list of questions that can be asked to give a starting point of where to go next.

Below are the questions.  I will post a new post of the answer of each:-

 List All DataBases \ Servers

List of Instances \ environments \ Clusters

HA / DR / Replication relationships

SLAs

Leaving

On a jet plane, don't know when I will be back again.

So you read blogs all the time - Mine - about what things to check on starting a new role as a DBA.

But, what should we do on the way out?

As the outgoing DBA manager, it occurred to me that there was an awful lot of things that needed checking. So I thought a little blog about it might help me create a check list, and others to create theirs.

Dependency

In an ideal world you should not have thing set to run as you, and you should have checks in place to stop this but it can happen.

So ensure that the following are not set as owned by your login, as once your log in is disabled, the pople you leave behind could face all sorts of issues, and no one wants there name to become mud after leaving.

Jobs

Schema

Tables

Jobs

Databases

SSRS Packages

Reporting

So we all have our own way to set up reporting and our own set of vital reports.  (Mine coming, in later post)  But unless someone is receiving it all its pointless.  So make sure that all them emails that have plagued you for the duration of your employment are going to people in your team. Also ensure they understand them and how to act on them.

SLA - Environment

Ensure that the SLAs are fully documented, and the remaining team know what systems are PROD, DEV, QC and UAT.  So that they can be sure they can act accordingly.

Documentation

The word no one wants to hear, but the vital act of ensuring that every thing is fully documented and up to date. So that you dont get calls after you have left asking for HELP!

If any one else can think of any thing let me know.

Powershell to find User details from AD

Seen a computername hitting your databases not sure who it is.  This will help you find the owner information of the computer.  Enter the PC name, and it will show the details stored in AD under owner.

$strResponse

= "Quit"

do

{

$machine

= Read-Host "Please supply computer to lookup"

$strFilter

= "(&(objectCategory=Computer)(name="+$machine+"))"

$objDomain

= New-Object System.DirectoryServices.DirectoryEntry

$objSearcher

= New-Object System.DirectoryServices.DirectorySearcher

$objSearcher

.SearchRoot = $objDomain

$objSearcher

.PageSize = 1000

$objSearcher

.Filter = $strFilter

$objSearcher

.SearchScope = "Subtree"

$colProplist

= "name", "description"

foreach

($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}

$colResults

= $objSearcher.FindAll()

foreach

($objResult in $colResults)

{
$objItem = $objResult.Properties; $objItem.name; $objItem.description}

　


$strResponse

= Read-Host "Are you sure you want to quit application? (Y/N)"}


until ($strResponse -eq "Y")

Ckeck whats using the Log

Ever wnated to truncate a log and found that its in use?  Or that it cant be shrunk, even after a backup.  Well maybe its in use.  Use this to find out...

 DECLARE @DatabaseName VARCHAR(50);
 SET @DatabaseName = '<Databasename>'
 SELECT name, recovery_model_desc, log_reuse_wait_desc
   FROM sys.databases
   WHERE name = @DatabaseName

Fail all Availibility Groups to local node

Use this to fail over AG's back to the local node.

select 'ALTER AVAILABILITY GROUP ['+name+'] FAILOVER;
go'
from sys.availability_groups
order by name desc

Dont delete ##MS_PolicyEventProcessingLogin## or ##MS_PolicyTsqlExecutionLogin##

One of our developers today deleted the above account.  These are used by Policy Based management.  And ARE needed.

If you have deleted them, follow these steps:-

1)

Run:-

use  msdb

go
sp_change_users_login 'Report'


2) Take the Sids from above and place them below:-

CREATE LOGIN [##MS_PolicyEventProcessingLogin##] WITH PASSWORD = 0x0100EBB95886A3A65AD6C770157B4E767D146D83E079A3A33321 HASHED,

SID = <Sid 1>, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; ALTER LOGIN [##MS_PolicyEventProcessingLogin##] DISABLE

　

CREATE LOGIN [##MS_PolicyTsqlExecutionLogin##] WITH PASSWORD = 0x01008D22A249DF5EF3B79ED321563A1DCCDC9CFC5FF954DD2D0F HASHED,


SID = <Sid 2>, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; ALTER LOGIN [##MS_PolicyTsqlExecutionLogin##] DISABLE


Hope that helps.

Somthings got a hold on .... Your Log

There are times when shrinking a log on a database is needed.  This could be if your transaction backups have failed for a period of time.  And have now grown.

However there are times when they dont shrink.  Here is a nice script to see why they dont:-

DECLARE @DatabaseName VARCHAR(50);

 SET @DatabaseName = 'YourDataBASE'

 

 SELECT name, recovery_model_desc, log_reuse_wait_desc

   FROM sys.databases

   WHERE name = @DatabaseName