Using CROSS APPLY instead of a calculated variable

Wed, 22 May 2013 06:54:17 -0500

Earlier this week I posted The many uses of CROSS APPLY and I’m quite glad I did. I’ve been working on a series of audit queries for the last couple of weeks and got thrown a bit of a curve today. The original specs included several comparisons against a date. Basically the following.

WHERE (date1 >= '5/31/2008' AND date2 >= '5/31/2008')
	OR (date3 >= '5/31/2008' AND date4 >= '5/31/2008')

I was a little shocked when I was told several weeks into the project “Next month it will change to June 30 2008.” So in reality rather than a constant (used no less than 7 times throughout each of 13 views) I now needed a calculation.

Not really a huge issue. It’s a simple enough calculation and it would be simple enough to just copy and paste it everywhere it’s needed. I just really don’t want the repetitive code. Not to mention adding that much more complexity to the views. And I shudder to think of having to change it in over 90 different places if the business specs change. So the next thought I had was using a variable, unfortunately these have to be views, although I guess I could have used TVFs (table valued functions) but still not the best solution.

Now recently I watched Kendra Little’s 5 T-SQL Features You’re Missing Out On and learned that you could use CROSS APPLY for reusable calculations. Well that’s what this is right? A reusable calculation.

So now instead of looking like this: (The calculation is from one of Pinal Dave’s blogs, and the calculation is for September 2003. If you are reading this past May 2013 that will have changed.)

SELECT *
FROM Sales.SalesOrderHeader
WHERE (DueDate >= DATEADD(mm, DATEDIFF(mm,0,GETDATE())-116,0) 
			AND DueDate < DATEADD(mm, DATEDIFF(mm,0,GETDATE())-115,0))
   OR (ShipDate >= DATEADD(mm, DATEDIFF(mm,0,GETDATE())-116,0) 
			AND ShipDate < DATEADD(mm, DATEDIFF(mm,0,GETDATE())-115,0))

It now instead looks like this.

SELECT *
FROM Sales.SalesOrderHeader
CROSS APPLY (SELECT DATEADD(mm, DATEDIFF(mm,0,GETDATE())-116,0) AS StartDate, 
			DATEADD(mm, DATEDIFF(mm,0,GETDATE())-115,0) AS EndDate) AS Vars
WHERE (DueDate >= Vars.StartDate AND DueDate < Vars.EndDate)
   OR (ShipDate >= Vars.StartDate AND ShipDate < Vars.EndDate

Personally I find this much easier to read. Of course if the performance isn’t there then it doesn’t really matter. Fortunately or unfortunately depending on how you look at it, all of my tests so far have been inconclusive. The query plans are very slightly different on some queries and the same on others. The run times are only slightly different and neither one runs faster every time. Until I see something conclusive one way or the other I’ll consider performance close enough with a big edge on readability and maintainability going to the CROSS APPLY.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, CROSS APPLY, language sql, microsoft sql server, sql statements, T-SQL

Cumulative Update – 11 for SQL Server 2008 SP3 Is Now Available !

Wed, 22 May 2013 05:29:33 -0500

The 11th cumulative update release for SQL Server 2008 Service Pack 3 is now available. Cumulative Update 11 contains all the hotfixes released since the initial release of SQL Server 2008 SP3.

Those who are facing severe issues with their environment, they can plan to test CU11 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP4 final release to deploy on your production environment, to have consolidate build.

KB Article For CU11 of SQL Server 2008 SP3

§ CU#11 KB Article: http://support.microsoft.com/kb/2834048

Previous Cumulative Update KB Articles of SQL Server 2008 SP3:

§ CU#10 KB Article: http://support.microsoft.com/kb/2814783

§ CU#9 KB Article: http://support.microsoft.com/kb/2799883

§ CU#8 KB Article: http://support.microsoft.com/kb/2771833

§ CU#7 KB Article: http://support.microsoft.com/kb/2738350

§ CU#6 KB Article: http://support.microsoft.com/kb/2715953

§ CU#5 KB Article: http://support.microsoft.com/kb/2696626

§ CU#4 KB Article: http://support.microsoft.com/kb/2673383

§ CU#3 KB Article: http://support.microsoft.com/kb/2648098

§ CU#2 KB Article: http://support.microsoft.com/kb/2633143

§ CU#1 KB Article: http://support.microsoft.com/kb/2617146

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

MongoDB 2.4 Feature Demo and Q&A on Geo Capabilities

Wed, 22 May 2013 05:20:56 -0500

Watch Webinar on Geo Capabilities of MongoDB

Connecting a service between Publishing and Consuming SharePoint 2010 farm

Wed, 22 May 2013 03:11:23 -0500

Hello Friends,

As discussed in my previous blog the new architecture of Service Application in 2010. Let’s today discuss about connecting two different farms:

Connecting a service between Publishing and Consuming SharePoint farm

There are some service applications which have the capability to Publish and which can be used by different SharePoint farms. What that actually means:

We all know Search and crawl is the most intensive task in SharePoint. If a company has 2-3 SharePoint farms and every farms runs its own search/index application. It will be very intensive and time consuming task. Instead of running index in every SharePoint Farm:

We can just do the index in one farm and use that publishing service in different farm. It will save lot of resources and will be very less intensive task.

So what to do next:

As we have other farm where some services are hosted locally as that cannot be published or consumed. So they reside in the same farm and we can consume the search application from the other farm. Below are the steps how to consume the publishing service from other farm:

Note: To publish or consume the service applications, two farms should be in a far trust.

Once the farm-trust is configured, below are the steps:

Publishing a Service Application

a) Go to a publishing server whose service you want to publish and be used in other farms, open Central Administration.

b) Go to Application Management and then move to è Manage service applications.

c) Click to the right of the service application which you want to publish, clicking on application will open it features be sure to click on its right.

d) On the SharePoint Ribbon, click on Publish. Feature.

e) On the next page check the option “Publish this Service Application to other farms” .

f) Now we have to copy the whole url: It will begin with urn: and ends with .svc.

ex: urn:schemas-microsoft-com:sharepoint:service:ac51d6g73agg
96z8a6d89dg45fk972c243dbec93f0c7#authority=urn:uuid:da764
dko856wgbvfk86542564757&authority=https://xyz:2500/Topolo
gy/topology.svc

g) Make it ok and proceed.

h) Again do the same step as step C.

i) On the SharePoint Ribbon, click on Publish. Feature.

j) Provide farm Id of the consuming farm, you can find as: Get-SPFarm | Select Id

k) Add the same.

l) Click the farm id you entered, for the permission.

Now the part of publishing farm is over, we have to enter the url in the consuming farm to use the service:

a) Go to a Consuming server where publishing service needs to be used, open Central Administration.

b) Go to Application Management and then move to manage service applications.

c) On the SharePoint Ribbon, click on Connect.

d) Pass the URL for the service application you have taken in step (f) of publishing server and press OK.

e) Highlight the service application by clicking on it.

f) Here you have an option to can choose, whether or not to include this service application in the default service application group. Click OK once done.

