SQL Azure

Fri, 10 Feb 2012 10:00:31 -0600

SQL Azure is a cloud-based service from Microsoft that uses a special version of SQL Server as its backend. SQL Azure helps to ease provisioning and deployment of multiple databases. Developers do not have to install, setup, patch or manage any software, as all that is taken care of by Microsoft with this platform as a service (PAAS). High availability and fault tolerance is built-in and no physical administration is required. It is an elastic platform which provides you the means to instantly increase or decrease your server capacity. You only pay for what you use and don’t have to estimate capacity needs and buy a reserve capacity.

SQL Azure is getting very popular, and while I think it will be a few years before many companies replace their internal data centers with it, it is a good idea to become familiar with it now. Unfortunately it does not include SSAS yet, but does include SSRS and you can use SSIS to move data into and out of SQL Azure. Slowing its adoption is it does not yet support all the SQL Server features such as: integrated security, SQL Server agent, transaction log backups, cross-database references, linked servers, fulltext indexes, connection pooling, multiple database collations, data compression, table partitioning, replication, mirroring. See SQL Server Feature Limitations (SQL Azure Database) and Unsupported Transact-SQL Statements (SQL Azure Database)

It’s real simple to get to know how SQL Azure works by creating an account for free and playing with it. Here is how to quickly start:

1) If you have an MSDN subscription, you can create a Windows Azure account for free. To do this, login to MSDN and go to “My Account” and choose “Activate Windows Azure”. Check out the benefits. If you do not have MSDN, you can get a 1 month trial (no credit card required) or a 3 month trial (credit card required).

2) Watch the following short videos SQL Azure at a Glance, SQL Azure: Creating Your First Database, and Interacting with a SQL Azure Database. Another good resource is Introduction To SQL Azure by the Microsoft Virtual Academy.

That’s it! I spent about 30 minutes creating my account and watching those videos, and I was then able to create a server, create a database, and tinker around with creating and querying tables. There is even a wizard to help you migrate databases from SQL Server to SQL Azure.

More info:

Windows Azure Team Blog

SQLAzure – My First Cloud

SQL Azure Tools

Dipping My Toes Into SQL Azure

Dipping My Toes Into SQL Azure – Part 2 – Protection Mechanisms

SQL Azure – why use it and what makes it different from SQL Server?

Windows Azure Learning Plan – SQL Azure

SQL Azure Performance and Elasticity Guide

Video Building Scalable Database Solutions Using Microsoft SQL Azure Database Federations

Video Go Large with SQL Azure Federations

Video Developing with SQL Azure: Tools & Frameworks In Action

Video Getting Started with Cloud Business Intelligence

Video Microsoft SQL Azure Overview: Tools,Demos and Walkthroughs of Key Features

Video Using Cloud (Microsoft SQL Azure) and PowerPivot to Deliver Data and Self-Service BI at Microsoft

Video Microsoft SQL Azure Performance Considerations and Troubleshooting

Video Using Microsoft SQL Azure with On-Premises Data: Migration and Synchronization Strategies and Practices

Video SQL Azure-Design Concepts

SQL Denali T-SQL features in SQL Azure now

February 2012 PASSMN Meeting – SSRS to Address Business Cases

Fri, 10 Feb 2012 08:00:00 -0600

Sponsor

Meeting Details

