Here’s my bookmarked links for October 26th through October 30th:

SQL Server Links

• Windows Azure Platform Launch Update – The blog notes that “a number of new features in Windows Azure will be made available for the first time” – but it’s at PDC, not the Professional Association for SQL Server Summit. This should be your clue that cloud-based SQL Server isn’t targeted at DBAs, folks – it’s targeted at developers.
• Don’t be a DBA – Be a Data Professional – Buck Woody explains that your real job isn’t tied to the exact version/edition/brand you’re using – it’s tied to the value you provide.
• SQL University: Diving into Authorization – Do you understand the differences between GRANT, REVOKE and DENY? K. Brian Kelley dives in for SQL University.
• SQL Server with NetApp SAN – Collection of best practices documents from NetApp.
• Checklist for installing SQL Server 2005 as a clustered instance – Your first cluster is painful – get help from Cindy Gross’s simplified checklist.
• How People Abuse Their SQL Server Transaction Log – Things NOT to Do – A laundry list of SQL Server abuses.
• Merrill Aldrich : Using Historical Perf Counter Data For Storage Planning – Dives into some of the cool metrics you can get from the Quest Spotlight repositories.
• Suggested Max Memory Settings for SQL Server 2005/2008 – I like hard-coding the maximum memory size for SQL Server whenever it’s not being shared with multiple applications. Glenn shows you some guidelines for the right numbers.
• SQL University: Authentication and Authorization – K. Brian Kelly talks about his specialty – making sure your data stays yours.
• Amazon EC2 – Now an Even Better Value – Amazon’s prices are lower now for SQL Server in the cloud.
• SSMS Tools Pack 1.7 is out! New feature: SQL Snippets – Great free tool.
• Deterministic Functions – Steve Jones talks about how to determine whether something’s deterministic – it affects caching of execution plans, too.
• Developing Data-tier Applications Using Visual Studio 2010 – Video showing off what’s coming for DBAs long term.
• Templates for New Query Window – Buck Woody shows how to force yourself into commenting your code better.
• High Availability Improvements in SharePoint 2010 – Woohoo! It’ll support SQL Server database mirroring out of the box.
• Introducing Amazon RDS – The Amazon Relational Database Service – MySQL in the cloud, cheap.

#SQLPASS Links

