This article was linked to from a recent Brent Ozar PLC newsletter.  It contains a bunch of tips about how to be a programmer(Not how to program, but how to be a programmer – the distinction is important).  I think it was absolutely brilliant and has huge areas of crossover to other technical roles…such as DBA’s.

(Dead Link)http://samizdat.mines.edu/howto/HowToBeAProgrammer.html?x#id2855381(Dead Link)

I particularly liked the sections on how to deal with difficult people, how to provide estimates, and perhaps best was knowing when to go home:)

I’ve recently had to look over a client server where a maintenance plan had been failing for some time.  The inhouse DBA was unaware of the failures because he had no alerting on the jobs and the big red crosses weren’t big or red enough to have caught his attention.  When I looked at the cause of the failures it was due to a native maintenance plan not being able to reoganise an index with page level locking disabled.  Whenever a reorganise plan hits a database with page level locking disabled it generates the following error:

Executing the query “ALTER INDEX Indexname] ON [dbo]….” failed with the following error: “The index “Indexname” (partition 1) on table “tablename” cannot be reorganized because page level locking is disabled.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

There are two ways of getting around this.  The first way is to generate TSQL scripts to enable Page level locking prior to the re-organise and then re-enable it after the maintenance is completed.  JP Chen has the scripts to do that here.  Note:  Haven’t tested those, but they look right.  I suspect you may need to exclude the system tables as well.

The second, and easier way, is to set up a rebuild step and move the affected databases into that step of your maintenance plan.  Rebuilds do not require a page lock because the index is completely rebuilt rather than shuffled around.  The TSQL to figure out which database you need to move from the re-organise to the rebuild step is:

exec sp_MSforeachdb ‘use ? select db_name(), (case when count(*)>0 then ”Page Locks Disabled:” ELSE ”Page Locks” END) from sys.indexes where allow_page_locks=0 and name not in(”queue_clustered_index”,”queue_secondary_index”)’

and if you want to get the specific indexes you can use this:

exec sp_MSforeachdb ‘use ? select db_name(), object_id,name from sys.indexes where allow_page_locks=0 and name not in(”queue_clustered_index”,”queue_secondary_index”)’

Standard disclaimer about sp_MSforeachdb being an undocumented stored procedure and maintenance plans being somewhat limited form of doing your index mainteance apply:)

Here’s a TSQL snippet that will give you the sizes of the indexes that the query optimiser isn’t using.  Note that the NULL values are SINCE THE LAST RESTART.  So if you just restarted your server then this is rather a waste of time.

 
SELECT object_name(ui.object_id), si.name as [Index Name],ui.index_id, ui.last_user_seek, ui.last_user_scan, ui.user_seeks, ui.user_scans,
ui.user_lookups,ui.user_updates,
8 * sa.used_pages AS [Index Size]
from sys.dm_db_index_usage_stats ui
inner join sys.indexes si on ui.object_id=si.object_id and ui.index_id=si.index_id
INNER JOIN
sys.partitions sp ON si.object_id = sp.OBJECT_ID AND si.index_id = sp.index_id
INNER JOIN sys.allocation_units AS sa ON sa.container_id = sp.partition_id
where database_id=7
and object_name(ui.object_id) not like ‘sys%’
and ui.last_user_seek is NULL
and ui.last_user_scan is NULL
ORDER by 10 desc

Something I have run into a few times now which is an annoying timewaster is the following error:

Error While Enabling Windows Feature netfx3

You’ll get it on windows 2012 installations when installing SQL 2012(And probably lower versions) and it’s caused by dotnet 3.5not being enabled on the windows installation. It’s frustrating because it happens during the actual installation and is not flagged in any of the pre-requisite checks.  This is because Windows assumes it either has access to the install media to install it from, or can download it from the internet.  On your SQL Servers you shouldn’t have direct internet access, and you are unlikely to have the install media lying around.  At any rate – here’s how to fix it:

1.  Place the install media back to it’s original location(Most likely the CD drive).

2.  From Server Manager choose ‘Add Roles and Features’.

3.  Add ‘.NET framework 3.5 features’.

4.  Now redo your SQL Install and you should be fine.

There are about a billion different technologies used to connect remotely to SQL Servers and some are better than others.  Some allow file transfer, some allow sharing clipboards and some don’t.  Of course, better depends entirely on your perspective.  In most cases those ‘inconvenient’ things are there for a reason – security.  nonetheless it’s a pain when you have some nice query output that you want to drop into a CSV file and you can’t get it to your own laptop.  Well….here’s how.

First of all you need a few things set up.  you need:

• Database Mail XP’s enabled.
• A SQl Databasemail profile set up.
• mail relaying enabled from the server you are sending from.

In other words, you aren’t going to sneakily move data off a server.  You have to have the rights to set those things up and the co-operation of a system administrator to get the mail sorted.  Now, if it’s just a query result you want sent back you can just use this:

USE msdb
EXEC sp_send_dbmail
@profile_name = ‘ManagedSQL’,
@recipients = ‘me@mymail.com’,
@subject = ‘ROSA OUTPUT’,
@body = ‘Daily Statistics are below.’,
@execute_query_database = ‘MYDB’,
@query = ‘SELECT * FROM [MYDB].[dbo].[tbl_color]’

But if you’ve already got a large output you may have to save it to the server and then send it as an attachment:

USE msdb

GO

EXEC sp_send_dbmail
@profile_name=’ManagedSQL’,
@recipients=’me@mymail.com’,
@subject=’Healthcheck Output’,
@body=’Server Healthcheck data attached.’,
@file_attachments=’C:\temp\config.txt’

The below is an extract from my presentation at SQL Saturday 252 on high availability and disaster recovery.  This post covers the basics of Always On Availability Groups.

So if you have mirroring you need to fail over each database, and sometimes you are going to have multiple databases servicing the same application and some will fail over automatically and leave you with databases in different places, attempting to join with each other and failing, and connection strings bouncing back and forwards between servers and generally….a big mess.

Wouldn’t it be nice if we could guarantee that if one database failed over, it took all of the other associated or reliant databases across with it.  And wouldn’t it be nice if we could use a virtual name like with a cluster so we could point an application at one place and then the windows cluster, active directory and SQL server would do the donkey work?  Well….the good news is if you have SQL Server 2012 then you can.

Availability groups are like mirroring, except well…better.  You can have multiple databases in a group and guarantee you fail them over together, you can reliable report off any of those mirrors and you can use a single connection string and point it at a virtual name and let it decide which server it needs to action your query on.

You can have up to 4 secondary replicas as well as your read\write primary.  You have the same options around whether you want any individual replica to be Synchronous or Asynchronous.  You can specify a shared backup location so when you fail over between replicas your recoverability chain remains intact.  You can take backups from replicas in copy only mode.

Initially….initially it looks like all the best bits of mirroring and clustering have been beautifully merged together to give us the HA\DR solution we have been waiting for.   But…not quite.

SQL 2012 is the first crack at Availability Groups, and they are pretty good, but they still have a few things to sort out and are going to get better.

Let’s start with backups.  If you take backups on different replicas you need to be aware that each instance only keeps backup history from it’s own msdb database.  So while the backup chain itself will still be fine, you can’t query the history of backups on any individual instance, you need to write some smarts which looks at all the msdb databases and collates that information.  Now obviously you can be taking copy only backups off your secondary replicas, but they are going to be copy only.

SQL 2012 is really built to be running on Windows 2012 server, but it’s not required.  And there are a whole heap of considerations if you are using Windows Server 2008 R2.  There’s bugs that are known(and you can do a google to find out what they are) running availability groups on Windows 2008 R2 purely because when Windows Server 2008 R2 was being developed the whole concept of “Always On” availability was just an idea in the back of some developers head.  Mostly these are around the way and the times that the server quorums are calculated, but availabilities groups push windows clustering technology as hard….or probably harder….than any other application does, so there’s things being found all the time, and patches coming out all the time.

There are a lot of single points of failure.  Is the listener accepting writeable connections.  Is it sending read connections to the correct place.  Is load being balanced between replicas?  Are the replicas up to date.  These are all new considerations that you probably aren’t actively monitoring for with your current environment.  There’s a whole bunch of new moving parts in this setup and if one of them fails you are not necessarily going to know what and how if you haven’t worked out what needs to be monitored.  At best you may find that everything is going to your primary replica so you aren’t getting any scaled out performance, at worst – you may not even get that.

There’s no combined statistics and DMV’s.  So if you are doing your performance tuning you are going to look at a DMV and note a whole bunch of indexes not being used.  But the reason they aren’t being used might not be that they aren’t good….it might be that you are offloading all of the queries that would be using them to a different replica.  Likewise, a query might be performing badly and you go to troubleshoot it and find it’s incredibly fast on the server you test it on.  But maybe that’s because of the load on the server the user was being directed to.

Basically using Always on Availability groups is introducing a more complex environment, and while not all the things above are bad things, they are things you need to be aware of and consider.  The effective monitoring of an AOAG environment has different rules than monitoring a standalone server and the DBA needs to keep up with those.  But change is what we are all about in IT right?

