Bob Beauchemin - Page 2 of 65

Installing and Running HDP 2.1 on Windows

Posted on: April 28, 2014 3:22 pm

And now, for something completely different. Hadoop.

I’ve been playing with everything I can get my hands on (and can find time for) with Hadoop for a while now. Have tried Hortonworks on CentOS (seems like the Hortonworks “reference” platform), Hortonworks HDP on Windows, and HDInisght. I even think I may have a clue about what big data is and why it matters. But that’s a topic for another time. It’s intruiging that Hadoop on Linux has a built-in GUI dev tools (like Hue) but the Windows’ versions (put out only by Hortonworks/Microsoft) have no GUI except for the NameNode/YARN/HBase status pages, and almost mandate you work on PowerShell. I thought the Linux folks were the bigger command-line wonks. I haven’t tried any other distos (e.g. Cloudera) yet (or tried building it myself for that matter).

Over the weekend, I installed Hortonworks new GA 2.1.1.0 distro for Windows. The main reason I installed it was to try out Hive 0.13 (with vectorized query – think SQL Server columnstore batch mode, ORC files are the columnstore) and Hive under Tez. Think of Tez as a more flexible, powerful, faster MapReduce. I did a single-node install for simplicity. Ran into some problems and anomolies that I’ll mention here. But did get most of what there is working. I say “what there is” because the reference diagram at http://hortonworks.com/hdp/whats-new/ mentions all the things I do see, but the Windows distro doesn’t include Accumulo (another fast data storage offering based on BigTable) and Solr (a search offering). Perhaps the CentOS distro does. And, of course, no Hue. And, although there’s an exe and it’s in the list of Windows services, HWI (Hive Web Interface) doesn’t start up. That’s been reported by others too.

BTW, this version isn’t available on HDInight yet. Maybe soon. Microsoft allocated some engineers to Hive 0.13 (and other phases of the Stinger Initiative), so I’m pretty sure they want it in HDInsight too.