Location: 3601 West 76th Street, Suite 600 Edina, MN 55437
Date: February 21, 2012
Time: 4:00 PM – 6:00 PM – Later Time than normal
Live Meeting:
- URL: https://www.livemeeting.com/cc/usergroups/join?id=R9352R&role=attend&pw=QqGKDQ7%5Bp
- Meeting ID: R9352R
- Entry Code: QqGKDQ7[p

Please click here for meeting details and to RSVP

SQL Server and SSRS to Address Business Cases

Srinivasan Prasanna, CSM – North America

Leveraging the power of SQL Server and SSRS to address our business case, CSM – North America implemented a WMS (Warehouse Management System) to address the following:

Compliant – We want to be compliant of the (Bio Terrorism Act of 2002) for food manufacturers and distributors.
Improve inventory accuracy
Improve productivity
Improve Customer satisfaction and increase our service levels.

By mining the data from SQL Server and with the use of SSRS we have streamlined our requirements and achieved some of these objectives and created reports to address the business needs. The use of SQL Server, SSRS have helped to achieve these in an efficient manner.

Executing scripts with sp_addscriptexec

Fri, 10 Feb 2012 00:42:47 -0600

Looking for ways to do things more quickly and efficiently, I thought I would talk a little bit about sp_addscriptexec and how it can be leveraged to expedite the process of executing SQL scripts in a replication topology. By creating and saving a SQL script on a publisher, it can then be distributed to and executed on UNC deployed subscribers using sp_addscriptexec.

The syntax for sp_addscriptexec per BOL

sp_addscriptexec [ @publication = ] publication
[ , [ @scriptfile = ] 'scriptfile' ]
[ , [ @skiperror = ] 'skiperror' ]
[ , [ @publisher = ] 'publisher' ]

The problem with numbers

If you’re dealing with a large number of subscribers, database administration can be tricky. Tasks such as adding logins and users, granting permissions, maintaining indexes, and managing constraints must be done individually at each node and deploying all of the scripts can be very time consuming. Rather than visit each node, sp_addscriptexec should be used to post the ad-hoc script to all subscribers in the topology, saving valuable time. Put simply – if you’re visiting each node to execute a script, you’re doing it wrong.

The benefit with using sp_addscriptexec is that the publisher can act as a centralized script repository. Scripts can be saved to the publisher and executed on demand for subscribers. This process is quicker and more efficient than copying, saving, and executing scripts directly at each subscriber. Not only does this save time, but space as well.

Executing scripts

Applying scripts to subscribers from the publisher can be done by:

Create and test the script
Save the script to the publisher
Execute sp_addscriptexec at the publisher to apply script to subscribers

From here, the script will be executed at each subscriber on the next synchronization. Taking this one step further — to limit the script to run on only certain subscribers, a check for HOST_NAME can be performed in the script.

-- Limit script to Subscriber1, Subscriber2, and Subscriber3
IF HOST_NAME() IN ('Subscriber1', 'Subscriber2', 'Subscriber3')
	-- script goes here

Something to consider

A caveat with sp_addscriptexec is that by default, if the script being executed encounters an error at the subscriber, the replication agent will fail and retry on subsequent syncs in an endless loop, be aware of this. This behavior can be overridden by passing a value of 1 for @skiperror which instructs the agent to skip errors. At the very least, SQL scripts should be thoroughly tested at the publisher before calling sp_addscriptexec to apply them to subscribers.

SQL SERVER – A Quick Puzzle on JOIN and NULL – SQL Brain Teaser

Thu, 09 Feb 2012 19:30:26 -0600

It seems that we all love to solve puzzles. On SQLAuthority Page, we have been playing the number game and those who are playing with us know how much fun we are having. Sometimes, the answers are so innovative and informative that they open up those aspects of the technology which I have not thought of.

Today, I have a very relaxing puzzle for all of you. It is based on my earlier blog post on INNER JOIN and NULL, so I suggest reading the said post first if you want to get the complete idea.

The puzzle has three parts:

Setup
Fill in the blanks – T-SQL code
Query and Resultset

We will set up the sample table first. We will run the sample query, which will give us some result. You will have to figure out what the missing script will be, which in turn will generate a specific displayed result.

USE TempDB GO -- Sample Table CREATE TABLE FirstTable (Col1 INT) CREATE TABLE SecondTable (Col1 INT) GO ALTER TABLE FirstTable ADD CONSTRAINT UX_FirstTable UNIQUE NONCLUSTERED (Col1); ALTER TABLE SecondTable ADD CONSTRAINT UX_SecondTable UNIQUE NONCLUSTERED (Col1); GO INSERT INTO FirstTable (Col1) VALUES (NULL) GO INSERT INTO SecondTable (Col1) VALUES (NULL) GO -------------------------------------- -- Missing T-SQL Statement -------------------------------------- -- SQL Query SELECT st.col1 FROM FirstTable ft LEFT JOIN SecondTable st ON st.Col1 = ft.Col1 GO -- Clean up DROP TABLE FirstTable; DROP TABLE SecondTable; GO

Current Resultset:

Expected Resultset:

Try harder. You will figure it out. If you cannot figure out and you want to give up, here is the answer.

If you do not feel like doing SQL any further, try the following image of Mars taken by Viking 1 in 1976.

Try to find the human face:

Do let me know if you like this kind of puzzle and we will have many more of these.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Filed under: Pinal Dave, PostADay, Readers Question, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology

An open letter to anyone with “SQL Server” on their resume

Thu, 09 Feb 2012 15:20:50 -0600

Dear Sir or Madam,

I have your resume here in my hand and couldn’t help but notice that you have listed several versions of SQL Server in the experience section. You may be an interviewee for a position in SQL Server, a .NET, or some other IT related discipline. Regardless of which particular job you’re going for, I would like you to commit a few things to memory regarding the above-mentioned bullet point on your resume. Take these notes as reminders in the kind and helpful manner in which I mean them.

Data Types are Important

If you have touched SQL Server enough to warrant a line on page 1 of your resume, you need to know some of the extreme basics, such as data types. Data types are the fundamental building blocks of databases, so take a few minutes to familiarize yourself with a few:

char and varchar are slightly different. char is a fixed-length string type; varchar is a variable-length string type. However many characters you declare your char field or variable to have, that’s how many it will have. Set a char(4) equal to ‘a’, and the string will actually by ‘a ‘. (Note their comparable Unicode types, nchar and nvarchar.)
There are several different date and time types, each of which have a different precision and date range.
Briefly familiarize yourself with the integer data types (part of the family of exact numerics, which also includes decimal and others).

SP_ means System Stored Procedure – Don’t use it

Don’t begin your stored procedures with sp_. In addition to the reasons listed behind the link, a stored procedure whose name begins with sp_ will cause SQL Server to look in the master database for the SP, before it looks in the current database. That’s what we call a performance hit. And certainly, don’t be like this guy.

Database Objects are Important

Again, if your work with SQL Server warrants a page 1 bullet, it’d be an awfully good idea to know of a few database objects.

Tables contain rows of data. Each column in a row has a name and a type.
Indexes make tables more quickly searchable (for reads). Indexes come at a cost to writes (inserts and updates).
Stored Procedures are saved in the database. They can accept input parameters, contain one or more statments, and return a value. Stored procedures can be called manually, or from applications or scheduled jobs.
Triggers are similar to stored procedures, but they are event-driven: they are defined to execute when a specific event (such as an insert on a table) occurs.

Know What you Know

ORMs are not “faster” than SQL Server. Oracle is not “better” than SQL Server (or, necessarily, worse). SQL Server 2005 didn’t make DTS “better” than SQL Server 2000. There is no SQL Server 2003. There is no SQL Server 2003. There is no SQL Server 2003. There is no SQL Server 2003. There is no SQL Server 2003. There is no SQL Server 2003. There is no SQL Server 2003.

There is no SQL Server 2003. Know what you know, and don’t claim to know more.

If you didn’t know most of the above, then you might think about removing SQL Server from your list of skills.

Helpfully yours,
Jen McCown
MidnightDBA.com/Jen

P.S. To the folks I have interviewed thus far in 2012: None of this is actually directed at you, but you did inspire me (due to remembered interviews). Good job, seriously.

Is Your Data Being Collected?

Thu, 09 Feb 2012 14:44:40 -0600

For those of you who have read my posts then you will know I am very big on Utility Databases. I like them because there is a plethora of information that can be gathered that you can trend, baseline, troubleshoot, etc.

In browsing some forums and whatnot I came across a good article regarding the data collector within SQL. I decided in some late night reading to study up on it; I found it to be something that in our line of business pretty beneficial. This component of SQL 2008 collects different data sets. I like it for many reasons; one perk that stood out to me was configuring the different types of monitors on a scheduled bases. You may ask what is the difference between this and the Utility Databases I always speak of. I am glad you asked; my Utility Databases are custom fitting to me to meet my exact needs; this data collector component comes out of the box you just have to configure it.

I’ve started utilizing both; configuring the data collector takes a matter of minutes and you can set schedules and retention periods of how long you want to keep your data. I basically set up an empty database shell; then the configuration takes care of creating the necessary stored procedures and schema to support it. Another feature that I liked are the 3 reports that come with it out of the box – disk usage, query stats history, and server stats history. Granted all these can be gathered via scripts but why not take advantage of something already built for you.

This link by Brad McGehee provides information on how to set up and configure the Data Collector.

This link on the MSDN site will provide a basic understanding of what the Data Collector does.

After research I found that this resides best on a CMS Server if you have one. If you are unfamiliar with the CMS Server check out John Sterret’s blog. I sat on one of his sessions at PASS and really enjoyed it.

If you aren’t collecting data your missing out; if you are collecting data and aren’t using the two methodologies take some time and review them; it might just meet your needs.

February #Meme15 Assignment

Wed, 08 Feb 2012 19:35:00 -0600

It’s been a couple months now since a number of us got together to share how we use social networks to enhance our careers and professional development. These blog posts will be called #meme15. For more on what people talked about talked about in December and January round-up posts.

Before we get to the assignment for this month, let’s go over a few guidelines for this:

Answer the question(s) with as much or as little detail as you wish. Try to stay true to the heart of the question, but answering where it connects to you.
Watch for the new assignment about 8th, or the Monday after the 8th if it falls on a weekend.
Post your answer on the 15th, or the Monday after the 15th if it falls on a weekend.
Include the #meme15 logo your post (courtesy of Matt Velic (Blog | @mvelic))
Comment on the assignment blog post, which seems more reliable, or trackback to it.

#Meme15 Assignment #3

The third writing assignment for #meme15 will be on Linkedin. The post should answer the following:

Why and how you use LinkedIn?

You’ve got a week, happy writing.

Other SQL Community Memes

If you aren’t already participating, there are already a few going on in the community #TSQLTuesday, #MemeMonday, and Un-SQL Friday. Watch for those for opportunities to blog without having to come up with your own topic. Beyond having a easy topic choice, they also provide a window for more people to see what you have to say.

DAX Measures, MDX Measures and Type

Wed, 08 Feb 2012 15:39:15 -0600

One of the strengths of MDX is the fact that calculated measures in MDX are not strongly typed: they return variants. This means that it’s possible to create calculations that return values of several different types, such as the example below that returns text in some cases and integers in others:

WITH
MEMBER MEASURES.DEMO AS
IIF(
[Measures].[Internet Sales Amount]>7000000
, "TOO HIGH!"
, [Measures].[Internet Sales Amount])

SELECT
{[Measures].[Internet Sales Amount], MEASURES.DEMO}
ON 0,
[Date].[Calendar Year].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]

