The Database is not accessible after creating Always On Availability Group

 Having created a fully functional SQL Server Always On Availability Group, I connect using SSMS.

Upon trying to expand the database, I get this message: "The database [...] is not accessible (Object Explorer).

The stack trace mentions get_CanGetChildren().

File this one under "sometimes, our lives are meant to serve as a warning to others."

I was connecting to the Windows Server Failover Cluster, rather than the SQL Server listener. SSMS / SQL Server is happy to let you do this, but then you're not able to expand the database.

When I connected via the listener, it worked without issue.

Using PowerShell as a port scanner

I've been having a lot of servers lately that have been stood up for me by the hero's in our IT department (that is not sarcasm - these guys are awesome) without having the right holes poked in the firewall.

For instance, SQL Server listens, by default on port 1433. (NOTE: Best practice for server hardening is to change the default instance name and port)

To test this, I'd been using a clunky old port scanner that I wasn't happy with.

I therefore googled and found this excellent blog post by jblanchard.

The portion that I actually use is this:

PowerShell port scanner:

1..1024 | % {echo ((new-object Net.Sockets.TcpClient).Connect("10.0.0.100",$_)) "Port $_ is open!"} 2>$null

I usually change the first bit to 1433 (or whatever specific port I want to check )

1433 | % {echo ((new-object Net.Sockets.TcpClient).Connect("Myserver.MyDomain.com",$_)) "Port $_ is open!"} 2>$null

Getting read-only access to DMV's for querying system and performance stats

Most of what we're going to want to look at when you're having production issues are available through DMV's.

  

If granting select to sys.dm* is a bit too much, these are the tables I'm generally most interested in:

Required Permissions:

To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission.

For Query tuning:

sys.dm_exec_query_stats

sys.dm_exec_sql_text

sys.dm_exec_text_query_plan

For general Waits and locks:

sys.dm_tran_locks

sys.dm_os_wait_stats

For Missing Indexes:

sys.dm_db_missing_index_group_stats

sys.dm_db_missing_index_groups

sys.dm_db_missing_index_details

sys.databases

For IO_STALL:

sys.dm_io_virtual_file_stats

sys.master_files

/* NOTE: The following should not be needed as users are already set up

USE [master]

GO

CREATE USER [theUser] FOR LOGIN [theUser] WITH DEFAULT_SCHEMA=[dbo]

GO

USE [master]

GO

GRANT CONNECT TO [theUser] 

GO

*/

USE [master]

GO

GRANT SELECT ON sys.dm_os_hosts TO [theUser]

GRANT SELECT ON sys.dm_os_memory_brokers TO [theUser]

GRANT SELECT ON sys.dm_os_memory_allocations TO [theUser]

GRANT SELECT ON sys.dm_db_mirroring_past_actions TO [theUser]

GRANT SELECT ON sys.dm_xe_session_object_columns TO [theUser]

GRANT SELECT ON sys.dm_os_loaded_modules TO [theUser]

GRANT SELECT ON sys.dm_db_task_space_usage TO [theUser]

GRANT SELECT ON sys.dm_os_memory_objects TO [theUser]

GRANT SELECT ON sys.dm_audit_class_type_map TO [theUser]

GRANT SELECT ON sys.dm_os_schedulers TO [theUser]

GRANT SELECT ON sys.dm_os_server_diagnostics_log_configurations TO [theUser]

GRANT SELECT ON sys.dm_hadr_instance_node_map TO [theUser]

GRANT SELECT ON sys.dm_os_dispatcher_pools TO [theUser]

GRANT SELECT ON sys.dm_os_threads TO [theUser]

GRANT SELECT ON sys.dm_exec_requests TO [theUser]

GRANT SELECT ON sys.dm_tran_commit_table TO [theUser]

GRANT SELECT ON sys.dm_fts_outstanding_batches TO [theUser]

GRANT SELECT ON sys.dm_repl_tranhash TO [theUser]

GRANT SELECT ON sys.dm_hadr_cluster TO [theUser]

GRANT SELECT ON sys.dm_qn_subscriptions TO [theUser]

