Bob Beauchemin - SQL Server Blog

Over 1000 XEvents in SQL Server 2016 CTP2. Here are the new ones.

Posted on: June 2, 2015 10:59 am

Extended events has firmly established itself as the premier diagnostic feature in SQL Server and SQL Server 2016 brings along more events to correspond to new features and fill in some diagnostic gaps. I always like to investigate a new release by seeing what’s new to trace. So here’s a “diff” of SQL Server 2016 CTP2 vs SQL Server 2014 (RTM) with respect to extended events. In SQL Server 2016 CTP2, the team has provided OVER 1000 different events, 1034 to be precise. 165 of them are new.

First, a couple of caveats:

The SQL Server team occasionally introduces events before the feature is available. An example of this is the query store, where a number of events were included in the metadata for SQL Server 2014, before the feature was available.

Because SQL Server and Azure SQL Database (ASD) share a common codebase, some events may be more useful (or only useful) in an ASD environment. An example of this may be the sqlserver.fulltextlog_written event. Full-text search was just introduced in ASD V1.2 and in ASD, you can’t read the full-text logs, a common diagnostic. Maybe this event will fill the gap.

Some of the events may have actually been introduced in SQL Server 2014 SP1. I remember a blog post that mentioned columnstore events in 2014 SP1, or for ASD V1.2. Figured that the 2016CTP2 vs 2014 comparison was more useful.

If you’re looking for these events in the XEvent GUI, don’t forget to turn on the debug channel events, which are off by default in the GUI. There appears to be some refactoring going on the the GUI categories, too, so use the GUI’s “event name completion” instead.

And of course, my observations:

The columnstore feature, including batch mode in queries, which had little to no coverage in 2014 extended events, now has rich coverage.

The new stretch tables and query store features are covered. Even JSON production has some diagnostics.

There are new series of events for availability groups (HADR) and in-memory tables (XTP), as well as Azure IO.

I’m especially happy with the new SQLCLR events. These will be helpful for folks who use built-in SQLCLR features (like spatial data) and those who work on the edges of user SQLCLR (i.e. unsafe assemblies).

Only 3 events were “discontinued” in SQL Server 2016 CTP2 vs. 2014, two refactored query store events, and one likely internal one, for columnstore code coverage.

Here’s the list…enjoy.

@bobbeauch