This gives you a lot of flexibility when writing calculations but it also can be a big problem when you want to extract data from a cube into another system, as anyone who has tried to do this will know.

In DAX and the Tabular model, on the other hand measures, like columns (calculated or otherwise) are strongly typed. Although you can’t see the type of a measure in SQL Server Data Tools when you create it, the automatically inferred type can be found in the MDSCHEMA_MEASURES schema rowset and which be queried as follows:

select *
from $system.mdschema_measures

If you try to use an expression for a measure like this that, as in my first example, could return text or an integer:

testtype:=IF(SUM(‘Internet Sales’[Sales Amount Value])>1000, 1, "test")

Then you get the error:

Measure ‘Internet Sales’[testtype] : The second and third arguments of function IF have different data types. This is not supported.

On balance I think I prefer having measures strongly typed, and for one thing it opens up the possibility of using the Tabular model for certain forms of ETL. I’ve already seen one customer of mine replace a CTE in TSQL by loading their data into Tabular and using the PATH functions instead, getting some significant performance benefits as a result, and I’m sure there will be plenty of other scenarios where ETL requires complex calculations to take place that the incredible performance of DAX will make loading all the data into a Tabular model (even if the final destination of the data is a relational data warehouse) a serious option.

What is Microsoft Connect?

Wed, 08 Feb 2012 10:00:20 -0600