Now when all the stuff is over you can use the service application of a farm just as a locally hosted service application of your farm.

Hope I was able to describe the content correctly based on my knowledge and learning.

If you liked this post, do like on Facebook at: http://www.facebook.com/Ashishsharepointblog
Feel free to Rate and provide feedback if you find post useful

Hope this help
Ashi

Filed under: SharePoint2010, SQLServerPedia

Resolving Error Connecting Report Builder to a PowerPivot Data Source

Tue, 21 May 2013 19:08:41 -0500

Recently I saw the following error while working with Report Builder in SharePoint 2013:

“An error occurred while connecting to the data source. Only the text-based query designer will be available. The selected data extension DAX is not installed or cannot be loaded. Verify that the selected data extension is installed on the client for local reports and on the report server for published reports.”

Huh? I had tested the connection when I set it up, and knew it worked. But no dice when using it for Report Builder. Interestingly, my report executed but had empty results.

It turns out I had a connection with the wrong data source type. Below is info re: setting up a connection in SharePoint for use with Report Builder.

Creating a Data Source for Report Builder Reports in SharePoint 2013

The first thing we need is a Report Data Source (RSDS) created so we can point to it (as a shared data source) when we create the report in Report Builder.

The data connection can reside in a Data Connections library (if you prefer to centralize), or within the same library as the reports are stored (if you prefer to keep reports & data connections together). For simplicity, the library shown below contains both reports and a data connection which is sales-specific.

Under Files on the ribbon, click the down arrow for New Document and choose Report Data Source.

Sidenote: If you don’t see Report Data Source under the New Document menu, you’ll need to add the content type to the library first. That’s a two-step process within the Library Settings…first, within the Advanced Settings, set “Allow Management of Content Types” to Yes…then back under General Settings choose “Add from existing content types” and select the various options that are related to BI data connections and/or reports (depending on what you need this library).

Set the various Data Source Properties. Make sure to use the “Microsoft SQL Server Analysis Services” data source type, as shown here:

In the above screen shot, note the structure of the connection string. For a bit more information about creating connections, see my blog entry titled “Comparison of Direct URL, BISM, and RSDS Data Connections for a Power View Report.”

The problem related to my original error was that within this RSDS connection, I had a data source configured with a type of “Microsoft BI Semantic Model for Power View” which – as the name implies – will work for Power View but not for Report Builder. Problem solved when the type was changed to SSAS.

Here’s what the Report Data Source looks like when it’s been added to the document library:

Binding the RSDS Connection to a Report Builder report in SharePoint 2013

To associate the Report Data Connection it to a Report Builder report, click the ellipses next to an existing report name, then the ellipses one more time, then select Manage Data Sources.

Click the link on the name of your data source.

Ensure the “Shared data source” radio button is selected, and paste in the URL to the Report Data Source created previously. Note this will have an RSDS file extension.

Security Questions: What permissions are required to create temporary tables?

Tue, 21 May 2013 12:30:22 -0500

As I mentioned in the introductory post, in the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer. Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic. Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.

Temporary table question

The first question in the list is:

What permissions are required to create temporary tables?

I’ll be honest, this was a new one for me. Not because it’s a bad question, but because I’ve never had problems trying to create temporary tables. And also, I’ve never had complaints from users about not being able to create temporary tables.

That fact of the matter is, that as long as a user can connect to a SQL Server instance, the user is able to create temporary tables. There are no special permissions. A login with the most basic permissions has full rights to create and use temporary tables. As it also turns out, there are no permissions for blocking or limiting temporary table creation. This last bit would actually be useful, given the ability in other users I’ve seen before to fill tempdb with their temporary data sets.

Temporary Table Demo

Just to demonstrate that any login with access can create temporary tables, we’ll run through a quick demo. Using the script in listing 1, create a new login on a SQL Server instance.


--Listing 1. Create new login

USE [master]
GO
CREATE LOGIN [TempTableUser] WITH PASSWORD=N'pass@word1'
, DEFAULT_DATABASE=[master]
, CHECK_EXPIRATION=ON
, CHECK_POLICY=ON
GO

Then connect to the SQL Server instance and run the create table script provided in listing 2. You’ll see that without any issues, the user can create a temporary table.


--Listing 2.

CREATE TABLE #temp (Column1 INT)

Summary

Now this might not have been the deepest content on this blog ever, but it is a quick answer and proof that, as DBA’s we don’t have to do anything to allow temporary table access for users. This question, though, makes me wonder, have you ever had issues with creating temporary tables? Maybe someone out there has worked in a place where someone figured out how to block temporary tables create permissions.

SQL Server Security Webcast Questions – Series Introduction

Tue, 21 May 2013 12:00:31 -0500

Earlier this month, I presented an Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s. A video of the session is available at the Pragmatic Works website. As a part of that session, I received a couple dozen questions about security that we didn’t have a chance to go over during the Q&A portion of the webcast.

Rather than write a short, possibly insufficient, answer for each question, I decided instead to put each question into a blog post. That way, they’ll be a bit easier to track down, read, and get the information you want out of them. These questions run from simple to complex.

Security Questions Asked

For the questions, I’ve made a few edits here and there for clarity. Overall, though, these are all of the questions that I received. As I answer the questions, I’ll add links to the posts.

What permissions are required to create temporary tables?
Do we have easy way to grant all stored procedures execution in a single shot? (live on 5-22-2013 10 AM CST)
Can you please expound on the difference between “Grant” and “With Grant?”
What is the difference between sysadmin and CONTROL SERVER Permission?
Whats the best role(s) to assign a junior DBA if you want them to see jobs, error logs, activity monitor, and run profiler, but not be able to manage jobs or kill processes?
Do the different ways of accessing SQL Server (Windows Athentication, SQL Server authentication, certificate or key) have differing authentication and authorization performance? If so, can you order the list?
Does 2012 provide TRUNCATE TABLE permissions?
How do you access the list of Server Securables?
How can i be sure a user is no longer used, so it can be deleted? Or when was the last time the logon was used?
How can you migrate users and passwords from one server to another server?
How would handle permissions for people that need full SQL Agent permissions (including being able to edit other peoples jobs) without giving sysadmin rights?
I work in a bank and federal inspectors are always looking at how secure are my databases. How would you prioritize security for the SQL Server?
What is the relationship between logins, credentials, and proxies? And why they were introduced?
What are some tips regarding roles other than sysadmin?
If an associate leaves, what is the best way to remove them from not only the logins but also all the databases?
What is the difference between db_datawriter and db_ddladmin?
How do I resolve the error: Drop Failed for User – The database principal owns a schema in the database, and cannot be dropped?
In 2008, a user at the DB level remains when you remove them at the Server level. Is this by design or perhaps ‘fixed’ in 2012 so that if a user is deleted at the server level she is also deleted from all database security?
Is the chart or dashboard in the Policy Based Management slide a standard one in SQL Server?
Is the grantor important? When, I as sysadmin grant rigths to a user I believe I come up as sa as grantor.
Is there a way to grant user to create alerts?
Is there a way to hide column/restrict from users?
Is there way to limit permission with date and time? For example, I want to give read access to a table within certain time period. For example, during business hours only.
Can you grant access through a job that runs at a specified time and remove this access through another job with the end period?
On a clustered server, can I grant access to a user to run SQL Trace (Alter SQL Trace permission) on specific databases but not all of them?
I have a login that does not have an entry in view sys.server_permissions. Shouldn’t every login has at least one entry, for “Connect SQL” as value for permission_name?
Temp tables: as a DBA, can i delete temp tables that are much too big?
Anyways to identify host name if an app is used through web, as well as mobile, to access SQL database? This question is just for tracking DB SELECT activity and the device/app usage from mobile?
What is the name of the recommended book again?

Last Words

Some of these post may inspire additional questions. Please leave those questions in the comments and I’ll either write an additional post to cover them or re-direct you to a post or other resource that can answer the question.

Consulting company: Perm/Salaried vs 1099/W2

Tue, 21 May 2013 10:00:12 -0500

One of my pet peeves is seeing a top-notch BI architect/developer working as a salaried/perm employee of a consulting company where they are making a good salary but are being billed out to the client at a rate that is 3-4 times what they are making. Are they getting taken advantage if their salary is 120k and they are billed to clients at $200/hr (and they are billable 90% of the time and they must travel 50%+)? Is it better to go 1099/W2 instead (I wrote a bit about this at Salaried employee vs contractor)?

I asked a veteran recruiter who has worked for many placement firms and now runs his own small firm about this. Specifically, I asked him these questions:

Are you getting taken advantage of if your salary is 110k and you are billed to clients at $150/hr? Is it better to go 1099/W2 instead? When you were working as a recruiter for the big consulting companies, did you try to steer consultants to go perm instead of W2 or 1099 because you would make more money off them? Where consultants laid off if they were on the bench more than a couple of weeks? Did you place less-qualified people on projects because they were the only one’s available?

We never cared if a person was salaried or a 1099. I guess a salaried person is more likely to stay with you longer because it is a pain to change benefits (especially if they are really good benefits) when you change jobs, but other than that, the percentage spreadsheets I mentioned before (see Billing rates – other side of the fence, part 2) always spit out a number and it didn’t have a bias towards salaried vs. 1099.

As for the specific question about a salary of $110K on a bill rate of $150/hr, it depends on the benefits (for example, are you taking insurance for a single person, a married couple, or a family?). Also, what other benefits come with the salary? 401(k) match? Tuition reimbursement? Utilization bonus (if you work over a certain number of billable hours in a calendar year, do you get a bonus?). I’d need a clearer picture of what comes with the $110K salary to know if it is a bad deal for the consultant. I’d think at a rate of $150/hr the vendor could afford a higher salary, just my gut though until I know more about the vendor benefits.

Also, this type of stuff applies across different industries. I know a person in a law firm that has an hourly billable rate of $380/hr. She has a nice salary, but it isn’t anything out of this world. They scoreboard the entire firm to show how much money you bring in through your billable hours, how much new business you won, etc. There is a lot hidden behind the curtain in all industries, so it is hard to compare apples to apples on these things sometimes.

As for bench policy, it was always a case by case basis. Has the person been with the firm for a long time? Meaning, has the firm made a decent profit on this person over the years. At the $17B firm where I worked, there was a lady that had been there 30 years. She was rarely on the bench, but if she ever did need bench time, she had earned it and it was there no matter what. A general rule of thumb is a week of bench per year of service. Also, the person’s skill set was a factor – was the person in a marketable space (Java people in some cities are gold, you’d give that person bench no matter what because they’d be out on another assignment for you very soon. On the contrary, if the person was a mainframer, that work isn’t as hot in most cities after Y2K, so that person probably had a shorter shelf life. One vendor (a $1B company) was a huge mainframe shop and after Y2K they had a ton of employees that were no longer billable. They kept them all on the bench and retrained them in Java. It was a huge investment/loss to the vendor and Wall Street killed them for the move and their stock price sank. So even when they “did the right thing” and took care of their people, Wall Street slammed them.

Your Community, Your Voice, Your Forum

Tue, 21 May 2013 05:00:02 -0500

For me, one of the most rewarding things about blogging are the conversations we have together.

The opportunity to connect, share and grow with passionate people from all over the world is a great privilege. I’ve met so many wonderful people through our community, learning something from each of you about technology and even life.

On occasion I myself have attempted to share the odd pearl of wisdom or snippet of advice. Through my blogging tenure I’ve discovered that I’m particularly fond of waxing on about Professional Development. So much so in fact, that some folks seem to consider me to be some sort of authority on the subject. Ha!

A curious side-effect to this infamy has been that people often reach out to me for advice and guidance. This is something I take very seriously indeed and I do my utmost to provide a well considered personal reply to all those who reach out, whether via email, in a blog post or in the blog comments.

This got me thinking. What if there were some way that I could create a space to bring together all this advice, experience and hot air, making it available for the benefit of everyone. Better still, what if I could somehow tap into the collective wisdom and experience of the entire community, increasing the quality of advice available to those seeking guidance. Sounds like a marvelous idea.

That’s why I started the SQL Brit Community Forum.

let’s make our community a roaring success together

I wanted to create:

A place where we can talk about all the other, non-technical, stuff there is to be being a Data Professional.
A friendly, open and honest community, where members support each other on their journey towards becoming the Data Professionals they want to be.
An environment that moves beyond the limits of blog comments by providing a platform that enables passionate, vibrant and robust conversation.
A place where members can benefit from the collective experience, knowledge and wisdom of each other.

Your Community in Action

The community forum has been open for a couple of months already, with over 120 registered members and 700 posts, it’s only now however that I’m choosing to blog about it.

As with all ideas I was initially unsure if it would get much traction but then once I saw the positive effect the community was having, I became convinced that I must do what I can to share it further.

You see what really surprised me was that the community had become not just a place where the experienced folks supported the new on their journey (like here, here or here) but also a place where members could refine their existing expertise further (like here and here).

Folks are using the community to bounce ideas off of one another and are taking their professional development to the next level through collaborating together. This is in a word is, fantastic!

Creating a Roaring Success Together

You see the thing about community is the more you give the more rewarding the experience becomes for everyone involved.

As Data Professionals we’re all super busy chaps that have a lot of competing priorities for our time. I would not be asking for yours if I did not believe in what we’re doing here. My will alone is unfortunately not enough and for this community to truly be successful I need your help to make it happen.

I know that you are passionate about your career and the SQL community because you’re here reading this. Whether to share your own experiences or to enjoy the opportunity to learn from others, I would be delighted for you to join our new community forum and be a part of making it a roaring success.

Thank you!

The many uses of CROSS APPLY

Mon, 20 May 2013 12:01:04 -0500

Over the last few years of studying SQL I’ve noticed 4 different uses for the command CROSS APPLY.

In the first use I ever saw, and certainly the one I see the most commonly, CROSS APPLY is used to run a function for each row of the query. This is also the easiest use to find in BOL. Here is a very common example using a DMV and a DMF.

SELECT *
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

Next while studying XML (see A review of SQL Interoperability / Joes 2 Pros Volume 5 by Rick A Morelan ) I found out that you can use it to shred XML.

SELECT Store.Name, 
	StoreInfo.StoreDetails.value('declare default element namespace 
		"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; AnnualSales[1]','decimal') AS AnnualSales,
	StoreInfo.StoreDetails.value('declare default element namespace 
		"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; AnnualRevenue[1]','decimal') AS AnnualRevenue
FROM Sales.Store Store
CROSS APPLY Store.Demographics.nodes('declare default element namespace 
		"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
		/StoreSurvey') AS StoreInfo(StoreDetails)
GO

Then while reading this post “Is there a better option than Union All for multiple selects from the same row?“ on dba.stackexchange.com I saw an example of using CROSS APPLY to unpivot a table. I was stunned at how simple it was. To make sure I understood it well enough to remember when I needed it, and to share something I felt was pretty cool, I posted a blog entry “UNPIVOT a table using CROSS APPLY“. Later I found this great article on http://www.sqlservercentral.com. An Alternative (Better?) Method to UNPIVOT (SQL Spackle)

SELECT UnPivotMe.FirstName, UnPivotMe.LastName, 
        CrossApplied.Question, CrossApplied.Answer
FROM UnPivotMe
CROSS APPLY (VALUES (Question1, Answer1),
                    (Question2, Answer2),
                    (Question3, Answer3),
                    (Question4, Answer4),
                    (Question5, Answer5)) 
            CrossApplied (Question, Answer)

And last but certainly not least I learned how to use The APPLY operator for reusable computations while watching Kendra Little’s 5 T-SQL Features You’re Missing Out On. Something I highly recommend watching.

SELECT Person.Title, Person.FirstName, Person.MiddleName, Person.LastName, UPPER(ComputedColumn.FullName)
FROM Person.Person Person
CROSS APPLY (SELECT ISNULL(Title+' ','')+ISNULL(FirstName+' ','')+
			ISNULL(MiddleName+' ', '')+ISNULL(LastName,'') AS FullName) ComputedColumn

Now I will admit I didn’t go into great detail on any of these topics, but I did try to provide at least a basic example, and a link or two where you can to get more in-depth information.

I’m truly amazed at the versatility of this operator. These are the four major uses (there are a number of permutations on each) that I’ve seen so far and I wouldn’t be surprised to find more. If you know another then please let me know in the comments. I’d love to add it to the list.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, CROSS APPLY, language sql, microsoft sql server, sql statements, T-SQL

Before Triggers in SQL Server

Mon, 20 May 2013 10:46:39 -0500

There are lots of questions asking whether there are before triggers in SQL Server. There are nothing called Before Triggers in SQL Server.

What is the requirement for the Before Trigger?

Let us say, you want to verify some values other table before inserting it. In SQL Server, you can use INSTEAD OF TRIGGER.

CREATE TRIGGER tr_data_before ON Table_Data
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM Tablle_Data2)
BEGIN
    INSERT INTO dbo.Table_Data
SELECT *
FROM INSERTED
END
END

However, in Oracle and DB2 there are Before Triggers.

#SQLSATATL simply rocked…

Mon, 20 May 2013 10:39:28 -0500

So, last Saturday, I went to the first SQL Saturday in Atlanta that I had absolutely no responsibility on the actual day of the event. I wasn’t an organizer, I didn’t really even volunteer. As a chapter leader for AtlantaMDF, I do have to go in and pay the bills later this week, but for the most part, I got to walk around and revel in the day.

And it was a good day. The team (led by Geoff Hiten) pulled off another great event; 555 people attended the event, and there were some AWESOME sessions throughout the day. I was in the 9 am slot, and my session on Biggish Data went well, even if (as usual) I had WAY too many slides. Speaking of slides, you should be able to download the deck from the SQLSaturday 220 schedule (as well as many other great presentations). I did have to slip out early since this weekend was my weekend with the kids. My current youngest (Grace) had to get service hours for beta club, so she got to hang out with me, but I needed to get back home early to hang out with the oldest one.

So what was cool? What did I learn?

The number one highlight for me was watching Louis Davidson demo Red Gate’s SQL Monitor tool, and he chose one of my custom metrics to use in the show. I respect Louis a lot, and it made my day.
I learned that I need to cut out about 10 slides out of my presentation.
I had a great conversation with a few people about table partitioning and performance gains; I think there’s enough material for a future session explaining why you will or won’t see performance benefits from partitioning. Hmmmm….
I had another excellent conversation with Robert Cain regarding the growth of SQLSaturday’s, and bounced around a few ideas about moving to a mid-tier model. Kind of like what SQLRally was supposed to become, but different.
I also got to hang out with Andy Leonard. He and my Grace had a good time discussing movies (Where’s my super suit?).

Mostly, I just got jazzed about being back in the #sqlfamily. Hopefully, I can build off that momentum, and start blogging again. Granted, the next few months are going to be personally interesting, but I need to make time to share again.

Last Weeks Top “Reading” Links #33

Mon, 20 May 2013 10:00:07 -0500

For those that follow me on twitter (@StrateSQL), you’ll know that throughout the day I tweet out some links of things I find interesting. These tweets include “Reading” in the message and are items I’ve read over the past few days, usually after hours when sharing would be less than useful, and spaced out to avoid spamming. The content of the links usually pertain to SQL Server, technology, and career topics; which I think others would find useful.

Most Popular Link

The most popular link that I sent out this week doesn’t have the most safe-for-work content. It’s basically a couple with a restaurant in Arizona that decided to take on the internet after a poor reception on some reality show they were on. It was something like, “your restaurant sucks, let me show everybody what morons you are” – that’s not the name of the show, but it sums it up nicely. Anyways, they take on the internet, and low-and-behold, the internet wins.

“This Is The Most Epic Brand Meltdown On Facebook Ever” bit.ly/15LrL4X <– just wow

— Jason Strate (@StrateSQL) May 15, 2013

Last week’s top 11! “Reading” Links

Along with the top link, here are the top twenty items relating to SQL Server, technology and careers that were sent out last week. I had planned for a top 10, but number 11 is a review of my indexing book – so I’ve included it and recommend you read that… then read my indexing book.

Other Stuff Shared

Of course, no week would be complete without a few off-topic links. These have nothing to do with technology or your career, but they sure are entertaining and interesting (and a couple are pretty gross).

Got something you think I should read and share, leave a comment below. Also, if you want to see all of the links that were tweeted out last week? Then follow the links on my tumblr blog or subscribe to it’s RSS feed.

SQL Intersection Registration Open – and a $100 Off Code!

Mon, 20 May 2013 08:24:55 -0500

Today is your lucky day.

How would you like to go to a SQL Server conference in Las Vegas where the sessions are taught by Brent Ozar Unlimited, SQLskills, SQLServerCentral, and SQL Sentry?

Yep. Me, Jeremiah, Kendra, Kimberly Tripp, Paul Randal, Jonathan Kehayias, Erin Stellato, Steve Jones, and Aaron Bertrand. Between us, that’s 3 MCMs, 2 MCM instructors, 7 MVPs, and 2 MVP Regional Directors.

If you’re serious about learning SQL Server, this should be the very first conference on your fall priority list. Check out some of these sessions:

Troubleshooting SQL Servers in VMware and SANs (me)
Understanding Locking, Blocking, and Isolation Levels (Kimberly)
Understanding Logging and Recovery (Paul)
X-Ray Glasses for Your Indexes (Kendra)
Branding Yourself for a Dream Job (Steve)
Deadlocking for Mere Mortals (Jonathan)
Hadoop: The Great and Powerful (Jeremiah)
Making the Leap from Profiler to Extended Events (Erin)

Team building in a Boxster through canyons

How much would you pay for three days of awesome learning at a conference like this with top-notch speakers, all killer no filler?

You want more sessions? You’re in luck! Your registration also includes ASP.NET Intersection sessions and Visual Studio Intersection sessions for developers, SharePoint Intersection sessions for sharing pointers. If your coworkers want to attend an open-source-friendly conference focusing on JavaScript and the web, the Angle Brackets conference is happening in the same hotel at the same time, so it makes for a great company getaway.

And hey, it’s Vegas, so it’s a great team building city, like when Jeremiah and I rented cars last time and, uh, built teams. Yeah.

But wait – there’s more! Check out the pre-con workshops:

Accidental DBA Starter Kit (me, Jeremiah, Kendra – Pre-Con Sunday) - You’re responsible for managing SQL Servers, but you’ve never had formal training. You’re not entirely sure what’s going on inside this black box, and you need a fast education on how SQL Server works. In one day, you’ll learn how to make your SQL Server faster and more reliable. You’ll leave armed with free scripts to help you find health problems and bottlenecks, a digital set of posters that explains how SQL Server works, and an e-book that will keep your lessons moving forward over the next 6-12 months.
Queries Gone Wild: Real-World Solutions (Kimberly – Pre-Con Sunday) - Have you ever wondered why SQL Server did what it did to process your query? Have you wondered if it could have done better? And, if so, how? Transact-SQL was designed to be a declarative language that details what data you need, but without any information about how SQL Server should go about getting it. Join order, predicate analysis – how does SQL Server decide the order or when to evaluate a predicate? Most of the time SQL Server gets the data quickly but sometimes what SQL Server does just doesn’t seem to make sense. Inevitably you’ll encounter certain workloads and queries that just aren’t performing as well as you expect. There are numerous reasons why query performance can suffer and in this full-day workshop Kimberly will cover a number of critical areas while showing you how to analyze a variety of query plans throughout the day.
Scale Up or Scale Out: When NOLOCK Isn’t Enough (me, Jeremiah, Kendra – Post-Con Thursday) - Partitioning, replication, caching, sharding, AlwaysOn Availability Groups, Enterprise Edition, bigger boxes, or good old NOLOCK? You need to handle more data and deliver faster queries, but the options are confusing. In this full-day workshop, Brent, Kendra, and Jeremiah will share the techniques they use to speed up SQL Server environments both by scaling up and scaling out. We’ll share what features might save you hundreds of development hours, what features have been a struggle to implement, and how you can tell the difference. This workshop is for developers and DBAs who need to plan long term changes to their environment.
Practical Disaster Recovery Techniques (Paul – Post-Con Thursday) - Disasters happen – plain and simple. When disaster strikes a database you’re responsible for, can you recover within the down-time and/or data-loss limits your company requires? What if your plan doesn’t work? This workshop isn’t about how to achieve high-availability, it’s about how to prevent or overcome the obstacles you’re likely to hit when trying to recover from a disaster – such as not having the right backups, not having valid backups, or not having any backups! In this demo-heavy workshop, you’ll learn a ton of practical tips, tricks, and techniques learned from 15 years of experience helping customers plan for and recover from disasters, including less frequently seen problems and more advanced techniques. All attendees will also receive a set of lab scenarios for further study and practice after the class with assistance from Paul.

Ernie takes in Hoover Dam

Now how much would you pay for all this? Three thousand? Four thousand? Ten thousand? BUT WAIT, THERE’S MORE!

For $1,894 before June 24th, you can get the Show Package: the conference, PLUS a pre-con or post-con of your choice, PLUS your choice of a Surface RT, Xbox, or a $300 gift card.

For $2,294, you get all that plus ANOTHER pre-con or post-con – five days of nonstop learning from the absolute best in the business.

No? You want more? Okay, you drive a hard bargain, buddy. Use discount code OZAR and you get another $100 off. Register now. Operators are standing by.

...
Just check the boxes to see Bad DBA Job Detector, Top 3 Indexing Mistakes, and the Accidental DBA's 6 Month Training Plan.

Identifying Identity Columns Approaching Their Limit

Mon, 20 May 2013 08:00:56 -0500

Back in December, a couple of checks I wrote were included in Brent Ozar Unlimited's sp_Blitz script. I was chagrined to see that some people discovered some bugs in my code and submitted fixes. To be fair, one was a bug in SQL 2008 & 2008 R2 where DBCC DBINFO WITH TABLERESULTS returned the dbccLastKnownGood entry twice. (Microsoft has said they aren't going to fix this, unfortunately.) The other was an issue when systems were using the British date format. I guess this just goes to show how hard it is to test every possible use case to discover bugs.

Even though there were only problems with one of the three tests I submitted, I still felt the need to redeem myself and submit another check. I had recently read a blog post about monitoring the values of identity columns in tables to make sure you aren't approaching the maximum value for the data type used. Surprisingly, such a check wasn't already in sp_Blitz. I felt this was something I could tackle.

My first attempt worked, but it did so by cycling through all the tables in a database, finding any identity columns, and comparing the maximum value used to the maximum value the data type can accommodate (255 for TINYINT, 2147483648 for INT, etc.) I submitted this and the feedback from Brent was that he wasn't sure if this should be included because of the performance implications. Although I tested the code on all my servers without issue, Brent mentioned that some databases, especially those for SAP, may have 10,000 to 50,000 tables. Running code to look at each of those tables could have a severe impact on performance or, at the very least, greatly increase the time it took the script to complete. That's a valid point (and again goes to show how hard it is to test for all use cases). Apparently, he spoke to Kendra Little about this and she brought up a system table that both Brent and I didn't know about (or more likely, knew about, but forgot): sys.identity_columns. Using that, I can get the same information by querying one system table instead of each individual user table in the database.

So I re-wrote my script to use that and re-submitted it to sp_Blitz. It's in the queue for evaluation, so I don't know when (or if) it will be included, but I thought I post the test here. (And of course, once I had the name of this table, I discovered a Google search returns lots of scripts that provide the same functionality.)

A quick note: The script assumes the identity increment is positive and the identity values are positive. The script will issue a warning when the maximum value is more than 90% of the maximum value of the data type used.

  DECLARE @SchemaName sysname
  DECLARE @TableName sysname
  DECLARE @ColumnName sysname

  DECLARE @Name sysname
  DECLARE DBNameCursor CURSOR
  FOR
          SELECT    name
          FROM      sys.databases
          WHERE     source_database_id IS NULL	/* no database snapshots */
                    AND is_read_only = 0		/* no read-only dbs (log shipping) */
                    AND database_id <> 2		/* skip tempdb */
                    AND state = 0	/* online databases */
          ORDER BY  name;

  CREATE TABLE #IdentityCheck
         (
          PK INT IDENTITY(1, 1)
         ,DatabaseName sysname
         ,SchemaName sysname
         ,TableName sysname
         ,ColumnName sysname
         ,ColumnType VARCHAR(8)
         ,MaxValue BIGINT
         )

  OPEN DBNameCursor
  FETCH NEXT FROM DBNameCursor INTO @Name
  WHILE @@fetch_status = 0
        BEGIN

              EXEC('USE [' + @Name + '];  INSERT  INTO #IdentityCheck
              SELECT ''' + @Name + ''' as DatabaseName
              ,SCHEMA_NAME(o.SCHEMA_ID) AS SchemaName
              ,OBJECT_NAME(o.OBJECT_ID) AS TableName
              ,c.[name] AS ColumnName
              ,t.name AS ColumnType
              ,CAST(last_value AS bigint) AS MaxValue
              FROM    sys.identity_columns c
              ,sys.types t
              ,sys.objects o
              WHERE   c.is_identity = 1
              AND t.system_type_id = c.system_type_id
              AND o.object_id = c.object_id
              AND o.type = ''u''
              AND t.is_user_defined = 0 /* only look at system defined types */
              AND t.name IN (''int'', ''bigint'', ''smallint'', ''tinyint'')')
              FETCH NEXT FROM DBNameCursor INTO @Name
        END

  CLOSE DBNameCursor
  DEALLOCATE DBNameCursor

  SELECT    'Table [' + SchemaName + '].[' + TableName + '], column ['
            + ColumnName + '] in database [' + DatabaseName
            + '] is an identity column that has reached over 90% of the maximum value for that datatype.' AS Details
           ,'' AS ChangeRequirements
  FROM      #IdentityCheck
  WHERE     1 = CASE WHEN ColumnType = 'bigint'
                          AND (CONVERT(DECIMAL(19, 0), MaxValue)
                               / 9223372036854775807.0) > .90 THEN 1
                     WHEN ColumnType = 'int'
                          AND (CONVERT(DECIMAL(10, 0), MaxValue)
                               / 2147483648.0) > .90 THEN 1
                     WHEN ColumnType = 'smallint'
                          AND (CONVERT(DECIMAL(5, 0), MaxValue) / 32767.0) > .90
                     THEN 1
                     WHEN ColumnType = 'tinyint'
                          AND (CONVERT(DECIMAL(3, 0), MaxValue) / 255.0) > .90
                     THEN 1
                END

  DROP TABLE #IdentityCheck

So what do you do if you get this warning? Well, a quick fix that Michelle Ufford suggested, is to reseed your identity value to the lowest negative value the data type supports. For example, if your identity column is an integer, reseed the column to start at -2147483648 and increment by a positive number. (This assumes you started your initial identity value at zero and used a positive increment to begin with.)

One more note: you'll notice my script uses a cursor to cycle through the databases while many other scripts you find on the internet use ms_foreachdb. Either method works. I prefer the cursor because it's 100% supported. (For the cursors-give-sucky-performance crowd, the undocumented command uses a cursor behind the scenes anyway, so that's a moot point.) Ms_foreachdb is an unsupported command and can change at any time. There is also evidence that it can skip some databases and even completely lock up SQL 2005 servers in some situations. Given the issue I ran into above with the DBCC DBINFO command, which is also an unsupported command, and Microsoft's statement that they are not going to fix it, I am committed to not using any undocumented commands in any scripts I pan to re-use. I'll use them for any one-off work I might be doing, but for something that I want to save and re-use as part of my SQL toolkit, I will pass.

Per Member Per Month Per 1000 Calculations in MDX

Mon, 20 May 2013 07:00:00 -0500

In a lot of industries there is a popular calculation called “Per Member Per Month Per 1000” calculation or the “Per Customer Per Month Per 1000” calculation. This is used to determine the level at which the company is rendering services compared to the number of company customers. This calculation is sometimes called the 1000pmpm calculation for short.

In this article you will be looking at the member count numbers at no lower than the month level. Here is an example of the 1000pmpm calculation. If you are looking at the year level the number of months would be 12 so it would be 12 divided by 12 which is 1. So it can be removed from the calculation at the year level. This will be important later on.

[Auth Count] x ((12 / [number of months]) x 1000) / [Member Count]

In this example you will be looking at a health care company. The main number the company wants to look at is the number of authorizations per 1000 members. The data warehouse has a fact table with all of the authorizations. There is also another fact table with the member count on it also. The Fact Authorization table also has three date columns, Admit date, Discharge Date, and Date Received. This will be important when calculating the numbers at different levels of the date Hierarchy. The Fact table also has many other dimension surrogate keys mapped to the appropriate dimensions.

The Fact Membership table has a date on it named incurred date. This date will have a relationship to all three dates on the fact authorization table. The reason for this multiple date relationships is due to the users wanting to see the differences in discharge and admit numbers.

There are several other dimensions mapped to each fact table. In this example you will ignore all other dimensions except the date dimensions. But keep in mind those other dimensions need to have relationships set up to be able to slice the numbers by those dimensions.

Here is an example of part of the membership fact table. Notice that the incurred date repeats. This is because it depends on which dimensions you slice on which member count you will get. This also needs to be summed up to the month level. After the month level it is no longer valid as a sum. This seems tricky but can be done with a calculation. The original member count measure is set to sum on the measure properties. This measure is set to hidden also because the sum is invalid over the month level.

To fix the summing issue you will need to create a calculation to divide up the member count by the number of month. Here is that calculation:

[Member Count]:

Case

When [Date Received].[Date Hierarchy].currentmember.level is

[Date Received].[Date Hierarchy].[Year]

Then [Measures].[Members] / 12

When [Date Received].[Date Hierarchy].currentmember.level is

[Date Received].[Date Hierarchy].[Quarter]

Then [Measures].[Members] / 3

When [Admit Date].[Date Hierarchy].currentmember.level is

[Admit Date].[Date Hierarchy].[Year]

Then [Measures].[Members] / 12

When [Admit Date].[Date Hierarchy].currentmember.level is

[Admit Date].[Date Hierarchy].[Quarter]

Then [Measures].[Members] / 3

When [Discharge Date].[Date Hierarchy].currentmember.level is

[Discharge Date].[Date Hierarchy].[Year]

Then [Measures].[Members] / 12

When [Discharge Date].[Date Hierarchy].currentmember.level is

[Discharge Date].[Date Hierarchy].[Quarter]

Then [Measures].[Members] / 3

Else

[Measures].[Members]

End

In this calculation you can see the case statement is just dividing the member count by 12 at the year level and 3 at the quarter level. The member count is not valid below the month level so this takes care of all the possibilities. You will also notice that the calculation includes all three of the date hierarchies. This ensures the calculation works in all date dimensions. This can be shown on the reports and will show the correct member count for every level in all the date dimensions. The below image is of the member numbers with just the summation and the other after the above calculation. By dividing with the number of months we get the average instead of the sum at the quarter and year level, which is what we want.

The next calculation is just to divide the member count by 1000. This can also be shown on the reports at all date levels.

[Member Count / 1000]:

iif([Measures].[Member Count] <= 0,

null,

[Measures].[Member Count] / 1000)

The last calculation we need to do to the member count is used in the actual calculations where you divide the measures by the member count. In this example you will use the Authorization count. But instead of multiplying every measure by a number then dividing by the member count, you can do a little math and figure out that you just divide the member count number and then use this one number for all measures.

[MemberCount / 1000 for Calcs]:

Case

When IsEmpty([Member Count / 1000])

Then Null

When [Date Received].[Date Hierarchy].currentmember.level is

[Date Received].[Date Hierarchy].[Year]

Then [Member Count / 1000]

When [Date Received].[Date Hierarchy].currentmember.level is

[Date Received].[Date Hierarchy].[Quarter]

Then [Member Count / 1000] / 4

When [Admit Date].[Date Hierarchy].currentmember.level is

[Admit Date].[Date Hierarchy].[Year]

Then [Member Count / 1000]

When [Admit Date].[Date Hierarchy].currentmember.level is

[Admit Date].[Date Hierarchy].[Quarter]

Then [Member Count / 1000] / 4

When [Discharge Date].[Date Hierarchy].currentmember.level is

[Discharge Date].[Date Hierarchy].[Year]

Then [Member Count / 1000]

When [Discharge Date].[Date Hierarchy].currentmember.level is

[Discharge Date].[Date Hierarchy].[Quarter]

Then [Member Count / 1000] / 4

Else

[Member Count / 1000] /12

End

This last calculation may be confusing. To help clarify this, take a look at the 1000pmpm calculation again:

[Auth Count] x ((12 / [number of months]) x 1000) / [Member Count]

What you are basically doing is moving the ((12 / [number of months]) to the bottom of the calculation and dividing the member number by this answer. This ensures the company can compare the number across any level. So the quarter level should be very close to the month and year level. The last calculation makes sure this happens by dividing member count by the number of quarters in a year at the quarter level. At the year level it would be 12 divided by 12, which equals 1 so no division is needed. That final else is used to get all the months.

Now you can use this final calculation to divide all the measures in the cube. The below calculation can be applied to all measures. This calculation is checking for divided by zero errors then just dividing the measure by the member number we have worked hard to get above.

iif([Measures].[Member Count / 1000] <=0,

Null,

[Measures].[Auth Count] / [MemberCount / 1000 for Calcs])

Here you can see the final results:

By using this method you can use this final member count number easily and save lots of unnecessary work. If you try to calculate each measure separately all of the calculations would have been massive.

SQL Server – Show/Hide Results Pane in Management Studio 2012

Mon, 20 May 2013 06:15:22 -0500

In earlier version of SQL Server Management Studio (2005, 2008 and 2008 R2) you can show/hide results pane using keyboard shortcut Ctrl+R. This shortcut was also present in Query Analyzer (SQL Server 2000).

This shortcut is no longer available in SQL Server Management Studio 2012. In this version you need to use Ctrl+Shift+Alt+R to show/hide query results pane. Another alternative shortcuts you can use to show or hide results pane is Alt+WS and Alt+WI respectively. I find the later to be more easy to use.

If you have gotten used to using old shortcut Ctrl+R you can customize shortcuts in SQL Server Management Studio.

To change keyboard shortcut for show/hide results pane:

Step 1: Go to Tools > Options in SQL Server Management Studio:

Step 2: Navigate to Environment > Keyboard > Keyboard

Step 3: Search for Window.ShowResultsPane using Show commands containing: textbox.

Step 4:

(1) Select Ctrl+Shift+Alt+R (SQL Query Editor) in Shortcuts for selected command: list.
(2) Select SQL Query Editor in Use new shortcut in: list.
(3) Go to Press shortcut keys: textbox and press Ctrl+R or any other shortcut you need.
(4) Click on Assign.
(5) Click on OK to apply changes.

Step 5: You need to restart SQL Server Management Studio to apply these changes.

Note: The Window menu will still show Ctrl+Shift+Alt+R as shortcut we have only added an additional shortcut, we have not removed existing shortcut.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Filed under: Management Studio, SQL Configuration, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2, SQLServer 2012

What Operation Type Invoked a Trigger?

Sun, 19 May 2013 12:44:58 -0500

This was a question brought to my attention and I didn’t have an answer. After searching, I realized that there is no direct function for it. Hence I came up with following solution.

I used inserted and deleted virtual tables.

Statement	inserted	deleted
INSERT	rows just inserted
DELETE		rows just deleted
UPDATE	modified row contents	original row contents

With the above cases, I came up with following trigger.

CREATE TRIGGER trg_data_ins_del_upd
ON Data
FOR INSERT,DELETE,UPDATE
AS
BEGIN
    DECLARE @ins int ,@del int

    SELECT @ins = Count (*) From inserted
SELECT @del = Count (*) From deleted
IF @ins > 0 AND @del > 0 
INSERT INTO Operation  (Operation)  VALUES  ('Update')
ELSE IF @ins > 0 
INSERT INTO Operation  (Operation)  VALUES  ('Insert')
ELSE IF @del > 0 
INSERT INTO Operation  (Operation)  VALUES  ('Delete')
END

However, I feel in the above case, you better of having three separate triggers than loading everything to one trigger.

How to: Configure SQL Server 2012 AlwaysOn – Part 6 of 7

Thu, 16 May 2013 15:19:25 -0500

How to: Configure SQL Server 2012 AlwaysOn

How to: Fail-over

In this part of the blog series we will go over the various methods of manually failing over an Availability Group. Naturally if you are using automatic fail-over then there are no steps required for this to happen in an emergency but you might find a reason to conduct a planned manual fail-over when there is no actual problem (for example, fail-over testing).

What Happens During a Fail-over

Database Component:

A fail-over occurs by sending what amounts to a “make primary” command to a secondary replica. This happens because, of course, the primary database might not be available to receive commands.

Once the command to make primary comes through, the database will become transactional (in a read/write mode) and begin accepting connections of all types. If you were using synchronous commit then there will be no data loss and this new primary replica will begin stock piling transactions in the transaction log until the previous primary comes back online to receive the transactions or data transfer is manually severed. When the primary replica comes online there are no user actions required to resume the data transfer but a fail-back would require user interaction if it was desired.

If you are using asynchronous commit (like we are in our architecture) then the secondary replica will come back online but the data transfer will be paused. At this time you can use the database as a read-only replica to identify what data loss occurred or you can resume transfer. By resuming the transfer you lose the ability to identify what data might have been lost during the fail-over.

Cluster resource / AG listener

At the same time that the database component is failing over the cluster resource is also working on its fail-over.

Which ever secondary replica is becoming the new primary now needs to run the cluster resource for the Availability Group. The most important part of this fail-over is the AG listener. When crossing within the same sub-net the move causes a momentary outage of the IP address associated to the listener (no longer than the outage incurred by the database fail-over). If the resource is moving to a node where the listener must change sub-nets it will take the former sub-net IP offline and bring online the new sub-net’s IP address.

Availability Group Fail-over Wizard

The availability group fail-over wizard can be run from either the primary or secondary replica but you will have to be able to connect to the secondary replica in order to be successful.

There are two ways to access the fail-over wizard:

Availability group dashboard

The Start Failover Wizard link is available from both the primary and secondary replica dashboards.

SSMS Object explorer

To access the fail-over wizard from the Object Explorer; right click on the availability group and select Failover…

Wizard Walk-through

Upon opening the availability group fail-over wizard for the first time you will meet the Introduction page. If you’d like, check the Do not show this page again then click Next.

On the Select New Primary Replica page you will see a list of all available replicas with some status information. I find the most important column of this page to be the Failover Readiness column which will let you know whether you will have data loss by choosing this replica for fail-over.

NOTE: The Failover Readiness column is indicating data loss based on configuration not based on an actual representation of what has been synchronized. If you created a database inserted one record, verified that the secondary has the record, and then ran this wizard you would see that data loss is listed for asynchronous connections. It does not matter that there will actually not be any data loss in this situation.

Once you have selected your replica to fail-over to; click Next.

The Summary page will provide a list of the actions and affected components. This is where you can script out the process or go ahead and click Finish.

Finally, you reach the Results page and you are complete.

T-SQL Fail-over

Microsoft did a really great job at keeping the fail-over scripts simple for moving an Availability Group.

These commands must be run on the secondary replica that you wish to become the primary replica.

When no data will be lost by the fail-over

ALTER AVAILABILITY GROUP [AG_SQLTest] FAILOVER;

When no data will be lost by the fail-over

ALTER AVAILABILITY GROUP [AG_SQLTest] FORCE_FAILOVER_ALLOW_DATA_LOSS;

Wrap-up:

It is easy to see that both the Availability Group Fail-over Wizard and the T-SQL methods are both very straight forward and easy to use. That is one characteristic of AlwaysOn that is very appealing, especially to junior DBAs.

Follow the links below to move on through this tutorial.

Scope and Architecture - Part 1 of 7
Building the Environment – Part 2 of 7
Configuring AlwaysOn – Part 3 of 7
Building Availability Groups – Part 4 of 7
Explanation of Implicitly Created Components – Part 5 of 7
How to: Fail-Over – Part 6 of 7
The Availability Group Listener – Part 7 of 7

When the person you hire is not the person you hire

Thu, 16 May 2013 10:00:26 -0500

This is something I just heard about recently, but then experienced it for myself.

This story was told to me: A client is looking for a contractor and contacts a staffing firm to find candidates. The staffing firms sends a resume that looks real good. A technical interview is done via the phone, the candidate really knows his stuff and nails the interview, and he is hired. A few weeks later the new hire arrives and begins work. But something does not seem right. The contractor does not seem to be that sharp, nothing like he was during the interview. His coding skills are not that good. After a few weeks, the client does some digging, and it is discovered this person is not the person who did the interview!

It’s called the “bait and switch”, and the hope is the client remains unaware it is a different person. I’m guessing sometimes they get away with this switch, but you can imagine how upset the client is when they find out they have been tricked. It seems the staffing firm is tricked also. This happened twice, and both cases involved sponsored candidates from India. In one of the cases there was a group of seven who all lived together and one of them was really sharp (the “ring leader”) and would do all the interviews for the other six, who were junior-level. The resumes had the real name of the person but the experience was the ring leaders. Also, when the junior programmers were placed at a client the ring-leader would help out the junior programmers if they were struggling on the project they were on. And the client was none the wiser.

This then happened to me, and shows the extent some people will go to trick the client. The client I was at did a phone interview on a candidate that I participated in. The candidate did very well in the phone interview. Since the client had experienced the same bait-and-switch as above, the next step in the interview process is a required a face-to-face meeting. So they did a video interview on Skype, were we used Skype for the video and used a land-line for the voice. Once again the candidate did very well but….it turns out, we were talking to a different person on the land-line than who was on the video! We got suspicious when we saw how the video and voice were so out of sync, and doing some more digging turned up they were in fact different people. The guy on the video was pretending to be the guy talking on the land-line. Crazy!

I asked my recruiter friend about this, and he replied:

I saw it happen more often earlier in my career, not so much now (but it still does happen on rare occasions). Detroit in particular had a ton of foreign national firms set up show here in the 90’s and when this “bait & switch” became a trend, the Big 3 put an end to it. They’d make candidates give some form of ID (SS# or some other identifier). There would also be harsh penalties such as removal from the vendor blanket for firms that repeatedly used this tactic. The bait & switch is a rookie move. The staffing world can be a greasy business – unfortunately nothing really surprises me these days.

Recent Entries Feed | SQLServerPedia Blog

Using CROSS APPLY instead of a calculated variable

Cumulative Update – 11 for SQL Server 2008 SP3 Is Now Available !

MongoDB 2.4 Feature Demo and Q&A on Geo Capabilities

Connecting a service between Publishing and Consuming SharePoint 2010 farm

Resolving Error Connecting Report Builder to a PowerPivot Data Source

Creating a Data Source for Report Builder Reports in SharePoint 2013

Binding the RSDS Connection to a Report Builder report in SharePoint 2013

Security Questions: What permissions are required to create temporary tables?

Temporary table question

Temporary Table Demo

Summary

SQL Server Security Webcast Questions – Series Introduction

Security Questions Asked

Last Words

Consulting company: Perm/Salaried vs 1099/W2

Your Community, Your Voice, Your Forum

Your Community in Action

Creating a Roaring Success Together

Related posts:

The many uses of CROSS APPLY

Before Triggers in SQL Server

#SQLSATATL simply rocked…

Last Weeks Top “Reading” Links #33

Most Popular Link

Last week’s top 11! “Reading” Links

Other Stuff Shared

SQL Intersection Registration Open – and a $100 Off Code!

Identifying Identity Columns Approaching Their Limit

Per Member Per Month Per 1000 Calculations in MDX

SQL Server – Show/Hide Results Pane in Management Studio 2012

What Operation Type Invoked a Trigger?

How to: Configure SQL Server 2012 AlwaysOn – Part 6 of 7

How to: Configure SQL Server 2012 AlwaysOn

How to: Fail-over

What Happens During a Fail-over

Database Component:

Cluster resource / AG listener

Availability Group Fail-over Wizard

There are two ways to access the fail-over wizard:

Availability group dashboard

SSMS Object explorer

Wizard Walk-through

T-SQL Fail-over

When no data will be lost by the fail-over

When no data will be lost by the fail-over

Wrap-up:

When the person you hire is not the person you hire