The below is an extract from my presentation at SQL Saturday 252 on high availability and disaster recovery.  This post covers the basics of SQL Server Mirroring.

Like log shipping, mirroring is something that you are going to set up at the database level.   That means that if you change anything outside the database on the SQL instance, you are going to need to make the change on both sides of the equation.  So if you add a login on the principal then you need to manually make the same change on the mirror.  Incidentally you’ll notice there’s a fresh set of jargon with each new technology.  Logshipping has it’s primaries and secondaries.  Clustering has it’s active and passive nodes.  Mirroring has it’s principals and mirrors and replication has it’s publishers and subscribers.  Availability groups continue the pattern with primary and secondary replicas.

But anyway – we’re talking about mirroring.  So what mirroring is doing is redo-ing every insert, update or delete that happens on the principal server on another server.  So you are increasing your availability by effectively having two copies of the data which are pretty close to exactly the same.  I say pretty close because there’s a couple of ways you can set this up.

Firstly you can set it up as a synchronous mirror.  With a synchronous mirror every transaction that happens on the principal is written into the log on the principal, then moved over to the mirror, written into the log and committed on the mirror, then it sends an acknowledgement back to the principal to say the transaction is committed and only then does it actually commit the transaction and notify the application the transaction is successful.  It’s also known as High-Safety because your mirror is always going to hold all the data that the principal has….sometimes more infact.

Now if you are mirroring in high safety mode you can also add another server into the mix here – a witness server.  When you have a witness and your mirroring is configured in high safety mode you have a situation where automatic failover is available.  So, if something goes wrong on the principal, it’s automatically going to fail over to the mirror.  The principal now becomes the mirror and the mirror the principal.

That’s cool from the database point of view, and it’s nice to see that happen….when it’s meant to…and being able to think what a clever chap you are for keeping the data available, but it’s not like clustering, you aren’t pointing to a virtual server name that just knows where the actual data is sitting.  You need to make sure the applications connecting to the database have failover connection strings, so they know that if the principal server isn’t there, then they can try to get the data from a different server.  You also need to be pretty sure that all your logins and any agent jobs, maintenance plans and backups can all handle the state of the database.  So does your backup check to see if the database is online before it kicks off, or does it just fail?

Now, all that is available in the standard edition of SQL Server, and gives you a nice high availability option.  But if you spend your pennies on the Enterprise version….you can also have Asynchronous mirroring.  What does that do?  Well basically the same thing, it’s just that instead of committing on the mirror before it will commit on the principal, SQL just goes ahead and writes your transaction locally then fires off a copy of it to the mirror.  It doesn’t care if you have committed to a mirror, it just wants to get on with writing things locally and returning responses to the application.  You don’t have a witness involved and there is no automatic failover.  So….it seems like this is worse right?  You are paying Enterprise licensing for something that doesn’t give you an equal level of availability?  That seems like a pretty poor deal, but what you do buy is….performance.  Because there is no latency on the write across the network your application is going to perform much much faster.  And that’s why the other name for this is High-Performance mode.

There’s also some nice bonuses to mirroring.  In 2008 and higher….and we are all on 2008 or higher right?…..you have Automatic page repair.  So if there’s an attempt to read a page on the principal and it finds that page is bad it will go over to the mirror and try and get a clean copy of the page and fix the principal then send that info back to the application.

Also introduced in 2008 the data stream was compressed which lowers the amount of data being sent both ways, and as a result lowers the latency of the transaction.

And you can report off the mirrored databases.  CAN being the important word here.  You do this using snapshots on the mirror side and snapshots of course require enterprise edition.  It’s got some serious drawbacks around the point in time nature of the reporting, the database needing to be synchronised before you can do the snapshot.  I’m not going to talk about it too much more because I’ve never seen an implementation of it I’d be happy to call a reporting solution.

On the minus side, there’s implications to doing this in the SQL log and you need to do some planning around log sizes and physical structure.  And there’s network issues, particularly around High Safety.  You are sending a lot of information across the network and you want it to be pretty close to realtime.  If you are using High-Safety then you aren’t going to get a response to your application until it’s committed on the mirror as well as the principal, so if there’s lag, be it from limited hardware or just purely from volume of data being transferred, there’s a relatively high chance of a time out.  This can be mitigated by having good hardware, good network speeds and applications having good code.  If your lucky you may even be able to influence one of those things.

And an important point – Mirroring is deprecated.  That’s different from discontinued – it’s still there, it’s just on the chopping block.  In a future version it will be discontinued and we will be using Availability groups instead.