Esther Schindler writes "It's certainly fun to pretend to find work inspiration from our favorite SF films. That's what Carol Pinchefsky does in two posts, one about positive business lessons you can take away from SF films (such as 'agile thinking can save many a project (and project manager) in a crisis' from Robocop and team motivation lessons from Buffy), and the other, 5 Project Management Horror Stories Found in Sci-Fi Movies, with examples of the impact of poor documentation on Captain America."

Read more of this story at Slashdot.

During my migration from vSphere 4.1 to vSphere 5.5, I ran into an issue I had never experienced before. When your VMware HA network is partitioned, vCenter will not let you perform VMotions. At first I was surprised but after some searching I learned the reasons behind it and it makes complete sense now.

I had a cluster running ESX 4.0/4.1 hosts with a very basic network config. Service Console was in VLAN100, VMotion in VLAN110 each in different IP ranges. The new ESXi 5.5 hosts would get a completely different network config. First vmkernel port in VLAN200, second vmkernel port in VLAN210 both enabled for management traffic. And there were two VMotion vmkernel ports in VLAN220. When adding these new hosts to the same clusters as the ESX 4.0/4.1 hosts, I received a number of HA error messages:

• The vSphere HA Agent on the host is alive and has management network connectivity, but the management network has been partitioned.
• This state is reported by a vSphere HA Master Agent that is in a partition other than the one containing this host.
• The vSphere HA protected VMs running on the host are monitored by one or more vSphere HA Master Agents, and the agents will attempt to restart the VMs after a failure.

This seemed logical since the old hosts had their Service Console interface in a different IP range than the new ESXi 5.5 hosts. Shouldn’t be a problem, since this situation would only last one day during working hours, while I was working on that cluster and my plan was to VMotion the VMs in the cluster from the old hosts to the new hosts. And that is where I was stopped. I was unable to free an 4.1 host because VMotion was not allowed to a new ESXi 5.5 host. Fortunately, KB 1033634 “vSphere HA and FT Error Messages” came to help and explained why I could not VMotion.

And as in many situations before, it is completely logical that VMware decided to treat a partition HA situation in this way. Taken from the KB Article: “The powered-on virtual machine you are attempting to migrate will not be protected by vSphere HA after the vMotion operation completes because the vSphere HA master agent is not currently responsible for it. vSphere HA will not restart the virtual machine if it subsequently fails. To restore vSphere HA protection, resolve any network partitions or disk accessibility issues.“.

Makes perfect sense and now you know too or even better, you already knew

See full post at: Did you know, no VMotion when HA network partitioned

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series:

• A first look at the query_optimizer_estimate_cardinality XE event
• “CSelCalcCombineFilters_ExponentialBackoff” Calculator
• “CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II
• The CSelCalcAscendingKeyFilter Calculator
• Cardinality Estimation Model Version
• Comparing Root-Level Skews in the new Cardinality Estimator
• Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews

Continuing the subject of exponential backoffs (from the 2nd and 3rd posts in this series), let’s restore the Credit sample database back to the baseline version and execute the following script:

USE [master];
GO

ALTER DATABASE [Credit] SET COMPATIBILITY_LEVEL = 120;
GO

USE [Credit];
GO

-- Add four new columns
ALTER TABLE [dbo].[member]
ADD [arbitrary_1] BIGINT NULL;

ALTER TABLE [dbo].[member]
ADD [arbitrary_2] BIGINT NULL;

ALTER TABLE [dbo].[member]
ADD [arbitrary_3] BIGINT NULL;

ALTER TABLE [dbo].[member]
ADD [arbitrary_4] BIGINT NULL;

I changed the database to the latest version so we use the new CE and then added four new columns.

Next, let’s update the values of the four new columns using different distributions:

;WITH    CTE_NTILE
AS ( SELECT   [member_no] ,
NTILE(10) OVER ( ORDER BY [member_no] DESC ) AS [arbitrary_1] ,
NTILE(2) OVER ( ORDER BY [member_no] DESC ) AS [arbitrary_2] ,
NTILE(4) OVER ( ORDER BY [member_no] DESC ) AS [arbitrary_3] ,
NTILE(250) OVER ( ORDER BY [member_no] DESC ) AS [arbitrary_4]
FROM     [dbo].[member]
)
UPDATE  [dbo].[member]
SET     [arbitrary_1] = 1.[arbitrary_1] ,
[arbitrary_2] = 1.[arbitrary_2] ,
[arbitrary_3] = 1.[arbitrary_3] ,
[arbitrary_4] = 1.[arbitrary_4]
FROM    [dbo].[member] AS [m]
INNER JOIN CTE_NTILE AS 1
ON      1.[member_no] = [m].[member_no];
GO

Looking at the estimates for single-predicate queries, if I execute the following, I’ll get an estimate of 1,000 rows:

SELECT  [member_no]
FROM    [dbo].[member]
WHERE   [arbitrary_1] = 1
OPTION  ( RECOMPILE );

For this next query I’ll get an estimate of 5,000 rows:

SELECT  [member_no]
FROM    [dbo].[member]
WHERE   [arbitrary_2] = 1
OPTION  ( RECOMPILE );

And for this next query, an estimate of 2,500 rows:

SELECT  [member_no]
FROM    [dbo].[member]
WHERE   [arbitrary_3] = 1
OPTION  ( RECOMPILE );

And lastly (for single-predicate examples anyhow), an estimate of 40 rows:

SELECT  [member_no]
FROM    [dbo].[member]
WHERE   [arbitrary_4] = 1
OPTION  ( RECOMPILE );

Now let’s start adding multiple predicates per statement.  The first example with multiple predicates uses two predicates – one with a selectivity of 0.1 and one of 0.5:

SELECT  [member_no]
FROM    [dbo].[member]
WHERE   [arbitrary_1] = 1 AND -- 0.1 selectivity
[arbitrary_2] = 1 -- 0.5 selectivity
OPTION  ( RECOMPILE );

The estimate for this query is 707.107 with the new CE, which we can derive using the POWER function in T-SQL as follows (I used Excel last time to do this, so see the previous posts for the background information on this calculation):

SELECT  10000 *0.10 * POWER(0.500000, 0.50);

That returned 707.107.

Now what about a query with three predicates, with selectivities of 0.1, 0.5 and 0.25?

SELECT  [member_no]
FROM    [dbo].[member]
WHERE   [arbitrary_1] = 1 AND -- .1 selectivity
[arbitrary_2] = 1 AND -- .5 selectivity
[arbitrary_3] = 1 -- .25 selectivity
OPTION  ( RECOMPILE );

The estimate for this was 420.448, and we can derive this via the following expression (and notice the order of selectivities goes from smallest to highest):

-- Notice the selectivity order (0.10, 0.25, .50)
SELECT  10000 * 0.10 * POWER(0.250000,0.50) * POWER(0.500000, 0.25);

Now let’s reference all four columns (with selectivities of 0.1, 0.5, 0.25 and 0.004):

SELECT  [member_no]
FROM    [dbo].[member]
WHERE   [arbitrary_1] = 1 AND -- .1 selectivity
[arbitrary_2] = 1 AND -- .5 selectivity
[arbitrary_3] = 1 AND -- .25 selectivity
[arbitrary_4] = 1  -- 0.004 selectivity
OPTION  ( RECOMPILE );

The estimate is 8.20193 and we can derive this via the following:

SELECT  10000 * 0.004* POWER(0.1000000, 0.50) * POWER(0.2500000, 0.25) * POWER(0.5000000, 0.125);

The selectivities are ordered from most selective to least selective, and the the less selective values get the “back offs” in order of none, 1/2, 1/4, and 1/8.

The post More on Exponential Backoff appeared first on Joe Sack.

Benjamin Nevarez (@BenjaminNevarez) gives us some insight into the new cardinality estimator in SQL Server 2014 - both how it works as well as how to keep the functionality of the current version of the estimator.

It's been an active couple of months for O. Z.'s group.

In an effort to combat keying errors and all around bad data, some long overdue validation was added by one of O. Z.'s co-workers to several of the screens, and perhaps most importantly, to the address entry screens.

One area of particular attention, was adding flexibility to the zip code field. You see, the U.S. Post Office uses a 5-digit zip code, plus an optional 4-digit extension. The extension lets them subdivide the zip codes for more efficient handling, and while most folks use the 5-digit version it was decided that the screens should be able to accept and validate either version.

However, being a large, lethargic shop, most code reviews come way after-the-fact and it wasn't until the updated screens were in Production (and working as designed) that O. Z. discovered something curious in a table definition that was modified by this co-worker. Apparently, the co-worker managed to validate the entered zip code, but chose to handle longer variants in a unique way:

Create Table Addresses (
         StreetAddress1  varchar2(64) not null,
         StreetAddress2  varchar2(64),
         City            varchar2(32) not null,
         State           varchar2(2)  not null,
         Zip5            varchar2(5)  Comment 'No matter what they enter, one of these will be populated',
         Zip6            varchar2(6)  Comment 'No matter what they enter, one of these will be populated',
         Zip7            varchar2(7)  Comment 'No matter what they enter, one of these will be populated',
         Zip8            varchar2(8)  Comment 'No matter what they enter, one of these will be populated',
         Zip9            varchar2(9)  Comment 'No matter what they enter, one of these will be populated'
  );

On a hunch, O. Z. dug deeper and found this logic - repeated in the UI, client-side logic behind the UI, the server and stored procedures in the database:

  if (zip5 != null) {
     // …
  } else if (zip6 != null) {
     // …
  } else if (zip7 != null) {
     // …
  } else if (zip8 != null) {
     // …
  } else if (zip9 != null) {
     // …
  }

At this point, O. Z. began to have that feeling, you know the one, deep in the pit of his stomach, so he investigated further, only to find that this co-worker had used this pattern on every field he was assigned to touch. Across screens. Across modules. Across applications. Literally, everywhere.

O. Z.'s co-worker is no more...mercifully, he found some greener pastures to inhabit...but for the time being, his legacy lives on.

“Could you be on this call too, Bruce?”

Bruce puzzled over the request. “Abby,” he said, “if Dale will be there, I’m not sure I need to be.”

“We might need your ban-hammer,” said Abby. “I don’t want this UAT review to turn into a complete re-write.”

“Hey, they’re the customers. If they feel something is missing or not working right, we’ll fix it for them.”

Abby shook her head. “Just come, and chime in if you hear anything you don’t like.” She slipped past on her way to the break room, empty coffee cup dangling from one finger while the other nine gripped a laptop and stack of functional specs.

This project was simple. One application allowed user entry of customer demographic data. One legacy system needed that data. The users wanted a web service to connect the two, and Bruce thought it an ideal assignment for fresh-from-college Dale to tackle. Veteran Abby was there to mentor, but Bruce now worried about Abby’s pessimism coloring Dale’s perceptions.

Bruce wanted his developers to be dedicated to getting the best possible results for the business. So, maybe the users would discover big changes in UAT. So what? That was what user acceptance testing was for. Bruce planned to be the positive influence on the call, not a force for “no.”

Abby, Dale and Bruce sat down together in a windowless conference room. They dialed in and suffered through muzak that sounded like Kenny G’s ode to suicide. Their wait dragged through multiple loops. Abby turned to her laptop, while Dale tugged at the collar of his ill-fitting shirt. Bruce sat with his notebook, pencil poised.

The business unit users called in ten minutes late. “Hi, Art!” Abby greeted, not looking away from her computer. “How’s UAT going?”