GRANT SELECT ON sys.dm_db_session_space_usage TO [theUser]

GRANT SELECT ON sys.dm_exec_query_optimizer_info TO [theUser]

GRANT SELECT ON sys.dm_xe_map_values TO [theUser]

GRANT SELECT ON sys.dm_tran_top_version_generators TO [theUser]

GRANT SELECT ON sys.dm_fts_fdhosts TO [theUser]

GRANT SELECT ON sys.dm_xe_sessions TO [theUser]

GRANT SELECT ON sys.dm_db_log_space_usage TO [theUser]

GRANT SELECT ON sys.dm_hadr_name_id_map TO [theUser]

GRANT SELECT ON sys.dm_os_waiting_tasks TO [theUser]

GRANT SELECT ON sys.dm_exec_background_job_queue TO [theUser]

GRANT SELECT ON sys.dm_db_missing_index_details TO [theUser]

GRANT SELECT ON sys.dm_clr_properties TO [theUser]

GRANT SELECT ON sys.dm_os_sublatches TO [theUser]

GRANT SELECT ON sys.dm_exec_query_memory_grants TO [theUser]

GRANT SELECT ON sys.dm_logpool_hashentries TO [theUser]

GRANT SELECT ON sys.dm_tran_current_snapshot TO [theUser]

GRANT SELECT ON sys.dm_os_wait_stats TO [theUser]

GRANT SELECT ON sys.dm_os_memory_node_access_stats TO [theUser]

GRANT SELECT ON sys.dm_os_spinlock_stats TO [theUser]

GRANT SELECT ON sys.dm_database_encryption_keys TO [theUser]

GRANT SELECT ON sys.dm_hadr_availability_replica_states TO [theUser]

GRANT SELECT ON sys.dm_broker_connections TO [theUser]

GRANT SELECT ON sys.dm_db_mirroring_auto_page_repair TO [theUser]

GRANT SELECT ON sys.dm_server_registry TO [theUser]

GRANT SELECT ON sys.dm_os_dispatchers TO [theUser]

GRANT SELECT ON sys.dm_os_stacks TO [theUser]

GRANT SELECT ON sys.dm_filestream_non_transacted_handles TO [theUser]

GRANT SELECT ON sys.dm_xe_session_targets TO [theUser]

GRANT SELECT ON sys.dm_audit_actions TO [theUser]

GRANT SELECT ON sys.dm_hadr_availability_group_states TO [theUser]

GRANT SELECT ON sys.dm_os_ring_buffers TO [theUser]

GRANT SELECT ON sys.dm_db_missing_index_groups TO [theUser]

GRANT SELECT ON sys.dm_hadr_cluster_members TO [theUser]

GRANT SELECT ON sys.dm_db_uncontained_entities TO [theUser]

GRANT SELECT ON sys.dm_exec_cached_plans TO [theUser]

GRANT SELECT ON sys.dm_hadr_availability_replica_cluster_states TO [theUser]

GRANT SELECT ON sys.dm_exec_sessions TO [theUser]

GRANT SELECT ON sys.dm_broker_forwarded_messages TO [theUser]

GRANT SELECT ON sys.dm_resource_governor_resource_pools TO [theUser]

GRANT SELECT ON sys.dm_os_memory_clerks TO [theUser]

GRANT SELECT ON sys.dm_hadr_auto_page_repair TO [theUser]

GRANT SELECT ON sys.dm_repl_articles TO [theUser]

GRANT SELECT ON sys.dm_xe_session_events TO [theUser]

GRANT SELECT ON sys.dm_fts_memory_buffers TO [theUser]

GRANT SELECT ON sys.dm_fts_index_population TO [theUser]

GRANT SELECT ON sys.dm_tran_current_transaction TO [theUser]

GRANT SELECT ON sys.dm_os_cluster_properties TO [theUser]

GRANT SELECT ON sys.dm_os_child_instances TO [theUser]

GRANT SELECT ON sys.dm_exec_connections TO [theUser]

GRANT SELECT ON sys.dm_server_memory_dumps TO [theUser]