Microsoft Connect (blog, twitter) is a site where you can post feedback to improve the quality of many Microsoft products and impact the direction of those products. As of this writing, the site accepts bugs for 51 products and suggestions for 34 products, all from the community. You will need a Microsoft Live ID to submit a bug or suggestion.

The site has a nice dashboard where you can view feedback you have selected to watch, feedback you have submitted, and feedback you have participated in. Each line in the dashboard shows the number of comments, the number of workarounds, and the status.

On many occasions I have run into a bug with SQL Server, and have used connect to see if someone else has notified Microsoft of the bug. And many times Microsoft or another community member will have posted a work around. I have also posted suggestions for SQL Server, along with many others, and have seen those suggestions implemented. So if you find a bug, or have an idea to improve a Microsoft product, check out Microsoft Connect.

More info:

Use Microsoft Connect to Drive Product Improvement

Posting An Issue To Microsoft Connect

Going To TechEd? See You There!

Wed, 08 Feb 2012 10:00:00 -0600

If you are planning on attending TechEd this year then you will have a chance to see myself and Denny Cherry (blog | @mrdenny) deliver a precon seminar. The title is “Microsoft SQL Server Performance Tuning and Optimization” and we are very excited to have this opportunity. And apparently Microsoft is excited to have us as well, because they are also sending the two of us to Amsterdam to do a repeat performance two weeks later for TechEd Europe.

Denny and I have been working on the precon now for a couple of weeks. It’s tough since we both have full time jobs and live on opposite ends of the country, but we manage to meet at least once a week over Skype to discuss the session. Here are just a few of the performance tuning items we are planning to discuss:

Table Partitioning
Filtered Indexes
Columnstore

In addition to those items, we are also going to discuss how you can implement AlwaysOn read only replicas to help scale your system for performance as well as walk you through some general troubleshooting techniques with regarding to execution plans.

The last registration update had our precon in the lead for attendees, in both countries. We’d like to keep that trend going.

Go and get yourself registered for Orlando, Amsterdam, or both!

Going To TechEd? See You There! is a post from: SQLRockstar | Thomas LaRock

Join Denny Cherry (@mrdenny) and me for two days of SQL instruction, training, and wine tasting in the California sunshine this May for $799.

Not enough storage is available to complete this operation

Wed, 08 Feb 2012 09:27:40 -0600

This message appears in the TDP SQL dsmsched file. It’s usually associated with low levels of contiguous memory

BackupVirtualDeviceSet::SetBufferParms: Request large buffers failure on backup device 'TDPSQL-00001BDC-0000'. Operating system error 0x8007000e(Not enough storage is available to complete this operation.). A nonrecoverable I/O error occurred on file "TDPSQL-00001BDC-0000:" 995(The I/O operation has been aborted because of either a thread exit or an application request.). BACKUP DATABASE is terminating abnormally.

Notes

1) Likely SQL Server has run out of large memory blocks to allocate to the backup/restore process

2) Monitor the memory usage pattern over time, with and without SQL Backup running. The numbers to look out for are the 'free' memory values

3) When a SQL Server backup fails due to the lack of a large enough contiguous block of free memory, SQL Backup automatically retries the backup using smaller MAXTRANSFERSIZE values, until it hits the minimum of 65536 bytes.

4) Immediate but drastic action may include restart of the SQL Server Instance. Proceed with caution and double check there aren’t open transactions .

SQL SERVER – Convert Subquery to CTE – SQL in Sixty Seconds #001 – Video

Tue, 07 Feb 2012 19:30:58 -0600

SQL Server is ocean of information.. I believe if one starts learning today after 60 years he/she may be still learning the subject (there are always few exceptions)! Recently I published SQL Server Questions and Answers video tutorial, since the course is out, I have been receiving lots of request to share SQL Tips which are small and easy to digest.

While writing SQL books with my co-authors Vinod Kumar and Rick Morelan we often come across very interesting and useful tips which we believe will be helpful to readers. Sometimes the tips are so small that we cannot find appropriate place for it in book. We call them Sixty Seconds Tips. The tips are so small that sometimes sixty seconds are too big for it. Keeping the notion of quick and easy tips – we have decided to come up with new series of SQL in Sixty Seconds. Every week we will post one small but useful video tip. We believe that Sixty Seconds is metaphor for quick and easy tip.

I have seen many developers staying away from CTE (Common Table Expression), their excuse is that they do not know how to convert subquery to CTE or they think it is very complicated to do so. In very first episode of SQL in Sixty Seconds we will see how easily one can convert Subquery to CTE and CTE to Subquery.

More on CTE:
Simple Example of Recursive CTE
Multiple CTE in One SELECT Statement Query
Common Table Expression (CTE) and Few Observation
Delete Duplicate Rows
Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop
T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table