“Great, great. Looking good so far.”

Bruce was relieved. Dale smiled. Abby mouthed, “Wait for it…”

“Except… well, our data guys noticed the web service isn’t validating customer phone numbers.”

“Um, that wasn’t in the spec,” Dale said.

Abby nodded, still staring at her laptop. “You told us to mirror the functionality of the old DTS feed. The old feed doesn’t do any validation, it just passes it to your legacy store.”

“But that’s half the reason we wanted a new feed!” Art cried. “We don’t want bad data passed around!”

Bruce introduced himself to the call, and took the wheel. “Of course you don’t want bad data passed around,” he said with zen-like patience. “What validation do you need the service to perform?”

“Um, I’m not the person to ask,” Art faltered. “It was in that spreadsheet John sent out with his UAT comments.”

Abby’s turned her frown on the phone. “I didn’t get anything. Who’s John? Is he on this call?”

Noise burst onto the line. “Hey, this is John. Sorry, I was on mute. I’m on the bus.”

“Oh no, not you, John,” Art said. “I was talking about John McNeil.”

“OK, I’m going to go back on mute then. Bye,” John said.

“All right, is-” Abby began.

“See ya, John!” Art said. A chorus of goodbyes from the other users bounced around the conference call. A few people who were only paying half-attention to the call hung up, causing the conference system to spend two minutes announcing, “‘Bob Johnson’ has left the call.”

“Art!” Abby cried. “Is John McNeil on this call?”

“No, we didn’t think he needed to be at this meeting,” Art said.

“Can you forward the spreadsheet to me?” Abby asked.

“Sure, hang on a second…”

The sound of shuffling papers clogged the speaker. Someone quoted a line from a television ad, causing an eruption of laughter that spiraled into multiple off-topic conversations. The sound of a chair scraping followed as someone else loudly announced the need for more coffee.

Dale had detached with a thousand-yard stare. Abby muted the phone, then buried her head in the crook of her elbow.

“Just part of the job, guys,” Bruce said with a reassuring smile.

“I’d have better luck wrangling kindergarteners at a hyena rodeo.” Abby un-muted the phone and raised her voice. “Art? Don’t worry about it, OK? Can anyone explain how you want us to validate the phone number?”

“Sure,” an unnamed voice took over. “You have to make sure the string contains no non-numeric characters, and that it’s 10 digits in length.”

“What should we do if it fails?” Dale asked.

“Remove the non-numerics.”

“And if it’s too long?”

“Just take the first 10 characters.”

“And if it’s too short?”

“Prepend zeroes until it’s 10 characters long.”

“Wait- don’t you want an error telling you the data is bad?” Abby said.

“Oh, no!” the voice replied. “We don’t want any errorneous data, so we don’t want any errors. Errors are bad.”

Abby’s wide eyes targeted Bruce, pleading No! Bruce cringed, but montioned for Dale to keep going.

“If we do this, the data won’t be accurate,” Dale said. “You know there’s no area code ‘000’, right?”

“The legacy system requires phone numbers to be exactly 10 characters long.”

“But the customer UI that feeds the web service already validates the phone numbers,” Dale said.

“We can’t know that.”

Bruce wanted to be customer-first, but this? He rose to his feet, the authority in his voice rising accordingly. “Everyone, if I may: if we’re rebuilding this thing, we want to do it right. Adding zeroes to the front of a phone number is a, uh…” Stupid? Crazy? “In my professional opinion, it’s not the best way we could go about this. We don’t want to introduce extra code that will slow down the web service for everyone, do we?” Yes! That sounded good. “Dale, can you confirm the UI currently does the validation and not the web service?”

“Yeah,” Dale said.

“Great- Dale will email all of us with those details after the meeting,” Bruce said. “Is there anything else?”

A long pause followed, growing scarier as time passed.

“No,” Art finally said. “I think we’re good.”

Abby de-tensed, ended the call, then glanced sharply to the developers. “How does the UI currently validate?”

“Don’t worry, it’s nothing that bad,” Dale said.

“So we don’t have hundreds of area code 000 customers in production right now?” Abby asked.

“No.”

“Good,” Bruce said. “Don’t worry, we never will.”

toygeek writes "Earlier this year my family and I moved out into the woods, where high speed is simply not available. We traded in high speed for high latency, clean air and peace and quiet. We've made it work, and can even watch Netflix and Hulu while I'm off in another room working from home full time. Read along as I share some tips about how we've made it work, and the compromises we've had to make." It can be done; low-end DSL from AT&T is also what I somehow muddled through with for most of the last 18 months; though the connection often failed and the followup support was terrible, it worked well enough most of the time, and sure beat a 56K modem.

Read more of this story at Slashdot.

Nerval's Lobster writes "Tech publications and pundits alike have crowed about the benefits we're soon to collectively reap from healthcare analytics. In theory, sensors attached to our bodies (and appliances such as the fridge) will send a stream of health-related data — everything from calorie and footstep counts to blood pressure and sleep activity — to the cloud, which will analyze it for insight; doctors and other healthcare professionals will use that data to tailor treatments or advise changes in behavior and diet. But the sensors still leave a lot to be desired: 'smart bracelets' such as Nike's FuelBand and FitBit can prove poor judges of physical activity, and FitBit's associated app still requires you to manually input records of daily food intake (the FuelBand is also a poor judge of lower-body activity, such as running). FDA-approved ingestible sensors are still being researched, and it'd be hard to convince most people that swallowing one is in their best interests. Despite the hype about data's ability to improve peoples' health, we could be a long way from any sort of meaningful consumer technology that truly makes that happen."

Read more of this story at Slashdot.

jfruh writes "You may find it mildly creepy that Microsoft has a private police force, but the Digital Crimes Unit has helped real law enforcement do things like disrupt huge botnets. According to Richard Boscovich, assistant general counsel for the Digital Crimes Unit, Microsoft is only able to do all this by relying on the company's existing infrastructure, including its Azure cloud service. The DCU can provision compute time from the cloud as necessary to combat complex threats, he said, and also uses cloud services to share information with law enforcement agencies quickly."

Read more of this story at Slashdot.

At my current customer I was upgrading their vSphere 4.1 environment to vSphere 5.5. When installing and configuring the first ESXi 5.5 host, I noticed a difference in the Path Selection Policy (PSP) between the ESX 4.1 hosts and the ESXi 5.5 host. Each host is connected to two Hitachi storage arrays, each a different model. In the current 4.1 config a general setting is used to have both arrays use Round Robin:

esxcli nmp satp setdefaultpsp --psp VMW_PSP_RR --satp VMW_SATP_DEFAULT_AA

Because of the above rule in the ESX 4.1 hosts, all storage that is not captured by the existing rule sets, will be set to Round Robin (VMW_PSP_RR). Although applying this rule to my ESXi 5.5 host, would solve the problem for me on short term, I could imagine running into an issue when the customer would attach a third storage that didn’t required Round Robin, but would then somehow still default to it, because of this “catch-all” rule.

To write a very specific rule for this storage array I would need to find out what vendor and model this storage array is according to ESXi. This was actually the hardest part, since I was using the wrong command at first, but with some help of the VMware Community and specifically Tom Verhaeg, I found the right command. Let me take you through the steps.

Changing SATP Claimrule

Changing the SATP Claimrules is easiest when done on the command line of the ESXi host using the esxcli module. First we need to find out for a specific LUN, what the current path policy is by running the command below. The naa number used is the LUN that has the incorrect Path Selection Policy. You see that for this LUN the current policy is VMW_PSP_FIXED, since the Storage Array Type (SATP) is VMW_SATP_DEFAULT_AA according to ESXi.

COMMAND:

esxcli storage nmp device list -d naa.60060e80132892005020289200001001

RESULT:

 naa.60060e80132892005020289200001001
 Device Display Name: HITACHI Fibre Channel Disk (naa.60060e80132892005020289200001001)
 Storage Array Type: VMW_SATP_DEFAULT_AA
 Storage Array Type Device Config: SATP VMW_SATP_DEFAULT_AA does not support device configuration.
 Path Selection Policy: VMW_PSP_FIXED
 Path Selection Policy Device Config: {preferred=vmhba1:C0:T0:L37;current=vmhba1:C0:T0:L37}
 Path Selection Policy Device Custom Config:
 Working Paths: vmhba1:C0:T0:L37
 Is Local SAS Device: false
 Is Boot USB Device: false

The next step is to find out what storage array Vendor and Model type this LUN is coming from, because we need this info to create a new SATP claiming rule. Running the following command, shows that we’re working with Vendor = Hitachi and Model = OPEN-V.

COMMAND:

esxcli storage core device list -d naa.60060e80132892005020289200001001

RESULT:

naa.60060e80132892005020289200001001
 Display Name: HITACHI Fibre Channel Disk (naa.60060e80132892005020289200001001)
 Has Settable Display Name: true
 Size: 1048576
 Device Type: Direct-Access
 Multipath Plugin: NMP
 Devfs Path: /vmfs/devices/disks/naa.60060e80132892005020289200001001
 Vendor: HITACHI
 Model: OPEN-V
 Revision: 5001
 SCSI Level: 4
 Is Pseudo: false
 Status: on
 Is RDM Capable: true
 Is Local: false
 Is Removable: false
 Is SSD: false
 Is Offline: false
 Is Perennially Reserved: false
 Queue Full Sample Size: 0
 Queue Full Threshold: 0
 Thin Provisioning Status: yes
 Attached Filters: VAAI_FILTER
 VAAI Status: supported
 Other UIDs: vml.020025000060060e801328920050202892000010014f50454e2d56
 Is Local SAS Device: false
 Is Boot USB Device: false
 No of outstanding IOs with competing worlds: 32

Before creating the new rule, I wanted to check the current SATP rule list, so I could also check after creating the rule, if there was a change. For easy of reading I only display the results here that were important to me:

COMMAND:

esxcli storage nmp satp rule list

RESULT:

Name	Vendor	Model	Rule Group	Claim Options	Default PSP
VMW_SATP_DEFAULT_AA	HITACHI		system	inq_data[128]={0x44 0x46 0x30 0x30}	VMW_PSP_RR
VMW_SATP_DEFAULT_AA	HITACHI		system

The first line tells ESXi that if you find a storage of Vendor Hitachi with specific claim options “inq_data[128]                              ={0x44 0x46 0x30 0x30}” (which I don’t fully understand), then the VMW_PSP_RR policy should be used. The second line says to apply the system default connected to VMW_SATP_DEFAULT_AA for all Hitachi arrays. Let’s check what the default for VMW_SATP_DEFAULT_AA is, although we already know by what we’ve seen before.

COMMAND:

esxcli storage nmp satp list

RESULT:

Name                 Default PSP    Description
——————-  ————-  ——————————————
VMW_SATP_MSA         VMW_PSP_MRU    Placeholder (plugin not loaded)
VMW_SATP_ALUA        VMW_PSP_MRU    Placeholder (plugin not loaded)
VMW_SATP_DEFAULT_AP  VMW_PSP_MRU    Placeholder (plugin not loaded)
VMW_SATP_SVC         VMW_PSP_FIXED  Placeholder (plugin not loaded)
VMW_SATP_EQL         VMW_PSP_FIXED  Placeholder (plugin not loaded)
VMW_SATP_INV         VMW_PSP_FIXED  Placeholder (plugin not loaded)
VMW_SATP_EVA         VMW_PSP_FIXED  Placeholder (plugin not loaded)
VMW_SATP_ALUA_CX     VMW_PSP_RR     Placeholder (plugin not loaded)
VMW_SATP_SYMM        VMW_PSP_RR     Placeholder (plugin not loaded)
VMW_SATP_CX          VMW_PSP_MRU    Placeholder (plugin not loaded)
VMW_SATP_LSI         VMW_PSP_MRU    Placeholder (plugin not loaded)
VMW_SATP_DEFAULT_AA  VMW_PSP_FIXED  Supports non-specific active/active arrays
VMW_SATP_LOCAL       VMW_PSP_FIXED  Supports direct attached devices