GRANT SELECT ON sys.dm_exec_background_job_queue_stats TO [theUser]

GRANT SELECT ON sys.dm_os_memory_broker_clerks TO [theUser]

GRANT SELECT ON sys.dm_filestream_file_io_handles TO [theUser]

GRANT SELECT ON sys.dm_hadr_availability_replica_cluster_nodes TO [theUser]

GRANT SELECT ON sys.dm_fts_active_catalogs TO [theUser]

GRANT SELECT ON sys.dm_tran_database_transactions TO [theUser]

GRANT SELECT ON sys.dm_filestream_file_io_requests TO [theUser]

GRANT SELECT ON sys.dm_cdc_log_scan_sessions TO [theUser]

GRANT SELECT ON sys.dm_os_memory_cache_clock_hands TO [theUser]

GRANT SELECT ON sys.dm_repl_schemas TO [theUser]

GRANT SELECT ON sys.dm_db_mirroring_connections TO [theUser]

GRANT SELECT ON sys.dm_db_partition_stats TO [theUser]

GRANT SELECT ON sys.dm_os_sys_memory TO [theUser]

GRANT SELECT ON sys.dm_io_pending_io_requests TO [theUser]

GRANT SELECT ON sys.dm_hadr_cluster_networks TO [theUser]

GRANT SELECT ON sys.dm_os_nodes TO [theUser]

GRANT SELECT ON sys.dm_tcp_listener_states TO [theUser]

GRANT SELECT ON sys.dm_os_memory_cache_entries TO [theUser]

GRANT SELECT ON sys.dm_os_virtual_address_dump TO [theUser]

GRANT SELECT ON sys.dm_cryptographic_provider_properties TO [theUser]

GRANT SELECT ON sys.dm_tran_transactions_snapshot TO [theUser]

GRANT SELECT ON sys.dm_os_memory_cache_hash_tables TO [theUser]

GRANT SELECT ON sys.dm_cdc_errors TO [theUser]

GRANT SELECT ON sys.dm_resource_governor_configuration TO [theUser]

GRANT SELECT ON sys.dm_exec_query_stats TO [theUser]

GRANT SELECT ON sys.dm_fts_semantic_similarity_population TO [theUser]

GRANT SELECT ON sys.dm_clr_tasks TO [theUser]

GRANT SELECT ON sys.dm_os_worker_local_storage TO [theUser]

GRANT SELECT ON sys.dm_db_persisted_sku_features TO [theUser]

GRANT SELECT ON sys.dm_db_index_usage_stats TO [theUser]

GRANT SELECT ON sys.dm_os_buffer_descriptors TO [theUser]

GRANT SELECT ON sys.dm_tran_active_snapshot_database_transactions TO [theUser]

GRANT SELECT ON sys.dm_server_services TO [theUser]

GRANT SELECT ON sys.dm_tran_active_transactions TO [theUser]

GRANT SELECT ON sys.dm_db_file_space_usage TO [theUser]

GRANT SELECT ON sys.dm_broker_activated_tasks TO [theUser]

GRANT SELECT ON sys.dm_broker_queue_monitors TO [theUser]

GRANT SELECT ON sys.dm_os_memory_cache_counters TO [theUser]

GRANT SELECT ON sys.dm_tran_session_transactions TO [theUser]

GRANT SELECT ON sys.dm_clr_appdomains TO [theUser]

GRANT SELECT ON sys.dm_exec_trigger_stats TO [theUser]

GRANT SELECT ON sys.dm_os_memory_pools TO [theUser]

GRANT SELECT ON sys.dm_os_latch_stats TO [theUser]

GRANT SELECT ON sys.dm_io_backup_tapes TO [theUser]

GRANT SELECT ON sys.dm_resource_governor_workload_groups TO [theUser]

GRANT SELECT ON sys.dm_hadr_database_replica_states TO [theUser]

GRANT SELECT ON sys.dm_fts_memory_pools TO [theUser]

GRANT SELECT ON sys.dm_resource_governor_resource_pool_affinity TO [theUser]