I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Filed under: Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Video

What is the best Disaster Recovery Plan (DRP)?

Tue, 07 Feb 2012 17:47:42 -0600

I am really surprised at how often that I see questions like this. I have been answering a few questions on a few different forums and I see this more and more often. Here is the quick answer: I don’t know. There really is no way to find the book answer to your question. Disaster Recovery Plans (DRP) can be very deep or very cheap. I would hope that most people here have a basic DRP in one fashion or another. This doesn’t mean that it will work, though. Heck, it doesn’t even mean that it will cover your database other than one or two specific disasters. See, I think that a lot of people don’t realize that even RAID 5 can be considered part of your DRP. Let think about this: A disaster is anything that could happen to your server that would cause it to be disrupted. So with looking at this simple definition we can consider anything from a bad hard drive or a power supply going bad to an F-5 tornado ripping through your datacenter to be a disaster. Fair enough? Is the impact to end user of the database any more or any less depending on the cause of the outage? End result is the database is down or it is not down.

When you consider that something as simple as a power supply going bad can be a disaster for your database, your perspective may change. Have you done anything to protect yourself from a bad hard drive or a bad power supply? Consider the server class of the machine that your SQL Server is on; do you have multiple power supplies; do you have your data on some storage that will protect you from a single hard drive going bad? If yes then you have at least started on the right foot. There are levels of disasters. Some are small and are easy to recover from, others are all out life changing events. Yet when I see this question as often as I do I think we may have lost a bit of common sense. If you are being asked to implement a Disaster Recovery Plan then you should have some sort of requirements around it. More often than not, you didn’t get any requirements. What you got was a task and the people and or companies that rely on you expect that you are going to make the right decisions, to help them out of a sticky situation. To have a effective DRP, you need to be able to understand what kind of disasters you are being asked to recover the database from so you can make a plan.

Your company needs to define Disaster.

Let’s look at an example. Let’s say that your company contracts with a disaster recovery company. This hypothetical company (we will call it DR Vendor) charges your company to be “on-call”, but they provide a location that is different from your datacenter that is ready to be turned up with your data in just a few hours. The only thing that DR Vendor requires is that you show up at the door with an authorized person and you have a backup in your hand. Sounds pretty simple doesn’t it? But what happens if you have a internet connection go offline? One would think that this could be really bad for a database that is serving up data to the internet. The website would be down, but is it worth turning up DR Vendor, for something that may be fixed in a short period of time? But then again, how much time is considered a short period of time? A small web store that does a few hundred thousand a year in sales may not have the same finical impact that a huge website like Amazon does if they were offline for an hour.

If your company does not know how to define a disaster than it is your job to ask them. Present them with questions that will get you the information that you need. How long can you be down; how much data can you lose; what is considered down (is users can read, but not write to the database is that considered down)? Then there is the most important question, what is the budget for your DRP?

So I present you with the question that we started with: What is the best Disaster Recovery Plan? I don’t know – you tell me, what do you require? Once you have the requirements you can now start to design the best plan for the disaster. Once you have that documented, the rest becomes a matter of research to understand the potential solutions. A recovery plan can be something as simple as a backup or as complex as a hot standby site. Common answers to most of the previous questions sound like, “we can never lose data” or “99.999 % uptime is the requirement”, or “we need to be able to bring our site up in seconds if natural disaster destroys our data center”. But often those answers are slightly altered when the costs are reviewed. This brings me to the next discussion point.

Does it meet common business logic?

Some databases require strict uptime or zero data loss requirements. Think of the implications of a bank losing 15 minutes worth of data. Are these the same requirements that a local convince store has? I was sitting a local coffee store the other day and they were helping one customer about every 3 or 4 min. Let’s say the average cup of joe is $4.00, so if there was an endless line of steady customers they would help about 20 people an hour, at $4.00 a coffee, they are looking at losing about $80.00 an hour, and that is if they could not serve any coffee at all. (This crosses the line into business continuation planning.) If they were to try to protect the business from 3 hours worth of down time we are talking about $240.00 worth of sales. Sure they may upset some customers to the point they never come back, or they may miss the one customer that hour that is ordering for everyone in the office, but in general numbers, what is the potential loss to the potential cost? However, what if that coffee shop is a national chain, and the corporate office goes offline and this caused everyone to stop taking orders for 3 hours? At 1,000 stores we are talking almost $250,000.00. Make sure the loss that you want to protect is logical to the business.

At one point in time I was working with a client who was talking to me about another consultant who recommended a DRP. The total cost was going to be close to $100,000. Well, depending on the company or organization that can be pretty affordable. But, the more I thought about it, I started to have questions. When he told me the requirements were that his 2 gig database could be offline for up to a week without impacting revenue, all the sudden this $100,000 did not meet the business logic rule. The database requirements were light, and so light that a laptop could host the database. This problem was solved by a simple backup schedule with tests that proved the database could be restored.

SQL Server offers a variety of options to recover your data, or to make another copy of your data somewhere. These options come with different costs, not just financial in nature, and some of these costs are linked directly to performance. The more information that you can supply or the more information you can get as a DBA, the better off you are going to be when you are faced with that disaster.

SQLPass Business Intelligence Virtual Chapter References and Slides