Full Name   description
qds.query_store_background_task_persist_finished   Fired if the background task for Query Store data persistence is completed successfully
qds.query_store_background_task_persist_started   Fired if the background task for Query Store data persistence started execution
qds.query_store_capture_policy_abort_capture   Fired when an UNDECIDED query failed to transition to CAPTURED.
qds.query_store_capture_policy_evaluate   Fired when the capture policy is evaluated for a query.
qds.query_store_capture_policy_start_capture   Fired when an UNDECIDED query is transitioning to CAPTURED.
qds.query_store_db_data_structs_not_released   Fired if Query Store data structures are not released when feature is turned OFF.
qds.query_store_db_diagnostics   Periodically fired with Query Store diagnostics on database level.
qds.query_store_db_settings_changed   Fired when Query Store settings are changed.
qds.query_store_db_whitelisting_changed   Fired when Query Store database whitelisting state is changed.
qds.query_store_generate_showplan_failure   Fired when Query Store failed to store a query plan because the showplan generation failed.
qds.query_store_global_mem_obj_size_kb   Periodically fired with Query Store global memory object size.
qds.query_store_load_started   Fired when query store load is started
qds.query_store_schema_consistency_check_failure   Fired when the QDS schema consistency check failed.
qds.query_store_size_retention_cleanup_finished   Fired when size retention policy clean-up task is finished.
qds.query_store_size_retention_cleanup_skipped   Fired when starting of size retention policy clean-up task is skipped because its minimum repeating period did not pass yet.
qds.query_store_size_retention_cleanup_started   Fired when size retention policy clean-up task is started.
qds.query_store_size_retention_plan_cost   Fired when eviction cost is calculated for the plan.
qds.query_store_size_retention_query_cost   Fired when query eviction cost is calculated for the query.
qds.query_store_size_retention_query_deleted   Fired when size based retention policy deletes a query from Query Store.
sqlclr.clr_context_dump   ClrContextDump triggered.
sqlclr.notify_on_clr_disabled   Event_ClrDisabled has been triggered in ClrHost.
sqlclr.on_app_domain_failure   AppDomain hit a failure.
sqlclr.on_app_domain_unloading   AppDomain is unloading.
sqlclr.on_host_policy_callback   IHostPolicyManager received an event.
sqlclr.on_host_policy_failure   IHostPolicyManager received an event.
sqlos.ex_raise2   Raised exception
sqlos.premature_systemthread_wakeup   system thread is woken up prematurely
sqlos.recalculate_mem_target   New Memory Targets which are set after RecalculateTarget is executed
sqlserver.availability_replica_database_fault_reporting   Occurs when a database reports a fault to the availability replica manager which will trigger a replica restart if the database is critical
sqlserver.backup_restore_progress_trace   Prints backup/restore progress trace messages with details
sqlserver.batchmode_sort_spill_file   Record the spill file read/write information for batch mode sort
sqlserver.batchmode_sort_status   Record batch mode sort status
sqlserver.column_store_expression_filter_apply   An expression bitmap filter was applied on a rowgroup column batch.
sqlserver.column_store_expression_filter_bitmap_set   An expression bitmap filter was set on a rowgroup column at rowgroup compile time.
sqlserver.columnstore_delete_buffer_closed_rowgroup_with_generationid_found   Delete buffer can not be flushed due to existence of one or more closed rowgroups with generation ID.
sqlserver.columnstore_delete_buffer_flush_failed   Columnstore delete buffer flush failed.
sqlserver.columnstore_delete_buffer_state_transition   Occurs when closed delete buffer state changes.
sqlserver.columnstore_delta_rowgroup_closed   A delta rowgroup was closed.
sqlserver.columnstore_no_rowgroup_qualified_for_merge   A user invoked a REORG command but based on the policy, no rowgroup qualified.
sqlserver.columnstore_rowgroup_compressed   A compressed rowgroup was created.
sqlserver.columnstore_rowgroup_merge_complete   A MERGE operation completed merging columnstore rowgroups together.
sqlserver.columnstore_rowgroup_merge_start   A MERGE operation started merging columnstore rowgroups together.
sqlserver.columnstore_tuple_mover_begin_delete_buffer_flush   Columnstore tuple mover started flushing a delete buffer.
sqlserver.columnstore_tuple_mover_compression_stats   Statistics about the movement of a deltastore to a compressed rowgroup, including duration, size, etc.
sqlserver.columnstore_tuple_mover_delete_buffer_flush_requirements_not_met   Occurs when column store tuple mover was not able to acquire required locks for flushing a delete buffer.
sqlserver.columnstore_tuple_mover_delete_buffer_truncate_requirements_not_met   Occurs when column store tuple mover was not able to acquire required locks for truncating a delete buffer.
sqlserver.columnstore_tuple_mover_delete_buffer_truncated   Columnstore tuple mover truncated delete buffer.
sqlserver.columnstore_tuple_mover_delete_buffers_swapped   Columnstore tuple mover swapped delete buffers.
sqlserver.columnstore_tuple_mover_end_delete_buffer_flush   Columnstore tuple mover completed flushing a delete buffer.
sqlserver.columnstore_tuple_mover_met_requirements_for_delete_buffer_flush   Occurs when column store tuple mover has acquired required locks and is ready to start flushing a delete buffer.
sqlserver.columnstore_tuple_mover_met_requirements_for_delete_buffer_truncate   Occurs when column store tuple mover has acquired required locks and is ready to start truncating a delete buffer.
sqlserver.columnstore_x_dbfl_acquired   Occurs when X Delete Buffer Flush Lock is acquired.
sqlserver.compressed_alter_column_is_md_only   Occurs during an alter column operation. Indicates whether the alter column is metadata only or not.
sqlserver.connection_accept   Occurs when a new connection is accepted by (or duplicated into) the server. This event serves to log all connection attempts.
sqlserver.connection_duplication_failure   Occurs when connection duplication fails
sqlserver.data_purity_checks_for_dbcompat_130   Occurs when an operation that may require a data purity check for dbcompat level 130 occurs.
sqlserver.database_recovery_times   Database recovery times
sqlserver.database_tde_encryption_scan_duration   Database TDE Encryption Scan
sqlserver.database_transaction_yield   Occurs when a database transaction yields execution due to TDS buffer being full.
sqlserver.fulltextlog_written   Errorlog written
sqlserver.global_transaction   Occurs when global transaction is started.
sqlserver.hadr_db_log_throttle   Occurs when DB log generation throttle changes.
sqlserver.hadr_db_log_throttle_configuration_parameters   Occurs when DB log generation throttle configuration parameters are read.
sqlserver.hadr_db_log_throttle_input   Occurs when HADR Fabric log management component updates log throttle.
sqlserver.hadr_db_marked_for_reseed   Occurs when a HADR secondary DB falls too far behind primary and is marked for reseed.
sqlserver.hadr_db_remote_harden_failure   A harden request as part of a commit or prepare failed due to remote failure.
sqlserver.hadr_log_block_send_complete   Occurs after a log block message has been sent. This event is only used for failpoints.
sqlserver.hadr_partner_log_send_transition   Log send transition between log writer and log capture.
sqlserver.hadr_partner_restart_scan   Restart partner scans for this partner.
sqlserver.hadr_physical_seeding_backup_state_change   Physical Seeding Backup Side State Change.
sqlserver.hadr_physical_seeding_failure   Physical Seeding Failure Event.
sqlserver.hadr_physical_seeding_forwarder_state_change   Physical Seeding Forwarder Side State Change.
sqlserver.hadr_physical_seeding_forwarder_target_state_change   Physical Seeding Forwarder Target Side State Change.
sqlserver.hadr_physical_seeding_progress   Physical Seeding Progress Event.
sqlserver.hadr_physical_seeding_restore_state_change   Physical Seeding Restore Side State Change.
sqlserver.hadr_physical_seeding_submit_callback   Physical Seeding Submit Callback Event.
sqlserver.hadr_send_harden_lsn_message   Occurs when we’re crafting a message to send containing a new hardened LSN on a secondary. Test only.
sqlserver.hadr_transport_configuration_state   Indicates session state changes
sqlserver.hadr_transport_dump_dropped_message   Use this event to trace dropped HADR transport messages throughout the system.
sqlserver.hadr_transport_dump_failure_message   Use this event to help trace HADR failure messages.
sqlserver.hadr_transport_dump_preconfig_message   Use this event to help trace HADR preconfig messages.
sqlserver.hadr_transport_sync_send_failure   Synchronous send failure in hadr transport.
sqlserver.hadr_transport_ucs_registration   Reports UCS registration state changes
sqlserver.json_depth_error   Occurs when depth of json text being parsed is bigger than 128.
sqlserver.json_parsing_error   Indicates json parser error. Occurs when json format is not valid.
sqlserver.json_stackoverflow_error   Json parser stack overflow.
sqlserver.json_unescaped_character   Jsonparser hitted unescaped character in json string.
sqlserver.log_pool_cache_miss   Occurs when a log consumer attempts to lookup a block from the log pool but fails to find it.
sqlserver.log_pool_push_no_free_buffer   Occurs when log pool push fails to get a free buffer and bails out.
sqlserver.login_event   This is an abbreviated version of process_login_finish, containing only the columns required by external monitoring telemetry pipeline.
sqlserver.page_cache_trace   Modification of the page cache.
sqlserver.private_login_accept   TDS connection accept event that is logged to private MDS table.
sqlserver.private_login_finish   TDS login finish event that is logged to private MDS table.
sqlserver.process_login_finish   This event is generated when server is done processing a login (success or failure).
sqlserver.query_execution_batch_filter   Occurs when batch processing filters one batch using expression services.
sqlserver.query_execution_batch_spill_started   Occurs when batch operator runs out of granted memory and initiates spilling to disk of another partition of in-memory data.
sqlserver.query_execution_column_store_rowgroup_scan_finished   Occurs when row bucket processor finishes column store row group scan.
sqlserver.query_execution_column_store_segment_scan_finished   Occurs when row bucket processor finishes column store segment scan.
sqlserver.query_execution_column_store_segment_scan_started   Occurs when column segment scan starts.
sqlserver.query_memory_grant_blocking   Occurs when a query is blocking other queries while waiting for memory grant
sqlserver.query_memory_grant_usage   Occurs at the end of query processing for queries with memory grant over 5MB to let users know about memory grant inaccuracies
sqlserver.query_trace_column_values   Trace output column values of each row on each query plan operator
sqlserver.remote_data_archive_db_ddl   Occurs when the database T-SQL ddl for stretching data is processed.
sqlserver.remote_data_archive_provision_operation   Occurs when a provisioning operation starts or ends.
sqlserver.remote_data_archive_query_rewrite   Occurs when RelOp_Get is replaced during query rewrite for Stretch.
sqlserver.remote_data_archive_table_ddl   Occurs when the table T-SQL ddl for stretching data is processed.
sqlserver.remote_data_archive_telemetry   Occurs whenever an on premise system transmits a telemetry event to Azure DB.
sqlserver.remote_data_archive_telemetry_rejected   Occurs whenever an AzureDB Stretch telemetry event is rejected
sqlserver.report_login_failure   This event is generated for a TDS login failure.
sqlserver.rpc_starting_aggregate   Occurs periodically, aggregating all occasions an rpc call is started.
sqlserver.rpc_starting_aggregate_xdb   Occurs periodically, aggregating all occasions an rpc call is started.
sqlserver.sql_batch_starting_aggregate   Occurs periodically, aggregating all occasions a sql batch is started.
sqlserver.sql_batch_starting_aggregate_xdb   Occurs periodically, aggregating all occasions a sql batch is started.
sqlserver.startup_dependency_completed   Occurs on the completion of a startup dependency in the SQL Server startup sequence
sqlserver.stretch_codegen_errorlog   Reports the output from the code generator
sqlserver.stretch_codegen_start   Reports the start of stretch code generation
sqlserver.stretch_create_migration_proc_start   Reports the start of migration procedure creation
sqlserver.stretch_create_remote_table_start   Reports the start of remote table creation
sqlserver.stretch_create_update_trigger_start   Reports the start of create update trigger for remote data archive table
sqlserver.stretch_database_disable_completed   Reports the completion of a ALTER DATABASE SET REMOTE_DATA_ARCHIVE OFF command
sqlserver.stretch_database_enable_completed   Reports the completion of a ALTER DATABASE SET REMOTE_DATA_ARCHIVE ON command
sqlserver.stretch_database_events_submitted   Reports the completion telemetry transfer
sqlserver.stretch_migration_debug_trace   Debug trace of stretch migration actions.
sqlserver.stretch_migration_queue_migration   Queue a packet for starting migration of the database and object.
sqlserver.stretch_migration_sp_stretch_get_batch_id   Call sp_stretch_get_batch_id
sqlserver.stretch_migration_start_migration   Start migration of the database and object.
sqlserver.stretch_sync_metadata_start   Reports the start of metadata checks during the migration task.
sqlserver.stretch_table_codegen_completed   Reports the completion of code generation for a stretched table
sqlserver.stretch_table_provisioning_step_duration   Reports the duration of a stretched table provisioning operation
sqlserver.stretch_table_remote_creation_completed   Reports the completion of remote execution for the generated code for a stretched table
sqlserver.stretch_table_row_migration_event   Reports the completion of the migration of a batch of rows
sqlserver.stretch_table_row_migration_results_event   Reports an error or completion of a successful migration of a number of batches of rows
sqlserver.stretch_table_unprovision_completed   Reports the completion removal of local resources for a table that was unstretched
sqlserver.stretch_table_validation_error   Reports the completion of validation for a table when the user enables stretch
sqlserver.stretch_unprovision_table_start   Reports the start of stretch table un-provisioning
sqlserver.trust_verification_failed   Occurs when a SQL Server binary fails Authenticode signature verification.
sqlserver.unable_to_verify_trust   Occurs when SQL Server is unable to perform Authenticode signature verification on binaries.
sqlserver.xio_blob_properties_obtained   Windows Azure Storage blob property is obtained from response header.
sqlserver.xio_failed_request   Failed to complete a request to Windows Azure Storage.
sqlserver.xio_header_obtained   Response header is obtained from request to Windows Azure Storage.
sqlserver.xio_read_complete   Read complete from Windows Azure Storage response.
sqlserver.xio_request_opened   A request is opened to Windows Azure Storage.
sqlserver.xio_send_complete   Request send to Windows Azure Storage is complete.
sqlserver.xio_write_complete   Request send to Windows Azure Storage is complete.
sqlserver.xstore_acquire_lease   The properties of the lease acquire reques.
sqlserver.xstore_create_file   Creating an XStore file has been attempted with the options below.
sqlserver.xstore_debug_trace   Telemetry tracing event has occurred.
sqlserver.xstore_lease_renewal_request   Attempt to renew blob lease
sqlserver.xtp_alter_table   Occurs at start of XTP table altering.
sqlserver.xtp_drop_table   Occurs after an XTP table has been dropped.
ucs.ucs_negotiation_completion   UCS transport connection negotiation completed
XtpCompile.cl_duration   Reports the duration of the C compilation.
XtpEngine.trace_dump_deleted_object_table_row   Dump deleted object table row
XtpEngine.xtp_ckptctrl_abort_checkpoint   Indicates that the checkpoint close thread aborted a checkpoint.
XtpEngine.xtp_ckptctrl_close_checkpoint   Indicates that the checkpoint close thread hardened a checkpoint.
XtpEngine.xtp_ckptctrl_close_install_merge   Indicates that the checkpoint close thread installed a merge.
XtpEngine.xtp_ckptctrl_new_segment_definition   Indicates that the checkpoint controller processed a new segment definition.
XtpEngine.xtp_ckptctrl_storage_array_grow   Indicates the XTP storage array has grown in size.
XtpEngine.xtp_merge_request_start   Indicates that an XTP storage merge was requested.
XtpEngine.xtp_merge_request_stop   Indicates that an XTP storage merge request ended.
XtpEngine.xtp_merge_start   Indicates that an XTP storage merge range is starting.
XtpEngine.xtp_merge_stop   Indicates that an XTP storage merge range completed.
XtpEngine.xtp_redo_log_corruption   Indicates that the redo process failed due to log corruption.
XtpEngine.xtp_root_file_deserialized   Indicates that the load of a checkpoint root file is complete.
XtpEngine.xtp_root_file_serialized   Indicates that the write of the checkpoint root file is complete.

Posted in: Extended Events, SQL Server 2016

Comments Off

Taking the Azure SQL Database row-level security preview for a spin

By: Bob Beauchemin

Posted on: January 30, 2015 3:35 pm

The security announcements around Azure SQL Database keep coming. Auditing was implemented a few months ago, and today it was followed by a preview of row-level security. There were also announcements around transparent data encryption (TDE) and a new dynamic data masking feature in future but these aren’t available, even in preview, yet. The row-level security announcements like the one in the SQL Server Team Blog preceded posting the documentation page, so if you originally got a “page not found” it’s up there now.

So I thought I’d provision-up a database (takes about 30 seconds if you have an Azure subscription) and take RLS for a spin. It’s quite important to read the documentation page first, not only because there’s a nice demo in there, but also to get a feel for the implementation details, i.e. what the feature is and what exactly it’s documented to do. I’ll try not to simply regurgitate the documentation here. That’s difficult because it’s quite a nice one-page summary with additional info in the “other resources” section.

RLS is implemented by means of a new schema-scoped object, the SECURITY POLICY object, and inline table-valued functions (TVFs) that implement the policy. The CREATE SECURITY POLICY DDL uses filter predicate clauses to tie the TVFs to individual tables, one filter predicate per table. The TVFs return a one-column table, with a column name of fn_securitypredicate_result. You can use any of the mechanisms and built-in security functions to determine which principals you’re dealing with and you can access which rows they can access with the TVF parameters can be column names. I’d stay away from security functions marked deprecated in books online. Remember, an inline TVF only contains a single SQL SELECT statement.