What the admin of this environment did in the past, was changing this “overall” default to VMW_PSP_RR, which is not the approach I want to take. The above table explains why the ESXi 5.5 host is now working with the VMW_PSP_FIXED policy. We’re now ready to change the SATP rule using “HITACHI” as storage Vendor, “OPEN-V” as model type. We’re telling the ESXi to use VMW_SATP_DEFAULT_AA with a PSP of “VMW_PSP_RR” when Venor and Model match our specification:

COMMANDS:

esxcli storage nmp satp rule add -V HITACHI -M "OPEN-V" -P VMW_PSP_RR -s VMW_SATP_DEFAULT_AA
esxcli storage core claimrule load

To check how this worked out, we check the satp rule list again:
COMMAND:

esxcli storage nmp satp rule list

RESULT:  (filtered results)

Name	Vendor	Model	Rule Group	Claim Options	Default PSP
VMW_SATP_DEFAULT_AA	HITACHI		system	inq_data[128]={0x44 0x46 0x30 0x30}	VMW_PSP_RR
VMW_SATP_DEFAULT_AA	HITACHI	OPEN-V	user		VMW_PSP_RR
VMW_SATP_DEFAULT_AA	HITACHI		system

To check if this changed the way the policy was applied to the LUNs, run the command below. In the documentation I found that the host should check the claimrules every 5 minutes, but I decided to reboot the host after adding and loading the new SATP rule. Maybe I was just being impatient

COMMAND:

esxcli storage nmp device list -d naa.60060e80132892005020289200001001

RESULT:
naa.60060e80132892005020289200001001
Device Display Name: HITACHI Fibre Channel Disk (naa.60060e80132892005020289200001001)
Storage Array Type: VMW_SATP_DEFAULT_AA
Storage Array Type Device Config: SATP VMW_SATP_DEFAULT_AA does not support device configuration.
Path Selection Policy: VMW_PSP_RR
Path Selection Policy Device Config: {policy=rr,iops=1000,bytes=10485760,useANO=0; lastPathIndex=0: NumIOsPending=0,numBytesPending=0}
Path Selection Policy Device Custom Config:
Working Paths: vmhba1:C0:T0:L37, vmhba0:C0:T0:L37
Is Local SAS Device: false
Is Boot USB Device: false

WARNING: Changing Path Selection Policies or SATP rules, should only be done when you are 100% sure of what you’re about to do. Always check the VMware HCL to see what the prefered policy is for your storage array in combination with firmware and ESXi version. Also check what your vendor’s documentation recommends.

See full post at: Changing SATP Claimrules for specific storage configurations

As as we come to the end of another year, it is worth looking forward to see what if anything is going to change in the storage world next year because this year has pretty much been a bust as to innovation and radical new products. So what is going to change? I get the feeling not a huge amount. Storage growth is going to continue for the end-users but the vendors are going to continue to experience a plateau of revenues. As end-users, we will expect more for our money but it will be mostly more of the same. More hype around Software-Defined-Everything will keep the marketeers and the marchitecture specialists well employed for the next twelve months but don’t expect anything radical. The only innovation is going to be around pricing and consumption models as vendors try to maintain margins. Early conversations this year point to the fact that the vendors really have little idea how to price their products in this space; if your software+commodity-hardware=cost-of-enterprise-array, what is in it for me?  If vendors get their pricing right; this could be very disruptive but at what cost to their own market position? We shall see more attempts to integrate storage into the whole-stacks and we’ll see more attempts to converge compute, network and storage at hardware and software levels. Most of these will be some kind of Frankenpliance and converged only in shrink-wrap. Flash will continue to be hyped as the saviour of the data-centre but we’ll still struggle to find real value in the proposition in many places as will many investors. There is a reckoning coming. I think some of the hybrid manufacturers might do better than the All-Flash challengers. Hopefully however the costs of commodity SSDs will keep coming down and it’ll finally allow everyone to enjoy better performance on their work-laptops! Shingled Magnetic Recording will allow storage densities to increase and we’ll see larger capacity drives ship but don’t expect them to appear in mainstream arrays soon; the vibration issues and re-write process is going to require some clever software and hardware to fully commercialise these. Still for those of us who are interested in long-term archive disks, this is an area worth watching. FCoE will continue to be a side-show and FC, like tape, will soldier on happily. NAS will continue to eat away at the block storage market and perhaps 2014 will be the year that Object storage finally takes off.

[Disclaimer: CrashPlan in AU provided me with a free 12-month Family subscription and use of a seed drive. This isn't a paid review but clearly I've benefitted.] So, a short time after my post on Backblaze and Mozy and why I was going for the cheapest (but not necessarily nastiest) personal cloud backup solution, the Australian arm of CrashPlan got in touch and offered to help get me started with them. So I thought I’d do a post to cover off on some initial thoughts and feelings and provide some public feedback on how it went. Just a reminder, every product is different, and every user’s circumstances are different, so don’t complain to me if you find that CrashPlan isn’t for you. Additionally, I hope you appreciate just how hard it is to take photos that look this bad. So, the killer feature that CrashPlan offers for me, and residents of the US, is seeded backup. You can read more about how that works here. This was one of my complaints with Backblaze – I couldn’t get all of the data I wanted to up to the provider due to the extraordinarily shitty ADSL1 connection at my house. So gigabytes of home movies and other media were, beyond Time Machine backups, at risk. So, Adrian Johnson from Code42 offered me the use of a seeded backup drive, and I must say it’s been a really smooth experience. Again, here’re the rough steps, but you can look it up for yourself: Support contact me to confirm my details; Courier arrives with hard drive; I attach hard drive to computer and add it as a destination; I backup my stuff to hard drive; I box up hard drive and send by pre-paid courier back to CrashPlan; They contact me when they receive it; They contact me when seed data is uploaded at their end; I restart cloudy backup. Everything is pretty much there, barring a few new files from iPhoto; and Profit. It was pretty much that simple. So, here are some pictures to fill in the space where I should be offering thoughts. Firstly, I was mildly panicked when I saw that the drive was formatted as FAT32. It seemed like that would just suck as a transfer mechanism, especially for large files. And at that start of the process, it certainly looked like it was going to take some time. But the key thing with this service is compatibility. It is compatible with Mac OS X, Windows, Linux and Dots OS (?). I also found that by fiddling with some of the power saving settings on my Mac I was able to get the transfer speeds up to a more reasonable level. Also, like most backup products, lots of small files will choke the I/O, whereas big DV files go through at a healthy clip. Note also that this isn’t a straight file transfer. The data is being de-duped, compressed and encrypted. So, you know, that can take some time. Particularly on a 850GB backup set. So what’s in the box? You get: Instructions; A LaCie rugged drive (1TB); A USB3 cable; and A pre-paid courier satchel to send it back in. I took some photos, to make me look more like a tech journo. And, then, magically, a little over 2 weeks after the drive arrived, I have 850GB of my data in the cloud. Almost like magic. There are a few other things you can do with CrashPlan but I’ll look to cover those off in the next post. Because I’m tired now. In short, the NAS compatibility is cool (if you’re a QNAP owner – check this post out), as is the ability to send data to your friends. So, I’ll wrap up with some of what I thought were good things about the product. Firstly, I can pay in Australian dollars. This may not seem like a big thing, as we’ve had parity with the US for a while, but recently the dollar has dipped to 85 cents. So, on a $50 subscription, I pay, after fees and charges, $60. Which, isn’t that big a deal, but it’s enough to make me pause. Secondly, the access to local support and a seed drive service is fricking awesome. And support have been helpful and informative every step of the way. Thirdly, CrashPlan pricing, for unlimited storage, is pretty competitive. Here’s a link to the Australian offering. Whether they can sustain that pricing remains to be seen. As an aside, I often wonder what Mozy’s pricing would have been like if they hadn’t been bought by EMC. But that may have had nothing to do with it. So, in short, I’ve been really happy with my CrashPlan experience thus far, and am looking forward to doing some more stuff with it. I still won’t hesitate to recommend Backblaze to people, if it seems like a good fit for them, but I’m having a hard time arguing against a local presence and the somewhat parochial comfort that that provides. Thanks again to Adrian Johnson and the team at Code42 support for making this a really simple and effective exercise.

New submitter ttyler writes "It turns out a MacBook's built-in camera can be activated without turning on the green LED. An earlier report suggested the FBI could activate a device's camera without having the light turn on, and there was a case in the news where a woman had nude pictures taken of her without her knowledge. The new research out of Johns Hopkins University confirms both situations are possible. All it takes are a few tweaks to the camera's firmware."

Read more of this story at Slashdot.

Trailrunner7 writes "The NSA surveillance scandal has created ripples all across the Internet, and the latest one is a new effort from the IETF to change the way that encryption is used in a variety of critical application protocols, including HTTP and SMTP. The new TLS application working group was formed to help developers and the people who deploy their applications incorporate the encryption protocol correctly. TLS is the successor to SSL and is used to encrypt information in a variety of applications, but is most often encountered by users in their Web browsers. Sites use it to secure their communications with users, and in the wake of the revelations about the ways that the NSA is eavesdropping on email and Web traffic its use has become much more important. The IETF is trying to help ensure that it's deployed properly, reducing the errors that could make surveillance and other attacks easier."

Read more of this story at Slashdot.

Hi Friends,

 

Last week I was in talk with some junior DBA’s and they wanted to understand different types of locks in SQL Server with examples. I believe this question is very common for most of the new entrants. Locking is very important feature of any RDBMS which helps us in working in multiuser environments. In our day to day work we do come across different types of locks when we check SQL DMV’s and so on; it is very much important to understand them at first place before we start troubleshooting anything. I thought it would be good, if I post my demonstration here on my blog which may be helpful to the ones with similar questions on their minds.

 

Locking is not a physical problem and a logical one. I’ll explain some basic lock types with an example under READ COMMITTED isolation mode and as you can understand for simplicity, detailed explanation of all lock types are beyond scope of this blog post. I’ve divided my posts in two parts and today in Part 1 we will cover following lock types;

 

1. Shared Locks (S)
2. Update Locks (U)
3. Exclusive Locks (X)

 

The intention of this post is just to demonstrate lock modes with simple examples. We would be using sys.dm_tran_locks DMV to identify different lock types used in my example(s).

 

Shared Locks (S) : Shared locks are held when data is read under pessimistic concurrency model. Other transactions can read data but cannot modify records that are locked. When the locked data is read, shared locks are released. For demo, I’ve used WITH (HOLDLOCK) in select statement to simulate them;

 

 

 

Update Locks (U) : When you execute any DML statements, at first SQL engine has to find the data that it wants to modify; to avoid any lock conversion to deadlocks an update lock is used. You can say, these kinds of locks are a combination of shared and exclusive locks (covered next). Only one update lock is allowed on data at a time but this lock is unable to modify data on its own until converted to exclusive locks (covered next). I’ve used WITH (UPDLOCK) hint for demo purpose;

 

 

 

Exclusive Locks (X) : Exclusive locks are held to lock data that are modified by one transaction in order to prevent data modifications by other concurrent transactions. Only when you specify NOLOCK hint or read uncommitted isolation level you can read data. As discussed on update lock, for DML statements first data needs to read data before they modify them as such you will always observe exclusive locks after a shared lock on the data.

 

I’ll stop here today; watch out this space for next part where I will explore some more lock types with examples.

 

If you liked the post, do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks

Have a SQL Server question? Join the fastest growing SQL Server Facebook group at: http://www.facebook.com/groups/458103987564477/

 

Regards,

Kanchan

Hello Friends,

Following are the migration steps where initial level of SSRS understanding are pre required.

1)      Take RSReportServer.config file backup on 2012 instance.

2)      Take a backup for ReportServer and ReportServerTemp database from 2008 instance and copy these backup into 2012 instance.

3)      Take a backup for encryption key from report server configuration 2008 and copy this key into 2012 instance. Please enter solid password to save key.

 

 

4)      Connect to SQL Server 2012 instance.

5)      Stop SQL Server reporting services. Here we are considering that SSRS already installed into 2012 instance. Configuration of reporting services on 2012 is another topic.

  

 

6)      Open SQL Server Management studio and restore ReportServer and ReportServerTemp database there with over right option.

 

 

7)      Start SQL Server reporting services.

8)      Set database connection with ReportServer database with used service credential.

 

9)      Restore Encryption key on Report Services Configuration Manager. Use same password what used at time of backup (Step 2).

 

Copy Custom Configuration Settings to RSReportServer.config File:

If you modified the RSReportServer.config file or RSWebApplication.config file in the previous installation, you should make the same modifications in the new RSReportServer.config file.

 

Verify Report Server:

To verify Report Server are working fine please click on Report Manager URL which is already set on report service configuration manager. You may encounter an error like this -

Error:

 The feature: "Scale-out deployment" is not supported in this edition of Reporting Services.

Solution:

The easiest is open the ReportServer database in SSMS on the new server and open the Keys table. You should see an entry for the old server and the new server, delete the entry for the old server .

USE [ReportServer]

select * from dbo.Keys where MachineName = 'XXX-Old' – Check if old one exist

delete from dbo.Keys where MachineName = ' XXX-Old ' – Delete if exist

 

Check Subscription:

If reports have subscription then it should be copied over 2012 instance. You can check subscription on Report Manager URL. Just expand report and click on Manage option. Then you see window where select Subscription to see all for this particular report.

 

You can check subscription either click on Edit or New Subscription. If lt is good then you can Add/Edit subscription. Doing this might result in below error.

Error:

Subscriptions cannot be created because the credentials used to run the report are not stored.

Solution: You need to add user name and password into report data source into “Credentials stored securely into report server”. In case of domain account check the “Use as windows credentials when connecting to the data source” check box to avoid below error

Login failed for user “DOMAIN\USER NAME”

Below screen shot can help to understand settings.

 

Other than the above shown steps the important part is configuration file RSReportServer.config which is stored at:

\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer

Please take backup from target server before changes in report service configuration manager.

Thanks

If you liked the post, do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks

This summer we started blogging about all of the innovation in the products that make up the Microsoft Cloud OS vision.  We’ve talked about Windows Server 2012 R2, Windows Azure and of course Microsoft SQL Server 2014. 

In the case of SQL Server 2014, we did a series of posts over the past few months on In-Memory OLTP. SQL Server 2014 introduces memory-optimized database technology for optimizing the performance of OLTP workloads. In particular, it introduces memory-optimized tables for efficient, contention-free data access, and natively compiled stored procedures for efficient execution of business logic.    

Sound interesting?  There is a lot to learn so here is the complete list of those posts in the series:

• SQL Server 2014 In-Memory Technology Blog Series Introduction
• Getting Started with SQL Server 2014 In-Memory OLTP
• In-Memory OLTP: Q & A Myths and Realities
• Architectural Overview of SQL Server 2014’s In-Memory OLTP Technology
• SQL Server 2014 In-Memory OLTP bwin Migration and Production Experience
• Hardware Considerations for In-Memory OLTP in SQL Server 2014
• How In-Memory Optimized Database Technology is Integrated into SQL Server 2014
• SQL Server 2014 In-Memory OLTP App Migration Scenario Leveraging the Integrated Approach
• Improved Application Availability During Online Operations in SQL Server 2014
• Solving Session Management Database Bottlenecks with In-Memory OLTP
• New AMR Tool: Simplifying the Migration to In-Memory OLTP
• In-Memory OLTP Common Design Pattern – High Data Input Rate/Shock Absorber
• In-Memory OLTP Programmability: Concurrency and Transaction Isolation for Memory-Optimized Tables
• Concurrency Control in the In-Memory OLTP Engine
• Troubleshooting Common Performance Problems with Memory-Optimized Hash Indexes
• In-Memory OLTP: How Durability is Achieved for Memory-Optimized Tables

Since SQL Server In-Memory OLTP (formerly known as project code-named “Hekaton”) was unveiled at PASS last November, there has been a lot of conversation and buzz about it. Some of the discussion was well informed and accurate, and some… not so much.  We hope you find the list above and the information in those posts informative. 

Stay tuned for more information coming out of PASS Summit 2013.  We’ll certainly be sharing more on In-Memory OLTP and other innovations in SQL Server 2014, and we have many more blogs to come on the new features of SQL Server 2014!

Ready to download and try it for yourself?  Be sure and head over to the Try/Buy Center and take a lab or download the bits.  For one stop shopping on evaluation bits for all of the Microsoft products, head on over to the TechNet Evaluation Center. Enjoy!

Microsoft recently announced several new ways to back up and recover SQL Server databases with Windows Azure.  These features, now available in SQL Server 2014 CTP2 and as a standalone tool for prior versions, provide an easy path to cloud backup and disaster recovery for on-premises SQL Server databases.  The capabilities for backing up to Windows Azure Storage help to reduce storage costs and unlock the data protection and disaster recovery benefits of cloud data storage.

Benefits of the new SQL Server Backup to Windows Azure include:

• Cost-effective – Backing up to the cloud reduces CAPEX and OPEX by shifting from on-premises storage to Windows Azure Blob Storage Service.  Windows Azure offers lower TCO than many on-premises storage solutions and decreased administrative burden.
• Secure – Backup to Windows Azure adds encryption to backups stored both in the cloud or on-premises, for an extra layer of security and compliance.
• Durable – Backups and replicas in the cloud enable users to recover from local failures quickly and easily.  Windows Azure storage is reliable and built with data durability in mind: off-site, geo-redundant and easily accessible. 
• Smart – SQL Server can now manage your back up schedule using the new Managed Backup to Windows Azure feature. It determines backup frequency based on database usage patterns.
• Consistent – The combination of SQL Server 2014 in-box functionality and the Backup to Windows Azure Tool for prior versions create a single back up to cloud strategy across all your SQL databases.

Simplified Cloud Backup

There are several new capabilities enabling users to utilize SQL Server Backup and Disaster Recovery Windows Azure.

• SQL Server Backup to Windows Azure – With SQL Server 2014, users can easily configure Azure backup storage. In the event of a failure, a backup can be restored to an on-premises SQL Server or one running in a Windows Azure Virtual Machine. Options for setting up backup include:
  • Manual Backup to Windows Azure - Users can configure back up to Windows Azure by creating a credential in SQL Server Management Studio (SSMS). These backups can be automated using backup policy. 
  • Managed Backup to Windows Azure – Managed Backup is a premium capability of Backup to Window Azure, measuring database usage and patterns to set the frequency of backups to Windows Azure to optimize networking and storage.  Managed Backup helps customers reduce costs while achieving greater data protection. 
  • Encrypted Backup – SQL Server 2014 offers users the ability to encrypt both on-premises backup and backups to Windows Azure for enhanced security.

• SQL Server Backup to Windows Azure Tool - A stand-alone download that quickly and easily configures to back up to Windows Azure Storage for versions of SQL Server 2005 and forward. It can also encrypt backups stored either locally or in the cloud.

Learn More

SQL Server SQL Server 2014’s goal is to deliver mission critical performance along with faster insights into any data big or small. At the same time, it will enable new hybrid cloud solutions that can provide greater data protection and positively impact your bottom line. Early adopters are already leveraging new hybrid scenarios to extend their backup and disaster recovery capabilities around the globe without the need for additional storage replication technologies.

If you would like to try the Backup and Recovery Enhancements in SQL Server 2014 CTP2, Thursday’s blog post will help you get started configuring and using these new capabilities.  You can also preview the SQL Server Backup to Windows Azure Tool, enabling backup to Windows Azure for SQL Server 2005 and forward.

You can learn more about Hybrid Cloud scenarios in SQL Server 2014 by reading the SQL Server 2014 Hybrid Cloud White Paper, which is part of the SQL Server 2014 Product Guide. 

SQL Server 2014 CTP1 introduced hash indexes for memory-optimized tables. Hash indexes are very efficient for point lookups, when you know exactly the value you are looking for. However, they do not perform well if you need a range of value, for example a date range, or if you need to retrieve the rows in a particular order.

Memory-optimized nonclustered indexes are a new index type for memory-optimized tables in CTP2. Nonclustered indexes support retrieving ranges of values, and also support retrieving the table rows in the order that was specified when the index was created. They can be used to optimize the performance of queries on memory-optimized tables that use inequality predicates like ‘<’ and ‘>’, and queries using an ORDER BY clause.

Nonclustered indexes also support point lookups, but hash indexes still offer far better performance. You should continue to use hash indexes to optimize the performance of queries with equality predicates.

Hash Index Limitations

Consider, for example, the following (simplified) CREATE TABLE statement. The table SalesOrderHeader_inmem has a hash index on the primary key column ‘SalesOrderID’, as well as a hash index on the column ‘OrderDate’. The index definitions are bold-faced. Note that the BUCKET_COUNT should typically be set between one and two times the number of unique index key values.

CREATE TABLE [Sales].[SalesOrderHeader_inmem](
 [SalesOrderID] uniqueidentifier NOT NULL 
PRIMARY KEY 
 NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000) 
DEFAULT (NEWID()),
 [RevisionNumber] [tinyint] NOT NULL CONSTRAINT 
[IMDF_SalesOrderHeader_RevisionNumber] DEFAULT ((0)),
 [OrderDate] [datetime2] NOT NULL
INDEX ix_OrderDate HASH WITH (BUCKET_COUNT=1000000)
 ) WITH (MEMORY_OPTIMIZED=ON)

If you run a query of the form:

SELECT * FROM Sales.SalesOrderHeader_inmem WHERE SalesOrderID = @ID

SQL Server will use the hash index on the column SalesOrderID to quickly identify the row corresponding to the parameter @ID. Witness the Index Seek operation in the query plan:

 

Hash indexes are very efficient for point lookup operations, because they require only a simple lookup in a hash table rather than traversing an index tree structure, as is required for traditional (non)clustered indexes. If you are looking for the rows corresponding to a particular index key value, using a hash index is the way to go.

Now, hash indexes do have some limitations. Because of the nature of hash tables, rows appear in the index in random order. This means it is not possible to retrieve ranges of values, or to scan rows in any particular order. Therefore, hash indexes do not support Index Seek operations on inequality predicates, and do not support ordered scans. The former results in a full index scan, and the latter results in a scan followed by a(n expensive) sort operator. All this results in a potential performance degradation when using such queries with hash indexes.

Consider the following two example queries:

Query with an inequality predicate

SELECT * FROM Sales.SalesOrderHeader_inmem 
WHERE OrderDate > @Date

The plan for this query does not use the index on OrderDate; it simply includes full index scan for the primary index. This means that SQL Server will process all the rows in the table, and only later filter out the ones with OrderDate > @Date.

 

Query with an ORDER BY

SELECT * FROM Sales.SalesOrderHeader_inmem 
ORDER BY OrderDate

The plan for this query includes a sort operator, which is very costly: after scanning the rows, all rows will need to be ordered to obtain the desired sort-order.

 

Nonclustered indexes

The new memory-optimized nonclustered indexes support both Index Seek operations using inequality predicates, and ordered scans. Consider the following amended CREATE TABLE statement, which now includes a nonclustered index on the column OrderDate.

‘SalesOrderID’, as well as a hash index on the column ‘OrderDate’. The index definitions are bold-faced.

CREATE TABLE [Sales].[SalesOrderHeader_inmem](
 [SalesOrderID] uniqueidentifier NOT NULL 
PRIMARY KEY 
 NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000) 
DEFAULT (NEWID()),
 [RevisionNumber] [tinyint] NOT NULL CONSTRAINT 
[IMDF_SalesOrderHeader_RevisionNumber] DEFAULT ((0)),
 [OrderDate] [datetime2] NOT NULL
INDEX ix_OrderDate NONCLUSTERED
 ) WITH (MEMORY_OPTIMIZED=ON)

Note that the keyword NONCLUSTERED is optional in this example. Also note that the syntax for memory-optimized nonclustered indexes is similar to the traditional disk-based nonclustered indexes. The only difference is that with memory-optimized indexes you need to specify the index with the CREATE TABLE statement, while with traditional indexes, you can create the index after creating the table.

Consider now the following two queries:

Query with an inequality predicate

SELECT * FROM Sales.SalesOrderHeader_inmem 
WHERE OrderDate > @Date

The plan for this query now uses the index on OrderDate. And when inspecting the properties of the Index Seek operator, you will see the inequality predicate OrderDate > @Date. This means that SQL Server will only need to process the rows with OrderDate > @Date.

Query with an ORDER BY

SELECT * FROM Sales.SalesOrderHeader_inmem 
ORDER BY OrderDate

The plan for this query does not include a sort operator. SQL Server will scan the rows in the index in order; in this case the sort-order of the index is the same as the sort-order required by the query, thus no additional sort operator is needed.

 

Note that, in contrast to disk-based nonclustered indexes, memory-optimized nonclustered indexes are uni-directional. This means that they support index scans only in the order that was specified when the index was created. If the ORDER BY clause in the above example would require OrderDate in DESC order, the index ix_OrderDate could not be used.

Limitations on memory-optimized indexes

Memory-optimized indexes do have a few limitations in SQL Server 2014 that we hope to address in future versions. You do need to consider these limitations when deciding on an indexing strategy for your memory-optimized tables.

• At most 8 indexes – you cannot specify more than 8 indexes on a single memory-optimized table.
• BIN2 collations – when using n(var)char columns in an index key, the columns must have a _BIN2 collation. Using BIN2 allows very fast lookup, as this can be based simply on binary comparison. However, you need to consider the implications of using a BIN2 collation, such as case and accent sensitivity. For more details see the Books Online topic on Collations and Code Pages.
• NOT NULL columns in the index key – memory-optimized indexes do not support nullable columns; all columns in the index key must be declared as NOT NULL.

Guidelines for choosing indexes

A few simple guidelines to help choose the type of index you need for your application:

• If you need to perform only point lookups, meaning you need to retrieve only the rows corresponding to a single index key value, use a hash index.
• If you need to retrieve ranges of rows, or need to retrieve the rows in a particular sort-order, use a nonclustered index.
• If you need to do both, particularly if point lookups are frequent, you can consider creating two indexes: it is possible to create both a hash and a nonclustered index with the same index key.

Hash indexes require an estimate of the number of unique index key values, to determine an appropriate value for the BUCKET_COUNT – typically between 1X and 2X. Because nonclustered indexes do not require setting the BUCKET_COUNT, it can be tempting to simply use a nonclustered index instead of a hash index. However, this could lead to sub-optimal performance for point lookup operations. In many scenarios where point lookup performance is critical and there is no good estimate for the number of unique index key values, it is better to over-provision the BUCKET_COUNT: i.e., pick a very high bucket count that you know is going to be larger than the number of index keys. Note that over-provisioning does not affect the performance of point lookups, but under-provisioning does. However, over-provisioning does increase memory-consumption and it slows down full index scans.

For more details on usage of memory-optimized indexes see the Books Online article on Guidelines for Using Indexes on Memory-Optimized Tables.

Memory-optimized tables must fully reside in memory and can’t be paged out. Unlike disk-based tables where insufficient memory can slowdown an application, the impact to  memory-optimized tables upon encountering out-of-memory can be severe, causing  DML (i.e. delete, insert or update) operations to fail. While this adds a new dimension to managing memory, the application failure due to resource errors is not something new. For example, applications using disk-based tables can fail with resource errors such as running out of transaction log or TempDB or out of storage. It is the responsibility of DBAs/Administrators to make sure resources are provisioned and managed appropriately to avoid such failures. SQL Server provides a rich set of monitoring tools, including DMVs, PerfMon and XEvents to help administrators identify problems earlier so that a corrective action can be taken. Similarly, for memory-optimized tables, SQL Server provides a rich set of monitoring capabilities and configuration options so that you can manage your database/instance well and keep your application running smoothly.  The remainder of this blog walks thru each of the challenges and details how it can be addressed.

How do I estimate the memory needed?

This is the first question that you need consider when migrating an existing table(s) to memory-optimized table(s) or when you are considering a new application using memory-optimized tables. When migrating a disk-based table, you know, for example using sp_spaceused as described in http://technet.microsoft.com/en-us/library/ms188776.aspx ,  its current size so it is just a simple mathematical calculation to find the corresponding size for memory-optimized tables.  The key differences to be aware of are that memory-optimized tables cannot compressed like disk-based tables with ROW and PAGE compression, so, the memory-optimized table will likely be bigger. However, unlike indexes for disk-based tables, the indexes on memory tables are much smaller. For example, the index key is not stored with hash indexes and all indexes are, by definition, covered indexes. Please refer to http://msdn.microsoft.com/en-us/library/dn247639(v=sql.120).aspx for details. A more challenging task is to estimate the data growth.  While you can make a reasonable guess, the best way is the continuous to monitor the table size and the memory consumed by memory-optimized table (s) in your database and instance.  The same monitoring approach holds for new applications that are created with in-memory OLTP in mind.

How does SQL Server manage memory for memory-optimized tables?

The in-memory OLTP engine is integrated with SQL Server. Memory allocations to memory-optimized tables are managed by SQL Server Memory Manager and the allocated memory is tracked using familiar constructs and tools such as memory clerks and DMVs. The following DMV shows XTP memory clerks. The first row shows the memory allocated by system threads. The second row with name DB_ID_5 represents the consumers in the database objects and the third row with memory node-id 64 represents memory allocated to DAC (Dedicated Admin Connection).

-- Show the memory allocated to in-mempory OLTP objects

select type, name, memory_node_id, pages_kb/1024 as pages_MB

from sys.dm_os_memory_clerks where type like '%xtp%'

type                 name       memory_node_id pages_MB

-------------------- ---------- -------------- --------------------

MEMORYCLERK_XTP      Default    0              18

MEMORYCLERK_XTP      DB_ID_5    0              1358

MEMORYCLERK_XTP      Default    64             0

Also, there are new DMVs that can be used to monitor the memory consumed by the in-memory OLTP engine and memory-optimized tables. Please refer to http://msdn.microsoft.com/en-us/library/dn133203(v=sql.120).aspx for details.

Like any other memory consumer, the in-memory OLTP engine responds to memory-pressure, but to a limited degree. For example, the memory consumed by data and indexes can’t be released even under memory pressure. This is different than disk-based tables where an external memory pressure may cause the buffer pool to shrink which simply means there will be fewer data/index pages in memory. For this reason, it is all the more important to provision the memory for memory-optimized tables appropriately, otherwise in-memory OLTP engine  can starve other memory consumers including the memory needed by SQL Server for its operations which can ultimately leads to slow or unresponsive application. To address this, SQL provides a configuration option to limit the memory consumed by memory-optimized tables.

How can I limit memory consumed by memory-optimized tables?

Starting with SQL Server 2014, you can bind a database to a Resource Pool. This binding is only relevant when the database has one or more memory-optimized table. The memory available in the resource pool controls the total memory available to memory-optimized tables in the database.

For example, create a resource pool, mem_optpool as follows

CREATE RESOURCE POOL mem_optpool WITH (MAX_MEMORY_PERCENT = 40);

Now map the database, mydatabase, to this resource pool by executing the following command. With this command, you are specifying that the total memory taken by memory-optimized tables and indexes cannot exceed the limit in the resource pool. So for this case, the other 60% memory is available to other consumers.

EXEC sp_xtp_bind_db_resource_pool 'mydatabase', 'mem_optpool'

When configuring memory for memory-optimized tables, the capacity planning should be done based on MIN_MEMORY_PERCENT, not on MAX_MEMORY_PERCENT. This provides more predictable memory availability for memory-optimized tables as pools that have the min_memory_percent option set can cause memory pressure notifications against other pools to ensure the minimum percentage is honored.. To ensure that memory is available for the In-Memory OLTP database and help avoid OOM (Out of Memory) conditions, the values for MAX_MEMORY_PERCENT and MIN_MEMORY_PERCENT should be the same. SQL Server target memory is dynamic relative to the OS and setting a minimum memory would be recommended only if the server is not dedicated. For details, please refer to http://msdn.microsoft.com/en-us/library/dn465873(v=sql.120).aspx.

How does SQL Server reclaim memory taken by deleted/updated rows

The rows for memory-optimized tables are stored in-memory and are linked through Hash and non-clustered indexes as described http://msdn.microsoft.com/en-us/library/dn133190(v=sql.120).aspx. Concurrent access to memory-optimized table uses optimistic concurrency control  based on row versions.   Over time, the existing rows may get updated (update operation generates a row version(s)) and deleted but these rows can’t immediately be removed as there may be concurrent transactions that need these rows versions. These older row versions are garbage collected (GC’d) asynchronously when it is determined, based on the active transactions, that they are no longer needed. There is a GC system thread that shares the row version cleanup (i.e. GC) with user transaction activity to ensure that SQL Server is able to keep up with the GC.   When you configure the memory for your workload, you must account for additional memory needed for stale row versions. You can roughly estimate the memory needed for stale row versions using the following steps:

1. Find the average number of rows updates/deleted by querying DMV dm_db_xtp_object_stats  at the beginning and end of a desired duration for your peak workload, and then compu
2. Estimate the number (NR) of row versions = (duration of the longest transaction accessing HK tables in seconds) * (average number rows updated/deleted/sec)
3. Assuming that delete/update operations were uniformly distributed across all memory-optimized tables, find the average row length RLen (including row header and index pointers).  You can now estimate the memory for row versions as (NR * Nlen)

You can use DMVs and Perfmon counters to monitor the progress of Garbage collection. Please refer to http://msdn.microsoft.com/en-us/library/dn133203(v=sql.120).aspx.

With the release of SQL Server 2014 CTP2, you can now significantly boost the performance of your OLTP workloads in Windows Azure Virtual Machines. By creating a new VM with our preloaded image of SQL Server 2014 CTP2 on Windows Server 2012 R2, or installing SQL Server 2014 CTP2 on your VM, In-Memory OLTP functionalities are immediately available to you. This blog post provides a good guide on how to create a Windows Azure VM.

However, since the transition to In-Memory OLTP is not as simple as flipping a switch, you must carefully evaluate your application scenario and see if it is the right solution for you.

Recommended Scenarios

For SQL Server 2014 CTP 2, we recommend the following scenarios for SQL Server In-Memory OLTP on a Windows Azure Virtual Machine:

• Development and Test scenarios, e.g. familiarizing with In-Memory OLTP’s capabilities. The on-demand provisioning of Windows Azure VMs and its low cost make it easy to gain full access to In-Memory OLTP functionalities without a large capital investment. Once you are comfortable with its capabilities and understand its limitations, you can move to deploy In-Memory OLTP on your local server or keep using the VM if it suits your business needs.

• Scenarios with relaxed data persistence requirements, e.g. web browser session state. SQL Server In-Memory OLTP provides non-durable tables that are perfect for transient data regardless of the transaction read/write mix. By completely eliminating I/O operations, non-durable tables could provide amazing boosts to performance for your non-persistent workload. If some persistence is still required but strict durability is not, you can leverage the new Delayed Durability features we have added to CTP2. Delayed Durability commits transactions but do not immediately write the log records to disk, lightening log I/O pressure by allowing larger and less frequent log writes to disk. For more details see the Books Online topic here.

• Read-mostly and read-only scenarios, e.g. an online product catalogue. In-Memory OLTP provides extreme performance and parallelism in read-mostly and read-only scenarios due to new algorithms and data structures optimized for memory-resident data. Furthermore, native compilation for stored procedures can dramatically increase CPU efficiency and throughput.

• Durable read-write scenarios not under log I/O pressure, e.g. workloads with heavy contention. In-Memory OLTP can also provide benefits for workloads with full durability requirements that is not under pressure from log I/O latency or throughput. Because In-Memory OLTP eliminates page contention by using an optimistic concurrency system, it could provide significant boosts to parallelism for your workload if it is suffering from contention problems. In addition, native compilation can improve the speed of a stored procedure with heavy business logic processing.

However, if your workload is suffering from long log I/O latency or if it is under pressure from log I/O throughput, and at the same time you require strict durability of your data, In-Memory OLTP on Windows Azure VM will not alleviate these problems.

SQL Server 2014 CTP2 on Windows Azure VM is not suitable for testing the performance of applications deployed in machines with similar configuration on premise.

Selecting the Right Windows Azure VM Size

The selection of VM sizes is important for any workload running in the IaaS space, whether you are provisioning a new VM using our preloaded CTP2 image or adding CTP2 onto an existing instance. Windows Azure provides a selection of virtual machine sizes for a variety of purposes, as listed in this article.

Since In-Memory OLTP is designed to serve extreme high session concurrency and the nature of memory-resident data requires sufficient memory space, we recommend the following Windows Azure VM sizes for adopting In-Memory OLTP:

The exact instance size that you will choose will depend on the scenario you wish to run and the size of data you wish to make memory-resident in SQL Server In-Memory OLTP. We recommend provisioning 100% more memory than the estimated size of data in memory and leave sufficient space for the buffer pool used by disk-based workloads on the same machine. This article on SQL Server Books Online has more information on how to estimate the size of a table in memory, and we have published a blog post on hardware considerations for In-Memory OLTP, some of which apply to provisioning VMs as well.

Virtual Machine Configuration

To configure a Windows Azure Virtual Machine for best performance in In-Memory OLTP, we suggest that you should follow the best practices outlined in this whitepaper. A summary of key considerations, plus some unique attributes for In-Memory OLTP, are listed below:

• Use a single storage account for all disks attached to the same VM.
• Do not turn on disk host caching for the data and log drives.
  When creating new drives for a Windows Azure VM, the disk host caching option is turned off by default. We do not recommend you change this option.
• Do not use the Temporary Disk (D: Drive) for log storage.
  This will sacrifice durability for your database, as the content on the Temporary Disk is transient and may be lost on a VM failure or reboot. Please use a persistent Windows Azure Storage disk for your log storage needs.
• Do not use native operating system striping for storing the log of an In-Memory OLTP-enabled database.
  Logging memory-optimized tables is latency-sensitive, and striping of drives increase the latency of I/O operations. If you wish to use any form of striping for your log drive, you should test your configuration to ensure that the I/O latency is sufficiently low.
• Compression is not supported for In-Memory OLTP tables. Compression will still work with regular tables in an In-Memory OLTP-enabled database.

Those of you who have tried the new version of SQL Server Management Studio might have noticed already that it has a couple of new wizards added. One of those wizards is sitting next to the already existing that has been allowing you to deploy to a Windows Azure SQL Database service (former SQL Azure) and it is called Deploy Database to a Windows Azure Virtual Machine (VM).

You might be wondering why this wizard is here and what it can do. The blog post below tries to provide the answer to this and some of the related questions.

The Path to Windows Azure

We have seen many time that when people are trying a Virtual Machine in the Windows Azure environment they quickly come up with a number of questions. Once the first set of questions is resolved the next wave of questions come up, then the next wave, and a few next waves.

I have tried to compile the list of things you could expect.

1. Get access to a Windows Azure.
   Logging into a Windows Azure Portal might not be enough to unlock the full power of the Azure. In majority of cases you would need a Management Certificate. There is nothing special about it, except it should be available on the machine you are using, and it should be known to the Windows Azure. This means you should create a certificate somehow. Alternate option is to download Publishing Profile from the Portal. In this case Portal will generate certificate on your behalf, add it to the subscriptions you have access to, and share it with you in the form of XML file.
2. Create and configure your VM.
   When you have an access to the Azure environment you can start creating other pieces you are needed. The first thing to create is a VM. Don’t forget that you need a VM that:
   1. Has a SQL Server in it and version of the SQL Server is the same or higher as the version of SQL Server you have currently, otherwise your deployment capabilities will be very limited.
   2. Has an Azure Drive attached, you will need it to store your data.
   3. In case VM was created in a Cloud Service it should have endpoints configured properly, otherwise please make sure you have Azure VNet and corresponding VPN.
   4. You should be able to connect to the SQL Server instance meaning connections should be accepted by the SQL Server and customer should be able to authenticate. In case your machine is not joined to domain this means only SQL authentication is available, so don’t forget to set up a SQL login in advance to the instance configuration.
   5. Windows firewall should allow connections to the SQL server to allow them to go though.
   6. Plus many other steps needs to be taken.
   7. Once your target system is set up you need to focus on copying data from the source system. The best option that gives you the most of the recovery capabilities it to use the Azure Storage as an intermediate location. In this case you either copy your files directly using Backup to URL, or backup them locally and then use Azure SDK to upload them to the storage.
   8. Once data is in the cloud you need to get it to the VM. Depends on the data size you can either pull it to the VM and restore or just use Restore from URL.

Those steps are describing the things need to happen to end up with your workload in Windows Azure VM. There are multiple small details in this process, same as a lot of things to consider. You can consult this article if you need more information on the subject.

The Wizard

You might have question how the process above is related to the wizard described in this post. This is exactly what Deploy Database to a Windows Azure VM wizard does for you, plus a few additional convenience items.

Let’s see how this looks in the wizard.

The first screen tries to provide you an idea of what information might be useful for you during the use of the wizard.

 

The Source Settings screen only want you to provide with two pieces of information:

• Connection to the source server and database
• Where to place backup files (file or UNC path). Please note: this path should look the same for the wizard and the Database Engine

 

Once you told the wizard where to get the data from you might want to connect to Windows Azure. There are 3 different options:

• Manually provide with the management certificate and paste or type a subscription ID
• Import a publishing profile if you have downloaded it already
• Use you Microsoft Account (former Windows Live ID) to sign in to the portal and allow the Wizard to retrieve publishing profile behind the scenes for you

 

Once all authentication information is in place we connect to a Windows Azure environment, so you can start configuring it. There are a few things you could do:

• Type a new Cloud Service name (or select it from the list if you already have one)
• Type a new VM name (same as before, you can select it from the list if there are some to select from)
• Select the storage account which is in the same region with the VM (if you don’t have any, don’t worry – wizard will create an account for you)
• Press the Settings… button (One of the most important steps). This button behave differently depending on whether this is a new VM or an existing VM, but in all cases the button needs to be pressed to enter the information that is important for the process.

 

When you type a new VM name and press the Settings… button a new dialog comes up. If you have seen Windows Azure Portal before you should be familiar with most fields in this new dialog.

 

Please note: we are trying to warn you if we think that the target SQL Server instance might be lower version than the source instance. Sometimes those are hard errors that doesn’t allow you to continue, when we know for sure that the final configuration will not be compatible, while sometimes we can only guess. In either case the suggestion is to do not ignore those warnings:

 

When you select the VM that has existed before this makes the dialog look differently.

Please note two things here:

• This is one of the first times SQL Server Cloud Adapter comes up on the screen. This is a new component that runs as a service inside a VM (you can run it on the physical machine too) and does all the magic of the VM configuration. We will spend a bit of time on its capabilities and restrictions a little later.
• Since single Cloud Service can contain multiple VMs sharing the same public IP – this means Cloud Adapter Port might be different for those VMs. Also as the previous phrase suggests this is a public port.

Once you chose settings for a new VM or connected to an existing one the Target Database section became active. Please note that Database name field is editable meaning you can change your mind about database name on the target machine.

 

Now you are just two easy steps away from having your database in the cloud. The first step is to confirm the selection you have made when following the wizard:

 

And a second step is to wait until process completes. Depends on the database size and your connection speed the time might differ, but wizard will make sure it is done.

 

And finally you would see the confirmation screen that also contains a link to the detailed log.

 

This log is important for most of the troubleshooting actions, same as for digging for details.

The Result

Deploy Database to a Windows Azure VM wizard has guided you through the deployment process. Now this is the right time to observe what the end result is look like.

1. You have Virtual Machine running in Windows Azure
2. This VM has SQL Server instance configured
3. You database is deployed to the instance

However, a few manual steps might be needed.

1. You need to add a logins and give those logins access to the database the wizard has deployed.
2. If your application would be connecting to the VM from outside – make sure to configure the SQL Server to allow the connectivity.

Finally

The Deploy Database to a Windows Azure VM wizard is in place to simplify the steps needed to have a database in a Windows Azure VM environment. The whole wizard is made around the scenarios when you need your database in the cloud as soon as possible and willing to skip some studying or preparation steps as a tradeoff for the speed of the deployment. The other assumption was that you don’t want to do a deployment manually and prefer SQL Server to do some of this work for you.

The wizard was made as an extremely safe to your data. There is no chance it could delete, overwrite, or even modify your data. All operations it does are read only for the data.

We really hope this new feature will help you to save time during your deployments.

I have made quite a few updates and improvements to this set of queries for December 2013. I have added several new queries, and changed the order of some of the existing queries in the set, which now has a total of 63 separate queries. I also spent some time this month to true up the SQL Server 2014 version and the SQL Server 2012 version for the queries that are common between both versions.

The blank results spreadsheet has also been updated to match. You can download the queries and the results spreadsheet from the links below:

SQL Server 2012 Diagnostic Information Queries

SQL Server 2012 Blank Results

About half of these queries are instance specific and about half are database specific, so you will want to make sure you are connected to a user database that you are concerned about instead of the master system database.

The basic idea is that you would run each query in the set, one at a time (after reading the directions). You will need to click on the top left square of the results grid to select all of the results, and then right-click and select “Copy with Headers” to copy all of the results, including the column headers to the Windows clipboard. Then you paste the results into the matching tab in the results spreadsheet. There are also some comments on how to interpret the results after each query.

Note: These queries are stored on Dropbox. I occasionally get reports that the links to the queries and blank results spreadsheets do not work, which is most likely because Dropbox is blocked wherever people are trying to connect. I also occasionally get reports that some of the queries simply don’t work. This usually turns out to be an issue where people have some of their databases in 80 compatibility mode, which breaks many DMV queries.

There is an initial query in each version that tries to confirm that you are using the correct version of the script for your version of SQL Server. In this case, you need to have SQL Server 2012 RTM (or newer) to pass that check.

Please let me know what you think of these queries, and whether you have any suggestions for improvements. Thanks!

If you know someone who would benefit from our IE0: Immersion Event for Accidental/Junior DBA or IEHW: Immersion Event on SQL Server Hardware, refer them to us and when they register we’ll send you a $50 Amazon gift card. Either they or you just need to let us know you referred them, we’ll match it up to their registration and send you the gift card.

The post SQL Server 2012 Diagnostic Information Queries (December 2013) appeared first on Glenn Berry.

As I have been giving demos on Power BI for Office 365, I have received a tremendous amount of positive feedback, with many clients really loving it.  There are common questions that I hear, and I thought I would list them here with answers:

Will there be an on-premise version of Power BI for Office 365?

Power BI for Office 365 is an umbrella name for numerous technologies and tools that blend well together.  Most of the tools are strictly on-premise versions: Power Query, Power Pivot, Power View, Power Map, and Data Management Gateway.  There are three tools that are cloud-only versions (they only work with SharePoint Online): Power BI Sites, Power BI for mobile, and Power BI Q&A.  Microsoft’s strategy with new tools is “cloud first”, which gives the benefit of being able to have monthly updates.  Microsoft has not announced if there will be an on-premise version of these tools (meaning if they will work with on-premise SharePoint).  See Power BI for Office 365 requirements for more details.

What is stored on the cloud when using Power BI for Office 365?

You will have a SharePoint Online site that has the app “Power BI Sites” that is used to search shared queries and data sources.  Search is done via a Data Catalogue that indexes the metadata from the shared queries and optionally indexes the data.  You can also optionally upload workbooks to the Power BI Sites to share with others as well as use for use with Power BI Q&A feature.

Eventually there will be the ability to have data move from an on-premise source (via the Data Management Gateway) through the Power BI Sites to an external user (a user outside your network).  Right now this feature is not available as you can only route data to an internal user (one that is part of your network) in which case the data is not routed through the Power BI Sites but instead goes directly from the on-premise source to the internal user (the Data Management Gateway is smart enough to notice the user is internal and to not waste sending the data to the Power BI Site).

What will I miss out on with Power BI if our company does not allow SharePoint Online?

Because Power BI Sites does not have an on-premise version, for those companies that won’t or can’t use SharePoint Online, they will not be able to search on-premise data sources exposed by the Data Management Gateway via an OData feed and won’t be able to share queries created with Power Query (since they both require Online Search in Power Query which requires the metadata in the data catalogue, which is in the cloud via a Power BI site).  They also can’t use Power Q&A or Power BI for mobile, so much of the “power” in “Power BI” is not available to them.

What is the benefit of being able to share queries with Power Query?

This is a huge benefit!  Think of the old way of sharing queries: users write T-SQL queries and save them in .sql files out on a network folder, or they write the queries in Excel and save them in a network folder.  If you need to create a query and want to see if someone else has previously created something similar, you browse the network folder and hope the file name gives you a clue as to what data the query uses.  What a pain!  With Power Query, all the metadata about the query (query name, description, column names) is stored in a Data Catalogue in the cloud (via the Power BI site), and any user can use the Power Query Online Search feature to easily see if a query they need already exists.  You can even preview the data to see if it what you need.  Not having a data dictionary or metadata catalog has always been a missing piece in the Microsoft toolset, and that has been rectified with the Data Catalogue.

What is the benefit of exposing on-premise data sources via the Data Management Gateway (DMG)?

DMG allows the cloud to connect to on-premise data sources.  The way it does this is by exposing data from the on-premise data source as a OData feed, where the metadata for the OData feed (data source name and description, table names, column names) is automatically indexed in the Data Catalogue in the cloud (via a Power BI site).  Then, any user can use the Online Search feature in Power Query to search for data without having to know any server names or connection info.  Even better, they can search for data sources by data source name/description, table names, or column names.

Let’s use an example to better illustrate:  Say there are three servers, each with SQL Server.  Each has a database with various tables and users would like to pull data from all three databases via a query.  Let’s say customer info is spread across all three servers: one server has customer info that includes customer number and customer name, another server has customer number and customer address, and another server has customer number and customer phone.  A user wants to merge the data from all three servers into one table.  How would they do that now?  The common solution is a DBA exports those three tables into text files and puts them in a network folder for the user to import.  Or the DBA merges the data together into a SQL Server table somewhere and gives the user access to it.  Either way, it requires that the user gets the DBA involved, and you wind up with many copies of the data after similar requests are made by different users.

But if you use a Power BI site and DMG, any user can use the Online Search in Power Query and enter keywords like “customer number” and those three customer tables from the three different servers will show in the query results.  The user can then create a query for each one, and then merge those three queries together, all done easily inside Excel via Power Query.  The DBA does not need to get involved, so this is true “self-service BI”.  You can even preview the data to see if it what you need.  Not having a data dictionary or metadata catalog has always been a missing piece in the Microsoft toolset, and that has been rectified with the Data Catalogue.

Is the Data Management Gateway (DMG) still useful if it is against company policy to store metadata in the cloud?

No, unfortunately not.  The DMG needs to register with the Power BI Admin Center, which is only available via SharePoint Online, and will always index the metadata (there is not a way to turn that off).  So there is not a way to use the DMG without using SharePoint Online and indexing the metadata.

What type of security is used for the various tools?

1. Data Management Gateway enables you to expose the data from on-premises data sources as an Open Data Protocol (Odata) feed.  This data can be accessed either using HTTP or HTTPS.  When using HTTPS, a SSL certificate is required
2. Power Query adheres to the Crypto Mobility guidelines, according to the Microsoft SDL Process, by encrypting local credentials using DPAPI
3. SharePoint Online.  Explained at Is My Data Secure In SharePoint Online? and Security in Office 365 Whitepaper
4. Power BI Sites – Follows the same security as SharePoint Online
5. Data Catalogue – Follows the same security as SharePoint Online
6. Server connection info to data sources stored on SharePoint online – Follows the same security as SharePoint Online

When will Power BI for Office 365 be released?

Two of the tools are already released: Power Pivot and Power View.  The tools still in beta are Power Query,  Power Map, Power BI Sites, Power BI for mobile, Power BI Q&A, and Data Management Gateway.  Power Query has had monthly updates with the other tools having updates every 2-3 months.  Microsoft has not announced when these tools will be released, but all are quite stable based on my use of them.

A couple of weeks ago I gave you some scripts to collect wait stats from your SQL Server instance over time. Assuming that you downloaded those scripts and started using them right away, you now have two weeks worth of wait stats data collected. Do you know what to do with it?

It’s T-SQL Tuesday time again. The topic this month is “waits”. Just my luck – I wrote an amazing post on waits two weeks ago. Since reposting that one would violate the rules of T-SQL Tuesday, I wrote a followup! Hopefully you’re using the scripts from that earlier post and have collected some wait stats data.

Collecting all of that data means nothing if you don’t know how to make use of it. Your boss and your users don’t care – they just want to know why the server is slow now, or why it was slow yesterday. As a followup to that previous post, I’m going to show you some simple ways to do that using the data that you’ve been collecting.

Let’s start with some simple analysis – over the past seven days, what has our SQL instance spent the most time waiting on? Run this query in the database where you’re writing the wait stats data:

-- How many days past to report on?
DECLARE @PreviousDays INT = 7;

DECLARE @Command VARCHAR(MAX);

-- Build dynamic UNION statement to combine collection tables
WITH Dates_CTE (DateValue)
AS
	(
		SELECT DateValue = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
		UNION ALL
		SELECT DateValue = CAST(DATEADD(DAY, -1, DateValue) AS DATE)
		FROM Dates_CTE
		WHERE DateValue > DATEADD(DAY, -@PreviousDays, GETDATE())
	)
SELECT @Command = STUFF((
	SELECT ';SELECT * FROM WaitStats_Log_' + CONVERT(VARCHAR(25), DateValue, 112)
	FROM Dates_CTE
	FOR XML PATH('')), 1, 1, '');

-- Build dynamic SQL statement to sum the top 10 wait types
SET @Command = '
SELECT TOP 10 WaitType, TotalWaitSeconds = SUM(Wait_Seconds)
FROM (SELECT * FROM WaitStats_Log UNION ' + REPLACE(@Command, ';', ' UNION ') + ') AS TotalWaits
GROUP BY WaitType
ORDER BY SUM(Wait_Seconds) DESC;';

-- Execute dynamic SQL statement
EXECUTE(@Command);

You should get back some data that maybe looks something like this:

What does this tell us? Well, it tells us a couple of things. First, it tells us that this SQL instance spent more time waiting for network I/O than anything else. It also tells us that it spent less than 2% of the week (there are 604,800 seconds in a week) waiting on that network I/O. Unless somebody is complaining, I’m done here – I’m not chasing this any further for 2%.

For sake of example however, if somebody were complaining about performance, and these were the results from my SQL instance, I can comfortable say “it’s not a database problem”. Unless my server is experiencing network issues, which will be causing me other problems, these results point to an application problem. When an application requests data from SQL Server, the data is collected (typically by running a query) and then returned to the application. As SQL Server returns the data, it waits for the application to acknowledge that it received the data. This waiting is reported as ASYNC_NETWORK_IO. If the application can’t consume the data fast enough, we’ll see high ASYNC_NETWORK_IO waits.

Ok, that’s great, but why was the server slow between 3:00am and 4:00am this morning? Can you answer that, smart guy? Yep:

SELECT WaitType, TotalWaitSeconds = SUM(Wait_Seconds)
FROM WaitStats_Log
WHERE CollectionTime >= DATEADD(HOUR, 3, CAST(CAST(GETDATE() AS DATE) AS DATETIME))
	AND CollectionTime < DATEADD(HOUR, 4, CAST(CAST(GETDATE() AS DATE) AS DATETIME))
GROUP BY WaitType
ORDER BY SUM(Wait_Seconds) DESC

Seems pretty obvious – backups run at 3:00am. I think we can blame the slowness on backups. Nothing to see here, move along.

Taking a proactive stance – in terms of wait time, what has been the worst hour over the past week? Easy to figure that out since we have supporting data:

-- How many days past to report on?
DECLARE @PreviousDays INT = 7;

DECLARE @Command VARCHAR(MAX);

-- Build dynamic UNION statement to combine collection tables
WITH Dates_CTE (DateValue)
AS
	(
		SELECT DateValue = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
		UNION ALL
		SELECT DateValue = CAST(DATEADD(DAY, -1, DateValue) AS DATE)
		FROM Dates_CTE
		WHERE DateValue > DATEADD(DAY, -@PreviousDays, GETDATE())
	)
SELECT @Command = STUFF((
	SELECT ';SELECT * FROM WaitStats_Log_' + CONVERT(VARCHAR(25), DateValue, 112)
	FROM Dates_CTE
	FOR XML PATH('')), 1, 1, '');

-- Build dynamic SQL statement to sum the wait types by hour
SET @Command = '
SELECT TOP 10 TargetHour = DATEADD(HOUR, DATEDIFF(HOUR, 0, CollectionTime), 0), TotalWaitSeconds = SUM(Wait_Seconds)
FROM (SELECT * FROM WaitStats_Log UNION ' + REPLACE(@Command, ';', ' UNION ') + ') AS TotalWaits
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, CollectionTime), 0)
ORDER BY SUM(Wait_Seconds) DESC;';

-- Execute dynamic SQL statement
EXECUTE(@Command);

From this, we can see that the 4:00am hour last Monday was the worst hour in the past week. We can also see that 4:00am is routinely the worst hour of the day – might be worth investigating.

First, let’s see what was going on last Monday between 4:00am and 5:00am. To find out, we’re going to use the other collection table that I showed you in the previous article – the one collecting output from sp_WhoIsActive. Specifically, we’re interested in the collection table from 12-02-2013:

SELECT *
FROM WhoIsActive_Log_20131202
WHERE collection_time >= '12/2/2013 04:00'
	AND collection_time < '12/2/2013 05:00'
ORDER BY CPU DESC

Sorry, I can’t show you the full query (it’s a secret), but from what you see here we can tell a lot. Between 4:00am and 5:00am on December 2nd, multiple UPDATE statements were executed, each taking longer than 10 minutes to run. The longest took 15 minutes. Let’s check the 4:00am-5:00am hour on a different day:

SELECT *
FROM WhoIsActive_Log_20131208
WHERE collection_time >= '12/8/2013 04:00'
	AND collection_time < '12/8/2013 05:00'
ORDER BY CPU DESC

Again, a series of UPDATE statements taking 10-15 minutes to complete. Some of these we recognize from the collection from December 2nd. If *I* were analysing this SQL instance, I would suspect a nightly batch process of some kind that runs at 4:00am. At the very least, I have a set of queries that I can look into tuning.

What you’ve seen here really just scratches the surface of what you can do with wait stats analysis. By collecting wait stats on your server over time, combined with the active processes during that same time, you can easily spot potential problems on your own, or dig deep into user complaints of poor performance.

Setup your collection scripts, start collecting data, and then USE the data that you’re collecting. If you’re not using the data, you’re doing it wrong…

The post Don’t Bring a Tank to a Prison Fight – #TSQL2SDAY #49 appeared first on RealSQLGuy.

I stumbled upon an interesting little nuance of SQL Server’s behaviour over the past few days that I was not aware of and I figure its worth talking about it here so others are aware. It concerns the handling of the german character “ß” which I understand to be german shorthand for “ss” (I’m no expert on language or linguists so don’t beat me up if I’m wrong about that).

In short, two NVARCHAR values that differ only by one using “ß” and the other using “ss” will be treated as the same. This is easily demonstrated using the following code snippet:

SELECT 'Oktoberstrasse'
UNION
SELECT 'Oktoberstraße';
SELECT N'Oktoberstrasse'
UNION
SELECT N'Oktoberstraße';

which returns:

(Collation on my database is set to ‘SQL_Latin1_General_CP1_CI_AS’)

Notice that casting the values as NVARCHAR (which is what the N'' notation does) causes SQL Server to treat them as the same. Obviously this is going to cause a problem if you need to treat those as distinct values (such as inserting into a column with a unique key upon it – which is the problem I encountered that caused me to stumble across this)

There is a bug submission to Connect regarding this issue at 'ß' and 'ss' are NOT equal in which a member of the SQL Server team says:

Our current behavior follows the SQL/ISO standard and unless those standards are updated with the latest changes we don't intend to change the behavior in SQL Server. Changing existing SQL Server behavior has lot of implications and today we rely on Windows for all of our windows collation sorting capabilities. If and when in the future Windows adopts these new rules / Unicode standard we will incorporate it in SQL Server.

In other words, SQL Server is merely following the behaviour as defined by the International Standards Organisation so its not a bug in SQL Server as such, just a nuance that one needs to be aware of. And now you are.

@Jamiet

After an initial look at in-memory TVPs at small scale proved promising, SQL Sentry's Aaron Bertrand (@AaronBertrand) takes a closer look at trying to match more values against a much larger table.

Those of you who have tried the new version of SQL Server Management Studio might have noticed already that it has a couple of new wizards added. One of those wizards is sitting next to the already existing that has been allowing you to deploy to a Windows Azure SQL Database service (former SQL Azure) and it is called Deploy Database to a Windows Azure Virtual Machine (VM).

You might be wondering why this wizard is here and what it can do. The blog post below tries to provide the answer to this and some of the related questions.

The Path to Windows Azure

We have seen many time that when people are trying a Virtual Machine in the Windows Azure environment they quickly come up with a number of questions. Once the first set of questions is resolved the next wave of questions come up, then the next wave, and a few next waves.

I have tried to compile the list of things you could expect.

1. Get access to a Windows Azure.
   Logging into a Windows Azure Portal might not be enough to unlock the full power of the Azure. In majority of cases you would need a Management Certificate. There is nothing special about it, except it should be available on the machine you are using, and it should be known to the Windows Azure. This means you should create a certificate somehow. Alternate option is to download Publishing Profile from the Portal. In this case Portal will generate certificate on your behalf, add it to the subscriptions you have access to, and share it with you in the form of XML file.
2. Create and configure your VM.
   When you have an access to the Azure environment you can start creating other pieces you are needed. The first thing to create is a VM. Don’t forget that you need a VM that:
   1. Has a SQL Server in it and version of the SQL Server is the same or higher as the version of SQL Server you have currently, otherwise your deployment capabilities will be very limited.
   2. Has an Azure Drive attached, you will need it to store your data.
   3. In case VM was created in a Cloud Service it should have endpoints configured properly, otherwise please make sure you have Azure VNet and corresponding VPN.
   4. You should be able to connect to the SQL Server instance meaning connections should be accepted by the SQL Server and customer should be able to authenticate. In case your machine is not joined to domain this means only SQL authentication is available, so don’t forget to set up a SQL login in advance to the instance configuration.
   5. Windows firewall should allow connections to the SQL server to allow them to go though.
   6. Plus many other steps needs to be taken.
   7. Once your target system is set up you need to focus on copying data from the source system. The best option that gives you the most of the recovery capabilities it to use the Azure Storage as an intermediate location. In this case you either copy your files directly using Backup to URL, or backup them locally and then use Azure SDK to upload them to the storage.
   8. Once data is in the cloud you need to get it to the VM. Depends on the data size you can either pull it to the VM and restore or just use Restore from URL.

Those steps are describing the things need to happen to end up with your workload in Windows Azure VM. There are multiple small details in this process, same as a lot of things to consider. You can consult this article if you need more information on the subject.

The Wizard

You might have question how the process above is related to the wizard described in this post. This is exactly what Deploy Database to a Windows Azure VM wizard does for you, plus a few additional convenience items.

Let’s see how this looks in the wizard.

The first screen tries to provide you an idea of what information might be useful for you during the use of the wizard.

 

The Source Settings screen only want you to provide with two pieces of information:

• Connection to the source server and database
• Where to place backup files (file or UNC path). Please note: this path should look the same for the wizard and the Database Engine

 

Once you told the wizard where to get the data from you might want to connect to Windows Azure. There are 3 different options:

• Manually provide with the management certificate and paste or type a subscription ID
• Import a publishing profile if you have downloaded it already
• Use you Microsoft Account (former Windows Live ID) to sign in to the portal and allow the Wizard to retrieve publishing profile behind the scenes for you

 

Once all authentication information is in place we connect to a Windows Azure environment, so you can start configuring it. There are a few things you could do:

• Type a new Cloud Service name (or select it from the list if you already have one)
• Type a new VM name (same as before, you can select it from the list if there are some to select from)
• Select the storage account which is in the same region with the VM (if you don’t have any, don’t worry – wizard will create an account for you)
• Press the Settings… button (One of the most important steps). This button behave differently depending on whether this is a new VM or an existing VM, but in all cases the button needs to be pressed to enter the information that is important for the process.

 

When you type a new VM name and press the Settings… button a new dialog comes up. If you have seen Windows Azure Portal before you should be familiar with most fields in this new dialog.

 

Please note: we are trying to warn you if we think that the target SQL Server instance might be lower version than the source instance. Sometimes those are hard errors that doesn’t allow you to continue, when we know for sure that the final configuration will not be compatible, while sometimes we can only guess. In either case the suggestion is to do not ignore those warnings:

 

When you select the VM that has existed before this makes the dialog look differently.

Please note two things here:

• This is one of the first times SQL Server Cloud Adapter comes up on the screen. This is a new component that runs as a service inside a VM (you can run it on the physical machine too) and does all the magic of the VM configuration. We will spend a bit of time on its capabilities and restrictions a little later.
• Since single Cloud Service can contain multiple VMs sharing the same public IP – this means Cloud Adapter Port might be different for those VMs. Also as the previous phrase suggests this is a public port.

Once you chose settings for a new VM or connected to an existing one the Target Database section became active. Please note that Database name field is editable meaning you can change your mind about database name on the target machine.

 

Now you are just two easy steps away from having your database in the cloud. The first step is to confirm the selection you have made when following the wizard:

 

And a second step is to wait until process completes. Depends on the database size and your connection speed the time might differ, but wizard will make sure it is done.

 

And finally you would see the confirmation screen that also contains a link to the detailed log.

 

This log is important for most of the troubleshooting actions, same as for digging for details.

The Result

Deploy Database to a Windows Azure VM wizard has guided you through the deployment process. Now this is the right time to observe what the end result is look like.

1. You have Virtual Machine running in Windows Azure
2. This VM has SQL Server instance configured
3. You database is deployed to the instance

However, a few manual steps might be needed.

1. You need to add a logins and give those logins access to the database the wizard has deployed.
2. If your application would be connecting to the VM from outside – make sure to configure the SQL Server to allow the connectivity.

Finally

The Deploy Database to a Windows Azure VM wizard is in place to simplify the steps needed to have a database in a Windows Azure VM environment. The whole wizard is made around the scenarios when you need your database in the cloud as soon as possible and willing to skip some studying or preparation steps as a tradeoff for the speed of the deployment. The other assumption was that you don’t want to do a deployment manually and prefer SQL Server to do some of this work for you.

The wizard was made as an extremely safe to your data. There is no chance it could delete, overwrite, or even modify your data. All operations it does are read only for the data.

We really hope this new feature will help you to save time during your deployments.

John Sansom ( Blog | @SqlBrit ) is committed to sharing “what I believe it takes to be an outstanding Data Professional.” To that end, John shepherded a book project called DBA JumpStart . The concept of the (free!) e-book is: “If you could give a DBA just one piece of advice, what would it be?” The list of contributing authors reads like a Who’s Who of SQL Server database professionals: Jonathan Allen, Jes Borland, Jason Brimhall, Denny Cherry, Robert L. Davis, Richard Douglas, Rob Farley, Argenis...(read more)