Tue, 07 Feb 2012 14:41:00 -0600

Thank you to everyone who attended my SQLPass Business Intelligence Virtual Chapter webinar! I have lots of questions to follow up, from both during and after the event. Please watch this space for more responses to questions over the next few days, and I will try to ensure that I answer every question. The slides are at the bottom of this blog post.

I've provided references below to some of the Data Visualisation people and material that I mentioned today. I hope that these are useful to you.

Ben Schneiderman - Ben's Eight Golden Rules of Interface Design is an interesting read. In the presentation, I refer to one of Schneiderman's essays, "The Eyes have it." The guideline is summarise -> zoom and filter -> details-on-demand, and is known as the 'Visual Information-Seeking Mantra'.

Stephen Few - Stephen has written a number of books, which you can find at his site - there is a wealth of information there! If you're a fan of data visualisation, then you should consider his blog, Perceptual Edge, a must-read. In particular, I'd like to shout out the following books:

Now You See It: Simple Visualization Techniques for Quantitative Analysis
Information Dashboard Design

Malcolm Gladwell - Malcolm has a great deal of interesting things to say on a whole range of topics, and I'm never going to be able to introduce him properly.

Hans Rosling - What I love about Rosling's work, is that he highlights health and social factors, poverty and health. This is data visualisation in action, to help people in poor circumstances - the sort of people who cannot always read what is written about them. He promotes a 'fact-based' world view on these issues. The YouTube video of Rosling's data visualisation is below, which was made by BBC4.

I should add that I'm not in any way associated with this video - but I get asked about it a lot, so I've embedded it here from YouTube.

Here are the slides from today. I look forward to your comments, and I will answer the remaining questions in due course.

-J

Sql pass business intelligence chapter bi and data viz in sql server 2012

View more presentations from Jennifer Stirrup.

The Minimalist Guide to Database Administration

Tue, 07 Feb 2012 12:00:00 -0600

Your office should look like this.

Every great DBA I know is both driven AND lazy. They are driven to be as efficient as possible, so they can do less work. It’s a wonderful pair of personality traits to have as a DBA.

The unfortunate part is that the more driven they are, the more efficient and lazier they get, which often results in their boss looking at them and saying “why am I paying you to just sit around?”. This results in many DBAs being dragged into administering other applications (like Sharepoint) because, hey, they run on databases too, right? With you were sitting there looking like you weren’t doing anything it just made sense to give you extra work.

I have listed seven items below that I believe are universal for anyone that has the letters “DBA” in their job description. Some DBAs need to be a part-time SAN admin, or VM admin, or know all about security, or Active Directory, or .NET. It differs from one shop to another. Whether it is day one or one hundred in your career as a DBA you need a guide that helps you stay focused on your core duties. That’s what I have created here. It is the Minimalist Guide to Database Administration, and my goal is to make sure that no matter what your level of efficiency and laziness you are able to focus on the bare essentials (the minimum) necessary to be a rockstar DBA.

1. Backups

It’s simple, really: get them done.

There really isn’t a lot to say here, but as a DBA your number one job is to ensure that you can recover data. You cannot recover data without having configured your database backups to happen. And while it is surprisingly easy to configure database backups and yet a shocking number of people never bother. You can use custom scripts or maintenance plans to get the job done, just make sure you are getting them done. Grant Fritchey has an excellent article over at Simple-Talk that I would recommend you read.

In order to keep with the minimalist theme I am going to tell you to use backup compression (depends on your version). You can enable this at the instance level or as an option included within the backup command. This will help keep disk space to a minimum. Also, you need to talk with your business users to find out how often you need to do full backups, differential, and transaction log backups as well. My default choices were weekly full backups, nightly differential backups, and hourly transaction log backups. This was a good place to start and I could adjust as needed.

2. Only Be Alerted When Necessary

I’ve never been one to want to be alerted just for the sake of being alerted. It makes no sense to me to be paged at 11PM just to know that something successfully happened. I expect success, and would prefer to only be told about when something has failed. As far as failures go, I only want to be told about them when it is something for me to fix.

When I only had a handful of servers under my care I had alerts for all sorts of things. I was even alerted to when a session had been blocked for more than five minutes. Sounds good, right? Sure, except then I would call the end user to inform them of the situation and ask them what they wanted me to do and I would usually get told “I dunno”. Well, OK then. If you don’t want, or know, what action I should take when you have such blocking, then why would I continue to want to alert myself about this activity?

I wouldn’t. Neither should you. Only be alerted to the things you can take action upon immediately. Knowing your server CPU utilization is at 80% might sound like a nice thing, but tell me exactly what actions do you plan to take as a result? Now…tell me what actions you will take if you had over a dozen servers at the same time reporting more than 80% CPU utilization?

At some point your reach is greater than all your available hands. Split out your alerts into those that are informational versus those that are actionable. If they are not actionable then at some point you will simply turn a deaf ear to all alerts. That is not going to be good for anyone.

3. Automate

If you haven’t been automating any of your processes, get started. As you get more and more servers shoved your way you will find that automation will be your BFF. In my case I dove into Systems Center Operations Manager (OpsMgr) as a way to automate a lot of the things I needed done. Once OpsMgr recognized that an instance of SQL was running on a server it would simply execute all the scripts I needed done for me. I could use OpsMgr for just about anything I dreamed up, including having it alert me if I had not taken a backup recently.