• Starting the Travel Tips Meme – Great tips from Kevin Kline for the upcoming PASS summit.
• What Should PASS Strive To Be? – Jack Corbett’s thoughts on #SQLPASS’s directions.
• A Rendezvous With Destiny: PASS 2009 (#sqlpass) – If you’re going to the PASS Summit, you need to watch Jimmy May speak.

Tech Links

• Losing Net Neutrality: The Worst Case Scenario [Net Neutrality] – If you don’t understand the talk around “net neutrality”, this post will illustrate how the net will look like if we don’t have net neutrality.
• VCE-101 Thin Provisioning Part 1 – The Basics – Thin provisioning involves creating storage arrays without actually having as much storage as you think. Doing it right requires some awareness at the OS level.
• ScreenFlow 2.0: top Mac screen recording app gets even better – This is the tool I use for my podcasts and screencasts.
• Motorola DROID review – Engadget’s review of the highly awaited Android headset. Comes out with more cons than I’d have expected.
• Free Public Careers CVs – This is going to change the way geeks get hired.
• Announcing Google Maps Navigation for Android 2.0 – Free turn-by-turn navigation for Android phones. Interesting because Google is basically competing against the iPhone on price now – rather than paying for turn-by-turn nav, you can get an Android phone and get that for free.
• Dunbar’s Number isn’t just a number, it’s the law – Humans can’t handle having more than 150 friends.
• Now Available: The Virtual Storage Console vSphere Plug-in – If you manage VMware and you have a NetApp SAN, this lets you get better insight into your virtual server storage from inside VMware tools.

The Junk Drawer

• Newspaper circulation chart over the last 20 years – What a cliff. Scroll down.
• I Got Nowhere To Go – Tom LaRock questions why people are starting up all kinds of new sites that do the same thing as ones that are already popular & successful.
• Best Buy drops the price on the Netflix-streaming, Blu-ray playing Insignia Blu-ray player – $100 for a Blu-ray player is one heck of a breakthrough price.
• Book Review: Twitter Wit – Tom LaRock reviews a book with tweets.
• The Power of Being Personal on Your Blog – Wanna know why you should be personal? Ask a blogger who’s already been wildly successful.
• Which Religion Should I Follow? – Flowchart to pick your Jeebus.
• Caffeine & Edibles : Baconnaise – Man, if only there was some way I could get both bacon and mayo on my sandwich faster….ah-ha!

These bookmarks are automatically imported from my bookmarks at Delicious.com. If you’d like to get up-to-the-minute updates on what I’m bookmarking, you can subscribe to my bookmark RSS feed.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

R2′s new Data-Tier Application (DAC) capabilities give the DBA to manage databases as if they were more like virtual servers.  Today I’m going to talk about what that means short-term and long-term.

Short-Term: Nothing To See Here, Move Along

I don’t think most DBAs will see a .dacpack file in the wild for years:

• It’s SQL Server 2008 R2 only. The new SQL Server Utility model can’t be used to manage older versions of SQL Server.  Whenever I poll enterprise DBAs, the majority of ‘em are still on 2000 and 2005.
• It’s only available in Enterprise Edition. Microsoft has only put the coolest new features in Enterprise Edition lately, and this is no exception.
• They don’t develop themselves. Somebody has to develop the DACs before you can deploy ‘em to your server.  Yes, you can reverse-engineer an existing database into a new Data-Tier Application, but…
• Not all SQL Server objects are supported. Data-Tier Applications support only a subset of objects like tables, non-clustered indexes, views, functions, and stored procedures – and not just any stored procs, either.

To illustrate that last point, I tried extracting one of my favorite databases, a Twitter cache built using Tweet-SQL, and got the following errors:

DAC Pack Errors

Some of the errors included:

• This object depends on dbo.tweet_usr_followers(Stored Procedure) that is not supported in a DAC.
• Accessibility(SqlAssembly) – This object type is not supported in a DAC.
• dbo.tweet_acc_archive(StoredProcedure) – This object type is not supported in a DAC.

The problem: extended stored procedures and encrypted stored procedures.

Ironically, these techniques are often used by third party vendors to deliver their code – like TweetSQL, and like Quest, for that matter.  Yesterday, I blogged about why third-party vendors are a great use case for DACs, but now we can see why adoption in that user group won’t be quick.  Why would a third-party vendor deploy their database as a .dacpack if it can only be deployed on Enterprise Edition and none of the contents can be encrypted?

In fact, who would use this at all when it’s such a minor subset of SQL Server’s capabilities?

Flash Back to SQL Server 2008′s Release

At the PASS Summit in Seattle, I talked to a DBA who’d really had it with Microsoft.  He complained that 2008 didn’t have anything groundbreaking for production database administrators.  He’d loved SQL 2005′s introduction of SQL Server Management Studio, but since then, he hadn’t seen anything out of Microsoft to really make his job easier. PowerShell and Policy-Based Management were theoretical steps in the right direction, but not big enough.  (To date, I still don’t see widespread adoption of either, and I agree with his sentiments.)

More than making life easier for production DBAs, SQL Server 2008 started to install plumbing that would make life easier for BI users.  Much easier.  Much, much easier.  SQL Server 2008 R2 brings the faucets and shows just how easy it’s going to be.  To quote Microsoft’s R2 marketing page:

“Self-service analysis tools allow end-users to quickly create solutions from disparate data sources within a familiar Microsoft Office Excel user interface. By publishing these solutions in SharePoint Server, users can easily share them with others.”

As Microsoft delivers capabilities to end users, they’re focusing on self-service.

Not making it easier for administrators – making it self-service.  Making it easy enough that no dedicated administrator is required – in theory, at least.

The reality is that DBAs don’t buy SQL Server.  DBAs sell SQL Server.  They sell it to CIOs, developers, and BI users.  Microsoft’s approach with self-service BI in SQL Server 2008 R2 means that they’re selling directly to the BI users, empowering them to do their own work without getting approval from the DBA.

That DBA who complained about Microsoft not focusing on DBA tools isn’t going to be any happier with SQL Server 2008 R2.  The features available at release will be focused on BI professionals, and the new-plumbing features that will work long-term are focused at a different audience altogether.  When you read that SQL Server Utility features will make life easier for production DBAs, you need to read between the lines.

Long-Term: Look Up, The Sky Is Coming

The subset of supported features is eerily similar to SQL Azure, Microsoft’s cloud-based SQL Server offering.

Oh Noes!

Did you hear that just now?

All over the universe, DBAs cried out in anguish at the prospect of reading yet more about how the cloud is coming to steal their buckets.

Just like SQL Server 2008 R2 starts to deliver self-service BI, the next versions of SQL Server will probably focus on delivering self-service data storage.  The “self” could be:

• Developers who just want to write applications that store data without the hassles of asking for DBA permission
• Network admins who want to manage SQL Server the same way they manage the rest of their servers – as virtual servers, slicing up pools of resources
• Project managers who want to buy a third-party application and don’t care about how the database works

Virtualization didn’t become popular by catering to the people who liked their own dedicated servers.  It caught on because it catered to the people who paid the bills and the people who had to manage all those dedicated servers.  Likewise, the DAC concept might catch on not by catering to DBAs, but by catering to people who never really liked DBAs.

These types of users will love the DAC concept, and furthermore, they might like the concept of just hosting their database in the cloud.  Us hard-core DBAs look at Azure’s pricing model ($10/mo for 1gb, $100/mo for 10gb) as crazy high, but to project managers and network admins, that’s not too bad at all.  Compare it to the cost of a fully configured SQL Server Enterprise Edition box with licensing (because remember, DACs only work on Enterprise Edition) and the cost is downright sensible.

DBAs complain about security problems in the cloud, but the DAC concept appears to have conquered some of those limitations.  Security requirements are built into the DAC package, including logins and permissions, and non-secure multi-database elements (like extended stored procedures) just aren’t allowed in .dacpack files.

When I interviewed Tom Casey at the PASS Summit in 2008, he hinted at this by saying that SQL Server is already somewhat multi-tenant, and now I see where this is going.  Down the road, you can choose whether to deploy a DAC in your internal cloud (SQL Server resource pool managed by the SQL Server Utility Control Point) or to Microsoft’s cloud.  If your developers have already confined their application to using a subset of SQL Server’s functionality, then it’s no additional headaches for you.

The SQL Server Utility model won’t change the way you work this year, but ask your Windows administrators how their job has changed with the advent of virtualization.  Take heed of the lessons they learned, because your job will be changing next.

Related SQL Server 2008 R2 Posts:

• SQL Server 2008 R2: Virtualization for Databases
• SQL Server 2008 R2: Introducing the DAC Pack
• Project Kilimanjaro, Gemini Announcements
• My PASS Summit 2008 Interview with Tom Casey

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts

R2 makes it easier for DBAs to move databases around from server to server in much the same way virtualization admins move guest OS’s around between physical hosts.  In my last blog post about SQL Server 2008 R2, I explained that databases are becoming more like virtual servers, and today I’ll talk about what that means to DBAs.

R2: Bringing Sexy DAC

SQL Server 2008 R2 still has the same concept of databases, but it’s added a new level above databases called Data-Tier Applications, abbreviated DAC because the abbreviation DTA was already too widely-known.  The DAC includes the database schema plus some server-level objects required in order to support the database, like logins.

The DAC does not include the data inside your database. For deployment best practices, you should have any necessary data (configuration tables, basic lookup tables) already scripted out as part of your deployment strategy. With the DAC approach, it makes sense to put these scripts inside the database as objects. For example, you might have a stored procedure called usp_deploy that populates all of the necessary configuration tables via insert statements.

In R2′s SQL Server Management Studio, right-click on a database and click Tasks, Extract Data-Tier Application.  This starts a wizard that will reverse-engineer your database schema, figure out what makes it tick, and package it in a way that you can redeploy it on another server.  The information is saved in a file with a .dacpac extension, and if you try to open it with SQL Server Management Server, you’ll hit a stumbling block:

Not So Fast

Microsoft’s taking an interesting approach here by drawing a line in the sand.  The first hint pops up in Books Online:

“A DAC can be authored and built using a SQL Server Data-tier Application project in Microsoft Visual Studio. Current plans are to introduce the SQL Server Data-tier Application project type in a future beta release of Visual Studio 2010.”

Henceforth:

• SQL Server Management Studio is for production database administrators.
• Visual Studio is for database developers.

What DACs Mean for Database Administrators

If you never had a change control process and your developers just implemented changes willy-nilly in production, then the DAC approach won’t change anything.  Your developers will do what they’ve always done.

If you’ve got change control processes in place, your developers probably hand you change scripts and tell you to implement them in production. If you’re ambitious, you audit their work as a sanity check to make sure their work will scale.

In the future, your developers may be creating and updating their database schema, stored procedures, functions, etc. inside Visual Studio, packaging them into DAC Packs, and handing them to you.  In order for you to check their work, you’ll need to switch over into Visual Studio, or perhaps log onto their development SQL Servers to see the schema changes there.  This is another nail in the coffin of the power of the DBA.  From the nosql movement to the DBA-less cloud, DBAs need to be acutely aware of how things are changing.

This isn’t necessarily a bad thing; it’s worked great in the world of virtualization.  As a VMware sysadmin, I didn’t need to understand what each virtual server was doing, whether it conformed to best practices, or even what was running on it.  I managed them in large quantities with low overhead simply by moving things around based on the resources they needed.  If a server’s needs grew, I could move them to a larger VMware host or a less-active host.  I only purchased resources incrementally for the entire pool rather than micromanaging what each server needed.  I didn’t do as good of a job as if I’d micromanaged each server’s configuration, but I was able to manage more servers with less manpower.  Everything’s a tradeoff.

What if you, as a production DBA, could manage more instances and more databases with less time?  What if, instead of looking at lines of T-SQL code, you were able to step back and see the bigger picture?  What if you treated every application as a sealed, hands-off third-party app?

Perfect DAC User: Third-Party App Vendors

At Quest Software, we build multi-tier applications that store data in SQL Server databases.  For example, Foglight Performance Analysis monitors the most offensive queries in your applications and gives you insight as to what developer you should shoot first.

We have basic requirements for our repository databases, but when we hand the product off to the customer, we’re relying on the honor system.  We ask that you not deploy the repository with Auto-Close enabled, for example, but we have to trust that you know what that means and how to make sure it’s not enabled.  If we deploy our repository as a DAC, however, we can build in policies that check for things at deployment time.

Another downside of storing our data in SQL Server is that you, the SQL Server DBA, tend to poke around.  I’ve worked with DBAs who wanted to extend Quest’s database to store more stuff, or they wanted to store their utility queries in Spotlight’s work database.  They say, “Hey, this QuestWorkDatabase is on all of my servers – I’ll just stash my queries in here and nobody will notice.”  Next thing you know, they’re storing tables with data in there too, and then it’s only a matter of time until they break our schema.

I would love to deliver our repository as a sealed, hands-off appliance database that the DBA couldn’t break.  Unfortunately, though, Books Online says:

“After deployment, the database is managed like any other database. Configuration of the database is done using common mechanisms such as the ALTER DATABASE Transact-SQL statement, the database management dialogs in Management Studio, or using the SQL Server Management Objects in the SQL Server PowerShell provider.”

The vendor in me says, “Damn! The DBAs can still hose up our schema.”

The DBA in me says, “Yay! I can still add indexes and fix bad code in vendor products!”

Next Up: DACs and The Cloud

What DACs don’t support gives us a clue about where database technology is going, and I’ll talk about that in my next SQL Server 2008 R2 blog post.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts