Showing posts with label DBA. Show all posts
Showing posts with label DBA. Show all posts

Tuesday, September 16, 2014

Does your SQL Smell? The free "119 SQL Code Smells" eBook might help you sniff it out (and fix it)

simple talk - 119 SQL Code Smells

Written with advice, help or contributions from over 25 SQL professionals
Download the free PDF: from Red Gate

Once you've done a number of SQL code-reviews, you'll be able to identify signs in the code that indicate all might not be well. These 'code smells' are coding styles that, while not bugs, suggest design problems with the code. In this PDF, Phil's put together 119 of those code smells so you can see what to avoid and why.

Kent Beck and Massimo Arnoldi seem to have coined the term 'CodeSmell' in the 'Once And Only Once' page of www.C2.com, where Kent also said that code ‘wants to be simple’. Kent Beck and Martin Fowler expand on the issue of code challenges in their essay ‘Bad Smells in Code’, published as Chapter 3 of the book ‘Refactoring: Improving the Design of Existing Code’ (ISBN 978-0201485677).

Although there are generic code smells, SQL has its own particular habits that will alert the programmer to the need to refactor code...

...

imageimageimage

...

One can be a bit defensive about SQL code smells. I will cheerfully write very long stored procedures, even though they are frowned upon. I’ll even use dynamic SQL on occasion. You should use code smells only as an aid. It is fine to ‘sign them off’ as being inappropriate in certain circumstances. In fact, whole classes of code smells may be irrelevant for a particular database. The use of proprietary SQL, for example, is only a code smell if there is a chance that the database will be ported to another RDBMS. The use of dynamic SQL is a risk only with certain security models. Ultimately, you should rely on your own judgment. As the saying goes, a code smell is a hint of possible bad practice to a pragmatist, but a sure sign of bad practice to a purist.

In describing all these 119 code-smells in a booklet, I’ve been very constrained on space to describe each code smell. Some code smells would require a whole article to explain them properly. Fortunately, SQL Server Central and Simple-Talk have, between them, published material on almost all these code smells, so if you get interested, please explore these essential archives of information.

...

nuff said...

Wednesday, September 10, 2014

Visualizing Database Schema changes, with a little help from the Google Charts API

Maria Zakourdaev - Using Google Charts API to Visualize Schema Changes

Last week I have worked on the new email report using Google Charts and liked it so much that decided to share it here with anyone who finds it useful.

I have a Schema Changes Audit table which is being maintained by the DDL Trigger. The relevant record is added to this table every time anyone changes objects on the server

image

image

..."

I just thought this something interesting, kind of cool and different. I'd have never thought to use the Google Charts API (or other charting API) like this...

Wednesday, September 03, 2014

Using Brent Ozar's magic SQL steps to query and find unanswered StackExchange questions

Brent Ozar Unlimited - Finding Unanswered StackExchange Questions with SQL

You love Q&A sites like StackOverflow.com and DBA.StackExchange.com, but sometimes it’s hard to find interesting questions that need to be answered. So many people just sit around hitting refresh, knocking out the new incoming questions as soon as they come in. What’s a database person to do?

Use the power of the SQL.

Data.StackExchange.com lets you run real T-SQL queries against a recently restored copy of the StackExchange databases. Here’s my super-secret 3-step process to find questions that I have a shot at answering.

Step 1. Find out how old the restored database is....

...

Step 2. Find questions everybody’s talking about....

image

...

Step 3. Find questions that people keep looking at....

...

image..."

Why web query when you can just SQL your way through StackExchange? I don't know about you, but I often dream in SQL (no lie.. sigh), so this approach to StackExchange struck a cord for me. Now, if only I was actually smart enough to provide good answers... :O

 

Related Past Post XRef:
SELECT * FROM StackExchange. There's the easy way and the hard, yet much more data fun, way...
Stacks and stacks of data - Your copy of the Stack Overflow’s (and family) public data is a download away

The Stack Family (StackOverflow, SuperUser, etc) gets OData’d via Stack Exchange Data Explorer
Build something awesome with the new StackExchange v2 API and win something awesome...
Stacking up the Open Source Projects, Stack Exchange is...

Monday, August 18, 2014

New SQL Server 2014 Virtual Labs (aka Hands On Labs) available... 18, no... 20!

A Story of BI, BIG Data and SQL Server in Canada - 18 Free SQL Server 2014, Azure and BI virtual labs

image

Do you want to learn about SQL Server 2014, Azure SQL Databases, Corporate BI, and Power BI without the need of installing and configuring the installation bits?

We’ve just released 18 (eighteen) new virtual labs based on the integrated demo Virtual Machines with full step-by-step instructions.

All that you need to start using these online labs is the Internet access and browser (IE, Chrome, and FireFox)*

image

..."

SQL Server 2014 has been out for a little bit now, time for you to start wrapping your head around its new features... And there's nothing to install, just learn...

imageSNAGHTML12cc5ae

image

Tuesday, July 29, 2014

"Killer Free [Free as in free from nag's, calls, whines, free] SQL Tools" from ApexSQL. Includes ApexSQL [Auto] Complete, ApexSQL Refactor and ApexSQL Search

Dirk Strauss - ApexSQL Provides Excellent SQL Tools For Free

"ApexSQL has a few free tools on their site. Before I carry on, I was in no way compensated for this blog post at all. Now that is out of the way, check out ApexSQL tools. I was amazed by the feature richness of these tools. I would be amiss if I didn’t share this with the developer community out there. If you are a developer using SSMS, you definitely must download their free tools and give them a try.

...

These tools are:

  • ApexSQL Complete
    • SSMS and VS integration
    • SQL syntax checking
    • SQL object descriptions
    • SQL code completion
    • SQL code visualization
    • Snippet management
  • ApexSQL Refactor
    • SQL parameter management
    • SQL formatting
    • Consistent code layout
    • Database object refactoring
    • Batch formatting
    • One-to-many relationship replacement
  • ApexSQL Search
    • Smart renaming
    • Text search
    • Database object search
    • Easy SSMS tab navigation
    • SQL code cleaning
    • Graphical dependencies

..."

ApexSQL - Killer Free SQL Tools

"...

image

Why choose ApexSQL free tools?

Great software

We strive to ensure that even though these tools are Free, we still strive for a "Best of Class" product, including, performance, usability, and quality in its respective product class. If you think we can improve our Free tools please contact us to let us know how!

Feature rich

Our free tools implement all the features you need for FREE, including SQL formatting, Database object and Text search, Smart renaming, SSMS and Visual Studio integration, SQL refactoring, and much more. These are features that you would actually have to pay for … but don’t have to!

Continuous development

Just like all of our other tools, we strive to release updates to our Free SQL tools regularly, fix problems, improve performance, and add features. We look to maintain host integration with new versions of SSMS and Visual Studio when they come out. See What's next, to check out our product roadmap

Killer support

Don’t think that because these tools are free that they are unsupported! We still offer Full support via bundle subscriptions including email, phone, and WebEx for all of our Free tools. Even without a subscription to a bundle we are happy to answer questions via our forum and attempt to address any and all issues quickly

Yes, these tools are really FREE

  • No crippleware, time bombs, or bait and switch
  • No annoying embedded ads or nag screens
  • No additional costs to upgrade to newer versions of SQL Server e.g. 2012, 2014
  • No professional version or non-free features you must pay for
  • No mandatory re-installs
  • No requirement to install additional, non-free software
  • No phone calls from sales people

..."

I'd recently seen, and have been meaning to blog about, ApexSQL Complete being free, but I didn't know about the other tools being available. Kudo's to Dirk for making it really clear what was free and how "free" they were!

Monday, April 28, 2014

Free (Email Address-ware) "Performance Tuning with SQL Server Dynamic Management Views" eBook from Red Gate

Red Gate - Performance Tuning with SQL Server Dynamic Management Views

"Dynamic Management Views (DMVs) reveal the hidden activity of your database sessions and transactions – and this book is packed full of practical advice on getting to grips with them.

Download your free copy of Performance Tuning with SQL Server Dynamic Management Views by Tim Ford and Louis Davidson to demystify DMVs. The eBook provides you with the core techniques and scripts to monitor your query execution, index usage, session and transaction activity, disk IO, and more.

You can track down and fix even more SQL Server problems with the SQL DBA Bundle. You’ll get web-based monitoring and six more tools to support your core SQL Server administration tasks, so pick up a free trial and put your new knowledge into practice.

1. Free eBook: Performance Tuning with SQL Server Dynamic Management Views

  • Root out the queries that are causing memory or CPU pressure on your system
  • Investigate caching, and query plan reuse
  • Identify index usage patterns
  • Track fragmentation in clustered indexes and heaps

2. Free trial of the SQL DBA Bundle

  • Real-time performance data and alerts
  • Built with advice from SQL Server experts and MVPs
  • Compress SQL Backups by up to 95%
  • The industry-standard schema and data
    comparison tools

..."

imageSNAGHTML168a7e1

With the advent of the Dynamic Management Objects (DMOs) in SQL Server 2005, Microsoft vastly expanded the range and depth of metadata that could be exposed regarding the connections, sessions, transactions, statements, and processes that are, or have been, executing against a database instance. These DMOs provide insight into the resultant workload generated on the server, how it is distributed, where the pressure points are, and so on, and are a significant and valuable addition to the DBA's troubleshooting armory.

...

In short, if you look hard enough, you will find an almost overwhelming amount of data regarding user activity on your SQL Server instances, and the CPU, I/O, and memory use and abuse that results. The focus of this book is squarely on core engine activity, and troubleshooting. We'll describe the most important columns that these DMOs return, and provide a core set of scripts that can be saved and adapted for your own environment, to shine some light on previously dark corners of SQL Server performance optimization. In the process, we'll cover about a third of the total number of available DMOs, spanning 6 of the available 19 categories of DMO, outlined below.

...

Full time DBA, or accidental, this free 337 eBook is one that's going to be hard to beat. Normally these run 60+ bucks, so get it... get it now (or later, it should be around for a while... ;)

(via Tatworth - Free eBook: Performance Tuning with SQL Server Dynamic Management Views)

 

Related Past Post XRef:
“The SQL Server DMV Start Pack” (eBook and T-SQL) from Red Gate and more Dynamic Management Views (DMV) stuff

Friday, April 18, 2014

Data Editing in SQL Server Data Tools just got a little cooler (you can filter and sort now...)

Deborah's Developer MindScape - SSDT Data Editor Now Has Sorting and Filtering!

The SQL Server Data Tools (SSDT) provide access to many SQL Server features from within Visual Studio. One of those features, available from the SQL Server Object Explorer, is the visual Data Editor.

While the Data Editor has always been great for inserting, updating, and viewing data, it did not support any sorting or filtering … until now!

image

NOTE:

  • For an introduction to SSDT and the SQL Server Object Explorer, see this post.
  • For an introduction to the SSDT Data Editor, see this post.

The March 2014 release of SSDT added support for SQL Server 2014 databases. But it ALSO provided new features in VS 2012 and VS 2013 for sorting and filtering the data in the Data Editor!

If you are using VS 2012, you can use the update option to get this update (SQL | Check for Updates).

If you are using VS 2013, the update should appear in the Notification window when you click the notification flag:

...

image

While I don't suggest this as a main stream means to edit your data, but some times it's just so much easier to fire this up and edit knock out your quick updates. This also helps you avoid the "oh crap, I used the wrong WHERE in my Query Window update" game (not that I've ever done that, updating every row in a table, without a BEGIN TRAN... nope, not me! :/  )

Thursday, April 17, 2014

SQL Server 2014 Secret Killer New Feature, Cardinality Estimator (okay, it's not Secret, but it seems pretty killer...)

Jimmy May's Blog - MSDN Whitepaper: More Cowbell—Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

Hot off the presses is this new MSDN white paper:

Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

One of the gems introduced in SQL Server 2014 is the Cardinality Estimator (CE)—new! improved! & now with more cowbell.  I'm thrilled to be a Technical Reviewer for a superb MSDN white paper authored by my friend, buddy, & pal Joe Sack (b|t). It's exciting & humbling to see my name among such an array of Contributors & Reviewers—including several former colleagues from Azure CAT (formerly SQL CAT b|t).

What’s a CE?

As described on the Cardinality Estimation (SQL Server) page:

Cardinality estimates are a prediction of the number of rows in the query result. The query optimizer uses these estimates to choose a plan for executing the query. The quality of the query plan has a direct impact on improving query performance.

Why a New CE?

The pre-existing CE is more than a decade old.  Both OLTP & DW workloads have changed—& databases are bigger by far than they used to be.  Often, cardinality changes spawned disparate plans (in one prototype, over 78 different plans were generated by the former CE).  Plainly & simply—the CE needed more cowbell.

What’s New?

During SQL14 TAP, SQL Engineer Kate Smith provided a heads up.  Highlights included:

image

Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

Summary: SQL Server 2014 introduces the first major redesign of the SQL Server Query Optimizer cardinality estimation process since version 7.0.  The goal for the redesign was to improve accuracy, consistency and supportability of key areas within the cardinality estimation process, ultimately affecting average query execution plan quality and associated workload performance.  This paper provides an overview of the primary changes made to the cardinality estimator functionality by the Microsoft query processor team, covering how to enable and disable the new cardinality estimator behavior, and showing how to troubleshoot plan-quality regressions if and when they occur.

Authors: Joseph Sack (SQLskills.com)

Contributers: Yi Fang (Microsoft), Vassilis Papadimos (Microsoft)

Technical Reviewers: Barbara Kess (Microsoft), Jack Li (Microsoft), Jimmy May (Microsoft), Sanjay Mishra (Microsoft), Shep Sheppard (Microsoft), Mike Weiner (Microsoft), Paul White (SQL Kiwi Limited)

image

Introduction

The SQL Server Query Optimizer’s purpose is to find an efficient physical execution plan that fulfills a query request. It attempts this by assigning estimated costs to various query execution plan alternatives and then choosing the plan alternative with the lowest estimated cost. One key factor for determining operator cost is the estimation of rows that will be processed for each operator within a query execution plan. This row estimation process is commonly referred to as cardinality estimation. SQL Server 2014 marks the first, significant redesign of the SQL Server Query Optimizer cardinality estimation component since version SQL Server 7.0.

The SQL Server query optimization process seeks the most efficient processing strategy for executing queries across a wide variety of workloads. Achieving predictable query performance across online transaction processing (OLTP), relational data warehousing, and hybrid database schemas is inherently difficult. While many workloads will benefit from the new cardinality estimator changes, in some cases, workload performance may degrade without a specific tuning effort.

In this paper, we will discuss the fundamentals of the SQL Server 2014 cardinality estimator changes. We will provide details on activating and deactivating the new cardinality estimator. We will also provide troubleshooting guidance for scenarios where query performance degrades as a direct result of cardinality estimate issues.

...

Summary

SQL Server 2014 marks the first significant redesign of the SQL Server Query Optimizer cardinality estimation process since version 7.0. Use of the new CE can result in an overall improvement in average query performance for a wide range of application workloads. The new CE also provides diagnostic output for use in troubleshooting cardinality estimate issues. As described in this paper, some workloads may encounter degraded performance with the new CE. We recommend that you thoroughly test existing applications before migrating. When using the new CE, users can leverage trace flags to use the legacy model for queries that regress. This allows you to still benefit from queries that improved under the new model.

I've been hearing a number of good things about SQL Server 2014's new Cardinality Estimator, for example, The SQL 2014 Cardinality Estimator Eats Bad TSQL for Breakfast. If you've been hearing the same thing, or are just interested, you should check out this 43 page doc.

Wednesday, April 16, 2014

On SQL Server 2005, 2008, 2008 R2, 2012? Here's what you get when you jump to SQL Server 2014

A Story of BI, BIG Data and SQL Server in Canada - SQL Server 2014. Benefits of upgrading from SQL Server 2005, 2008, 2008R2 and 2012.

image

Are you running a previous version of SQL Server and want to know what new SQL Server capabilities you will be able to use by upgrading to the latest version?

Please find below the list of new features introduced since SQL Server 2005, 2008, 2008R2 and SQL Server 2012 (it will be a very long blog post!).

SNAGHTML1706301

SNAGHTML170155e

I thought this a great roll-up of features added to SQL Server in the last decade. Prefect if you're a version or two (or three) behind.

Tuesday, April 08, 2014

Providing your users some DBaaS (that's Database as a Service)

Microsoft Downloads - Database as a Service Reference Architecture Guide: SQL Server 2014

Explains how to build an infrastructure for hosting Microsoft SQL Server Database as a Service by using the features of SQL Server 2014 and Hyper-V virtual machines with Microsoft System Center 2012.

Version: April 2014

Date Published: 4/7/2014

Database Hosting Reference Architecture Guide_SQL_2014.docx, 302 KB

This guide explains how to build an infrastructure for hosting Microsoft SQL Server Database as a Service (DBaaS). By using the features of SQL Server 2014 and Hyper-V virtual machines with Microsoft System Center 2012, a hosting service provider can start with very small tenant databases and scale to meet the needs of the largest and busiest SQL Server applications. This reference architecture includes information about hardware, software, system design, and component configuration.

image

image

...

image

1 Overview

This guide to building the infrastructure for hosting Microsoft® SQL Server® Database as a Service (DBaaS) is not limited to a particular type of hardware. By using the features of SQL Server 2014 and Hyper-V® virtual machines with Microsoft System Center 2012, a hosting service provider can start with very small tenant databases and scale out or scale up to meet the needs of the largest and busiest SQL Server applications. This reference architecture includes hardware, software, system design, and component configuration.

2 Hosted Services

Database as a Service, for the purposes of this reference architecture, is a multitenant offering with isolation at the SQL Server database level. Many tenants can share an instance of SQL Server 2014 Enterprise Edition, each tenant with its own database. SQL Servers are hosted on Hyper-V virtual machines running Windows Server® 2012 R2 or using Windows® Core services. Hyper-V virtual machines are managed and monitored by System Center 2012 R2 Virtual Machine Manager and Operations Manager.

A hosted service provider (HSP), the intended audience for this guide, may offer a single server or many hundreds or thousands of servers. Servers may be in the same data center or distributed across data centers for load balancing and disaster recovery.

As an HSP, you make individual SQL Server databases available to the tenant with an agreed-upon maximum size and an agreed-upon amount of resources available. You are responsible for maintaining the SQL Server instance, the virtual host, and the physical host compute, network, and storage infrastructure.

You can secure a single instance of SQL Server easily. When you use the Partially Contained Database and Contained Users features of SQL Server 2014, a database can be made into a secure environment. Users cannot access other databases or the metadata about tenant databases.

You can also use the SQL Server 2014 Enterprise Resource Governor to prevent a single user or a single tenant from using too much of the available resources, and resource usage can be balanced among tenants.

In a SQL Server 2014 DBaaS environment, each tenant is responsible for the data in his SQL Server database. Tenants create the database architecture consisting of objects that store data and application code to maintain and search data and return results to clients. Within the database, a tenant database administrator (DBA) can set permissions enabling subsets of tenant users and groups to carry out these tasks.

The architecture of the database and how well the tenant optimizes code that performs searches will have a direct impact on performance and resource usage. You may wish to offer services to help tenants to optimize design and code to improve response times and minimize resource usage.

As the HSP, you are responsible for Windows and SQL Server maintenance and your standard agreement to provide hosted SQL Server services should define your maintenance windows. You may patch SQL Server and Windows on the virtual and physical hosts, migrate the database to a new virtual machine host when resource requirements require it, and migrate the virtual machine to a new physical host during routine maintenance.

You may also perform certain database-level services on behalf of the tenant, such as running scheduled jobs and backups. Scheduled jobs can run common maintenance tasks like Update Statistics or large scale data modification and aggregation, such as month-end processing. You may also make tools, such as an API or a self-service control panel, available to the tenant to manage such jobs while restricting the tenant’s access to only the data she needs.

You can automate provisioning services by using management APIs and offer features like self-service provisioning to your customers. Such services reduce operational expenses for both you and your tenants.

You can monitor usage at a very detailed level with tools like Microsoft System Center Operations Manager and the SQL Server Management Pack. Tenants can be billed according to very broad guidelines or for only what they use.

Customers will see lower capital expenditures and total cost of ownership when they consolidate database servers, reduce the proliferation of on-premises applications, and share the cost of administrative expertise with other tenants. Tenants can take advantage of advanced solutions without having to buy and administer an entire enterprise solution.

By leasing SQL Server DBaaS, the tenant can pay for only those resources required for an application with the option to scale up in the future. There is no need to over-provision for what might or might not happen in the future, because when more capacity is needed, the hardware will be available.

...

While targeted at hosted service provider, I still think this has value for large organizations or those dealing with SQL Server Sprawl... SQL Server Enterprise isn't cheap, so ensuring that you're using it to its maximum is important for everyone. IT groups like to say they are "service providers." Doing something like this makes that a real statement, without them giving away the keys to the store...

Monday, April 07, 2014

Free eBook of the Day: "Introducing Microsoft SQL Server 2014 - Technical Overview" [Keyword: "Overview"]

Microsoft Press - Free ebook: Introducing Microsoft SQL Server 2014

image

We’re pleased to announce our next free ebook – Introducing Microsoft SQL Server 2014 – by Ross Mistry and Stacia Misner.

Introduction

Microsoft SQL Server 2014 is the next generation of Microsoft’s information platform, with new features that deliver faster performance, expand capabilities in the cloud, and provide powerful business insights. In this book, we explain how SQL Server 2014 incorporates in-memory technology to boost performance in online transactional processing (OLTP) and data-warehouse solutions. We also describe how it eases the transition from on-premises solutions to the cloud with added support for hybrid environments. SQL Server 2014 continues to include components that support analysis, although no major new features for business intelligence were included in this release. However, several advances of note have been made in related technologies such as Microsoft Excel 2013, Power BI for Office 365, HDInsight, and PolyBase, and we describe these advances in this book as well.

Who should read this book?

This book is for anyone who has an interest in SQL Server 2014 and wants to understand its capabilities. Many new improvements have been made to SQL Server 2014, but in a book of this size we cannot cover every improvement in its entirety—or cover every feature that distinguishes SQL Server from other databases or SQL Server 2014 from previous versions. Consequently, we assume that you have some familiarity with SQL Server already. You might be a database administrator (DBA), an application developer, a business intelligence solution architect, a power user, or a technical decision maker. Regardless of your role, we hope that you can use this book to discover the features in SQL Server 2014 that are most beneficial to you. [GD: Post Leached in Full]

You heard that SQL Server 2014 became available for download last week, April 1st (no fooling), right? And now that it's available, you're ready to really invest a little time and learn more about it? This free ebook is for you then...

Get it from the Microsoft Virtual Academy eBook Shelf

image

I'm cheating a little, but here are the direct download links...

Here are some snaps from the PDF;

image

imageimageimageimage

Who should read this book?
This book is for anyone who has an interest in SQL Server 2014 and wants to understand its capabilities. Many new improvements have been made to SQL Server 2014, but in a book of this size we cannot cover every improvement in its entirety—or cover every feature that distinguishes SQL Server from other databases or SQL Server 2014 from previous versions. Consequently, we assume that you have some familiarity with SQL Server already. You might be a database administrator (DBA), an application developer, a business intelligence solution architect, a power user, or a technical decision maker. Regardless of your role, we hope that you can use this book to discover the features in SQL Server 2014 that are most beneficial to you.

Assumptions
We assume that you have at least a minimal understanding of SQL Server from both a database administrator’s perspective and a business-intelligence perspective, including a general understanding of Microsoft Excel, which is often used with SQL Server. In addition, having a basic understanding of Windows Azure is helpful for getting the most from the topics associated with private, public, and hybrid-cloud solutions.

Who should not read this book
As mentioned earlier, the purpose of this book is to provide readers with a high-level preview of the capabilities and features of SQL Server 2014. This book is not intended to be a step-by-step, comprehensive guide.

Related Past Post XRef:
Six videos toward helping you upgrade your inner DBA to SQL Server 2014'ness
SQL Server 2014 RTM's, available for download on the worse possible day (no fooling)?
"SQL Server 2014 Developer Training Kit"
"Microsoft SQL Server 2014 CTP1 Product Guide" - One new SQL Server, 12 PDF's...
"How the heck is Hekaton part of SQL Server 2014..." More on SQL Server 2014's In-Memory Tech
Can you Hekaton? Intro to the SQL Server 2014 Analysis, Migration and Reporting tool...
Playing with SQL Server 2014 (and VS2013) the Azure VM way
Dev:"But it's new and shinny! Let's upgrade!" DBA:"Over my..." - Preparing to upgrade your SQL Server
TechEd NA 2013 Day 1 Announcement Round-up - VS 2013, TFS 2013, InRelease, SQL 2014, Server 2012 R2, BizTalk Services, Azure-in-a-box and even more Azure...

Friday, March 28, 2014

Search SQL scripts simply with the SQL Scripts SSMS plugin

simple talk - Tony Davis - D.R.Y. with SQL Scripts

Developers strive to write well-tested, reusable code with well-defined interfaces so that when they need to update the functionality, they need do so in one place only. It is the principle of ‘Don’t Repeat Yourself’ (D.R.Y.).

However, it is common for developers to be poor at applying D.R.Y. to their own past work. When it comes time to implement some complex new routine, a faint bell rings in their mind…didn’t I write something similar for that CRM project? What year was that…? If a brute-force search through their chaotic script archive doesn’t unearth it, within a few minutes, they then roll up sleeves, crack knuckles and set about writing it again from scratch, convinced they will do a better job of it this time, anyway: And, after all, it’s fun.

However, what if you really don’t have time to write the code from scratch, or need some pointers to get started? You might trawl a few of your favorite blogs, or find something on Stack Overflow. After all, many developers and DBAs blog all sorts of snippets and scripts, suggesting hopefully that they may be useful to others, but admitting that their main motivation is to know where to come the next time they need it themselves!

Another option, when in need of SQL code, is to search a public script archive such as the one on SQLServerCentral.com. This always used to be a tricky operation. If, for example, we wanted a string-splitting function, we’d need to type into Google something like ‘site:www.sqlservercentral.com/Scripts/ list split‘. Now it is so much easier, with the addition of a small SSMS plug-in called SQL Scripts.

...

image

In addition, you can search the whole archive directly from within SSMS, as well as add scripts to your briefcase and to the archive as a whole.

...

If you’re an occasional or frequent miner of the SSC script archive, we’d love you to try out SQL Scripts and let us know what you think. How much might it help you reuse the code of others, as well as find and reuse your own?

We just don't see SSMS plugins often enough. This one looks interesting and pretty useful for both the DBA and SQL Dev...

Thursday, March 20, 2014

Six videos toward helping you upgrade your inner DBA to SQL Server 2014'ness

Channel 9 - Updating your Database Management Skills to SQL Server 2014

Do you manage online transaction processing (OLTP) database workloads? Want to learn about the new and enhanced capabilities in SQL Server 2014 to help you do so? Watch this course to learn about enhancements and capabilities new to SQL Server and the Microsoft data platform since the release of SQL Server 2008.

image

Since the RTM bits are not yet available you can get a jump on your friends and co-workers and start upgrading your DBA brain cells to support (or sell to your bosses) SQL Server 2014. As I've said, this is a pretty awesome release and there's a great deal to grok. The sooner you start...

 

Related Past Post XRef:
SQL Server 2014 RTM's, available for download on the worse possible day (no fooling)?
"SQL Server 2014 Developer Training Kit"
"Microsoft SQL Server 2014 CTP1 Product Guide" - One new SQL Server, 12 PDF's...
"How the heck is Hekaton part of SQL Server 2014..." More on SQL Server 2014's In-Memory Tech
Can you Hekaton? Intro to the SQL Server 2014 Analysis, Migration and Reporting tool...
Playing with SQL Server 2014 (and VS2013) the Azure VM way
Dev:"But it's new and shinny! Let's upgrade!" DBA:"Over my..." - Preparing to upgrade your SQL Server
TechEd NA 2013 Day 1 Announcement Round-up - VS 2013, TFS 2013, InRelease, SQL 2014, Server 2012 R2, BizTalk Services, Azure-in-a-box and even more Azure...

Wednesday, March 19, 2014

SQL Server 2014 RTM's, available for download on the worse possible day (no fooling)?

The Official Microsoft Blog - SQL Server 2014 released to manufacturers, will be generally available April 1

Today I am very happy to announce SQL Server 2014 has been released to manufacturing and will be generally available on April 1.

SQL Server 2014 is the culmination of thousands of hours of hard work from Microsoft engineers and thousands of hours of testing and input from our preview customers. The result is an important component of Microsoft’s overall cloud-first data platform. The platform delivers breakthrough performance, accelerated insights through tools everyone uses and the ability to scale globally on-premises and in the cloud – letting our customers get the most from their data.

This release of SQL Server is significant in that, in addition to delivering key hybrid scenarios, it rounds out our journey to embrace in-memory technology. Several years back, we began exploring the changing hardware landscape – memory being one of the key areas of focus. Today, our in-memory technology spans the core workloads in the data platform: business intelligence as part of Analysis Services, Excel and Power BI for Office 365; complex event processing with StreamInsight; in-memory columnstore in SQL Server and our data warehousing product; and now with SQL Server 2014 – in-memory transaction processing.

...

Windows Azure and SQL Server provide a continuum of capability and flexibility in deployment options for our customers – on-premises and in the cloud. Windows Azure also supports an important data workload – Windows Azure HDInsight, our Apache Hadoop-based solution in the cloud. Today, we’re announcing the general availability of Hadoop 2.2 support in Windows Azure HDInsight, which has been updated to take full advantage of the latest Hadoop 2.2 platform, including support for YARN and Stinger Phase 2.

There has never been a more exciting time in the database and broader data platform industry. In an era where nearly everything will become digitized, today we’re delivering the data platform that will help our customers digitize their business. Customers can download SQL Server 2014 on April 1 or register today to be notified once the release is available. Customers can also learn more about the release and our data platform strategy at our “Accelerate your Insights” event on April 15, where I will be joined by Microsoft CEO Satya Nadella and COO Kevin Turner. In addition, some of our customers deriving value from our data platform today will also be in attendance. I encourage you save the date and tune in for the event.

image

<rant>April Fools Day? Really? You couldn't release it a day earlier or later? Think what a Build bullet point this would have made during the Day One Keynote on the 2nd? But noooo... This will now and forever be known as the SQL Server April Fools edition.

Yeah, yeah, I know we're all tired of the April Fools crap, but ... really? really?

Don't get me wrong, I think this is one of the more existing versions of SQL Server in a while and there's some awesome tech in it. Congrats to the team, just do me a favor and smack the marketing wonk who pick this date...</rant>

Friday, March 14, 2014

ERwin gets a new home at Embarcadero

PCWorld - Embarcadero moves into data modeling with CA ERwin buy

Embarcadero Technologies is acquiring the ERwin data modeling software and associated personnel from CA Technologies, giving the vendor of software development tools an instant and formable presence in the growing field of data architecture.

The purchase makes data architecture the largest part of Embarcadero’s business, Embarcadero CEO Wayne Williams said in a statement.

...

To date, Embarcadero has been most well known for its software and database development tools. The company’s first product, released in 1993, was a cross-platform SQL IDE (integrated development environment) called Rapid SQL. It also maintains the Delphi language and toolbase, as well as C++Builder IDE, both acquired from Borland Software in 2007

...

image

..."

I've used ERwin in a number of past lives and found it pretty awesome. But the price was always a pain, and when it went to CA <snarky> aka the place where products go to die </snarky> well...

Embarcadero <snarky> aka the Island of Orphan Products, </snarky> seems to treat the new products it acquires with much more respect. Here's to hoping that they give ERwin some TLC. I'm looking forward to seeing a new and improved ERwin (that hopefully I don't have to take a 2nd out on the house to buy... ;)

Thursday, February 27, 2014

Making Relativity relatively faster... Partition it baby, (sometimes)

Brent Ozar - How to Use Partitioning to Make kCura Relativity Faster

kCura Relativity is an e-discovery program used by law firms to find evidence quickly. I’ve blogged about performance tuning Relativity, and today I’m going to go a little deeper to explain why DBAs have to be aware of Relativity database contents.

In Relativity, every workspace (case) lives in its own SQL Server database. That one database houses:

  • Document metadata – where the document was found, what type of document it is
  • Extracted text from each document – the content of emails, spreadsheets, files
  • Document tagging and highlighting – things the lawyers discovered about the documents and noted for later review
  • Workspace configuration – permissions data about who’s allowed to see what documents
  • Auditing trails – who’s searched for what terms, what documents they’ve looked at, and what changes they made

For performance tuners like me, that last one is kinda interesting. I totally understand that we have to capture every activity in Relativity and log it to a table, but log-sourced data has different performance and recoverability requirements than other e-discovery data.

...

However, I don’t recommend doing this by default across all your databases. This technique is going to instantly double the number of databases you have and make your management much more complex. However, I do recommend reviewing your largest workspaces to see if AuditRecord is consuming half or more of the database space. If so, consider partitioning their AuditRecord tables to get faster backups, database maintenance jobs, and restores.

At the risk of sounding like a fanboy, this is one of the reasons I love working with the kCura folks. They really care about database performance, they take suggestions like this, and they implement it in a way that makes a real difference for customers.

This is also why database administrators need to:

  1. Understand the real business purpose of the biggest tables in their databases
  2. Build working, productive relationships with their software vendors
  3. Come up with creative approaches to ease SQL Server pains
  4. Help the vendors implement these approaches in software

..."

If you're a Relativity shop, Brent's one of those "must go to dba guys," which this post makes very apparent...

 

Related Past Post XRef:
Making SQL Server a happy kCura Relativity camper (and your users too)

sp_AskBrent - Your new, "OMG, my SQL Server is sooo slow" free uber SP from Brent Ozar
Two SQL Server Resources that you might want to take another look at...

Free Training SQL Server Training DVD’s (or online) from Quest (reg-ware) - 12 Sessions, Two DVD’s, Zero cost…

"How to Develop Your DBA Career" Free eBook (and posters and whitepapers and more [oh my])

Saturday, February 08, 2014

"SQL Server 2014 Developer Training Kit"

Microsoft Downloads - SQL Server 2014 Developer Training Kit (and related SQL Server 2014 Developer Training Kit Sample Databases)

The SQL Server 2014 Developer Training Kit includes technical content designed to help you learn how to develop SQL Server 2014 databases, BI Solutions, and gain insights into the new cloud capabilities to simplify cloud adoption for your SQL databases and help you unlock new hybrid scenarios.

Version: 1.0.0

Date Published: 2/7/2014

SQL 2014 Dev Training Guide.exe, 251.8 MB

The purpose of this training kit is to help DBAs and Developers become familiar with some of the new and improved features and functionality in SQL Server 2014. This training kit is a great resource for developers, trainers, consultants and evangelists who need to understand the new features and key improvements introduced in SQL Server 2014 from a developer perspective. It contains a rich set of presentations and demos that are perfect for self-paced learning or for conducting your own training event. The easiest way to get started with the training kit is to download it, install it, and browse the kit for the content that you are most interested in. Many of the presentations and demos in the training kit reference a video that you can watch to familiarize yourself with the content. When you are ready for some hands-on experience, try working through one of the demos. Microsoft SQL Server 2014 builds on the mission-critical capabilities delivered in SQL Server 2012 by providing breakthrough performance, availability, and manageability for mission-critical applications. SQL Server 2014 delivers new capabilities built into the core database for OLTP and data warehousing, which complement our existing OLTP, data warehousing, and BI capabilities for the most comprehensive database solution in the market.

Some snaps from the install;

image

The SQL Server 2014 Developers Training Kit is a great resource for developers, trainers, consultants and evangelists who need to understand the key improvements introduced in SQL Server 2014 from a developer perspective. It contains a rich set of presentations and demos that are perfect for self-paced learning or for conducting your own training event.

The easiest way to get started with the training kit is to download it, install it, and browse the kit for the content that you are most interested in.  Many of the presentations and demos in the training kit reference a video that you can watch to familiarize yourself with the content.  When you are ready for some hands-on experience, try working through one of the demos.

Thank you for downloading the training kit, we hope you find it to be a valuable resource for driving SQL Server adoption.

Once installed, here's what you get;

image

image

image

SQL Server 2014 builds on the foundation of SQL Server 2012, so it's nice seeing that this kit provides information on both,

image

If you're looking at, or thinking about looking at, SQL Server 2014, you've got to get this...

 

Related Past Post XRef:
RTM SQL Server 2012 Developer Training Kit available as a full download or web installer

Monday, January 27, 2014

Can you script table data, not schema but actual data as insert statements, directly from native SQL Server Management Studio? (Tip, yes)

Dirk Strauss - Script Table Data In SQL Server Management Studio

Script Table Data – I have used other tools to do this for ages. I can’t believe that I never knew that it existed in SQL Server Management Studio. Well ok, in my defence it isn’t quite the most ‘In-your-face’ setting. Nevertheless, it is very convenient and best of all, you don’t need to leave the IDE to script table data. So where is this function? Why, under Tasks of course.

Script Table Data With Generate Scripts

Well, I guess we all know and have used the Generate Scripts function in SQL Server Management Studio before. But with this, you can do so much more in fact. So right click on your Database and select Tasks -> Generate Scripts…

...

Well the option to script table data still isn’t screaming at you from this screen either. Under the General section, select the last option ‘Types of data to script’ and choose one of the options from the dropdown menu. For my purposes, I only want the data in the table so I selected ‘Data Only’. You might want the schema and data, but the important thing is that you have a choice

...

After you click ok, you can choose to script all the objects to one single file or split them up into a separate file for each object. This is obviously a decision you need to make according to your preference. I prefer to have a script per object. So yeah, that’s it! As I said, I never knew this option existed until I went exploring around the settings a bit. Any comments (nice ones) are always welcome.

image

Not like this option/feature wasn't burred deep enough or anything... :/

I wonder how long this has been around (probably knowing my luck, like the better part of a decade..lol )

Friday, January 17, 2014

SELECT * FROM StackExchange. There's the easy way and the hard, yet much more data fun, way...

Brent Ozar - How to Query the StackExchange Databases

During next week’s Watch Brent Tune Queries webcast, I’m using my favorite demo database: Stack Overflow. The Stack Exchange folks are kind enough to make all of their data available via BitTorrent for Creative Commons usage as long as you properly attribute the source.

There’s two ways you can get started writing queries against Stack’s databases – the easy way and the hard way.

The Easy Way to Query StackOverflow.com

Point your browser over to Data.StackExchange.com and the available database list shows the number of questions and answers, plus the date of the database you’ll be querying:

...

The Hard Way to Query StackOverflow.COM

First, you’ll need to download a copy of the most recent XML data dump. These files are pretty big – around 15GB total – so there’s no direct download for the entire repository. There’s two ways you can get the September 2013 export:

I strongly recommend working with a smaller site’s data first like DBA.StackExchange. If you decide to work with the monster StackOverflow.com’s data, you’re going to temporarily need:

  • ~15GB of space for the download
  • ~60GB after the StackOverflow.com exports are expanded with 7zip. They’re XML, so they compress extremely well for download, but holy cow, XML is wordy.
  • ~50GB for the SQL Server database (and this will stick around)

Next, you need a tool to load that XML into the database platform of your choosing. For Microsoft SQL Server, I use Jeremiah’s improved version of the old Sky Sanders’ SODDI. Sky stopped updating his version a few years ago, and it’s no longer compatible with the current Stack dumps. Jeremiah’s current download is here, and it works with the September 2013 data dump.

...

image

...

Why Go to All This Work?

When I’m teaching performance tuning of queries and indexes, there’s no substitute for a local copy of the database. I want to show the impact of new indexes, analyze execution plans with SQL Sentry Plan Explorer, and run load tests with HammerDB.

That’s what we do in our SQL Server Performance Troubleshooting class – specifically, in my modules on How to Think Like the Engine, What Queries are Killing My Server, T-SQL Anti-patterns, and My T-SQL Tuning Process. Forget AdventureWorks – it’s so much more fun to use real StackOverflow.com data to discover tag patterns, interesting questions, and helpful users.

A great resource, both Brent's post and of course the data for when you need some "safe" data, yet in a large enough volume to be meaningful...

 

Related Past Post XRef:
Stacks and stacks of data - Your copy of the Stack Overflow’s (and family) public data is a download away

The Stack Family (StackOverflow, SuperUser, etc) gets OData’d via Stack Exchange Data Explorer
Build something awesome with the new StackExchange v2 API and win something awesome...
Stacking up the Open Source Projects, Stack Exchange is...

Wednesday, December 11, 2013

The annual "What version of SQL Server are we running?" post...

nullinterface - Microsoft SQL Server Versions and Editions

Time and again some partners send in requests and detail out only the version numbers for the SQL Server they're working on. Isn't always permissible for us to go bak to them for further info, so we dwell into the KB articles to determine the correct editions and Service Packs installed to assist the partners the best way possible. I though sharing the collated list could benefit some more of our DB folks.

To determine the version of SQL Server, you can use any of the following methods:

Method 1

Connect to the server by using Object Explorer in SQL Server Management Studio. After Object Explorer is connected, it will show the version information in parentheses, together with the user name that is used to connect to the specific instance of SQL Server.

Method 2

Connect to the instance of SQL Server, and then run the following query:

Select @@version

Method 3

Connect to the instance of SQL Server, and then run the following query:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
Note This query works with any instance of SQL Server 2000 or later

image

How to determine the version and edition of SQL Server and its components

This article describes how to determine your current Microsoft SQL Server version number and the corresponding product or service pack level. It also describes how to determine the specific edition of SQL Server that you are using.

Note If you just need to know what a specific version number of SQL server maps to or the KB information for a specific cumulative update package or a service pack, refer to the section of this article and search for the version number.

Note If you just want to find the latest builds for SQL Server, you can refer to the following article. Or, you can check the tables that correspond to your product in the section of this article. 957826

Where to find information about the latest SQL Server builds

Where to find information about the latest SQL Server builds

This article lists the Knowledge Base articles that provide more information about the following:

  • The Microsoft SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1.
  • The Microsoft SQL Server 2012 builds that were released after SQL Server 2012.
  • The Microsoft SQL Server 2008 R2 builds that were released after SQL Server 2008 R2.
  • The Microsoft SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 1.
  • The Microsoft SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 2.
  • The Microsoft SQL Server 2008 builds that were released after SQL Server 2008.
  • The Microsoft SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1.
  • The Microsoft SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 2.
  • The Microsoft SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 3.
  • The Microsoft SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2.
  • The Microsoft SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 3.
  • The Microsoft SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 4

Seems this comes up every year or so, and with the number of supported SQL Server versions out there, I thought it a good time to re-post this info...

 

Related Past Post XRef:
Goodbye @@version, hello xp_msver...
SQL Server Version Number Database (SQL Server 6.5 through 2008 R2 SP1 CTP...)

"Select @@Version" is old school... Check out the ServerProperty function
SQL Server Version Numbers - That @@version number means my SQL Server is running what SP?