There are many other ways to automate your systems, including the use of Powershell, Central Management Servers and policy-based management, and even old-school SQLCMD. Use whatever works best for you, but use something and get your stuff automated. Don’t wait until you have too many servers to manage before you start automating your processes. If you have more than one server under your care you should be automating things right now.

4. A DBA Has Got to Have Standards

You want to have a standard build and configuration for your database servers. It makes troubleshooting much easier when you know that each server is essentially similar to all the others. The fewer of those servers classified as “one offs”, the better it is for you and ultimately for your end users that need you to solve problems quickly.

Some of the things you want to have standard would include (but not limited to) the following:

data and logs on separate disks
authentication mode (pick one and use it everywhere, consistency is key)
version level/patching
naming standards

Those are just a few examples to get started. Your shop may focus in some particular area and you will probably want to have standards specific to that need. Whatever the standards you want, just get them written down and agreed upon and then make sure they are adhered to at all times. This is where automation comes in to help as well, you can build a set of scripts/policies to quickly check to see if any particular instance is not adhering to a defined standard.

5. Only Install What You Need

Another way to reduce your surface area of administration and troubleshooting is to only install the minimum services needed for your database servers. This has an added benefit of also

Get used to these screens.

reducing the surface area for an attacker, something your security folks would appreciate. A great way to get this done for SQL 2012 is to utilize Server Core.

What Server Core does for you is it allows for you to have a “low-maintenance” environment. What that essentially means is that your server isn’t running any unnecessary software, including a GUI for you to use. That’s right, when you go Core you end up doing everything from a command line. It’s as if Microsoft looked at everyone and said “You like the Linux? Fine, here you go, enjoy!”

As a DBA (for any platform), this makes perfect sense for you as it reduces the number of things you need to be an expert in and examine while troubleshooting. Don’t install unnecessary services and you won’t have to worry about them interfering with your database engine.

6. Restores

I told you that your number one job is to recover data, right? And you are doing your backups, right? OK, quick question for you: how do you know you can restore those backups? Is there a way for you to verify that your backup can be restored?

Yes, there is, it is called a restore.

That’s right. The only way to truly know if your backup can be restored is to actually perform a restore. This may not be practical for you, doing restores of thousands of databases each day in order to verify that they are valid. That’s why I wrote a post on how to get the job done in an efficient, minimalist style. Turns out you may not need to do all of them after all, but you certainly do need to be testing some of them.

7. Moment of zen

Any time you hear about “minimalist” it is usually accompanied with the word “zen”. There’s something to be said for that with regards to being a DBA.

Don’t panic.

That’s right, stay calm. Breathe deep. You don’t know everything. You will constantly be learning on the job. Never panic, no matter what. When everyone about you is losing their composure they will need someone to be a steady influence. Let that be you.

In your career as a DBA you are going to be subjected to a lot of stress by others. Don’t let their negative energy become your burden.

It will serve you well to remember that life is an experience, and there is much for everyone to learn.

Also, stabbing people with a paper clip in the neck just annoys them more.

Those are my seven bare essentials for any DBA, my “minimalist” guide. What would be in your guide? What tips and tricks have you learned to help yourself scale as a DBA, to be able to take on more and more responsibilities as you get more and more efficient? Start writing down your own guide and compare your list to mine. Did I capture the essentials? Did I include something that shouldn’t be there? Leave a comment.

The Minimalist Guide to Database Administration is a post from: SQLRockstar | Thomas LaRock

Join Denny Cherry (@mrdenny) and me for two days of SQL instruction, training, and wine tasting in the California sunshine this May for $799.

Send email from Powershell with attachment

Tue, 07 Feb 2012 09:10:07 -0600

This post describes how to call to add an email attachment using Powershell.

In an earlier post - SQL Server – Send email using Powershell – the new-object Net.Mail.SmtpClient() Class was used.

To add an email attachment , use New-Object System.Net.Mail.MailMessage and New-Object System.Net.Mail.Attachment, which are part of the .Net Framework

The example script includes creating the variables, the function and calling the function .

$emailFrom = "jvamvas@sqlserver-dba.com"
$emailTo = "jvamvas@sqlserver-dba.com"
$subject = "SQLServer-DBA.com: Powershell Function calling an SMTP server with attachment"
$body = "SQLServer-DBA.com : Send an email through SMTP in Powershell with attachment "
$smtpServer = "mysmtpserver"
$filePath = "C:\sql_server_health_sqlserver_jobs_2012-02.html"


Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$body,[string]$smtpServer,[string]$filePath)
{
#initate message
$email = New-Object System.Net.Mail.MailMessage 
$email.From = $emailFrom
$email.To.Add($emailTo)
$email.Subject = $subject
$email.Body = $body
# initiate email attachment 
$emailAttach = New-Object System.Net.Mail.Attachment $filePath
$email.Attachments.Add($emailAttach) 
#initiate sending email 
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($email)
}

#Call Function 
sendEmail $emailFrom $emailTo $subject $body $smtpServer $filePath

Arithmetic Overflow in sp_MSarticle_validation

Mon, 06 Feb 2012 23:49:31 -0600