There a couple of nice things about using inline table-valued functions. First off, the functions need to be defined WITH SCHEMABINDING so there’s no way for someone to simply alter the function unless they drop and re-create the SECURITY POLICY object. In addition, the fact that it’s an inline TVF means that the function shows up as a specific “Filter” iterator in query plans because it’s inlined (unlike multi-statement and scalar TVFs which are not inlined, i.e. have a separate query plan of their own).

You can use ALTER SECURITY POLICY to turn the filters on and off, and to ADD and DROP filters. There are two new metadata tables, sys.security_policies and sys.security_predicates to allow visibility.

Naturally, DDL against SECURITY POLICY and the TVFs can be audited (I did try this out to check that it appeared in the audit file). Because the SECURITY POLICY object references the TVFs, I actually had to grant a number of permissions to allow “Manager” (from the first example in the docs) to create the policy (note: I changed the example a bit to define the SECURITY POLICY in the “Security” schema instead of dbo schema):

GRANT SELECT ON Security.fn_securitypredicate TO Manager;
GRANT REFERENCES ON Security.fn_securitypredicate TO Manager;
GRANT REFERENCES ON SCHEMA::dbo TO Manager; — Needed because the table lives in DBO schema
GRANT ALTER ANY SECURITY POLICY TO Manager;

— This allows ALTER or DROP, but not to CREATE unless ALTER ANY SECURITY POLICY is also given
— This may not be granular enough, unless the RLS objects are the only ones in the schema.
GRANT ALTER ON SCHEMA::Security TO Manager

It looks like this feature has the potential to make it into the box-product SQL Server in future (the docs refer to it not being supported with memory-optimized tables for example, which Azure SQL Database doesn’t support yet), so even if you don’t use Azure SQL Database, the documentation page provides a good opportunity to CAREFULLY evaluate whether it will meet your needs, and get your comments/requests in now. There are four specific places in the documentation page to look at:

1. Limitations during the preview (e.g. limitation on views)
2. Limitations and restrictions (e.g. DBCC SHOW_STATISTICS)
3. The Description section (e.g. INSERT anywhere is allowed, not filtered)
4. A special section called “Security Note: Side-Channel Attacks”

I’d propose that the “INSERT anywhere not blocked” limitation is the biggest worry (modulo the side-channel attacks), especially in the multi-tenant (listed as a specific use-case) scenario. Although conceivably you might ameliorate this with an INSERT trigger. This doesn’t address the problem of someone changing CONTEXT_INFO though (as is mentioned in the doc), you’d need to set up different roles for each tenant as well.

Cheers, Bob (@bobbeauch)

Posted in: Azure SQL Database, Security

3 Comments

Azure SQL Database V12 Preview – Spatial Fully Functional

By: Bob Beauchemin

Posted on: December 12, 2014 3:12 pm

Yesterday’s blog post about Azure SQL Database V12 mentioned that one of the features I was particularly interested in seeing/testing were the spatial features. Interestingly, this was not even mentioned as an enhancement in the technical “What’s New” page. This may be due to the fact that the only place where a list of the limitations of existing Azure SQL Database spatial implementation was documented is Ed Katibah’s article about “Updated Spatial Features in the SQL Azure Q4 2011 Service Release“. Q4 2011 was the last update for ASD spatial that I was aware of. As an example, the Transact-SQL books online page for CREATE SPATIAL INDEX makes no mention of the fact that ASD doesn’t support AUTO_GRID spatial indexes and spatial index compression.

So I fired up an S1 instance of both a current (V11) database and a new V12 preview database (to ensure that I hadn’t missed an update to V11), and tested out all the features that Ed mentioned. They were never updated in pre-V12, but EVERYTHING is there in V12. Very cool!

You can refer to Ed’s article for a complete list (not supported in V11, supported in V12 and SQL Server 2012/2014), but here’s some of the highlights:
Spatial objects larger than a hemisphere
FULLGLOBE, circular/curve types, and associated methods
AutoGrid spatial index
Compression for spatial indexes
Spatial_Windows_Max_Cells query hint
Spatial Analysis Stored Procedures and Histograms

As long as I was experimenting in a V12 database, I thought I’d try the Codeplex SQL Spatial Tools (http://sqlspatialtools.codeplex.com/) an extention library for spatial built using SQLCLR. As I’d suspected, you can run CREATE ASSEMBLY by with a file system or Azure blob storage reference. Tooling is coming soon, but until then, I had to catalog the assembly from the file system to a “box version” SQL Server and then use SSMS Object Explorer to locate and right-click on the “box” SQL Server Assembly and use “Script Assembly as CREATE” to obtain a “CREATE ASSEMBLY … [from bits]” statement. The create-from-bits worked fine. The failure message using “local DLL” syntax is kind of interesting: “Msg 6585, Level 16, State 1, Line 2 Could not impersonate the client during assembly file operation.”

The SQL Spatial Tools assembly uses SQLCLR UDT, UDAggs, and UDFs, all seemed to work fine. Using SQLCLR along with spatial enables writing speedy spatial manipulation functions because each operation on a spatial object is not a separate T-SQL statement. So these are two features (SQLCLR and SQL spatial) that work well together.

That’s it for now.

Cheers, @bobbeauch

Posted in: Azure SQL Database, SQL Server Spatial

Comments Off

Not a “me-too announcement” blog on Azure SQL Database V12 preview

By: Bob Beauchemin

Posted on: December 11, 2014 1:23 pm

In general, I usually hate “me too” announcement blog posts. Over the years, I’ve considered it less than useful to simply repeat “Product XXX released to RTM” when nice marketing/technical announcements have already been posted for the folks that are responsible. Or even to rehash books online pages, as another example.

In keeping with that concept, it was announced today that Azure SQL Database (the PAAS offering) is previewing a V12-level (that’s SQL Server 2014-major version number) version of the database. If you’ve ever heard me harping (politely, I think 😉 on the fact that Azure SQL Database (ASB) has been V11-level since its release, that’s great news by itself.

The marketing announcement is here. The somewhat more technical “what’s new” announcement is here.

So what’s there to add? Before actually trying this out, here’s some things that came to mind.

1. There’s no full-text search feature included in ASB V12 that I can see. BUT… because Change Tracking works with ASB V12, we should be able to use Liam’s lovely sample (that syncs with Azure Search but requires SQL Server Change Tracking enabled) to sync ASB V12 with Azure Search now. Great.

2. We’re going to need new tools (SSMS, SSDT, PowerShell Azure cmdlets) to work with the new ASB V12 features. Updates, the tools are here: Azure PowerShell 0.8.12 that supports ASB V12 is available as of 12/16/2014. SQL Server 2014 CU5 contains support for ASB V12 in SSMS. Previews of SSDT and DAC that support ASB V12 were announced on 12/17/2014.

3. There’s SAFE assembly SQLCLR support. Since I don’t see CREATE ASSEMBLY functionality that works with Azure Blob as DLL, I think we’ll need to use CREATE ASSEMBLY…. [from bits] for now. There’s no EXTERNAL ACCESS support. I can see that because you shouldn’t attempt to trundle around in a file system or registry that isn’t yours. But how about the ability to call a web service? That’s EXTERNAL ACCESS and a (sort-of) natural for a web-based offering. I always caution against doing this (web service thing) synchronously in on-prem SQL Server (what if the website is down?), but there’s no service broker in ASB to make it asynchronous. But anyway, it’s not supported.

4. Spatial support in ASB was sort-of a 75/25 WRT new SQL Server 2014 features. Can’t wait to try these out… as well as trying out the Extended Event sessions for real. They should work much better than when I got overly excited about some early artifacts a few months ago.

5. This is the first version of ASB (that I can remember) that has some in-database functions segregated to premium databases (e.g. columnstore, parallel queries). Ancillary service DB functions (e.g point-in-time restore) were always different-by-edition.

That’s all for now. Guess there’s a lot of tires to kick here. Thanks for all the fish.

Cheers, @bobbeauch

Posted in: Azure SQL Database

8 Comments

AzureML: What components are used by the sample experiments?

By: Bob Beauchemin

Posted on: December 3, 2014 11:19 am

A few months ago, I embarked on a project to learn more about data mining, machine learning and, as a prerequisite, statistics. I was tired of hearing “statistics show that…” without enough “proof” and have taken a long side-trip toward Statistical Inference and Reproducible Research. I’d be much more interested in reading a scientific paper that comes with code and data than one with paper and forgone conclusions (possibly influenced by grant-money) only. And after hearing “this would be clearer with an understanding of calculus”, I brushed up on my calculus and linear algebra as well (it had been over 40 years ago, after all). I’d been working in languages like Octave and R and when Azure Machine Learning (AzureML) arrived, the idea that you could use R scripts in AzureML experiments was intriguing.

AzureML features a drag-and-drop canvas-based project system (the projects are called “experiments”). I tend to like programming with code over drag-drop-cut-paste, but the killer feature of AzureML may be that you can publish an endpoint that enables singleton or batch scoring, using your work. But back to the R scripts.

AzureML currently has about 25 sample experiments to use as templates or exemplars. After looking at a few of them, I thought it would be nice to find the experiments that use R scripts and see how the samples used them. Turns out that you run into problems doing this automatically. You can’t, at this point, persist an experiment using, say, an XML vocabulary or some JSON format. At all. To me, this was quite strange, because you can’t pick them up and move them, like you can with say, an SSIS package. And since the only artifacts in the Azure storage account are the blob containers “experimentoutput” and “uploadedresources”, neither one of which, as far as I could see, contains the experiment “definition” (i.e. the project).

I validated this observation (on the AzureML forum) and put in a request on the AzureML improvement suggestions site. Since there already is a fairly standard XML vocabulary called PMML, I suggested using that. Other reasons for having this available would be for version control and providing an offline format in the event that you inadvertently delete your AzureML storage account. In addition to the uses I’ve already mentioned.

To end this post with something useful, I did do the search for R Scripts in the sample AzureML experiments “by hand”, by copying each sample opening the canvas and typing in each unique component use (but not the parameters or scripts) in notepad. Enclosed is a file that contains this information, I hope you’ll find it useful. Please excuse any inadvertent typos.

Hopefully in future, this would be a simple case of querying the set of “experiment projects” with XPath. Or if you’re XML-query-phobic, with grep.

Enjoy.
Cheers, @bobbeauch

azureml_sample-component xref

Posted in: Machine Learning

1 Comment

Book Review: Chris Webb’s “Power Query for Power BI and Excel”

By: Bob Beauchemin

Posted on: September 15, 2014 11:11 am

I’ve been experimenting with Power Query and the rest of the Power BI suite in Excel (desktop edition) since it was originally released. But, like most folks that aren’t completely centered in that part of the data-based offerings, the plethora of new functionality and new releases made it difficult to keep up. The online help has improved, but the distinction between Office 365 Power BI and Power BI desktop and the fast release cadence (updates appear almost monthly), made it difficult to write books (which require pre-production time). Having met Chris Webb at a few conferences, when I heard he stepped up to this challenge with a Power Query book entitled “Power Query for Power BI and Excel“, I was interested in reading.

The book weighs in at 272 pages, and I thought it might take a couple of weeks to do it justice. Once I started however, I was hooked and consumed it in a couple of days. Chapter 1 was a nice introduction that told the story of Power Query’s reason for existence, and described the differences between the experience with different versions (Office 365 Power BI vs Excel 2013 vs Excel 2010). It wouldn’t be too big of a stretch to call this an incantation of SSIS-like functionality for self-service BI. Reading quickly through chapters 2-4, about data sources-transformations-destinations was a thorough demonstration of the functionality of the product, and clarified exactly what was supported. I was introduced to the “Excel Data Model” concept here as well.

It was the chapter on M that was the gem of the book for me. I’d realized that you could extend your Power Query queries with M functions, but didn’t realize that every query was actually a script written in M. Chapter 5 describes M the language and how you can use it to not only embellish Power Query queries, but to even write them by hand. M code that comprises Power Query queries looks vaguely reminiscent of Hadoop Pig scripts, although obviously the surface area isn’t the same. I was impressed by the author’s treatment in “Introduction to M”.

The chapter on Power Query in Power BI Office 365 told me what I wanted to know about how Office 365 extends the functionality of Excel desktop Power Query into a shared, more enterprise-y offering (e.g. query sharing), as well as how it integrates with Sharepoint. And the Multiple Queries and Power Query Recipes chapters helped get me into the “thinking of solving problems with Power Query” mindset. The first sentence of the recipes chapter “…when you are learning about a new tool it isn’t enough to know what each button does when you press it, or what each function returns when you call it” was right on target.

I had a few minor quibbles. I’d like to have seen a mention of the exact version of Power Query (version number and release date) that the book covers. I knew that new releases are coming out monthly, and was working with the version that appeared a few days ago, but looking at a screenshot that said “Apply and Close” when my version said “Close and Load” (as well as other differences) in Chapter 1 was a bit disconcerting. But that’s more of a “don’t constantly change the GUI on something that’s supposed to be consumer-focused” comment on the product. Or maybe it looks different in the Office 365 version, I didn’t know.

And about the product…I was also surprised, product-wise, that they didn’t support OLE DB (at least for the Analysis Services OLAP provider), or even ODBC, although you can shoehorn these in via the vanilla Excel “data sources” tab. I guess it was to keep the GUI rich and consistent, functionality-wise, but if you can do it for OData… I did especially like the non-traditional data sources like HTML tables/pages and Facebook, although a generic JSON data source would also be useful. Maybe I just didn’t see where the generic JSON functionality lives. The “data sources” book chapter did make the level of support very clear, though.

Overall, I’d recommend this highly recommend this book if you’re getting started, or even if you think you’re pretty far along with this tool. The M and Office 365 Power Query chapters themselves may be worth the price of admission.

Cheers, Bob (@bobbeauch)

Posted in: Data Access

Comments Off

New Azure services and evolution of the Service/SQL Server relationship

By: Bob Beauchemin

Posted on: August 21, 2014 2:42 pm

Today, two new service offerings for the Azure platform were introduced, DocumentDB and Azure Search. These are exciting in themselves and worth a look-see but, to me, they are also occurrences of the phasing out of the “database as an application server” concept, formalized in the SQL Server 2005 timeframe. At least, in the cloud. Did everybody already notice, or was it too subtle?

Services moving inside the database (where they are more tightly integrated with the data they use) predates SQL Server 2005. Rather than rely on the OS for scheduling and alerting activities, SQL Server has always had its own facility (and separate Windows service) for this, known as SQL Agent. A mail component is included in the database as well. But possibly the first big service to be moved into the database was Microsoft Search Server. It was originally grafted on, but over time came to be completed integrated in SQL Server as the fulltext search feature. SQL Server 2005 was the watershed for this concept, including the XML/XQuery, Service Broker, event and query notifications and HTTP endpoints, with SQLCLR providing a programming substrate to be used in conjunction with T-SQL for the new services. This concept was formalized in the Dave Campbell’s paper “Service Oriented Database Architecture App-Server Lite”. Interestingly, these features are often artifices over relational constructs (e.g. XML and fulltext indexing are relational side-tables). There was even lots of work put into using SQL Server as a substrate for the Windows file system (WinFS).

The first move away from this concept of “database as substrate for all” was the removal of HTTP endpoints and it’s replacement by what finally came to be called the OData protocol. OData serves data as either an XML format (AtomPub) or JSON format, which is why, although it’s often requested, it’s doubtful we’ll see “native JSON support” inside SQL Server soon.

Then….to the cloud…

One of Microsoft’s first PaaS offerings, now known as Azure SQL Database, solidified what was to come. Azure SQL Database is a relational database engine only, shipped without most of the “app-server” services inside SQL Server proper. Features that do not exist inside Azure SQL Database include not only the features mentioned above (XML/XQuery survives, but XML indexes, as far as I’m aware, do not), but other nice infrastructure features such as replication. These are to be offered by “the platform”, as in PaaS.

DocumentDB and Azure Search are just the latest pieces of the platform. SQL Agent functionality can be implemented using Azure scheduling and notification services. Service Broker is implemented/subsumed by Azure Service Bus. OData services are part of the service layer, not the database layer. JSON is tightly coupled in DocumentDB, JSON documents subsuming XML documents in many use cases, e.g. document databases rather than XML databases.

A discussion of what precipitated the move from application servers and service-based architectures to database as application server to separate services with service coordination programmed into each application is a whole other set of blog entries…or discussions in bars. As is the comparing-and-contrasting of how this is being done in other ecosystems, e.g. cluster as substrate for all and a central resource negotiator (YARN) that manages the cluster. Note: SQL Server (and likely SQL Azure Database) uses it’s own Resource Governor to divvy-up the resources. And there’s always the question of who (application and administration-wise) manages and orchestrates the integration of these disparate services; now that’s a occupation that will certainly be in demand.

As far as SQL Server “proper” goes, DBAs need not worry about having less to do. The latest versions now integrate a column-based “database” and its optimizations, and an in-memory “database” (with different storage and isolation paradigms) into the mix. But these are data engines rather than services. Also note the cross-pollination between different parts of “the platform”; the XVelocity engine also lives quite nicely in SQL Server Analysis Services and in Excel. And there’s already rumblings of the implementation of DocumentDB using lessons learned with SQL Server’s in-memory implementation. So far, it’s interesting that neither columnstore nor hekaton have yet to make an appearance in Azure SQL Database. The same “redistribution, integration, and evolution of services” is going on in the ETL/Analysis/DataMining/Machine Learning Microsoft ecosystem too.

One thing about the technologies surrounding software; we always “live in interesting times”.

Cheers, Bob (@bobbeauch)

Posted in: Azure SQL Database, Data Access, SQL Server 2014

Comments Off

Don’t drop empty Azure SQL Database servers

By: Bob Beauchemin

Posted on: June 25, 2014 12:31 pm

In a previous blog entry, I mentioned changes in SQL Azure Database’s new tiers. Another one, that I think is indirectly related to the new tiers, is the disappearance of a message that used to appear (on the portal) when you deleted the last database on a particular Azure SQL Database server. That message asked “Do you want to delete the server as well?”. Lately, this message doesn’t appear.

I’d always answered “no” to the message in the past; having a server hanging around without databases didn’t cost you anything and if I restored (imported) the database in future, I didn’t want to change all my connection strings. The server would, it was said, go away after a certain number of days (60?) without databases.

With the introduction of the new tiers and restore/recovery there’s a better reason not to delete servers with no databases. Your collection of “Dropped but restorable databases” (the PowerShell property for these is RestorableDroppedDatabases) is tied to the server. When the server is deleted, you can’t restore them any more. And, as far as I know (the cost was likely amortized up-front) servers with only RestorableDroppedDatabases still don’t cost you money.

Because the Azure Portal has more mention of “create a new database”, the server concept has sort of taken a back seat. A server in Azure SQL Database is like a “virtual SQL Server instance”. It consists of a master database (similar in concept, but not completely in implementation to SQL Server’s master), an associated administrative login (as well as other logins you’ve added) and a set of firewall rules. In addition to associated user databases. For a complete list of associated properties (and methods and events), use the following lines of code in PowerShell, after getting a credential:

$ctx = New-AzureSqlDatabaseServerContext -ServerName “myserver” -credential $cred
$ctx | get-member

Currently (AzurePS 0.83), attempting to retrieve all the property values returns an exception (you get the same exception trying to access the RecoverableDatabases property so it might be that), so you’ll need to retrieve the values that you want by name.

So don’t forget that servers are important in the new tier world, and dont drop ’em unless you don’t care about (or you don’t have any, because of time limits) RestorableDroppedDatabases.

Cheers, @bobbeauch

Posted in: Azure SQL Database

2 Comments

A few tweaks to Azure SQL Database’s new tiers

By: Bob Beauchemin

Posted on: June 25, 2014 11:52 am

One of the things that make Azure SQL Database difficult to keep track of is that details of some database behaviors can change, usually without notice or announcement. There were supposed to be announcements on one of the Azure blogs, but that hasn’t happened for Azure SQL Database in over a year, except for the “big announcements” this April. There is no way to confirm that “things used to work this way, and now they don’t”. This is particularly difficult with preview features that are more likely to change during the preview.

That being said, there are a few changes that have happened with respect to the new Basic/Standard/Premium tiers preview. The first one is that you can mix “new tier” and “old tier” databases on the same server now. Previously they didn’t mix; a server could either contain old or new tier databases, but not both. I currently have a server with a Web edition and a Basic edition database. I first the server after the preview started (PowerShell identifies these as “V2” servers); I don’t have an “old tier” server (V1?) to test out whether it supports the new tiers. Using an “old tier” database on the same server as a “new tier” database doesn’t convey “new tier” behaviors. That is, deleting the “new tier” (Basic edition) database makes it available for restore under the “Deleted Databases” portal tab. Deleted the “old tier” (Web edition) database doesn’t.

Another change is in the implementation of restore/recovery of databases (and deleted databases). Not only have the Basic and Standard tiers “length of available backup” changed (Basic from “most recent daily backup” to “point in time within 7 days” and Standard’s point-in-time from 7 to 14 days), but all editions (including Basic) support point-in-time restore. This also means that the Start-AzureSqlDatabaseRestore (for editions that support point-in-time) now works on all new editions, whereas Start-AzureSqlDatabaseRecovery (for editions that don’t support point-in-time) doesn’t seem to work at all, with any edition.

Finally, the “Automated Export” feature (where you want to backup on a schedule and also keep your bacpac file) is now available for the new tiers as well as the old. Check it out under the “Configure” tab on any SQL Azure Database, old tier or new.

Cheers, @bobbeauch

Posted in: Azure SQL Database

Comments Off

Extended Events In Azure SQL Database. For real.

By: Bob Beauchemin

Posted on: May 28, 2014 12:06 pm

UPDATE: Last month (Sept 2015), Extended Events (not just the metadata that I wrote about) are officially available in Azure SQL Database!! For more information, here’s the main documentation article.

I’ve just returned home after teaching two weeks of SQLskills Immersion Events. Total post-event exhaustion for two days. Trying to produce some nice demos on the new SQL Azure Database tiers and services for my SQL Azure Database A-Z preconference ssession at SQLTuneIn. And then this happens…

About a year or so ago, after attending a TechEd talk on XEvents in Azure SQL Database (ASD, the PaaS offering), I looked for and found metadata and blogged about it. The feature never seemed to come to fruition and after a while, I stopped looking.

This morning, I received email from Egbert Schagen, a person I’d met in my conference travels. He let me know that XEvents now appeared to work in ASD. They appeared in the SSMS object explorer. Egbert said he’d seen no announcement for it, and by web search he came across my old blog entry. He thought I might like to know. And he was right.

And so they DO work. I tried this in an Standard S1 Edition database I had and Egbert said they also worked in Web/Business edition. As with almost everything ASD, these event sessions exist at a database level, rather than in master.

There are 12 pre-configured event sessions. It doesn’t look like you can create your own. The sessions are:
azure_xe_activity_tracking*
azure_xe_errors_warnings
azure_xe_object_ddl
azure_xe_post_query_detail
azure_xe_pre_query_detail
azure_xe_query
azure_xe_query_batch*
azure_xe_query_detail*
azure_xe_query_execution*
azure_xe_query_waits*
azure_xe_query_waits_detail*
azure_xe_waits

The event sessions with an asterisk can be turned on and you can “Watch Live Data” in SSMS. The rest of them look like they require a credential because they write to Azure blob storage. More on that as I try and figure through this with the metadata. It appears you can only run one event session at a time. The second concurrent one always produces a “system is currently busy” message on attempting to start it.

I’m not sure what the “official” status is, but for now it looks like you *can* trace in Azure SQL Database. Great! Thanks, ASD developers. Thanks, Egbert.

@bobbeauch

Posted in: Azure SQL Database, Extended Events

1 Comment