GRANT SELECT ON sys.dm_os_sys_info TO [theUser]

GRANT SELECT ON sys.dm_tran_locks TO [theUser]

GRANT SELECT ON sys.dm_exec_procedure_stats TO [theUser]

GRANT SELECT ON sys.dm_hadr_database_replica_cluster_states TO [theUser]

GRANT SELECT ON sys.dm_exec_query_transformation_stats TO [theUser]

GRANT SELECT ON sys.dm_exec_query_resource_semaphores TO [theUser]

GRANT SELECT ON sys.dm_repl_traninfo TO [theUser]

GRANT SELECT ON sys.dm_db_missing_index_group_stats TO [theUser]

GRANT SELECT ON sys.dm_fts_population_ranges TO [theUser]

GRANT SELECT ON sys.dm_os_performance_counters TO [theUser]

GRANT SELECT ON sys.dm_os_workers TO [theUser]

GRANT SELECT ON sys.dm_xe_session_event_actions TO [theUser]

GRANT SELECT ON sys.dm_db_script_level TO [theUser]

GRANT SELECT ON sys.dm_server_audit_status TO [theUser]

GRANT SELECT ON sys.dm_io_cluster_shared_drives TO [theUser]

GRANT SELECT ON sys.dm_os_tasks TO [theUser]

GRANT SELECT ON sys.dm_db_fts_index_physical_stats TO [theUser]

GRANT SELECT ON sys.dm_xe_packages TO [theUser]

GRANT SELECT ON sys.dm_logpool_stats TO [theUser]

GRANT SELECT ON sys.dm_os_memory_nodes TO [theUser]

GRANT SELECT ON sys.dm_tran_version_store TO [theUser]

GRANT SELECT ON sys.dm_os_windows_info TO [theUser]

GRANT SELECT ON sys.dm_os_cluster_nodes TO [theUser]

GRANT SELECT ON sys.dm_os_process_memory TO [theUser]

GRANT SELECT ON sys.dm_xe_objects TO [theUser]

GRANT SELECT ON sys.dm_xe_object_columns TO [theUser]

GRANT SELECT ON sys.dm_clr_loaded_assemblies TO [theUser]

GO

GRANT VIEW SERVER STATE TO [theUser]

GO

GRANT VIEW DATABASE STATE TO [theUser]

GO

SQL Server: T-SQL JOB to kill sessions with long-running transactions

We have a situation in a build/test server in which the application server (jboss, in this case) is unceremoniously killed by the build process.

This is leaving us with transactions that are not committed, nor are they rolled back. I like to call these zombies. SQL Server thinks they're just happily executing.

Because SQL Server has no server side lock, transaction, or query timeout, I developed this job to simulate a 10 minute transaction timeout.

It's not sophisticated, but it will give you a good starting point in case you should have to create your own .

if not exists (select * from sys.schemas where name = 'LOGSCHEMA')
EXEC ('CREATE SCHEMA [LOGSCHEMA] AUTHORIZATION [dbo]')
GO

if exists (select * from sys.tables where name = '#OLD_SPIDS')
EXEC ('DROP TABLE #OLD_SPIDS')
GO

 /* NOTE: In the following, I fully qualified the Kill_Long_Running_tran_log table as dbname.schema.tablename. DB_NAME will need to be changed */

if not exists (select * from sys.tables where name = 'Kill_Long_Running_tran_Log')
EXEC ('CREATE TABLE [/*change db_name */DB_NAME].[LOGSCHEMA].[Kill_Long_Running_tran_Log] (msg_timestamp datetime2(7), msg varchar(256))')

DECLARE @session_to_kill int = 0
DECLARE @kill_session_statement nvarchar(max)

select dtst.session_id
, dtst.transaction_id
, dtat.name
, dtat.transaction_begin_time
INTO #OLD_SPIDS
from sys.dm_tran_session_transactions dtst
join sys.dm_tran_active_transactions dtat
on dtst.transaction_id = dtat.transaction_id
where dtat.transaction_begin_time < DATEADD(MINUTE,-10,current_timestamp)

select @session_to_kill = session_id
from #OLD_SPIDS
where transaction_begin_time = (select min(transaction_begin_time) from #OLD_SPIDS )

select @kill_session_statement = 'kill ' + cast(@session_to_kill as varchar(10))

if (@session_to_kill != 0)
begin
insert into [LOGSCHEMA].[Kill_Long_Running_tran_Log]
values (current_timestamp, @kill_session_statement)
exec (@kill_session_statement)
end 

DROP TABLE #OLD_SPIDS

Regular Expressions in SQL Server, DB2 UDB, and Oracle

I was a bit surprised that not all RDBMS platforms offer robust, native support for regular expressions.

Oracle:

Oracle introduced this in 10g. It is implemented in roughly the way I would expect and looks like it's pretty useful.
Reference

DB2:

DB2 seems to have this capability in 9.7. NOTE that I have not checked to see if it was available in earlier releases.  However, it appears you have to go through the XQuery interface. I'm not surprised by this, but a bit disappointed.  I had hoped for an implementation more like Oracle's. I think that the DB2 way is going to be cumbersome for the average user.

Example:

db2 "with val as (
 select t.text
 from texts t
 where xmlcast(xmlquery('fn:matches(\$TEXT,''^[A-Za-z 0-9]*$'')') as integer) = 0
)
select * from val"

Reference1, IBM DB2 for LUW 9.7 information center reference

MS SQL Server: 

SQL Server, it seems, requires you to roll your own solution or look to some sort of 3rd party.  I'm very surprised by this.

There are 3rd party libraries that appear to be pretty robust.  One example can be found here: SQL Server Central article on RegEx in MSSQL Server

I suppose that they want us to use the full text search, which appears to have robust features, and address the same purpose.  Still, just a bit surprised ...

Email your SQL Server Administrators regarding the last backups for databases

The following script will send a notification that tells when the last database backup was performed.

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Sqlalerts',
@recipients = 'first.last@somoecompany.com',
@copy_recipients ='first.last@somoecompany.com',
@query ='exec msdb.dbo.uspMonitorbackups',
@query_result_separator=' ',
@subject = 'DB's w/o backups for last 10 days on SS Instance Name',
@attach_query_result_as_file = 0,
@body= 'Database without backup from last 10 days',
@body_format='text',
@query_result_width = 1000,
@append_query_error = 1,
@query_result_no_padding = 1;

Most of these parameters are self-explanatory.  I will say that profile_name = the profile FROM which the email will be sent.  This ends up in the "FROM" line of the email.

sp_send_dbmail documentation can be found here: http://msdn.microsoft.com/en-us/library/ms190307.aspx 

The contents of uspMonitorbackups is as follows:

USE [msdb]
GO
/****** Object:  StoredProcedure [dbo].[uspMonitorbackups]    Script Date: 03/19/2013 16:51:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  Create procedure [dbo].[uspMonitorbackups]
  as
   SELECT  sd.name,
        bs.TYPE,
        bs.database_name,
        max(bs.backup_start_date) as last_backup,
        CONVERT(varchar(30),DATABASEPROPERTYEX(sd.name,'Status')) as [Database Status]
FROM    master..sysdatabases sd
        Left outer join msdb..backupset bs on rtrim(bs.database_name) = rtrim(sd.name)
        left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE     bs.type = 'D'
Group by sd.name,
        bs.TYPE,
        bs.database_name
HAVING (MAX(bs.backup_start_date) < DATEADD(dd,-10,GETDATE()))

Trace SQL Server using Scripting and No Client for Better Performance

There is a very good article on creating server side traces in SQL Server here:
SQL Server Pedia - Server Side Traces

The essence of the whole thing is this:
Monitoring using the SQL Server Profiler creates considerable overhead, whether you run it on the server that hosts the database itself, or on a client machine.
Monitoring using a script of the sort generated in the linked article, on the other hand, creates nearly zero performance degradation (per Linchi Shea's fine article, found here: trace-profiler-test).  I have linked to Linchi's test results as well as the article.  Test Results