This is probably a rare occurrence — but I've noticed that data validation for an article with 10 billion or more rows will fail due to an arithmetic overflow error in sp_MSarticle_validation.

Performing data validation for a specific article in a transactional publication is done using sp_article_validation which in turn calls sp_MSarticle_validation. In the definition of sp_MSarticle_validation, a local variable named @actual_rowcount is defined which is of the type bigint. Later on in sp_MSarticle_validation, a command is built to execute sp_table_validation and the @expected_rowcount parameter passed in is derived by converting @actual_rowcount to a varchar(10).

The offending statement can be observed in the following code snippet from sp_MSarticle_validation:

select @command = 'exec dbo.sp_table_validation @table = ''' + replace(@destination_table, '''', '''''')  + ''', @expected_rowcount = ' +
	        convert(varchar(10), @actual_rowcount) + ', @expected_checksum = ' +
	        convert(varchar(100), @actual_checksum) + ', @rowcount_only = ' + convert(varchar(5),@rowcount_only) +
	        ', @full_or_fast = ' + convert(varchar(10), @full_or_fast) +
	        ', @shutdown_agent = ' + convert(varchar(10), @shutdown_agent)

Considering @actual_rowcount is a bigint, it should be converted to a varchar(19), rather than a varchar(10). This is where an arithmetic overflow error occurs when validating an article that has 10 billion or more rows, causing validation to fail.

If you find yourself needing to validate an article with 10 billion or more rows, please vote this item as important to expedite a fix.

February 2012 – Monthly SQL Server Checklist

Mon, 06 Feb 2012 22:00:00 -0600

It’s the first Monday of February, mostly, it’s a short month, but there’s no reason to skirt on checking out your SQL Servers.

If you made a resolution to check up on your servers for new years, are you keeping up? Did you find the time in January? If not, make the time today and find out the state of the state for your environment.

As has been said before, the premise of these posts is to help DBAs maintain their environments by getting together a monthly checklist for their environments. Followed by going through and evaluating the SQL Server environment. There aren’t many any changes to this month’s list, there haven’t been any CUs or SPs since last year.

Monthly Checklist

Backup Validation: Check everything involved in the backup process. Are your backups executing as desired? Are the monitoring jobs properly alerting to failures? Have their been any unexpected failures? Have backup duration times changed?
Recovery Validation: Is everything for your recovery collected and being backed up? Have you practiced restoring at least one of your SQL Server databases from production in the last month?
SQL Server Updates: Is your SQL Server environment up-to-date? Check each of your instances and review the most recent releases of SQL Server. Make a plan to determine when the most recent updates will be applied. Also, be aware that support for SQL Server releases do end at some point. Support for the version of SQL Server that you are currently using may no longer be supported.
- SQL Server 2012 RC0
- SQL Server 2008 R2 SP 1 CU3
- SQL Server 2008 R2 SP 1
- SQL Server 2008 R2 CU 10 – Support ends 7/10/2012
- SQL Server 2008 SP 3 CU 2
- SQL Server 2008 SP 3
- SQL Server 2008 SP 2 CU 7- Support ends 10/9/2012
- SQL Server 2008 SP 2 – Support ends 10/9/2012
- SQL Server 2008 SP 1 CU 16 – Support ends 10/11/2011
- SQL Server 2008 SP 1 – Support ends 10/11/2011
- SQL Server 2008 CU 10 – Support ended 4/13/2010
- SQL Server 2005 SP 4 CU 3 – Mainstream support ended 4/12/2011
- SQL Server 2005 SP 4 – Mainstream support ended 4/12/2011
Server Health: Check the performance statistics for your server(s). Were there any unexpected items in your event log? How does everything compare to the last baseline?
Database Health:Check the performance statistics for your database(s). Were there any unexpected items in your SQL Server logs?
1. Check wait stats.
2. Analyze your indexes (via IndexAnalysis)
Check Baselines: Are there any variances on the performance counters off of the baseline? Is the baseline still valid?
Validate Capacity Plan: If you have a capacity plan in place for your environment, check to see that what you had planned for June matches the actuals. Any threshold violations that may require adding capacity?
Status Report: What do you need to get done before next month? What did you get done this month? After the other tasks, write this all down and send it to your manager.

Something Missing?

Is there something missing in this list that you think should be included? Leave a comment and I’ll add it in for next month. I’ll follow-up next month on the first Monday of the month and we’ll see how everyone that reads this is doing.

Workaround for “Could not update a list of fields for the query” error in SSRS

Mon, 06 Feb 2012 19:29:16 -0600

Summary: A quick workaround for an intermittent SQL Server Reporting Services error.

Level: 101

Reporting Services Error

The error I received is:

Could not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct.

I received this error sometimes after updating the dataset (with the preview of the query running fine), at the point when I clicked OK to save the dataset changes. In my situation the data source was an Analysis Services cube (SQL Server 2008 R2).

Workaround

Since I started using this method, I haven’t seen the error.

1. Go to Dataset Properties. (Specifically…do not jump directly to the Query.)

2. Select the Query Designer button. (Yes, it’s an extra click, I know.)

3. Update your query. Click OK within the Query Designer.

4. Click the Refresh Fields button, and then OK.

That’s it. I haven’t had any trouble since I started accessing and saving the query “the long way” and now it’s just habit.

Recent Entries Feed | SQLServerPedia Blog