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.