There were the usual potholes in the docs, if you’re trying to follow the docs by rote. First, the docs warn you not to use path names with spaces in the Java or Python installs (http://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.1.1-Win/bk_installing_hdp_for_windows/content/win-software-install-gui.html). Good idea in any case (don’t have to put quotes around names in command lines) but especially with Unix ports to Windows. But they warn you in the Python directions and Python 2.7 doesn’t install in “Program Files”. Java does. And one of the instructions says you should add (step 5c) e.g C:\Java\jdk1.7.0_45\bin as the value for JAVA_HOME. Don’t use the “bin”. Step 6b is wrong too; the path should include the “bin” directory. Easy to fix. The install reports “JAVA_HOME is not set” if you mess it up. Ask me how I know…I should have been paying attention.

If you choose “Run Hive Under Tez” you must peform Section 6.1 “Setting up Tez for Hive”. But there’s a glitch there too. Step 5: “Copy the Tez home directory on the local machine into the HDFS /apps/tez directory” is wrong too.
%HADOOP_HOME%\bin\hadoop.cmd dfs -put %TEZ_HOME%* /apps/tez
should be:
%HADOOP_HOME%\bin\hadoop.cmd dfs -put %TEZ_HOME%/* /apps/tez
(note the slash after %TEZ_HOME% and before the *)

Until you do that, Hive fails starting up the command line app with /apps/tez/lib not found.

And its likely that step 6 should be:
%HADOOP_HOME%\bin\hadoop.cmd dfs -rmr -skipTrash /apps/tez/conf
because there is no conf3 directory.

With the right copy command the Tez smoke test and Hive under Tez run just fine. I did have trouble with the HCatalog and Hive smoke tests at first (something about hadoop user needing a GRANT, GRANTs are expanded in Hive 0.13), but after enough tries (3 reinstalls of the distro and 7-8 runs of smoke tests) it just started working. Don’t know exactly what I did to make that start happening. So all the smoke tests (including optional components) succeed.

So THANKS folks! Lots more to try. There’s a lovely Hive/Tez/vectorized query tutorial (do it sans-GUI) that shows the new features off. And I’m glad that I can specify precision/scale for decimal types. And all other Stinger features and additional ecosystem components too.

BTW, I ran this on Windows 2012 R2 OS, even though the docs say only Windows 2008 R2 an 2012 OS are officially supported. It lists these under minimal requirements.

Hopefully this information will be helpful to someone else.

Cheers, @bobbeauch

Posted in: Hadoop

3 Comments

Last week in Azure SQL Database – Part 5 – Wrapup

By: Bob Beauchemin

Posted on: April 28, 2014 2:05 pm

This post contains miscellaneous information about the current/future state of Azure SQL Database (AST). You know I couldn’t write just one more blog post when I said I would in Part3, didn’t ya’? This post has some properties of a rant in some places, but I’m genuinely interested. I try not to judge technologies, just tell people how they work…in detail. This post covers:
Additional metrics in the new tiers
ASD database functionality status
Sync Services status
Scale-out status

The new tiers contain 3-4 new metrics that can be turned on and observed in the portal. Select a existing new-tier database, choose “Monitor” then at the bottom, choose “Add Metrics”. New additonal metrics are:
CPU Percentage %
Log Writes Percentage %
Physical Data Reads Percentage %

Old “Additonal Metrics” are
Blocked by Firewall Count
Throttled Connections Count
Storage Megabytes

Original Metrics are:
Deadlocks Count
Failed Connections Count
Successful Connections Count

I said 3-4 new ones because I think “Storage Megabytes” was in the old tiers as well. There may be PowerShell properties to control and monitor these too. None of the additional metrics show up in Azure PowerShell 2.3, or at least I can’t find them.

Next, about new functionality (database functionality) in ASD. It’s been a while since there’s been any new (visible) database functionality in ASD. The new tiers don’t provide any; @@version is the same as the old tiers, as is the database metadata. Sequences and SQL2012 windowing functions are still missing. Event sessions were announced with fanfare at TechEd a year or two ago, then metadata appeared, but the feature hasn’t appeared.

The last new functionality tidbit that I remember is the addition of compression (ROW and PAGE) about a month ago. I tried this and it works. However, without a sys.partitions metadata table (it’s “not found”) it’s impossible to see what existing tables/indexes have been compressed. On a whim, I looked in sys.tables….and found some metadata fields from SQL2014! (referring to in-memory OLTP). Of course, in-memory OLTP isn’t there either, but this begs the question: what version of SQL Server is this based on, anyway?

Then there’s Sync Services. It still exists, still works (or can be configured) on the new tiers, but it’s still preview…for the last approximately 2 years. What I once referred to, in one of my more rude moments, as “eternal beta”. No word on it’s GA date or it’s fate yet either. Geo-replication may provide an alternative for Premium customers, but for non-Premium and sync to on-premises/SQL Server, we’re still using either Sync Services (preview) or Export/Import.

Finally, about Federations being deprecated… The replacement for federations, according to this page: http://msdn.microsoft.com/en-us/library/azure/dn495641.aspx is Custom Sharding. But it’s description: “Design custom sharding solutions to maximize scalability and flexibility for resource intense workloads. These solutions use application code across multiple databases.” leaves a lot to be desired. What does the custom code (that replaces a built-in feature) do, exactly?? I’d heard there was to be “prescriptive guidance” but, so far, I see no guidance at all, except “write your own”. Maybe, some SAMPLE CODE? Especially because the placement point for Azure SQL Database is “New applications that are designed to scale-out”.

Granted federations had a lot of missing features (fan-out queries and ALTER FEDERATION MERGE are two that come to mind) and had its drawbacks, but (MHO…) you can’t replace a built-in feature with (no, yet) prescriptive guidence unless no one’s using it currently. But some folks ARE using it. I’ll need to keep updated as things develop in this area. I’m hopeful that things will develop….

Wrapup: I like the new tiers. Love the new utility features. Wonder about the database features going forward. But these were not MEANT to be announced last week. There were enough announcements to get used to. 😉

Cheers, @bobbeauch

Posted in: Azure SQL Database, Data-Tier Applications (DAC)

Comments Off

Last week in Azure SQL Database – Part 4 – Performance Levels

By: Bob Beauchemin

Posted on: April 28, 2014 1:08 pm

In this post I’ll address perhaps the most important of all the announcements, performance levels.

The 6 new ASD tiers provide different levels of performance and different resource guarentees/reservations. There is a chart here: http://msdn.microsoft.com/library/azure/dn741340.aspx that lists performance levels (among other things) and there is a different chart here: http://msdn.microsoft.com/en-us/library/azure/dn741336.aspx that gives more details on performance, predictability, and resource guarantees (like max worker threads and max sessions).

The part concerning predictability is useful because ASD servers (physical servers in datacenters, not SQL Server master database they call “servers”) are shared. Because servers are shared, there is a syndrome that affects predictability called the “noisy neighbor” syndrome. Imagine someone who shared a physical server with you is performing a database stress test…maybe during your business’ peak time…

Performance is defined in a new, curious, unit of measure called a DTU or Database Throughput Unit. DTUs are meant to allow comparison between the new tiers. Currently, there is no direct comparison between the new tiers and the old (Web/Business) tiers, possibly because there was no performance SLA in the old tiers at all.

DTUs are based on throughput with a described benchmark (The Azure SQL Database Benchmark, see http://msdn.microsoft.com/en-us/library/azure/dn741327.aspx). This benchmark and how they run it are described in a nice level of detail. However, it would be better if the source code and running instructions could be released in future. Unless it’s already been released, and I missed it.

For now, the DTU is a nice way to compare the tiers and a known benchmark is a nice thing but, to reiterate, there’s no way to ensure that you’re getting your bang-per-buck. And remember, at this point, any “smoke tests” you do on your own are being performed against a preview, not production. With Basic, sometimes it seems to take “a long while” (a nebulous term) to connect the first time to a new Basic tier database. After that, it’s faster (another nebulous term). Others have reported that Basic/Standard is slower than Web/Business on self-invented performance test. It would be nice, before the new tiers go GA, if they run the benchmark on a traditional Web/Business database (maybe a few times and take the average, but post all detail runs) just to assuage the fears of folks before they need to convert to the new tiers. MHO… After everyone’s converted, we can start talking about DTUs again, and they become more interesting and meaningful.

BTW, to get information about your databases (new or old tiers) in T-SQL, just use this query. It has all of the tier and objective information. There is some redundancy in the metadata, so start with SELECT * and choose the information you’d like to see:

select o.name, o.description, st.*, do.*
from slo_objective_setting_selections sl
join slo_service_objectives o
on sl.objective_id = o.objective_id
join slo_dimension_settings st
on sl.setting_id = st.setting_id
join slo_database_objectives do
on o.objective_id = do.current_objective_id
order by o.name;

Cheers, @bobbeauch

Posted in: Azure SQL Database

Comments Off

Last week in Azure SQL Database – Part 3 – HADR preview service for premium

By: Bob Beauchemin

Posted on: April 28, 2014 12:11 pm

This post is about a new SQL Azure Database feature called a “Business Continuity Feature”, called “Disaster recovery/geo-replication”. This feature was announced last week as a preview. For the Premium tiers, this is a lovely feature that include “Active geo-replication” (their term) and cmdlets (and portal) for controlling it. For Basic and Standard tiers, you can use “Database copy + Manual export” according to the chart here: http://msdn.microsoft.com/library/azure/dn741340.aspx. By now, you know what “Database copy + Manual export” means (BACPACs, CREATE DATABASE AS COPY OF).

Terms concerning geo-replication are documented here: http://msdn.microsoft.com/en-US/library/azure/dn741339.aspx and there’s an entire docs section devoted to how to use it. A few points need to be called out:
1. Secondaries are readable.
2. You can create secondaries in the same region/different server or different region.
3. The target server must have an available Premium database quota to create the active secondary. You start with a quota of 2 and can “request” (from Azure support?) to have your quota increased.
4. The secondaries are always transactionally consistent but can run behind the primary.
5. Secondaries must be the same performance level (P1,P2,P3) as the primary. This has charge repercussions, as you could imply (nowhere is it directly stated but it seems obvious) that each secondary costs the same as the primary.
6. Each primary and secondary consists of a database that is itself replicated (passively and unseen by you) 3 times, because this is the design of ASD. And there is no info that this internal design has changed.

You start a continuous copy to the secondary using the portal or the (soon-to-be-released) PowerShell cmdlet Start-AzureSqlDatabaseCopy. This starts a seeding process, and eventually catches up to close to the primary. You can monitor this. Your can stop or cancel a continuous copy by using portal, the DROP DATABASE command on the copy, or Stop-AzureSqlDatabaseCopy. You need to use Stop-AzureSqlDatabaseCopy -ForcedTermination parameter to cancel the operation while it is still in progress or stop the replication immediately. See http://msdn.microsoft.com/en-us/library/azure/dn741337.aspx.

There are two ways to failover, planned or forced. Planned is performed only on the primary. Forced can be performed on the primary or secondary. Their terms for the types of types of disaster recovery that can be designed are:
1. Active-passive compute with coupled failover
2. Active-active compute with decoupled failover
3. Active-passive compute with decoupled failover

I know some folks in the disaster recovery biz have a problem with the terms active-active and active-passive, so perhaps these terms will change in future. There are a few recovery scenarios documented, I’d like to try these with the PowerShell cmdlets, when they appear.

This is an excellent feature for Premium edition but, to reiterate, nothing new in this space is currently announced for Basic and Standard, past the original, built-in and internal, 3 database replicas as with the original ASD. A change to the internal single-database design was not announced.

In the next post (which will be the last post, if I can make it short enough), I’ll talk about performance levels in the tiers and comment on the current state of ASD functionality.

Cheers, @bobbeauch

Posted in: Azure SQL Database, Data-Tier Applications (DAC)

Comments Off

Last week in Azure SQL Database – Part 2 – New preview services

By: Bob Beauchemin

Posted on: April 28, 2014 11:23 am

Note: Well that was quick. I’ve updated this blog entry (same day) to reflect clarifications provided by a member of the Azure SQL Database team. Thanks for these excellent clarifications. For now (I may go back and change this later) changes from the original blog post are indicated with italics.

The last post in this series was about the new tiers/pricing in Azure SQL Database (ASD). This post will be more exiting, as it covers the new services that come with the new tiers. I’m talking about what the announcement (and docs) call “Business continuity features”. To summarize these features are Self-Service Restore and Disaster Recovery – Geo-Replication.

Although the docs and the chart on http://msdn.microsoft.com/library/azure/dn741340.aspx show these features as available on all new tiers, currently, these services only appear on premium. And the PowerShell cmdlets mentioned in the docs aren’t in Azure PowerShell 2.3. I was told the cmdlets “will be out this(Apr 28) week”. See the chart for how the new features are implemented on different tiers.

One final thing about using “CREATE DATABASE .. AS COPY OF” and the preview. Last year’s Premium preview created a copy that was in a “disabled premium” state. The new preview will create a copy at the same level, so, for example, “CREATE DATABASE .. AS COPY OF” with a P2 database will create a P2 database. This has charge repercussions.

First, Self-Service Restore. Microsoft keeps (and has always kept) database backups at their data center. I’m guessing these are “traditional” database backups (not BACPACs). BUT, you can’t use their backups, because Backup and Restore are not supported on ASD. Export and Import are supported. Self-service restore is a way you can have them use THEIR backups to restore an ASD database. There are two flavors of self-service restore:
1. Restore a copy of a currently existing database as of a point-in-time. Perhaps you deleted a table, for example, or some data with a miscoded SQL DELETE statement. It happens…
2. Restore a copy of a database you deleted by mistake. Or that you want back. The database doesn’t currently exist now.

I’ve heard both of these referred to as “oops recovery”. I’m thrilled with this service, even though you and I have never made a mistake, right? 😉

Use the portal (see http://msdn.microsoft.com/en-us/library/azure/dn715779.aspx) or the PowerShell cmdlets Start-AzureSqlDatabaseRestore (for a Standard or Premium Edition database) or Start-AzureSqlDatabaseRecovery (for a Basic database, because it doesn’t have point-in-time recovery), and a restore request will be submitted for you. To restore a deleted Standard or Premium database, just restore to a point-in-time before you deleted it. There is no SLA on *how long* the request will take to process. I couldn’t even get a ballpark figure, because it depends on the size of the database and the amount of recent activity. You can, however, get information about the status of the restore operation. You can even get this a T-SQL with the sys.dm_operation_status metadata table.

Unlike “CREATE DATABASE .. AS COPY OF”, self-service restoring a database produces a database of the same tier, but the lowest performance level in that tier. For example, restoring a P3 database creates a P1 database as a copy. This lessens the charge repercussions, but you do, of course, pay for at least one day of the copy. If you’re manually just using it to recover a table, don’t forget to delete the restored copy when you are done. The database that’s created with a restore request can have the same name or a different name as the original and is always created on the same logical server, same data center. So to use a restored copy, you’ll need to change connection strings to point to the new database name or choose the same name when you when you submit the restore request. You may also want to increase the performance level, if you want to use the copy in place of the original afterwards.

If you’re the kind of person who wants their own backup (to Import the data on-premises, for example) you’ll still need to use Export/Import and BACPACs. The backup/restore capability is not available to you. BTW, if you used the Automated Export service (in preview itself) with Web/Business (it produces BACPACs to Azure Storage on a schedule), this is NOT available currently on the new tiers (at least that I could see, on the portal). No announcement when/if it will be.

To reiterate, the level of self-service restore (length of retained backups and point-in-time or not) is dependent on the service tier. See the chart referred to above. Also, here’s a clarification of what “Most recent daily DB backup in past 24 hours” means for Basic tier. For each database the service manages several types of backups: full backup created once a week, differential backup created once a day and transaction log backup created every 5 minutes. The first two are also copied to the Azure storage and that is what we refer to as “daily backups”. The actual time those backups are created differ therefore we can only guarantee that they will not be older than 24 hrs. Consequently, if a database is recovered using Start-AzureSqlDatabaseRecovery the data loss (RPO) will be less than 24 hrs.

As this post is getting too long, I’ll save disaster recovery – geo-replication for another post.

Cheers. @bobbeauch

Posted in: Azure SQL Database, Data-Tier Applications (DAC)

Comments Off

Last week in Azure SQL Database – Part 1

By: Bob Beauchemin

Posted on: April 28, 2014 10:38 am

This blog post is especially dedicated to those who attended my SQLIntersection post-conference talk on WASD just over one week ago. The announcements were made after the talk, and so the information here is mostly a delta for those who want to catch up. I think the post series will be useful above and beyond that, however. I’ll try not to paraphrase the announcements but may need to quote from it or provide URLs. I’ll also tell you where I’m currently a bit unclear about things.

Last week, Microsoft made some major announcements about the future of Azure SQL Database. As an aside, the announcements referred to it as “Azure SQL Database” (although there is one usage of the name Microsoft Azure SQL Database), so I’m going to refer to it as a new made-up acronym, ASD, rather than my previous acronym (WASD) for their previous name, Windows Azure SQL Database. “Azure SQL Database” is just too long. For folks who get the two confused, ASD is a platform as a service (PaaS) database offering based on the SQL Server code. As opposed to “SQL Server on an Azure VM”, which is an IaaS offering. With ASD, you don’t need to maintain a guest OS or maintain SQL Server software yourself. I’ve written about this previously.

The first announcement was a changing of the tiering and pricing structure. ASD goes from 2 tiers (Web and Business) to 6 tiers (Base, Standard S1 and S2, Premium P1, P2, P3). Even more important, the charges go from being database size-based to tier-based. To see the new pricing, go here (http://azure.microsoft.com/en-us/pricing/details/sql-database/), scroll down, and click on “Basic, Standard, and Premium” button to see the chart. The difference between the tiers (besides size limit) is the level of service. More on that later. The chart mentions “Database size limit (included)”, but I’m unsure that there isn’t SOME charge based on the database size (as was the case with Web and Business). I can’t find it if there is, and the word “included”, tends to suggest that there isn’t additional per-GB data size charge. One (1) day is the minimum charge and charges are daily. I guess 1 day means “24 hours after you create the DB”, but haven’t tested that out yet.

You’ll have 1 year to convert from Web/Business to Base/Standard/Premium. The announcement doesn’t say what will happen if you don’t. The new tier structure is in “preview mode”, which has two main consequences. Firstly, to use preview mode you forgo your SLA, until the new tiers become GA. Secondly, to try it out, you need to sign-up/opt-in at http://azure.microsoft.com/en-us/services/preview/. Scroll until you see “New Service Tiers for SQL Databases” and click “Try it”. If you have multiple Azure subscriptions, you must do it once per subscription. It was almost immediately visible for me on either the old or new Azure portal.

The new service tiers use different sets of hardware, so you can’t use a new tier on an existing “server”. You need a new server. The new tier choices also show up when you Import a BACPAC into a database (on portal, +New/Data Services/SQL Database/Import), too. On “Import” and “New/Custom Create” I get choices of all old and new tiers. They don’t show up on “quick create” in the same path, however, but when I tried it (after making the new tiers available) “quick create” (on a new server) created a Basic (new-tier) database. Not sure I like that, because preview tiers have no SLA.

You can also convert between tiers for an existing database. On the portal, choose your database, and it’s under “Scale”. Scale (on a Basic database) didn’t give me Web/Business as a choice, only the new tiers as you’d expect. Be careful with this because, if I read the charges right, switching from Basic -> P1 Premium -> Basic will cost you $15 (the cost for 1 day of P1). I didn’t do this yet to find out.

There’s much more to come, but I do want to close this blog entry by quickly mentioning two other things:
1. When new tiers go GA, the uptime SLA goes from 99.9 to 99.95 for all new tiers.
2. On same day, it was announced that Azure Federation feature also will be discontinued after a year (Apr 2015). More on this later.

Cheeers, @bobbeauch

Posted in: Azure SQL Database

Comments Off

SQL Server 2014 In-Memory OLTP: What exactly is a “dusty corner”?

By: Bob Beauchemin

Posted on: March 3, 2014 3:55 pm

There’s a set of performance counters for the new In-Memory OLTP in SQL Server 2014. You might have overlooked them because they’re not with the other SQL Server counters, but are in their own group that begins with “XTP” (eXtreme Transaction Processing).

Looking over those counters, both the XTP Garbage Collection and XTP Phantom Processor groups contain a counter that refers to “Dusty Corner scan retries”. So what, exactly, is a dusty corner? Searching both Books Online and the “SQL Server 2014 In-Memory OLTP TDM White Paper” yields no references, but the academic Sigmod-2013 paper contains a helpful description.

The term has to do with how the In-Memory OLTP feature stores data in memory and how its garbage collection works. Because a single copy of a row’s data is stored along with multiple index pointers (both hash and BwTree indexes are ultimately pointer-based), all of index pointers must be “unlinked” before an old row can be garbage collected. Since threads can unlink old pointers while running queries, any index ranges with a lot of activity will quickly unlink the appropriate pointers. However, if an index or index range is rarely used, special scans by the system garbage collector will be needed to find these pointers. They’re “hiding” in the dusty corners (apparently dust forms on unused index ranges, I envision Carol Burnett, duster in hand 😉

So the presence of dusty corner scans means some index ranges aren’t being used much. If, by looking at index usage, you can determine that an entire index is almost never being used (Database Engine Tuning Advisor doesn’t tune indexes for in-memory tables, that I’m aware of), that index would be a candidate for removal. However, in-memory tables don’t support filtered indexes so, if another part of the index range is frequently used, you’ll have to decide if it’s worth letting old versions hang around for longer. Until those dusty corner scans unlink that last pointer.

I’ll be doing preconference seminars covering all the SQL Server 2014 features in detail at SQLIntersection in Orlando and DevWeek London in April, as well as a performance-centered new and old feature seminar at Addskills in Stockholm later this month. if you’re at any of these locations and would like to see how these features would help you and you’re clients, I’ll see you there.

Cheers, @bobbeauch

Posted in: Hekaton, SQL Server 2014

Comments Off

Managing Windows Azure SQL Database’s newest feature – Premium Database

By: Bob Beauchemin

Posted on: February 28, 2014 10:10 am

The latest feature to make an appearance in Windows Azure SQL Database (actually it’s still a preview program) is the Premium Database. Physically, when you have a WASD database, you’re sharing that database and server with others. It’s possible that if one of your database “neighbors” decides to run a stress test, your database (and you) could be stressed as well. This is known as the “noisy neighbor” syndrome. Just like noisy neighbors in a hotel, you can call the management and complain and they may even “move” you, just like a hotel would. To guarantee a certain level of resources, WASD made the premium database available.

There’s two different reservation sizes, P1 and P2, and these sizes are defined in terms of CPU cores, concurrent active workers, number of sessions, data IOPS, and memory. The relevant reservation size guarantees you these resources. The reservations are defined using the term “service level objective” (SLO).

Once you’ve been accepted for the preview program, you can create a new premium level database using the Azure portal or PowerShell Azure cmdlets. You can also upgrade an existing database to premium and also downgrade from premium to “regular”, subject to limitations, as well as change the reservation size. BTW, the cmdlets are a boon for those of us that would like to manage WASD but don’t want to perform repetitive operations via a web-based GUI. I strongly suggest you give them a try if you’re doing any Azure work.

One of the things you need to get used to when moving from SQL Server to WASD is that WASD does not expose all the DMVs and metadata views that SQL Server does, BUT ALSO, WASD contains some DMV and metadata views that SQL Server does not have. To be able to work with the Premium Database feature, there are six new metadata tables, all beginning with “slo” in the DBO schema in the master database. For example, if I have a premium database named “consistant_db”, I can monitor the premium change history by using the SQL query:

SELECT *
FROM dbo.slo_assignment_history
WHERE database_name = ‘consistant_db’
ORDER BY operation_start_time DESC;

I can also monitor resource activity by looking at worker_counts in sys.resource_stats to make sure I’m getting my money’s worth or to decide when it’s best to update/downgrade to premium.

Keeping on top of WASD changes, as well as knowing how and when to use WASD can be, like keeping up with anything in the Azure space, chasing a moving target. At the SQLIntersection conference in Orlando in April, I’ll be presenting a full day post conference workshop on “Windows Azure SQL Database from A to Z”. Join me there for the whole story on managing, developing on, and interacting with this beast.

Cheers, @bobbeauch

Posted in: Azure SQL Database

Comments Off

SQL Intersection in April, I’ll be there!

By: Bob Beauchemin

Posted on: February 10, 2014 11:02 am

In mid-April, April 13-16 to be exact, the spring installment of SQL Intersection comes to Orlando. I’ll actually be there a little earlier and leave a little later than that. This conference has a whole lineup of speakers that like to add a little depth to how you’re used to thinking about your databases. There’s just too many excellent speakers to list them all, you’ll need to peruse this for yourself.

On Saturday before the conference, I’m doing a full day of SQL Server 2014 material. This includes some in-depth material on the new storage engine/execution engine/multi-version concurrency engine that was previously known at Hekaton. But it’s not just about Hekaton, I’ll cover everything database-related that’s in the new release, like the new columnstore and new cardinality estimation enhancements.

During the conference I’ll compare and contrast Windows Azure SQL Database (WASD) and SQL Server in an Azure VM, for those of you investigating going “to the cloud” and trying to decide on the proper vehicle. Also I’ll have a session on that age-old debate about an ORM-based vs. a stored procedure-based application. And a look at what Microsoft’s contributing to Hadoop, and their Azure-based implementation.

After the conference, I’ll have a WHOLE DAY to tell you all about WASD for folks who want to learn about that in more depth. It’s called “Windows Azure SQL Database from A-Z”.

Sounds like a fun week. If you’re at the conference, don’t forget to stop by and say hi.

Cheers, Bob

@bobbeauch

Posted in: Azure SQL Database, SQL Server 2014

Comments Off

Expertseminarium in Stockholm on performance and SQL Server 2014

By: Bob Beauchemin

Posted on: January 29, 2014 11:56 pm

This March, I’ll be presenting a 2-day seminar as part of AddSkills Expertseminarium series in Stockholm on “Performance and new developer features in SQL Server 2014”. I’ll start with techniques to measure and monitor query performance in general, as well as troubleshooting query performance problems. But from there, I’ll cover the features in SQL Server 2014 that were designed to give your SQL the best performance. Which features are those? Turns out that it’s just about all of them!

There’s more information and a more detiled outline at the AddSkills website, or you can contact them directly. Hope to see you there!

Cheers, @bobbeauch

Posted in: Performance, SQL Server 2014

Comments Off

SQL Server Blog