Mrdenny

As per information from Microsoft:

"It is my pleasure to announce the release of SQL Server 2012 SP2 Cumulative Update 4 on behalf of the team.  SQL Server 2012 SP2 Cumulative Update 4 incorporates 42 issues.

This CU will be available for download from the associated cumulative KB article that has also been published.  Customers are directed to contact CSS to get the CU build or obtain the hotfix package through the new self-service feature by clicking on the “Hotfix Download Available” button found at the top of the KB article."

To me the most important fix is the one regarding the possibility of having errors 17066 or 17310 during SQL Server startup (immediately after database recovery is complete and client connections are enabled.). Check out the link http://support.microsoft.com/kb/3027860 for details

Link to the official site of this update: http://support.microsoft.com/kb/3007556/en-us

Cheers

Damian 

When an execution plan includes a scan of a b-tree index structure, the storage engine may be able to choose between two physical access strategies when the plan is executed:

1. Follow the index b-tree structure; or,
2. locate pages using internal page allocation information.

Where a choice is available, the storage engine makes the runtime decision on each execution. A plan recompilation is not required for it to change its mind.

The b-tree strategy starts at the root of the tree, descends to an extreme edge of the leaf level (depending on whether the scan is forward or backward), then follows leaf-level page links until the other end of the index is reached. The allocation strategy uses Index Allocation Map (IAM) structures to locate database pages allocated to the index. Each IAM page maps allocations to a 4GB interval in a single physical database file, so scanning the IAM chains associated with an index tends to access index pages in physical file order (at least as far as SQL Server can tell).

The main differences between the two strategies are:

1. A b-tree scan can deliver rows to the query processor in index key order; an IAM-driven scan cannot;
2. a b-tree scan may not be able to issue large read-ahead I/O requests if logically contiguous index pages are not also physically contiguous (e.g. as a result of page splitting in the index).

A b-tree scan is always available for an index. The conditions often cited for allocation order scans to be available are:

1. The query plan must allow an unordered scan of the index;
2. the index must be at least 64 pages in size; and,
3. either a TABLOCK or NOLOCK hint must be specified.

The first condition simply means that the query optimizer must have marked the scan with the Ordered:False property. Marking the scan Ordered:False means that correct results from the execution plan do not require the scan to return rows in index key order (though it may do so if it is convenient or otherwise necessary).

The second condition (size) applies only to SQL Server 2005 and later. It reflects the fact that there is a certain start-up cost to performing an IAM-driven scan, so there needs to be a minimum number of pages for the potential savings to repay the initial investment. The “64 pages” refers to the value of data_pages for the IN_ROW_DATA allocation unit only, as reported in sys.allocation_units.

Of course, there can only be a payoff from an allocation order scan if the possibly-larger read-ahead considerations actually come into play, but SQL Server does not currently consider this factor. In particular, it does not account for how much of the index is currently in memory, nor does it care how fragmented the index is.

The third condition is probably the least complete description in the list. Hints are not in fact required, though they can be used to meet the real requirements: The data must be guaranteed not to change during the scan, or (more controversially) we must indicate that we do not care about potentially inaccurate results, by performing the scan at the read uncommitted isolation level.

Even with these clarifications, the list of conditions for an allocation-ordered scan is still not complete. There are a number of important caveats and exceptions, which we will come to shortly.

Demo

The following query uses the AdventureWorks sample database:

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
GO
SELECT
    P.BusinessEntityID,
    P.PersonType
FROM Person.Person AS P;

Note that the Person table contains 3,869 pages. The post-execution (actual) plan is as follows (shown in SQL Sentry Plan Explorer):

In terms of the allocation-order scanning requirements we have so far:

• The plan has the required Ordered:False property; and,
• the table has more than 64 pages; but,
• we have done nothing to ensure the data cannot change during the scan. Assuming our session is using the default read committed isolation level, the scan is not being performed at the read uncommitted isolation level either.

As a consequence, we would expect this scan to be performed by scanning the b-tree rather than being IAM-driven. The query results indicate that this is likely true:

The rows are returned in Clustered Index key order (by BusinessEntityID). I should state clearly that this result ordering is absolutely not guaranteed, and should not be relied on. Ordered results are only guaranteed by an appropriate top-level ORDER BY clause.

Nevertheless, the observed output order is circumstantial evidence that the scan was performed this time by following the clustered index b-tree structure. If more evidence is needed, we can attach a debugger and look at the code path SQL Server is executing during the scan:

The call stack clearly shows the scan following the b-tree.

Adding a table lock hint

We now modify the query to include a table-lock hint:

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
 
SELECT
    P.BusinessEntityID,
    P.PersonType
FROM Person.Person AS P
    WITH (TABLOCK);

At the default locking read committed isolation level, the shared table-level lock prevents any possible concurrent modifications to the data. With all three preconditions for IAM-driven scans met, we would now expect SQL Server to use an allocation-order scan. The execution plan is the same as before, so I won’t repeat it, but the query results certainly look different:

The results are still apparently ordered by BusinessEntityID, but the starting point (10866) is different. Indeed, if we scroll down the results, we soon encounter sections that are more obviously out of key order:

The partial ordering is due to the allocation-order scan processing a whole index page at a time. The results within a page happen to be returned ordered by the index key, but the order of the scanned pages is now different. Again, I should stress that the results may look different for you: there is no guarantee of output order, even within a page, without a top-level ORDER BY on the original query.

For comparison with the call stack shown earlier, this is a stack trace obtained while SQL Server was processing the query with the TABLOCK hint:

Stepping on a little further through the execution:

Clearly, SQL Server is performing an allocation-ordered scan when the table lock is specified. It is a shame there is no indication in a post-execution plan of which type of scan was used at runtime. As a reminder, the type of scan is chosen by the storage engine, and can change between executions without a plan recompilation.

Other ways to meet the third condition

I said before that to get an IAM-driven scan, we need to ensure the data cannot change underneath the scan while it is in progress, or we need to run the query at the read uncommitted isolation level. We have seen that a table lock hint at locking read committed isolation is sufficient to meet the first of those requirements, and it is easy to show that using a NOLOCK/READUNCOMMITTED hint also enables an allocation-order scan with the demo query.

In fact there are many ways to meet the third condition, including:

• Altering the index to only allow table locks;
• making the database read-only (so data is guaranteed not to change); or,
• changing the session isolation level to READ UNCOMMITTED.

There are, however, much more interesting variations on this theme that mean we need to amend the three conditions stated previously…

Row-versioning isolation levels

Enable read committed snapshot isolation (RCSI) on the AdventureWorks database, and run the test with the TABLOCK hint again (at read committed isolation):

ALTER DATABASE AdventureWorks2012
SET READ_COMMITTED_SNAPSHOT ON
    WITH ROLLBACK IMMEDIATE;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
GO
SELECT
    P.BusinessEntityID,
    P.PersonType
FROM Person.Person AS P
    WITH (TABLOCK);
GO
ALTER DATABASE AdventureWorks2012
SET READ_COMMITTED_SNAPSHOT OFF
    WITH ROLLBACK IMMEDIATE;

With RCSI active, an index-ordered scan is used with TABLOCK, not the allocation-order scan we saw just before. The reason is the TABLOCK hint specifies a table-level shared lock, but with RCSI enabled, no shared locks are taken. Without the shared table lock, we have not met the requirement to prevent concurrent modifications to the data while the scan is in progress, so an allocation-ordered scan cannot be used.

Achieving an allocation-ordered scan when RCSI is enabled is possible, however. One way is to use a TABLOCKX hint (for a table-level exclusive lock) instead of TABLOCK. We could also retain the TABLOCK hint and add another one like READCOMMITTEDLOCK, or REPEATABLE READ or SERIALIZABLE … and so on. All these work by preventing the possibility of concurrent modifications by taking a shared table lock, at the cost of losing the benefits of RCSI. We can also still achieve an allocation-order scan using a NOLOCK or READUNCOMMITTED hint, of course.

The situation under snapshot isolation (SI) is very similar to RCSI, and not explored in detail for space reasons.

TABLESAMPLE always* performs an allocation-order scan

The TABLESAMPLE clause is an interesting exception to many of the things we have discussed so far.

Specifying a TABLESAMPLE clause always* results in an allocation-order scan, even under RCSI or SI, and even without hints. To be clear about it, the allocation-order scan that results from using TABLESAMPLE retains RCSI/SI semantics – the scan uses row versions and reading does not block writing (and vice versa).

A second surprise is that TABLESAMPLE always* performs an IAM-driven scan even if the table has fewer than 64 pages. This makes some sense because the documentation at least hints that the SYSTEM sampling method uses the IAM structure (so there is no choice but to do an allocation-order scan):

SYSTEM Is an implementation-dependent sampling method specified by ISO standards. In SQL Server, this is the only sampling method available and is applied by default. SYSTEM applies a page-based sampling method in which a random set of pages from the table is chosen for the sample, and all the rows on those pages are returned as the sample subset.

* An exception occurs if the ROWS or PERCENT specification in the TABLESAMPLE clause works out to mean 100% of the table. Specifying more ROWS than the metadata indicates are currently in the table will not work either. Using TABLESAMPLE SYSTEM (100 PERCENT) or equivalent will not force an allocation-order scan.

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
GO
SELECT
    P.BusinessEntityID,
    P.PersonType
FROM Person.Person AS P
    TABLESAMPLE SYSTEM (50 ROWS)
    REPEATABLE (12345678)
    --WITH (TABLOCK);

Results:

The effect of TOP and SET ROWCOUNT

In short, neither of these has any effect on the decision to use an allocation-order scan or not. This may seem surprising in cases where it is "obvious" that fewer than 64 pages will be scanned.

For example, the following queries both use an IAM-driven scan to return 5 rows from a scan:

SELECT TOP (5)
    P.BusinessEntityID,
    P.PersonType
FROM Person.Person AS P WITH (TABLOCK)
 
SET ROWCOUNT 5;
 
SELECT
    P.BusinessEntityID,
    P.PersonType
FROM Person.Person AS P WITH (TABLOCK)
 
SET ROWCOUNT 0;

The results are the same for both:

This means that TOP and SET ROWCOUNT queries might incur the overhead of setting up an allocation-order scan, even if fewer than 64 pages are scanned. In mitigation, more complex TOP queries with selective predicates pushed into the scan could still benefit from an allocation-order scan. If the scan must process 10,000 pages to find the first 5 rows that match, an allocation-order scan could still be a win.

Preventing all* allocation-order scans instance-wide

This is not something you would ever likely do intentionally, but there is a server setting that will prevent allocation-order scans for all* user queries in all databases.

Unlikely as it may seem, the setting in question is the cursor threshold server configuration option, which has the following description in Books Online:

The cursor threshold option specifies the number of rows in the cursor set at which cursor keysets are generated asynchronously. When cursors generate a keyset for a result set, the query optimizer estimates the number of rows that will be returned for that result set. If the query optimizer estimates that the number of returned rows is greater than this threshold, the cursor is generated asynchronously, allowing the user to fetch rows from the cursor while the cursor continues to be populated. Otherwise, the cursor is generated synchronously, and the query waits until all rows are returned.

If the cursor threshold option is set to anything other than –1 (the default), no allocation-order scans will occur for user queries in any database on the SQL Server instance.

In other words, if asynchronous cursor population is enabled, no IAM-driven scans for you.

* The exception is (non-100%) TABLESAMPLE queries. The internal queries generated by the system for statistics creation and statistics updates also continue to use allocation-ordered scans.

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
GO
 
-- WARNING! Disables allocation-order scans instance-wide
EXECUTE sys.sp_configure 
    @configname = 'cursor threshold',
    @configvalue = 5000;
 
RECONFIGURE WITH OVERRIDE;
GO
 
-- Would normally result in an allocation-order scan
SELECT
    P.BusinessEntityID,
    P.PersonType
FROM Person.Person AS P
    WITH (READUNCOMMITTED);
GO
 
-- Reset to default allocation-order scans
EXECUTE sys.sp_configure 
    @configname = 'cursor threshold',
    @configvalue = -1;
 
RECONFIGURE WITH OVERRIDE;

Results (no allocation-order scan):

One can only guess that asynchronous cursor population does not work well with allocation-order scans for some reason. It is entirely unexpected that this restriction would affect all non-cursor user queries as well though. Perhaps it is too hard for SQL Server to detect if a query is running as part of an externally-issued API cursor? Who knows.

It would be nice if this side-effect were officially documented somewhere, though it is hard to know exactly where it should go in Books Online. I wonder how many production systems out there are not using allocation-order scans because of this? Maybe not many, but you never know.

To wrap things up, here is a summary. An allocation-ordered scan is available if:

1. The server option cursor threshold is set to –1 (the default); and,
2. the query plan scan operator has the Ordered:False property; and,
3. the total data_pages of the IN_ROW_DATA allocation units is at least 64; and,
4. either:
   1. SQL Server has an acceptable guarantee that concurrent modifications are impossible; or,
   2. the scan is running at the read uncommitted isolation level.

Regardless of all the above, a scan with a TABLESAMPLE clause always uses allocation-ordered scans (with the one technical exception noted in the main text).

The post Allocation Order Scans appeared first on SQLPerformance.com.

Microsoft announced two company acquisitions related to data analytics.

It announced that it will acquire Revolution Analytics. Revolution Analytics is the leading commercial provider of software and services for R, the world's most widely used programming language for statistical computing and predictive analytics. This acquisition could help more companies use the power of R and data science to unlock big data insights with advanced analytics.

Previously, Microsoft also announced that it will acquire Equivio. Equivio is a provider of machine learning technologies for eDiscovery and information governance to help customers tackle the legal and compliance challenges inherent in managing large quantities of email and documents.

If you’re an IT manager, the first thing I’d like to tell you is how invested Microsoft is in SQL Server. While earlier incarnations of executive leadership focused in other areas (anyone remember “Developers! Developers! Developers!”?), Microsoft’s new leadership is very bullish on SQL Server. And, why not? After Microsoft Office and Windows Server, SQL Server is Microsoft’s third biggest money-making product line. Check out the recording of the “Accelerate your insights” webcast, about SQL Server 2014, featuring appearances by CEO Satya Nadella, COO Kevin Turner and CVP Quentin Clark.

Read the rest of this article at http://www.dbta.com/Editorial/Think-About-It/The-Best-of-SQL-Server-2014-RTM-%E2%80%93-6-Reasons-to-Think-About-Upgrading-96300.aspx

The post DBTA – 6 Reasons to Think About Upgrading to SQL Server 2014 appeared first on Kevin Kline.

I have been working on the various aspects of DBCC performance and SQL CLR based User Defined Data Types.    I encountered a few issues that I have outlined below. 

1.      Memory Grant Bug

There is a bug, prior to SQL Server 2014, causing the memory grant for the DBCC operations (checktable or checkdb per table) to be improperly estimated.  Placing the session, running the DBCC command, in a specific resource workload group allows you to limit the memory grant size and increase the DBCC performance.  Reference the following link for more details” http://blogs.msdn.com/b/psssql/archive/2014/11/10/a-faster-checkdb-part-iii.aspx   I used a grant cap of 20GB on a 128 CPU, 512GB system with success rather than accepting the 90GB default grant request.

Note: This issue has been corrected in SQL 2012 (https://support.microsoft.com/en-us/kb/3007556) SQL Server 2012 SP2 - CU4

2.      Blob Handle Factory

Fix released (Microsoft Bug Id: 3939015).  Before the fix the DBCC command(s) created an internal structure (Blob Handles for each SQL CLR UDT based column as rows were processed) and failed to mark it for reuse when done processing the row.  Each spatial row/column would look over the blob handle list, create a new entry and add it to the list.   This resulted in wasted memory and CPU as the list continued to get larger and larger and the entries could not be reused.    The fix allows proper reuse of the BHF.   

The customer indicated it took 22 days to complete on the current SQL Server 2012 build.   The QFE now completes DBCC checkdb in 15.5 hours. (DL980 G7 with 512GB RAM)

Download: http://support.microsoft.com/kb/3007556/en-us  (SQL Server 2012 SP2 – CU4)
Article: http://support.microsoft.com/kb/3029825

3.      Parallelism

DBCC does use parallel, internal queries when possible.   In studying this closer I found that running individual checktable(s), from multiple sessions can decrease the overall DBCC maintenance window.

DBCC checkdb loops over each table and executes the fact queries.  The SQL engine many elect to execute the fact queries in parallel.    However, CheckDB only processes a single table at a time.   On a larger system you may be able to take advantage of multiple checktable invocations on different schedulers.

The fact queries used by DBCC command are prevented from using parallelism if a large UDT column is present.   Spatial is somewhat analogous to varbinary(max) and falls into this limitation.   This means the internal, DBCC, fact query against your largest table runs with a serial plan. 

If you manually shard the table, creating a covering view it may allow you to execute concurrent dbcc, index rebuild and other operations faster.   

Parallel Check Table Example:

DBCC CHECKALLOC
DBCC CHECKCATALOG
Parallel.ForEach(table in sys.tables)        --         Make sure the number of parallel executions is appropriate for resource consumption on the SQL Server
{
      DBCC CHECKTABLE(…) with …
}

Shard Example:

create view vwMyTable
as

    select <<column list>> from MyTable_1
       union all
    select <<column list>> from MyTable_2   …..

4.      Trace Flag 2566  (Ignore Data Purity Checks)

Once data purity checks have been successful on upgraded databases or the database was created on newer versions the DATA_PURITY check becomes ‘On By Default.’      DBCC DBINFO(<<DBNAME>>) shows the dbi_dbccFlags.   The 0x2 bit indicates data purity will be run by default when checkdb or checktable is executed.     You can avoid these checks using –T2566 or dbcc traceon(2566) in the same batch as checkdb or checktable execution.

The trace flag skips the purity checks as long as the checkdb or checktable command does not specify WITH DATA_PURITY, overriding the trace flag behavior.

My testing only shows a nominal performance gain.

Additional DBCC References

Part 1: http://blogs.msdn.com/b/psssql/archive/2011/12/20/a-faster-checkdb-part-i.aspx 
Part 2: http://blogs.msdn.com/b/psssql/archive/2012/02/23/a-faster-checkdb-part-ii.aspx 
Part 3: http://blogs.msdn.com/b/psssql/archive/2014/11/10/a-faster-checkdb-part-iii.aspx  

Spatial

Over the last 18 months Microsoft addressed various spatial performance issues.  The QFE build, mentioned above, contains the following corrections that you should consider enabling to improve overall spatial performance on the servers.  

In a nutshell:  Apply latest Service Pack and latest CU for SQL Server and in addition, enable STARTUP trace flags (-T8048 and -T6531)

Details:

Bob Dorr -  Principal SQL Server Escalation Engineer

It is sometimes common practice to enable Distributed Transaction (DTC) behavior but it can be unnecessary, and adds unwanted overhead.  

DTC has the ability to determine single phase vs two phase commit requirements.  A DTC transaction involves resource managers (RMs) of which SQL Server can be one of them.  If a single resource manager is involved in the transaction there is no need to perform 2-phase commit.   DTC shortcuts the activity and performs a single-phase commit safely.   This reduces the communication between the DTC and RM managers.  However, the overhead of the DTC manager is still involved making the transaction slightly slower than a native TSQL transaction.

Single Phase

The following is a single phase DTC commit example.

begin distributed tran
go

update dbTest.dbo.tblTest set object_id = 100
go

commit tran
go

Notice the trace output does not indicate a prepared state.  This is a direct indication of a single phase commit.

Two Phase

The following is a 2-phase commit example.

begin distributed tran
go

update MYREMOTESERVER.dbTest.dbo.tblTest set object_id = 100
go

commit tran
go

The transaction involved the local instance (RM=1) and a remote instance (RM=2).  With 2 RMs involved DTC commits the transaction under full, 2-phase commit protocol.   Notice the prepared state in the trace indicating full, 2-phase commit protocol is being used.

You may want to review the DTC transactions executing on your system, looking for prepared state.  If the DTC transactions running on your system are not using 2-phase commit protocol you should consider removing DTC from the transactions in order to improve performance.

Bob Dorr - Principal SQL Server Escalation Engineer

Top 10 Tips for SQL Server Performance and Resiliency

This article is part 3 of 10 in a series on the most common mistakes that I have seen impacting SQL Server Performance and Resiliency. This post is related to the most common errors I see and is not all inclusive.

#1 Most common mistake – Incomplete Backups

#2 Most common mistake – Check Your Security

Most common mistake #3: Improper Maintenance

When it comes to maintenance, SQL Server is no different than an automobile; both require regular maintenance to keep them running at peak performance. In similar fashion, the greater the load, the more often the maintenance is required. Neglecting maintenance is the third biggest mistake that I frequently see impacting SQL Server’s performance.

Fragmented Indexes

Indexes in SQL Server are very similar to indexes in a book. The primary difference between indexes in a book and in your database is the indexes contained within the database are much easier to rebuild when new information is inserted. Imagine you had a book with 100 pages of text. If the book were indexed, finding specific information wouldn’t be too difficult and shouldn’t take much time. Now add 50 pages of new text in the middle of the book. Finding specific text after the 50 pages has been added would be difficult, all the page numbers would be off and the new text wouldn’t be indexed.

To solve this problem with SQL Server, we have the option to rebuild the index or reorganize an index. This should be completed on a regular basis. This ensures data location accuracy, which eases the work load on SQL server. This allows SQL Server to find the specific data it needs quickly and efficiently.

Stale Statistics

Sometimes, people have difficulty understanding SQL Server Statistics. Think of it this way: If you managed a sports team and it was your job to find new talent, would you want to see how much potential the talent has? You may want to know how strong they are or how fast they can run. Trying to pick up a new quarterback without that information would be difficult. What if the player’s statistics you were looking at were 5 years old? Maybe the statistics were from when they were in the best shape of their career and now they have gone a little past their prime. You may hire a player based on bad information if you didn’t know the statistics were out of date, they may not be that star performer you were looking for.

SQL Server keeps statistics for you as well. This information keeps your SQL Server aware of the information in the database and helps it decide on what the fastest way to retrieve data is. However, there are times when this information needs to be refreshed. Keeping your statistics up to date is going to keep your SQL Server aware of what shape your data is in.

Consistency Check

Isn’t it nice when your car starts to warn you before something really bad is going to happen? Sometimes it can be something as simple as change the oil in the engine or changing the battery.

SQL Server has warning signs as well; DBCC commands or database consistency checks should be executed to catch when something may be amiss in your data. This doesn’t mean that it can catch everything, but this layer of protection can save you some serious pain down the road.

Monitoring

Children that are being watched at a child care center or by parents are a lot less likely to get themselves into trouble. SQL Server is no different. A monitored SQL Server is less likely to have critical non-recoverable errors than servers that are not monitored. For example, pretend you have a database that requires 100 gigs of space for a backup file. If that storage falls below having 100 gigs of space available, your SQL Server backup is going to fail. What would happen if you lost the storage due to a hardware failure and now you need to restore from a backup? You could lose a tremendous amount of data and time, assuming you are able to recover it at all.

Now consider the same situation with a monitored SQL Server. A monitored SQL Server should inform you that you had a backup failure, and if it is scheduled via a Job in SQL Server, you can receive an alert from the SQL Server itself. In addition, there would be entries in the error log that you would see as well. A monitored SQL Server is going to give you an opportunity to correct the backup issue, before you need to restore the database.

It’s getting easier to complete a basic install of SQL Server with each and every version. It takes very little time to have a database up and running. It is so simple that many organizations install their SQL Server and forget about it – at least until there becomes a problem with it. Most of the time when I am asked to perform an emergency fix or a restore of a database, it is being done on a server that isn’t being monitored.

Database Mail, Alerts and Operators not configured

As mentioned in the monitoring section of this post SQL Server can be configured to send emails in a number of situations; if a job fails or errors are raised with specific severities. These emails are sent to operators that are configured within SQL Server. So when a backup fails, SQL Server can send you an email, allowing you to fix the issue.

When my son started to learn how to drive a car, we spent a number of hours talking about the maintenance. We reviewed how he should keep sand in his trunk to help get traction on the ice, how his oil needs to be changed and the tires need to be rotated, all to ensure his safety while on the road. A well maintained SQL Server can give your organization a stable and well performing database.

SQL 2012 was a big release for working out the median in SQL Server, with the advent of the function PERCENTILE_CONT(). It’s a very elegant way of working out the median (hint, that’s the 0.5 point), even though it’s not actually an aggregate function, as I’ve written before.

Plus – it doesn’t even perform well. About a year ago, Aaron Bertrand (@aaronbertrand) wrote a fantastic post about different methods for getting medians, and showed that PERCENTILE_CONT() is actually one of the slowest methods, and that the best method is to use an idea from Peter Larsson (@SwePeso) that uses an OFFSET-FETCH clause to grab the rows of interest before doing an average of them.

Except that the OFFSET-FETCH clause was also new in 2012. So if you’re stuck on SQL 2008 R2 and earlier, you’re a bit more stuck.

All the pre-SQL 2012 methods that Aaron showed used ROW_NUMBER() except one – which used a combination of MIN/MAX over each half of the data. But one method that Aaron didn’t explore in his post was to simulate OFFSET-FETCH in earlier versions. Let me show you…

Here’s the OFFSET-FETCH method. Notice that it fetches either 1 or 2 rows (depending on whether the overall count is 1 or 2), but offsets by just under half of the set.

SELECT    d.SalesPerson, w.Median
FROM
(
  SELECT SalesPerson, COUNT(*) AS y
  FROM dbo.Sales
  GROUP BY SalesPerson
) AS d
CROSS APPLY
(
  SELECT AVG(0E + Amount)
  FROM
  (
    SELECT z.Amount
     FROM dbo.Sales AS z
     WHERE z.SalesPerson = d.SalesPerson
     ORDER BY z.Amount
     OFFSET (d.y - 1) / 2 ROWS
     FETCH NEXT 2 - d.y % 2 ROWS ONLY
  ) AS f
) AS w(Median);

What my pre-2012-compatible version does is to fetch slightly MORE than the set first, and then get the top 1 or 2 but in DESC order.

SELECT    d.SalesPerson, w.Median
FROM
(
  SELECT SalesPerson, COUNT(*) AS y
  FROM dbo.Sales
  GROUP BY SalesPerson
) AS d
CROSS APPLY
(
  SELECT AVG(0E + Amount)
  FROM
  (
    SELECT TOP (2 - d.y % 2) Amount
    FROM
    (
    SELECT TOP (d.y / 2 + 1) z.Amount
     FROM dbo.Sales AS z
     WHERE z.SalesPerson = d.SalesPerson
     ORDER BY z.Amount
     ) AS t
     ORDER BY Amount DESC
  ) AS f
) AS w(Median);

With OFFSET-FETCH, we’re grabbing the rows we want by skipping over the rows we’re not interested in until we find the ones that we are interested in. In the TOP/TOPDESC, we’re identifying the rows we want by the fact that they’re the bottom of the top slightly-more-than-half set.

Other than that, the idea is exactly the same. The results are identical, but what about the performance?

First, let’s give you the code to set up your environment (as found in Aaron’s post) – I used a clustered index.

CREATE TABLE dbo.Sales(SalesPerson INT, Amount INT);
GO
 
--CREATE CLUSTERED INDEX x ON dbo.Sales(SalesPerson, Amount);
--CREATE NONCLUSTERED INDEX x ON dbo.Sales(SalesPerson, Amount);
--DROP INDEX x ON dbo.sales;
 
;WITH x AS
(
  SELECT TOP (100) number FROM master.dbo.spt_values GROUP BY number
)
INSERT dbo.Sales WITH (TABLOCKX) (SalesPerson, Amount)
  SELECT x.number, ABS(CHECKSUM(NEWID())) % 99
  FROM x CROSS JOIN x AS x2 CROSS JOIN x AS x3;

What I want to do to evaluate this is to look at the query plans. Once I’ve done that, I’ll make a comment about the performance and where it fits into the mix from Aaron’s post.

So those plans… OFFSET-FETCH method first, followed by the TOP/TOPDESC method. I’m using a clustered index on the data – a nonclustered index gives the same shape but with nonclustered index operations instead of clustered index operations. Heaps are a different story that I’m not exploring here.

As you’d expect, there’s a lot of similarity. Both use Nested Loops, grabbing the Counts from a Scan on the outer branch, with a Seek on the inner branch. And both inner branches have a Top Operator pulling the data out of a Seek. But the TOP/TOPDESC method has TWO Top operators, with a Sort in between. This is because of the ‘TOPDESC’ bit. If we had a ‘Bottom’ operator, then that would avoid the need for a Sort, but no such animal exists, and it does ‘Bottom’ by doing a Top of re-Sorted data. It’s very disappointing. The Top operator in the OFFSET-FETCH method has a new property called OffsetExpression, which it uses to skip over as many rows as it needs – it’s simply not supported pre-2012.

(Quick side note: the arrow between the Compute Scalar and the right-most Top operator in both plans is quite thin – much thinner that you might expect. This is only a quirk of the plan because the Actuals haven’t been reported here. MSDN (https://technet.microsoft.com/en-us/library/ms178082.aspx) says: “Compute Scalar operators that appear in Showplans generated by SET STATISTICS XML might not contain the RunTimeInformation element. In graphical Showplans, Actual Rows, Actual Rebinds, and Actual Rewinds might be absent from the Properties window when the Include Actual Execution Plan option is selected in SQL Server Management Studio. When this occurs, it means that although these operators were used in the compiled query plan, their work was performed by other operators in the run-time query plan.” Therefore, the arrow coming out of the Compute Scalar operator is the width of the estimated number of rows, because it doesn’t have the actual number of rows. But it’s a Compute Scalar – it’s not going to change the number of rows, and you should consider the width of the arrow as being the width of the arrow going into it.)

Of course, this TOP/TOPDESC method is slower than OFFSET-FETCH. If we had a ‘Bottom’ operator, I think it wouldn’t be very much slower, but here we have a Sort operator! And those things are bad. The plans estimated that the cost of the Sort would be 27% of the total query, and that the ratio between the two queries would be 58:42, which is 1.38:1. But remember that those Cost percentages are based on estimated values, and we know those estimates are quite a long way out.

So instead, we use a more empirical method, which is to run them against each other.

On my machine (a Surface Pro 2), with a warm cache, the OFFSET-FETCH method took around 380ms, compared to around 570ms for the TOP/TOPDESC. It’s definitely slower – no surprises there. It’s a good 50% slower, if not more. But this still makes it faster than any of the pre-SQL 2012 versions that Aaron used.

I’m sure you’re going to point out that I’m clearly running this on a version of SQL Server that is at least 2012… so I ran it on a SQL 2008 R2 box as well, and found that the plan was identical as shown here, and that it was about 30% faster than the “2005_3” version from Aaron’s post with an index applied.

So if you’re using SQL 2008 R2 (or earlier) still, then don’t dismiss the best-performing median function from Aaron’s post (thanks again, Peso!), but instead, consider coming up with a 2008R2-compatible version, as I’ve done here.

Update: Another method is to consider simply filtering on ROW_NUMBER(), which isn’t included in Aaron’s post either. It’s still good, but doesn’t quite perform as quickly as the TOP/TOPDESC method on the million-row set, because it has to figure out the ROW_NUMBER() for a lot of rows. The OffsetExpression property in the Top operator of SQL 2012+ is your friend.

SELECT d.SalesPerson, w.Median
FROM
(
   SELECT SalesPerson, COUNT(*) AS y
   FROM dbo.Sales
   GROUP BY SalesPerson
) AS d
CROSS APPLY
(
SELECT AVG(0E + Amount)
FROM
(
   SELECT
       z.Amount,
        rn = ROW_NUMBER() OVER (ORDER BY z.Amount)
   FROM dbo.Sales AS z
   WHERE z.SalesPerson = d.SalesPerson
) AS f
WHERE
f.rn BETWEEN (d.y + 1) / 2 AND (d.y + 2) / 2
) AS w(Median);

@rob_farley

Moving On

After nearly nine years at Microsoft, I’ve chosen to move on.

I’ve accepted a position at Fusion-io—now a SanDisk company—as Worldwide SQL Server Solutions Architect where I’ll be evangelizing, documenting best practices, collaborating directly with the Windows & SQL product teams & other partners, doing cutting-edge performance work, & of course flipping the /faster bit—all things about which I’m passionate—& doing so with the full faith & support of the best enterprise flash company on the planet.  I collaborated closely with several of these fine folks while managing the SQL CAT Customer Lab.  Like me, they’re passionate about performance, defining engineering discipline, & delighting the customer.  It’s a fantastic fit.

Why “Hello, World! Encore Edition”?  My very first MSDN post, “Hello, World!” reflects aspirations that remain true today.  (It also includes an hilarious excerpt from Scott Adams on “the why” of blogging.)  So this post isn’t good-bye¸ it’s simply a transition.

Contact: Blog & Twitter

My primary blog will be here: http://sqlblog.com/blogs/jimmy_may where I’ll continue to post tips, tricks, speaking events, & of course, insights on aforementioned /faster bit.  That site, hosted by MVP Adam Machanic, features an awesome roster of talent that I’m honored to be a part of.

I invite you to look me up on Facebook as well as Twitter: @AspiringGeek.

Here’s my email:  SELECT REVERSE('moc.evil@keeggniripsa')

Changing the World

Anyone who’s read my blog or heard me speak know I’ve liberated my motto from @GapingVoid

Change the world is exactly what I’ve tried to do—& will continue to do—to make it a better place, to best serve our customers.

I had lots of help, working with lots of great engineers & lots of great customers—lots of great people.

Along the way I authored one white paper and was named contributor or technical reviewer on almost two dozen others.  I had the privilege of editing or contributing to several books, including the career- & life-changing Getting Results the Agile Way: A Personal Results System for Work & Life by MS Principal PM J.D. Meier (Sources of Insight | 30 Days of Getting Results | wiki | amazon).

 
Test-drive the book on how to make the most of work and life.
Read Getting Results the Agile Way for free online.

I spoke at the PASS International Summit five times, TechReady seven times, & at dozens of user groups across the country & some outside.

Here’s one of favorite moments on stage, demonstrating how columnstore turns conventional row store on its head with my #SQLWingMan, Shahry Hashemi aka @dsfnet (photos courtesy of @GEEQL).

As a consultant, I led over 60 on-site customer engagements around the world.  As a SQL CAT PM & manager of the Customer Lab, we shepherded over 100 customer engagements.  With a lot of help from my team, we architected the Customer Lab to host parallel engagements, remote engagements, streamlined our onboarding processes, paved the way for Cloud engagements, upgraded our network & I/O infrastructures, & enhanced our collaboration with several partners resulting in the acquisition of dozens of the latest-&-greatest servers & storage devices.  At MSIT I was part of the team troubleshooting performance issues & establishing best practices during our massive migration to Azure.

The Roles

Here’s a summary of my exciting roles while at Microsoft:

• Microsoft Consulting Services, Senior Consultant
• Microsoft A.C.E. Performance Team, Principal Consultant
• The SQL Server Customer Advisory Team—SQL CAT, Senior Program Manager
• Microsoft Information Technology—MSIT, Principal Architect

The Training: Microsoft Certified Master #MCM4Life

Just as SQL CAT was in so many ways a dream job, Microsoft Certified Masters training was the highlight of my professional training.  I prepped for months, reading every single page of the required reading—Kimberly Tripp later stating I was “The One” who’d done so. ;-)  At that time the cert had a required three-week on-site component during which, one after another, we were instructed by the best SQL Server trainers on the planet.  Just as importantly, I was privileged to sit alongside two dozen peers, all of whom remain friends.  On Labor Day weekend 2012, Microsoft Learning unceremoniously without warning terminated the program.  Many of us hope the program is resuscitated in a meaningful way.  In the meantime, MCM training was in so many ways an incredible, special, unique experience.

     
Certification logo courtesy of MSL (combined with lots of hard work).

Gang tat courtesy of Robert Davis, yo. 

The People

Attempting to thank all those who’ve supported, encouraged, & championed my career reminds me of the dilemma faced by Academy Award winners—it’s impossible to properly acknowledge everyone in the allotted time, & there’s the risk of leaving out many who richly deserve credit.  But I’m going to try anyway.

If I were to try enumerating my myriad community colleagues, allies, & friends outside of Microsoft, the list would be unmanageable.

Yet I must recognize those in Microsoft leadership positions without whom my success would not have been possible:

• Eddie Lau & Irfan Chaudhry of A.C.E. Perf
• Mark Souza & Lindsey Allen of SQL CAT

And here (in no special order) are just a few of the current & former Microsoft staff who’ve been invaluable:

• My “Master Mind”: J.D. Meier, Alik Levin, Rob Boucher
• Especially Special Persons: Marilyn Grant, Janelle Aberle
• Mentors: Ty Moore, James Day
• MCS Mentors: Joe Sack & Kate Baroni
• SQL CAT: Shaun Tinline, Sanjay Mishra, Mike Ruthruff, Thomas Kejser, Denny Lee, Cindy Gross, Lara Rubbelke, Kathy MacDonald, Chuck Heinzelman, Ewan Fairweather, Mike Weiner, Tom Davidson, Regina Jones, Mike Anderson
• MCMs: Jens Suessmeyer, Bertil Syamken, Jose Barrios, Robert Davis, Argenis Fernandez, Kalyan Yella, Tracey Jordan, Cris Benge
• MCM Trainers: Kalen Delaney, Paul Randal, Kimberly Tripp, Greg Low, Adam Machanic
• MSIT: Chris Lundquist, James Beeson, Vitaliy Konev, Casie Owen, Brian Walters, Ahmad Mahdi, Dale Hirt, Rob Beddard
• PFEs: Gennady Kostinsky, Clint Huffman, Shane Creamer, Ken Brumfield, Robert Smith, Kaitlin Goddard
• SQL BI: Kay Unkroth
• SQL PG:  Sunil Agarwal, Eric Hanson, Susan Price, Kevin Farlee, Luis Carlos Vargas Herring
• Others: Buck Woody, Pablo Brontvain, Steven Schneider, Brian Raymer, Steven Wort, Bruce Worthington, Dandy Weyn, Cephas Lin, Matthew Robertshaw, Mark Pohto, Bob Roudebush

Living Life to the varchar(max)

Thank you, everyone—inside & outside of Microsoft—for your help & friendship during my tenure. I couldn’t’ve done it without you.  Our journey continues, & I look forward to exploring the road of happy destiny together with all of you. 

In the meantime, here’s to living life to the varchar(max)!

Jimmy May, Aspiring Geek

We had a case where Reporting Services was crashing with the following error.

Failed allocate pages: FAIL_PAGE_ALLOCATION 2

The number at the end can be different.  In the customer’s case it was a 1.  In my local repro it was a 2.  This is basically an indication that we are out of memory for the process and the process crashes at that point as we can’t allocate any more memory.  You won’t see an OutOfMemory error, as the allocations come from SQL Operating System (SOS) which hosts the .NET CLR.  So, it is SOS that is trying to do the allocation that fails.  SOS is also used with the SQL Engine, so you will see this error from that side as well when you are out of memory.

Before we get into the meat of it, here is a link to an article that goes through the memory thresholds for Reporting Services and explains how it will handle memory when those thresholds are hit.

Configure Available Memory for Report Server Applications
https://msdn.microsoft.com/en-us/library/ms159206.aspx

When Reporting Services starts, it will calculate what the maximum amount of memory will be for the process.  This will be done in one of two ways.

First, if WorkingSetMaximum is not set in the rsreportserver.config (which it isn’t there by default), then Reporting Services will derive the maximum memory setting based on your total physical memory.  To see this happen, we can look at the Reporting Services Trace Log and look for Derived memory.

rshost!rshost!19c4!01/29/2015-05:03:22:: i INFO: Derived memory configuration based on physical memory as 33486264 KB

servicecontroller!DefaultDomain!b14!01/29/2015-05:03:22:: i INFO: Total Physical memory: 34289934336

You may or may not see something like the following entry if you configure WorkingSetMaximum.

library!DefaultDomain!3278!12/02/2014-16:11:18:: i INFO: Initializing WorkingSetMaximum to '12486264' kilobytes as specified in Configuration file.

We also have the concept of MemorySafetyMargin and MemoryThreshold.  These are used to alert Reporting Services and to start to back off as we need to allocate more memory and we are already pretty full.  These are also configured within the rsreportserver.config.  The default values are 80% and 90% respectively of whatever our Maximum value is set to.

<MemorySafetyMargin>80</MemorySafetyMargin>
<MemoryThreshold>90</MemoryThreshold>

We can also validate these values within the Reporting Services Trace Log.

library!DefaultDomain!19c4!01/29/2015-05:03:19:: i INFO: Initializing MemorySafetyMargin to '80' percent as specified in Configuration file.
library!DefaultDomain!19c4!01/29/2015-05:03:19:: i INFO: Initializing MemoryThreshold to '90' percent as specified in Configuration file.

All of this amounts to when Reporting Services will start triggering memory pressure notifications.  These notifications can either be Low, Medium or High.  The link above has a great image that shows you when each one will trigger.

NOTE:  You will only see the NotifyMemoryPressure items in the log if you have the log set to Verbose.  Specifically the applicationdomain category.

You also have the opportunity to define WorkingSetMaximum and WorkingSetMinimum in the rsreportserver.config if you know that you have other items running on this machine and you don’t want Reporting Services to starve other items.  Or at the minimum, for Reporting Services and the other services to play nice with each other.  This allows us to cap Reporting Services manually instead of going on the derived value.

Out of Memory Condition

Let’s go back to the FAIL_PAGE_ALLOCATION error that I mentioned at the beginning.  If you receive this, we ran out of memory and couldn't recover fast enough and then fail to allocate because nothing is available.

Without verbose logging, we can see the following type of behavior in the Reporting Services Log.

processing!ReportServer_0-2!199c!01/29/2015-05:28:37:: w WARN: Processing Scalability -- Memory Shrink Request Received
processing!ReportServer_0-2!199c!01/29/2015-05:28:38:: w WARN: Processing Scalability -- Memory Shrink Request Received
 Failed allocate pages: FAIL_PAGE_ALLOCATION 1

Here we can see that there were Memory Shrink Requests.  This is an indication that we are starting to hit the ceiling and Reporting Services wants to back off to have more breathing room.  The allocation error caused the process to crash.  It would then restart on it’s own.  There is no other information that is logged.  Nothing in the Event Logs or from the Reporting Services Trace log.

Troubleshooting

rsreportserver.log

The first thing I tend to look at for this is what are the settings defined in the rsreportserver.config.  In the customer’s case, we see the following.

<MemorySafetyMargin>80</MemorySafetyMargin>
<MemoryThreshold>90</MemoryThreshold>
<WorkingSetMaximum>1000000</WorkingSetMaximum>
<WorkingSetMinimum>400000</WorkingSetMinimum>

This alone is a giant red flag.  WorkingSetMaximum looks really low.  I don’t necessarily care about WorkingSetMinimum.  And the defaults for MemorySafetyMargin and MemoryThreshold are fine.

This is the definition of WorkingSetMaximum from the MSDN article referenced at the top of this blog.

WorkingSetMaximum

Specifies a memory threshold after which no new memory allocations requests are granted to report server applications.

By default, the report server sets WorkingSetMaximum to the amount of available memory on the computer. This value is detected when the service starts.

This setting does not appear in the RSReportServer.config file unless you add it manually. If you want the report server to use less memory, you can modify the RSReportServer.config file and add the element and value. Valid values range from 0 to maximum integer. This value is expressed in kilobytes.

When the value for WorkingSetMaximum is reached, the report server does not accept new requests. Requests that are currently in progress are allowed to complete. New requests are accepted only when memory use falls below the value specified through WorkingSetMaximum.

If existing requests continue to consume additional memory after the WorkingSetMaximum value has been reached, all report server application domains will be recycled. For more information, see Application Domains for Report Server Applications.

If this value is specified in kilobytes (KB), then the WorkingSetMaximum defined above is only about 1GB of memory.  That definitely won’t be enough.  We won’t get very far with only 1GB of maximum memory.  From the Reporting Services Trace Log, we can see what the customer’s total physical memory was.

servicecontroller!DefaultDomain!9e80!01/26/2015-13:48:48:: i INFO: Total Physical memory: 137372422144 <--137GB!!!

I’m guessing that the thought was that WorkingSetMaximum was defined in megabytes (MB) instead of kilobytes (KB).  So, if we really wanted 100GB for the WorkingSetMaximum, we would need to add two more 0’s.

Verbose Logging

Verbose Logging can also help you understand the situation a little better, especially if the out of memory condition doesn’t happen right away.  From a memory perspective, I only really care about the appdomainmanager category.  We can set that up for verbose by setting the following within the ReportingServicesService.exe.config file.

<system.diagnostics>
  <switches>
    <add name="DefaultTraceSwitch" value="4" />
  </switches>
</system.diagnostics>
<RStrace>
  <add name="FileName" value="ReportServerService_" />
  <add name="FileSizeLimitMb" value="32" />
  <add name="KeepFilesForDays" value="14" />
  <add name="Prefix" value="appdomain, tid, time" />
  <add name="TraceListeners" value="file" />
  <add name="TraceFileMode" value="unique" />
  <add name="Components" value="all:3;appdomainmanager:4" />
</RStrace>

Here is what the output will look like.  I included some INFO messages to show the flow of what happened.

library!DefaultDomain!33e4!01/29/2015-05:44:31:: i INFO: Initializing MemorySafetyMargin to '80' percent as specified in Configuration file.
library!DefaultDomain!33e4!01/29/2015-05:44:31:: i INFO: Initializing MemoryThreshold to '90' percent as specified in Configuration file.
library!DefaultDomain!33e4!01/29/2015-05:44:31:: i INFO: Initializing WorkingSetMaximum to '1000000' kilobytes as specified in Configuration file.
library!DefaultDomain!33e4!01/29/2015-05:44:31:: i INFO: Initializing WorkingSetMinimum to '400000' kilobytes as specified in Configuration file.

servicecontroller!DefaultDomain!3034!01/29/2015-05:44:35:: i INFO: Total Physical memory: 34289934336

library!ReportServer_0-2!17a0!01/29/2015-05:45:46:: i INFO: RenderForNewSession('/MemoryPressure/MemoryHogContoso')
library!ReportServer_0-2!31f8!01/29/2015-05:45:46:: i INFO: RenderForNewSession('/MemoryPressure/MemoryHogContoso') <-- This is the report request received before we started seeing the shrink requests.

appdomainmanager!DefaultDomain!18e4!01/29/2015-05:45:50:: v VERBOSE: Received NotifyMemoryPressure(pressureLevel=MediumPressure, kBytesToFree=33960)
appdomainmanager!WindowsService_0!18e4!01/29/2015-05:45:50:: v VERBOSE: Memory Statistics: 0 items, 0KB Audited, 0KB Freeable, 924384KB Private Bytes
appdomainmanager!WindowsService_0!18e4!01/29/2015-05:45:50:: v VERBOSE: Spent 3ms enumerating MAP items and 2ms dispatching notifications.

appdomainmanager!DefaultDomain!18e4!01/29/2015-05:45:51:: v VERBOSE: Appdomain (ReportServer) attempted to free 23016 KB.

appdomainmanager!DefaultDomain!18e4!01/29/2015-05:45:52:: v VERBOSE: Received NotifyMemoryPressure(pressureLevel=HighPressure, kBytesToFree=121216)

appdomainmanager!WindowsService_0!18e4!01/29/2015-05:45:52:: v VERBOSE: Skipping shrink request for appdomain (WindowsService_0) because no memory consuming requests are registered.
appdomainmanager!ReportServer_0-2!18e4!01/29/2015-05:45:52:: v VERBOSE: Skipping shrink request for appdomain (ReportServer_MSSQLSERVER_0-2-130670054877986461) because not enough time has passed since last shrink request.

Failed allocate pages: FAIL_PAGE_ALLOCATION 1

Assuming the issue was not due to a low value setting on the WorkingSetMaximum value, I would look to see what report executed before we started seeing the shrink requests and look at how that may be consuming a lot of memory.  Depending on your server, it may be a result of several report requests working together and we would need to see what we can do to stagger them or pull back the amount of data they are consuming. 

If it is due to the number of users hitting the Report Server, you may want to look at going into a scale-out configuration to spread the load. Also, if you are running subscriptions, you could look at offloading those to a separate server from on demand report requests.

Performance Monitor

Performance Monitor (perfmon) can be used to see the consumption as well.  Unfortunately, it won’t really do a lot to help you pinpoint the problem.  It will just help you confirm that you do have a problem.

The counters I used were the following.

Process : Private Bytes (ReportingServicesService)
Process : Virtual Bytes (ReportingServicesService)
Process : Working Set (ReportingServicesService)
Process : Working Set – Private (ReportingServicesService)
ReportServer : Service : Memory Pressure State
ReportServer : Service : Memory Shrink Amount
ReportServer : Service : Memory Shrink Notifications/sec

Unfortunately, the ReportServer : Service counters did not appear to pick anything up.

To wrap up, there isn’t a magic bullet when it comes to memory issues. We need to investigate what the Report Server is doing and what is running. It could be because of a Report, a 3rd Part Extension, custom code segments.  Try to narrow it down to a specific report and go from there.  Also, make sure Reporting Services is playing nice with other services on the machine, if there are any.  You can use WorkingSetMaximum to do that.

Adam W. Saxton | Microsoft Business Intelligence Server Escalation Services
Twitter    YouTube

In an effort to provide an extra level of convenience, we are releasing two features that will simplify the effort to ensure the health of your SQL Virtual Machine and your data. These features, Automated Backup and Automated Patching, automate the processes of backing up and patching your SQL Virtual Machines. Incredibly easy to set up, these features require little input to manage. And these will just be the initial services to be automated.

These services will be available to you for configuring SQL VMs in Azure via Portal and PowerShell. Via PowerShell, you will be able to enable these services for new and existing SQL VMs. In the Azure Portal, you will be able to enable these services when provisioning new VMs.

Automated Backup

This service enables you to configure a backup schedule on your SQL Server 2014 Enterprise and Standard Virtual Machines in a very convenient manner while ensuring your data is backed up consistently and safely. Automated Backup is configured to backup all existing and new databases for the default instance of SQL Server. This simplifies the usual process of configuring Managed Backup for new databases and then for each existing database by combining it into one simple automated setup.

This feature is disabled by default, and once it is enabled, requires very little effort to configure. If you do not wish to change the default settings, no work is required beyond enabling the service. If you wish to customize the settings, you can specify the retention period, storage account, and whether you want encryption to be enabled. The retention period, as is standard for Managed Backup, can be anywhere between 1 and 30 days. The storage account defaults to the same storage account as the VM, but can be changed to any other storage account. This provides you with a DR option, allowing you to back up your databases to storage in another datacenter. If you decide to encrypt your backups, an encryption certificate will be generated and saved in the same storage account as the backups. In this scenario, you will also need to enter a password which will be used to protect the encryption certificates used for encrypting and decrypting your backups. This allows you to not worry about your backups beyond the configuration of this feature, and also ensures you can trust that your backups are secure.

You can see a screenshot of what you will see in the Azure Portal here:

Automated Patching

Many customers told us that they would like to move their patching schedules off business hours. This feature enables you to do exactly this – define the maintenance window that would keep your patch installs in the range you have specified.

When you look on the settings available for the Automated Patching you could find you are familiar with those, because they mimic settings available from the Windows Update Agent (service that drives patching of your Windows machine). Settings are simple and powerful at the same time. All that you need to define to make sure patches are applied when you want is: day of the week, start of the maintenance window, and duration of the maintenance window. It relies on the Windows Update and the Microsoft Update infrastructure and installs any update that matches the ‘Important’ category for the machine.

This feature allows you to patch your Azure Virtual Machines in effective and predictable way even when those VMs are not joined to any domain and not controlled by any patching infrastructure.

There are a number of ways how you can configure Automated Patching, but the easiest way is to use new Azure Portal, you can see how the configuration screen can look like on the screenshot below.

SQL Server IaaS Agent

Both features are part of the new component that will be installed on the VM when features are enabled and this component is called SQL Server IaaS Agent. It is built in the form of Azure VM Extension meaning all the Azure VM Extension concepts are applicable making it perfect tool for the management of SQL in Azure VMs on scale. You can push this IaaS Agent to a number of VMs at once, you can configure, and you can remove or disable it as well.

This IaaS Agent moves SQL Server one step closer to be the best application to run in Azure VMs.

Try these features out for yourself at https://portal.azure.com.

For further details, here is the documentation page for these features.

In calendar year 2014, the SQL Server escalation team had the opportunity to work on several interesting and challenging customers issues. One trend we noticed is that many customers were migrating from old versions of SQL Server running on lean hardware to newer versions of SQL Server with powerful hardware configurations. Typical examples would look like this: SQL 2005 + Win 2003 on 16 cores + 128 GB RAM migrated to SQL 2012 + Win 2012 on 64 cores + 1 TB RAM. The application workload or patterns remained pretty much the same. These servers normally handle workloads that is multiple thousand batches per sec. Under these circumstances, the normal expectation is that the throughput and performance will increase in line with the increase in the capabilities of the hardware and software. That is usually the case. But there are some scenarios where you need to take some additional precautions or perform some configuration changes. These changes were done for specific user scenarios and workload patterns that encountered a specific bottleneck or a scalability challenge.

As we worked through these issues, we started to capture the common configuration changes or updates that were required on these newer hardware machines. The difference in throughput and performance is very noticeable on these systems when these configuration changes were implemented. The changes include the following:

- SQL Server product updates [Cumulative Updates for SQL Server 2012 and SQL Server 2014]

- Trace flags to enable certain scalability updates

- Configuration options in SQL Server related to scalability and concurrency

- Configuration options in Windows related to scalability and concurrency

All these recommendations are now available in the knowledge base article 2964518:

Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 used with high-performance workloads

As we continue to find new updates or tuning options that are used widely we will add them to this article. Note that these recommendations are primarily applicable for SQL Server 2012 and SQL Server 2014. Few of these options are available in previous versions and you can utilize them when applicable.

If you are bringing new servers online or migrating existing workloads to upgraded hardware and software, please consider all these updates and configuration options. They can save a lot of troubleshooting time and provide you with a smooth transition to powerful and faster systems. Our team is using this as a checklist while troubleshooting to make sure that SQL Servers running on newer hardware is using the appropriate and recommended configuration.

Several members of my team and the SQL Server product group contributed to various efforts related to these recommendations and product updates. We also worked with members of our SQL Server MVP group [thank you Aaron Bertrand and Glenn Berry] to ensure these recommendations are widely applicable and acceptable for performance tuning.

We hope that you will implement these updates and configuration changes in your SQL Server environment and realize good performance and scalability gains.

Suresh B. Kandoth

SQL Server Escalation Team

Microsoft SQL Server

Yesterday we announced exciting news for Power BI – a cloud-based business analytics service (software-as-a-service) for non-technical business users.  The preview introduces a number of new Power BI capabilities including dashboards, new visualizations, support for popular software-as-a-service applications, a native iPad app and live “hybrid” connectivity to on-premises SQL Server Analysis Services tabular models. With just a browser – any browser – or a Power BI mobile app, customers can keep a pulse on their business via live operational dashboards. They can explore their business data, through interactive visual reports, and enrich it with additional data sources.

How does it work with SQL Server?
To interact with SQL Server data in Power BI, connect to SSAS server via the ‘Get Data’ menu. From there, you can connect to a model and run queries for visualizations based on that model. Before your users can connect to an SSAS model, an administrator must configure a Power BI Analysis Services connector.

To learn more about the Power BI preview, watch as Michael Tejedor gives Jeremy Chapman from Office Mechanics a first look at what’s new.

The security announcements around Azure SQL Database keep coming. Auditing was implemented a few months ago, and today it was followed by a preview of row-level security. There were also announcements around transparent data encryption (TDE) and a new dynamic data masking feature in future but these aren’t available, even in preview, yet. The row-level security announcements like the one in the SQL Server Team Blog preceded posting the documentation page, so if you originally got a “page not found” it’s up there now.

So I thought I’d provision-up a database (takes about 30 seconds if you have an Azure subscription) and take RLS for a spin. It’s quite important to read the documentation page first, not only because there’s a nice demo in there, but also to get a feel for the implementation details, i.e. what the feature is and what exactly it’s documented to do. I’ll try not to simply regurgitate the documentation here. That’s difficult because it’s quite a nice one-page summary with additional info in the “other resources” section.

RLS is implemented by means of a new schema-scoped object, the SECURITY POLICY object, and inline table-valued functions (TVFs) that implement the policy. The CREATE SECURITY POLICY DDL uses filter predicate clauses to tie the TVFs to individual tables, one filter predicate per table. The TVFs return a one-column table, with a column name of fn_securitypredicate_result. You can use any of the mechanisms and built-in security functions to determine which principals you’re dealing with and you can access which rows they can access with the TVF parameters can be column names. I’d stay away from security functions marked deprecated in books online. Remember, an inline TVF only contains a single SQL SELECT statement.

There a couple of nice things about using inline table-valued functions. First off, the functions need to be defined WITH SCHEMABINDING so there’s no way for someone to simply alter the function unless they drop and re-create the SECURITY POLICY object. In addition, the fact that it’s an inline TVF means that the function shows up as a specific “Filter” iterator in query plans because it’s inlined (unlike multi-statement and scalar TVFs which are not inlined, i.e. have a separate query plan of their own).

You can use ALTER SECURITY POLICY to turn the filters on and off, and to ADD and DROP filters. There are two new metadata tables, sys.security_policies and sys.security_predicates to allow visibility.

Naturally, DDL against SECURITY POLICY and the TVFs can be audited (I did try this out to check that it appeared in the audit file). Because the SECURITY POLICY object references the TVFs, I actually had to grant a number of permissions to allow “Manager” (from the first example in the docs) to create the policy (note: I changed the example a bit to define the SECURITY POLICY in the “Security” schema instead of dbo schema):

GRANT SELECT ON Security.fn_securitypredicate TO Manager;
GRANT REFERENCES ON Security.fn_securitypredicate TO Manager;
GRANT REFERENCES ON SCHEMA::dbo TO Manager;  — Needed because the table lives in DBO schema
GRANT ALTER ANY SECURITY POLICY TO Manager;

— This allows ALTER or DROP, but not to CREATE unless ALTER ANY SECURITY POLICY is also given
— This may not be granular enough, unless the RLS objects are the only ones in the schema.
GRANT ALTER ON SCHEMA::Security TO Manager

It looks like this feature has the potential to make it into the box-product SQL Server in future (the docs refer to it not being supported with memory-optimized tables for example, which Azure SQL Database doesn’t support yet), so even if you don’t use Azure SQL Database, the documentation page provides a good opportunity to CAREFULLY evaluate whether it will meet your needs, and get your comments/requests in now. There are four specific places in the documentation page to look at:

1. Limitations during the preview (e.g. limitation on views)
2. Limitations and restrictions (e.g. DBCC SHOW_STATISTICS)
3. The Description section (e.g. INSERT anywhere is allowed, not filtered)
4. A special section called “Security Note: Side-Channel Attacks”

I’d propose that the “INSERT anywhere not blocked” limitation is the biggest worry (modulo the side-channel attacks), especially in the multi-tenant (listed as a specific use-case) scenario. Although conceivably you might ameliorate this with an INSERT trigger. This doesn’t address the problem of someone changing CONTEXT_INFO though (as is mentioned in the doc), you’d need to set up different roles for each tenant as well.

Cheers, Bob (@bobbeauch)

The post Taking the Azure SQL Database row-level security preview for a spin appeared first on Bob Beauchemin.

Being a database administrator has many responsibilities, and knowing what is happening on your SQL Server is one of them. Being proactive and alerted to errors is one of the traits that makes someone a great DBA. And I’m not just talking about things failing, which is what most people think of being alerted about; you can also be alerted about performance problems. Within SQL Server you have the ability to create SQL Server Agent Alerts (which I’ll just call ‘alerts’ from now on), and this is easily accomplished using the GUI or T-SQL.

Configuring SQL Server Agent Alerts

To use alerts you must have Database Mail and a SQL Agent Operator configured. Most SQL instances I have come across already have Database Mail configured for job failure notifications. If you need further information on setting up this feature, visit the Books Online topic, "Configure Database Mail."

A lesser-known task is configuring the Operator. You can create the Operator using SSMS or T-SQL. Within SSMS expand SQL Server Agent, right click on Operator and chose New Operator. You will have a new dialog box open where you can give the operator a name and specify the email address to notify. I prefer to use a distribution group for the email notifications. Most companies have more than one person responsible for the SQL environment and if you specify a distribution group then the entire team can be notified of the alerts. Using distribution groups also makes it much easier to add or remove people from the alerts.

Below is an example screenshot of the New Operator dialog:

I prefer using T-SQL so I can make sure that creating the Operator is part of a server build template. Example code for creating the above Operator is as follows:

EXEC msdb.dbo.sp_add_operator @name = N'SQL_Alerts', 
  @enabled = 1, 
  @email_address = N'sql_alerts@mydomain.com';

Once you have Database Mail and the Operator configured you can create the alerts and assign them to the Operator.

If using SSMS, you can expand SQL Server Agent and then Alerts. By default, no alerts are created. If you right click and chose New Alert, you will get a screen similar to the figure below:

You will notice that under Severity, there are 25 severity codes. Just like it sounds, error level severity describes how important the error is. Severity 10 is informational while 19-25 are fatal and you will want to be notified when those errors arise. If a severity 23 error arose, for example, then you most likely have corruption in one of your databases. These fatal errors can all impact the performance of your server, which in turn impacts the customer experience.

There is an additional alert that you need to create, for error 825. Error 825, as Paul Randal describes in his blog post, is related to an I/O operation that SQL Server had to retry but that eventually succeeds (whereas errors 823 and 824 indicate that an I/O retry operation was retried and eventually failed). Error 825 is critical to know about because it is alerting you to I/O issues that could end up becoming fatal in future. Any retry attempt is bad, you shouldn’t wait until an I/O operation fails to be notified. If you start getting Error 825 messages, you need to immediately reach out to your storage and hardware teams.

You can create each of the Alerts by specifying the name and selecting the severity. For Error 825 you would select Error and type the number. As with the Operator, I prefer to use T-SQL. If I can easily script a process then it is much easier to reuse and include as part of a server build.
Below you will find the script that I have used on my SQL Server 2014 Developer workstation. This script creates each of the alerts and adds a notification for the alert to the Operator SQL_Alerts.

EXEC msdb.dbo.sp_add_alert @name = N'Severity 19 Error', 
  @message_id = 0,   @severity = 19,  @include_event_description_in = 0;
 
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 19 Error', 
  @operator_name = N'SQL_Alerts', @notification_method = 1;
 
EXEC msdb.dbo.sp_add_alert @name = N'Severity 20 Error', 
  @message_id = 0,   @severity = 20,  @include_event_description_in = 0;
 
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 20 Error', 
  @operator_name = N'SQL_Alerts', @notification_method = 1;
 
EXEC msdb.dbo.sp_add_alert @name=N'Severity 21 Error', 
  @message_id = 0,   @severity = 21,  @include_event_description_in = 0;
 
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 21 Error', 
  @operator_name = N'SQL_Alerts', @notification_method = 1;
 
EXEC msdb.dbo.sp_add_alert @name = N'Severity 22 Error', 
  @message_id = 0,   @severity = 22,  @include_event_description_in = 0;
 
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 22 Error', 
  @operator_name = N'SQL_Alerts', @notification_method = 1;
 
EXEC msdb.dbo.sp_add_alert @name = N'Severity 23 Error', 
  @message_id = 0,   @severity = 23,  @include_event_description_in = 0;
 
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 23 Error', 
  @operator_name = N'SQL_Alerts', @notification_method = 1;
 
EXEC msdb.dbo.sp_add_alert @name = N'Severity 24 Error', 
  @message_id = 0,   @severity = 24,  @include_event_description_in = 0;
 
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 24 Error', 
  @operator_name = N'SQL_Alerts', @notification_method = 1;
 
EXEC msdb.dbo.sp_add_alert @name = N'Severity 25 Error', 
  @message_id = 0,   @severity = 25,  @include_event_description_in = 0;
 
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 25 Error',
  @operator_name = N'SQL_Alerts', @notification_method = 1;
 
EXEC msdb.dbo.sp_add_alert @name = N'Error 825', 
  @message_id = 825,  @severity = 0,  @include_event_description_in = 0;
 
EXEC msdb.dbo.sp_add_notification @alert_name = N'Error 825',
  @operator_name = N'SQL_Alerts', @notification_method = 1;

If you have followed along, you would have database mail configured, created an Operator to email you or a distribution group about potential errors, and SQL Server Agent Alerts configured for Severity 19 – 25 and error 825.

This is great. Any time one of those alerts are triggered an email will be sent to your team. In addition to event alerts, alerts can be configured for a performance condition, as I mentioned in the introduction. For example, if memory usage exceeds a defined threshold an alert could be triggered. I encourage you to explore the various performance alerts and create the ones your organization could benefit from. To find the SQL Server performance condition alerts, in the new alert dialog box, click the drop down box for Type. There you will see SQL Server performance condition alert listed. Once you chose that option you can browse the types of objects you can configure a performance condition alert on.

While we assigned an Operator to the alert response, you could also configure the alert to execute a SQL Agent job. While this gives you some flexibility to have event response task, it doesn’t provide the ability to have easy conditional alerting.

Using SQL Sentry for Advanced Alerting

For more advanced alerting, you need a better tool. This is where SQL Sentry can help. One of my favorite SQL Sentry alerting features is the ability to create custom conditions to alert or act when something has changed within the environment. For example, if someone changed the min or max memory value, modified maxdop or the cost threshold for parallelism you could get an alert or even kick off a process. This feature was introduced in SQL Sentry v8, and Greg Gonzalez (blog | @SQLsensei) blogged about it here: "SQL Sentry v8: Intelligent Alerting Redefined."

With this feature, you can also create custom conditions for different databases within a single alert. If you attempted this using SQL Agent alerts you would have to create different alerts per database.

Another great alerting feature is the ability to create different alerting schedules. Many organizations have teams that are responsible during different parts of the day. Some may have the production DBA’s responsible during the daytime hours with a Network Operations Center covering the night shift, then an on-call person over the weekends. Wouldn’t it be great to be able to customize an alerting schedule to notify the proper teams during their hours of responsibility?

You can create Alert Windows (as in a window of time) and tie those to different alerts or groups. This allows different alerts to be active during different times and for different groups to be notified at different times. This is really cool as it lets your alerting follow a support schedule so the correct people are notified. Scott Fallen details this feature in a blog post, "Alerting on a Call Schedule with SQL Sentry," walking you through creating alerts for various on-call teams.

Another alerting feature of Performance Advisor and Event Manager is the ability to configure other responses such as executing a Windows process, logging the event to a database or error log, sending an SNMP trap to another monitoring tool such as SCOM, or even killing a process. Your options are almost limitless as to what you can have predefined to happen when a certain event occurs. SQL Agent Alerts are not that customizable.

Summary

The important take away from this post is that you absolutely need to be alerting for errors and performance conditions. If you don’t have a tool such as SQL Sentry then utilizing SQL Agent Alerts is still a great start.

Over my next few posts, I’ll be diving into some of these performance impacting alerts and discussing what actions you would need to take when they arise.

The post SQL Server Agent Alerts appeared first on SQLPerformance.com.

The problem with SQL Injection is that most people don’t realise the fundamental concept which makes SQL Injection vulnerability not only easy to spot, but also easy to prevent. And it’s the thing that SQL Injection has in common with countless other hacking mechanisms that have been around since the early days of computing.

The simple truth is this: You are vulnerable if you confuse DATA with COMMAND.

SQL Injection occurs when a piece of user-entered data makes its way into the command that is being executed. Not simply as a piece of data, such as the value that a column is being compared to, but when it becomes something which actually gets executed. Buffer overflow attacks worked in the same way – although the method of getting the data to be executed is different.

So you should never put yourself in a situation where something that a user has entered gets executed. It doesn’t matter what sanitisation you have done, you should never do it. Creative hackers will be able to find ways around however you do your sanitisation. Just don’t run something that has come from the user – pass it in using parameters instead.

Code within a stored procedure is trustworthy – SQL code written in a stored procedure can be trusted. But...

Code within a web application is trustworthy – SQL code written in a web application can be trusted. But...

...but all this can be undermined if your developers tell user-entered code to be executed, rather than being treated as parameters.

(Of course, if your server is compromised and someone can tell rogue commands to run, then all bets are off – I’m just looking at SQL Injection here)

What happens with SQL Injection is this: a COMMAND is constructed that includes user-entered DATA, and is then executed. It doesn’t matter whether this is done within .NET code, or within perl code, or within a stored procedure – you just don’t ever execute data.

There are plenty of guides to avoiding SQL Injection. They say to avoid using dynamic SQL; to avoid ad hoc SQL; to only use stored procedures. Most of them miss the point. They’re not bad, but following these rules doesn’t make you injection-proof. You simply don’t ever execute anything that came from the user.

Dynamic SQL is fine. You can construct a query based on user input – so long as you don’t execute something that came from the user. If the user chooses a particular option, the dynamically-generated SQL can include or exclude some predicate, or can bring a new parameter into the mix. This gives the freedom of dynamic SQL without needing to become vulnerable to SQL Injection.

Ad hoc SQL is also fine, and you don’t need to use stored procedures. It doesn’t matter whether your SQL code is stored in a stored procedure within the database, or whether your SQL code is stored within your application.

Ad hoc code or dynamic SQL like this is okay:

if (UserInputtedFirstName.Length > 0) {
   cmd += " AND u.FirstName = @fn ";
   params.Add("@fn", .......
   ......
}

but code like this is not:

if (UserInputtedFirstName.Length > 0) {
   cmd += " AND u.FirstName = " + Sanitise(UserInputtedFirstName);
   ......
}

...no matter what the Sanitise functionality is. The only way to protect against SQL Injection is to only use controlled commands, and never anything else.

But what about generic procedures, that developers use to be able to be able to query whichever objects they want?

It’s easy to simply “Just say no”, to procedures like this. After all – it’s not healthy for all kinds of reasons, such as the plan cache, such as the effective use of indexes, and more. But if you have a stored procedure like:

create proc dbo.GetAnyData(@tablename nvarchar(256), @colname nvarchar(256), @filtercol nvarchar(256), @filterval sql_variant) as
begin
   declare @qry nvarchar(max) = 'select ' + @colname + ' from ' + @tablename + ' where ' + @filtercol + ' = @val';
   exec sp_executesql @qry, '@val sql_variant', @val = @filterval;
end

...with an application that uses it, then you may be somewhat worried (and legitimately so) about SQL Injection, and be wondering how you’re going refactor all the code that uses this procedure.

So how do you make this safe from SQL Injection, so that the calls to the procedure aren’t susceptible, while all the redevelopment is done?

Hopefully you know the answer – you need to control the commands.

The issue is not with the use of @filterval – that’s being passed in as data, and is being used as data. It’s never part of the command itself. The problem is that @tablename, @colname and @filtercol are all passed in from the calling application, potentially even from the user, and then used as part of the command. This is what needs to be fixed.

Luckily, we have access to a bunch of things that we know can be used here – actual table names and actual column names in the database’s catalog views. So therefore, we can safely use those things. They are controlled.

create proc dbo.GetAnyData(@tablename nvarchar(256), @colname nvarchar(256), @filtercol nvarchar(256), @filterval sql_variant) as
begin
   declare @knowntable nvarchar(256), @knowncol nvarchar(256), @knownfiltercol nvarchar(256);

   select @knowntable = quotename(object_name(object_id)), @knowncol = quotename(name)
   from sys.all_columns
   where object_id = object_id(@tablename) and name = @colname;
  
   select @knownfiltercol = quotename(name)
   from sys.all_columns
   where object_id = object_id(@tablename) and name = @filtercol;

   declare @qry nvarchar(max) = 'select ' + @knowncol + ' from ' + @knowntable + ' where ' + @knownfiltercol + ' = @val';
   if @qry is not null
      exec sp_executesql @qry, N'@val sql_variant', @val = @filterval;
end

I’m not saying this is good. I’m saying this is SAFE. We don’t execute anything that we don’t already control. The only tables we can query are ones that we know actually exist. The only columns that we can use are ones that must exist. You can always add more logic, to make sure that people can’t query system objects, and things like that – but they definitely can’t execute anything overly malicious. They can’t make calls to sp_configure or to xp_cmdshell. They can’t drop tables or update values. If you need to restrict the columns, you can easily filter the queries on all_columns, so that the calling code can’t access so much.

SQL Injection is serious. It’s a huge problem for people who deal with databases – and not just SQL Server, but all databases. The solution is easy, but requires some level of discipline.

Don’t execute user input, and don’t let your developers do it either (and if that means forcing them to use stored procedures that you can audit, then fine – do whatever it takes).

Oh, and as this month’s T-SQL Tuesday (hosted by Kenneth Fisher – @sqlstudent144) is on the topic of security, that’s what this post is on.

@rob_farley

The problem with SQL Injection is that most people don’t realise the fundamental concept which makes SQL Injection vulnerability not only easy to spot, but also easy to prevent. And it’s the thing that SQL Injection has in common with countless other hacking mechanisms that have been around since the early days of computing.

The simple truth is this: You are vulnerable if you confuse DATA with COMMAND.

SQL Injection occurs when a piece of user-entered data makes its way into the command that is being executed. Not simply as a piece of data, such as the value that a column is being compared to, but when it becomes something which actually gets executed. Buffer overflow attacks worked in the same way – although the method of getting the data to be executed is different.

So you should never put yourself in a situation where something that a user has entered gets executed. It doesn’t matter what sanitisation you have done, you should never do it. Creative hackers will be able to find ways around however you do your sanitisation. Just don’t run something that has come from the user – pass it in using parameters instead.

Code within a stored procedure is trustworthy – SQL code written in a stored procedure can be trusted. But...

Code within a web application is trustworthy – SQL code written in a web application can be trusted. But...

...but all this can be undermined if your developers tell user-entered code to be executed, rather than being treated as parameters.

(Of course, if your server is compromised and someone can tell rogue commands to run, then all bets are off – I’m just looking at SQL Injection here)

What happens with SQL Injection is this: a COMMAND is constructed that includes user-entered DATA, and is then executed. It doesn’t matter whether this is done within .NET code, or within perl code, or within a stored procedure – you just don’t ever execute data.

There are plenty of guides to avoiding SQL Injection. They say to avoid using dynamic SQL; to avoid ad hoc SQL; to only use stored procedures. Most of them miss the point. They’re not bad, but following these rules doesn’t make you injection-proof. You simply don’t ever execute anything that came from the user.

Dynamic SQL is fine. You can construct a query based on user input – so long as you don’t execute something that came from the user. If the user chooses a particular option, the dynamically-generated SQL can include or exclude some predicate, or can bring a new parameter into the mix. This gives the freedom of dynamic SQL without needing to become vulnerable to SQL Injection.

Ad hoc SQL is also fine, and you don’t need to use stored procedures. It doesn’t matter whether your SQL code is stored in a stored procedure within the database, or whether your SQL code is stored within your application.

Ad hoc code or dynamic SQL like this is okay:

if (UserInputtedFirstName.Length > 0) {
   cmd += " AND u.FirstName = @fn ";
   params.Add("@fn", .......
   ......
}

but code like this is not:

if (UserInputtedFirstName.Length > 0) {
   cmd += " AND u.FirstName = " + Sanitise(UserInputtedFirstName);
   ......
}

...no matter what the Sanitise functionality is. The only way to protect against SQL Injection is to only use controlled commands, and never anything else.

But what about generic procedures, that developers use to be able to be able to query whichever objects they want?

It’s easy to simply “Just say no”, to procedures like this. After all – it’s not healthy for all kinds of reasons, such as the plan cache, such as the effective use of indexes, and more. But if you have a stored procedure like:

create proc dbo.GetAnyData(@tablename nvarchar(256), @colname nvarchar(256), @filtercol nvarchar(256), @filterval sql_variant) as
begin
   declare @qry nvarchar(max) = 'select ' + @colname + ' from ' + @tablename + ' where ' + @filtercol + ' = @val';
   exec sp_executesql @qry, '@val sql_variant', @val = @filterval;
end

...with an application that uses it, then you may be somewhat worried (and legitimately so) about SQL Injection, and be wondering how you’re going refactor all the code that uses this procedure.

So how do you make this safe from SQL Injection, so that the calls to the procedure aren’t susceptible, while all the redevelopment is done?

Hopefully you know the answer – you need to control the commands.

The issue is not with the use of @filterval – that’s being passed in as data, and is being used as data. It’s never part of the command itself. The problem is that @tablename, @colname and @filtercol are all passed in from the calling application, potentially even from the user, and then used as part of the command. This is what needs to be fixed.

Luckily, we have access to a bunch of things that we know can be used here – actual table names and actual column names in the database’s catalog views. So therefore, we can safely use those things. They are controlled.

create proc dbo.GetAnyData(@tablename nvarchar(256), @colname nvarchar(256), @filtercol nvarchar(256), @filterval sql_variant) as
begin
   declare @knowntable nvarchar(256), @knowncol nvarchar(256), @knownfiltercol nvarchar(256);

   select @knowntable = quotename(object_name(object_id)), @knowncol = quotename(name)
   from sys.all_columns
   where object_id = object_id(@tablename) and name = @colname;
  
   select @knownfiltercol = quotename(name)
   from sys.all_columns
   where object_id = object_id(@tablename) and name = @filtercol;

   declare @qry nvarchar(max) = 'select ' + @knowncol + ' from ' + @knowntable + ' where ' + @knownfiltercol + ' = @val';
   if @qry is not null
      exec sp_executesql @qry, N'@val sql_variant', @val = @filterval;
end

I’m not saying this is good. I’m saying this is SAFE. We don’t execute anything that we don’t already control. The only tables we can query are ones that we know actually exist. The only columns that we can use are ones that must exist. You can always add more logic, to make sure that people can’t query system objects, and things like that – but they definitely can’t execute anything overly malicious. They can’t make calls to sp_configure or to xp_cmdshell. They can’t drop tables or update values. If you need to restrict the columns, you can easily filter the queries on all_columns, so that the calling code can’t access so much.

SQL Injection is serious. It’s a huge problem for people who deal with databases – and not just SQL Server, but all databases. The solution is easy, but requires some level of discipline.

Don’t execute user input, and don’t let your developers do it either (and if that means forcing them to use stored procedures that you can audit, then fine – do whatever it takes).

Oh, and as this month’s T-SQL Tuesday (hosted by Kenneth Fisher – @sqlstudent144) is on the topic of security, that’s what this post is on.

@rob_farley

We recently released the Automated Backup and Automated Patching features. These features automate the processes of backing up and patching your SQL Virtual Machine to provide an added level of convenience for your VMs. We’d like to outline some best practices for these features to ensure that you get the most out of these features.

Automated Backup

Backup of Encryption Certificates and Data

When backup encryption is enabled, we strongly recommend that you ascertain whether the encryption certificate has been successfully created and uploaded to ensure restorability of your databases. You can do so by creating a database right away and checking the encryption certificates and data were backed up to the newly created container properly. This will show that everything was configured correctly and no anomalies took place.

If the certificate failed to upload for some reason, you can use the certificate manager to export the certificate and save it. You do not want to save it on the same VM, however, as this does not ensure you have access to the certificate when the VM is down. To know if the certificate was backed up properly after changing or creating the Automated Backup configuration, you can check the event logs in the VM (Figure 1), and if it failed you will see this error message:

Figure 1: Error Message Shown in Event Log in VM

If the certificates were backed up correctly, you will see this message in the Event Logs:

Figure 2: Successful Backup of Encryption Certificate in Event Logs

As a general practice, it is recommended to check on the health of your backups from time to time. In order to be able to restore your backups, you should do the following:

1. Confirm that your encryption certificates have been backed up and you remember your password. If you do not do this, you will not be able to decrypt and restore your backups. If for some reason your certificates were not properly backed up, you can accomplish this manually by executing the following T-SQL query:
   
   BACKUP MASTER KEY TO FILE = <file_path> ENCRYPTION BY PASSWORD = <password>
   BACKUP CERTIFICATE [AutoBackup_Certificate] TO FILE = <file_path> WITH PRIVATE KEY (FILE = <file_path>, ENCRYPTION BY PASSWORD = <password>)
   
   
2. Confirm that your backup files are uploaded with at least 1 full backup. Because mistakes happen, you should be sure you always have at least one full backup before deleting your VM, or in case your VM gets corrupted, so you know you can still access your data. You should make sure the backup in storage is safe and recoverable before deleting your VM’s data disks.

Disaster Recovery

It is recommended that you select a storage account in a different region for your backups to provide Disaster Recovery for your data. Putting your backups in another region is critical for scenarios when a datacenter goes down and you need uninterrupted access to your data. However, if you have more interest in a short recovery time, rather than disaster recovery, then it may be better to store your backups in the same region. This decision depends on your specific requirements.

Encryption Password

Be sure to use a strong password to protect your certificates. Have some method of ensuring that you remember the password when the time comes to decrypt and restore your backup.

Automated Patching

Schedule

Be sure to schedule the Patching window during a time with low workload, but when the VM is still active. If you schedule during a window where the VM will be down, patching will not take place.

Windows Update compatibility boundaries

If you would like to manually install a specific update that you see in the Windows Update UI, you can do this with no interference to Automated Patching. However, keep in mind that turning Windows Update into automatic install mode will cause Automated Patching to be disabled. Despite this, all settings will persist, and you should manually re-enable Automated Patching to continue using it.

Azure only

Both Automated Backup and Automated Patching heavily rely on Azure VM Agent infrastructure. This means that there is no support for on-premises solution. If you plan to move your VM from Azure to any other environment, plan on uninstalling both SQL Server IaaS Agent and Azure VM Agent.

Try these features out for yourself at https://portal.azure.com.

If you haven’t already, start a free trial on SQL Server in Virtual Machines today.

This month’s T-SQL Tuesday (hosted by Kenneth Fisher – @sqlstudent144) is about security This hasn’t been my area of expertise for a long time, although I did write a long TechNet Magazine article about common security issues and solutions back in 2009.

There’s a huge amount that’s been written about implementing security in SQL Server and Windows – working towards the security of the data while it’s in the database, being sent to the client application, and within the client application. This can be incredibly important for your business and your clients and so the focus there is justifiable.

However, I think there’s an aspect to data security that’s often completely overlooked: physical security.

Consider the infrastructure in your environment, and ask yourself the following questions:

• Are the servers running SQL Server physically secured so only authorized people have access to them? I’m not just talking about whether someone can walk out with a server under their arm (and then get the hard drives with the data on – the actual server hardware isn’t a physical security risk if there is no data storage in it), although this is something you should consider. I also want you to consider whether an unauthorized person can walk up to such a server and insert a USB drive that could have an auto-run program on it that installs some kind of security hole.
• And what about if the server has server-local storage? An unauthorized person could grab a hard drive from a server and clone it really quickly, maybe overnight so no-one’s available onsite to see why the server went down. Here‘s a link on Amazon to a machine we use for quickly cloning laptop hard drives when we upgrade them. Really useful, but also useful in the hands of someone with nefarious aims.
• Are the storage arrays where the data resides physically secured so only authorized people have access to them? And what about the routers? Here is a thread from the Dell support forums about making an MD3000i password reset cable from scratch. You don’t want someone to be able to physically reset the password on some storage array, and then make a connection to it from an unauthorized server on the network and gain access to the data on the drives. And then there’s the question of someone just popping out some of the drives and walking away with them…
• Are there cameras monitoring all of the above?
• For the questions above, now ask them about your failover data center. And what if you data center is hosted? Does the hoster guarantee physical security of your data?
• Now let’s think about your admin users. What kind of physical security protects the desktops of the people with secure access to the data? Is it possible for them to walk away and leave their screen unlocked? Is it possible for someone to walk up to their computer and plug in a USB drive with some auto-run software on it?
• Now let’s think about your admin users’ laptops. Same questions as above. What about if they take their laptops home? Or they use their own systems at home? Are they physically secured so someone can’t access your data from these people’s systems?

Still think your data is secure?

I recently was working with a customer who had purchased a new Lenovo ThinkServer RD640 with two 22nm Intel Xeon E5-2697 v2 Ivy Bridge-EP processors. This processor was introduced in Q3 of 2013, and it is one generation behind the latest Intel Xeon E5-2600 v3 series processors.

This server had a new, default installation of Windows Server 2012 R2 Standard Edition, which meant that it was using the default Windows Balanced Power Plan. Running CPU-Z 1.71.1 showed the actual core speed of Core #0 while the system was at rest, with the Balanced Power Plan (Figure 1) and with the High Performance Power Plan (Figure 2).

Figure 1: CPU-Z Results with Balanced Power Plan

Changing the Power Plan to High Performance had an immediate effect on the processor core speed, as shown in Figure 2.

Figure 2: CPU-Z Results with High Performance Power Plan

Here are the Geekbench results for the default Balanced Power Plan (Figure 3) and the High Performance Power Plan (Figure 4). The Single-Core score is more relevant here, since the 32-bit GUI version of Geekbench 3.3 only uses 32 total cores (and there are 48 logical cores in this server).

Figure 3: Geekbench 3.3 Results with Balanced Power Plan

Figure 4: Geekbench 3.3 Results with High Performance Power Plan

You need to keep in mind that your BIOS power management settings will override your Windows Power Plan settings, so it is very important to check what is going on with CPU-Z. For you virtualization users, you need to check what your hypervisor power management settings are doing, because they will override what is happening with the guest OS Windows Power Plan settings.

Not only does power management affect your processor core speed (and CPU/memory benchmarks like Geekbench), but it also affects things like the performance of your PCIe expansion slots. In case you want some official Microsoft guidance on this subject, the Microsoft KB article linked below discusses it in quite a bit of detail.

Slow Performance on Windows Server 2008 R2 when using the “Balanced” Power Plan

This issue has been around since Windows Server 2008, and still exists with Windows Server 2012 R2.

The post Windows Power Plan Effects on Newer Intel Processors appeared first on Glenn Berry.

In the world of software development, you have customers, who request and ultimately use the software, and development organizations, who build and test said software. Of course, it's never that simple. At some point, the customer has to try using the software in order to sign off on it.

At least in a sane business arrangement. Sometimes, things get all twisted around.

TS found himself in the middle of a mind-bending dispute with a client.

You see, the client provided specifications as to what the software should do. TS' developers built the software to do exactly that. Afterward, the developers and QA folks did assorted rounds of testing. The package was then provided to the client so that they could do UAT. If all was well, the product would be deployed for general use.

This normalcy continued for several releases of the product.

Until...

One day, after invoicing the client for the work. the client responds with a check, and an invoice of their own for the time they put in to perform UAT on the software!

Wait, wha? TS called the client and inquired WTF they were talking about. After all, who accepts custom software without at least making sure it does what it was requested to do? The conversation left TS befuddled...

  TS:  Why are you billing us for acceptance testing? Only the customer can accept
       the software!
  Clt: It's taking us all kinds of time to verify your software does what we requested
  TS:  We do testing too, but ultimately you need to verify we did our jobs properly 
  Clt: Irrelevant: if we have to put in this much time to verify that your software
       works properly, then we should be reimbursed
  TS:  Wait, let me get The Big Boss in on this...

At this point, TS sent an email to The Big Boss with the subject:

   Urgent: Client wants to bill us for time they spent doing UAT

Needless to say, The Big Boss was equally befuddled, and set up a call with the customer.

  TS:  Client, this is The Big Boss, Big Boss, this is the Client...
  TBB: I understand you want to bill us for time you spend doing UAT?
  Clt: That's right; it's taking a lot of our time. The software should just work!
       We have been paying the cost of doing this testing since the inception of this
       contract and feel that we shouldn't pay for it any more!
  TBB: Ok, no problem. We will no longer ask you to do UAT, as long as you agree,
       in writing, that you'll accept our testing as sufficient and definitive;
       Any issues that crop up will be considered changes and/or enhancements and will
       be billed accordingly
  Clt: Unacceptable! We need to make sure your software works before we will accept it
  TBB: That is responsible thinking. Of course, it's also your responsibility, which is
       why we will not pay you to do it

This went around and around for a while until the client finally saw the light and rescinded the invoice.

[Advertisement] Use NuGet or npm? Check out ProGet, the easy-to-use package repository that lets you host and manage your own personal or enterprise-wide NuGet feeds and npm repositories. It's got an impressively-featured free edition, too!

Ah, Perl. Often derided as a "write-only language", Perl nevertheless enjoys some level of popularity among WTF-savvy workplaces. So when David inherited a codebase through the purchase of a company staffed entirely with, what he had dubbed "mentally challenged chimpanzees", it seemed only natural that the code was entirely in Perl.

Dealing with financial transactions is one of the key features required for any online shopping or banking applications, and as such, it's more or less a solved problem by now. So of course, the perl gurus at ChimpanCorp reinvented the wheel with their Real-time Financial Processing app. It would calculate the user's balance every ten minutes by compiling all data posted since the last calculation, leading to a balance that was never more than ten minutes out of date! Of course, that's as good as you can possibly get when the calculation takes nine minutes to run. Still, the client was wondering if maybe the could speed up the calculations so it could run, say, every five minutes? Or maybe, hypothetically speaking, immediately after every transaction?

David dug into the codebase, clocking various methods -- it was best to limit his exposure to the actual code as much as possible, given the warning labels attached to the documentation. Something about eye strain and fecal matter... In any event, he finally pinpointed one method that was taking seven minutes to run. If he could shave off a few minutes from that, they might be able to hit the mythical five-minute benchmark.

The code, of course, had to run twenty queries on un-indexed SQL; adding a few indexes removed two minutes from the runtime, which unfortunately wasn't quite enough. So he dug into the queries themselves, carefully commenting as he went, saving a few seconds here and there with better optimized joins. And then he came to this gem:

my $mysql = 'SELECT TOP (1) n = (ROW_NUMBER() OVER (ORDER BY number))-1 FROM [master]..spt_values ORDER BY n';
my $mysth = $dbh->prepare($sql);
$mysth->execute(1, 10);
while (my @row = $sth->fetchrow_array) {
   $OurZero= $row[0];
}

David laughed, replacing this with a simple "$OurZero = 0", and moved on. One query down, only nineteen more to optimize. Four hours later, he reached the bottom of the method. This was it, what the entire seven-minute runtime was calculating:

return ($ReturnValue * $OurZero); 

David stared at the return value, then up at the method he'd been labouring to understand all day. Seven. Minutes. With a final sigh, he went to the calling function and replaced the execution with this super-optimized bit of perl:

if (1 == 2)
{
 #Only run this if you've lost your mind
 CalculateZero();
} 

Just as he checked his revision into the repository, a coworker popped his head into David's cube. "David, you will not BELIEVE how they're calculating dates!" she gushed.

"Somehow, I suspect I will."

[Advertisement] BuildMaster is more than just an automation tool: it brings together the people, process, and practices that allow teams to deliver software rapidly, reliably, and responsibly. And it's incredibly easy to get started; download now and use the built-in tutorials and wizards to get your builds and/or deploys automated!

Guest Post by SQL Server Certified Master Warner Chaves

Hello everybody, my name is Warner Chaves, a SQL Server DBA for almost 10 years, MCM, and frequent community contributor. I currently work at Pythian and when speaking to many of our clients I frequently get asked to highlight for them some of the compelling reasons to upgrade to SQL 2014. In response to that, I created this 3 part series of the most underrated features in 2014 covering DelayedDurability, Parallel SELECT INTO, and New Partition Operations. Please comment, share opinions, or let me know of any other features that you would like to me to cover.

In this second video in the series, I discuss the significance of New Partition Operations, which make online index rebuilds and incremental statistics much more efficient. “For many clients, it was really hard to find maintenance windows that were big enough to actually rebuild the entire set [of partition tables] when they had fragmentation issues. 2014 now has online index rebuild on a partition level.” In this video, The Most Underrated Features of SQL Server 2014 — Part 2, you’ll hear use cases for new partition operations and learn why incremental statistics are more efficient.

About Warner Chaves

Warner is a SQL Server Certified Master and a Principal Consultant at Pythian, a global Canadian-based company specialized in DBA services. After working for enterprise customers in Hewlett-Packard's ITO organization he transitioned to his current position at Pythian, managing multiple customers and instances while leading a highly talented team of SQL Server DBAs. When he's not working out of his office in Ottawa, Ontario he can be found in the sandy beaches of his home country of Costa Rica.

Twitter | Pythian Blog |  SQL Turbo Blog

.

The core goal of Stack Exchange is education. Everything we build is geared toward helping people learn from one another — not just the nuts and bolts of their profession or passion, but the universal skills of how to better communicate and learn. As we’ve grown as a company, we’ve benefited from many resources to assist in educating developers out of our office space, including hosting local meetups and partnering with the Flatiron School and Fog Creek for a mentorship program.

Today, we’re excited to announce our partnership with the New York City Tech Talent Pipeline, Mayor de Blasio’s new initiative designed to increase the number of qualified candidates for open tech positions in New York City.

That’s nice. But how?

The city has brought together a number of major companies that hire developers in NYC and asked us to do two things:

1. Join a committee designed to help the city identify ways it can use its resources to attack the problem more broadly (through the education system, etc.), and
2. Implement programs we can run, possibly with the help and support of other awesome like-minded tech companies in the city.

On the first point, we’re excited that our VP of Engineering, David Fullerton, will be sitting in on quarterly meetings with other tech industry leaders convened by Mayor de Blasio, where we hope we can help to represent the developer voice and to share what skills and technologies we know are most in-demand.

For the second, we’ve already brought in a bunch of (awesome) NYC companies — including Trello, Kickstarter, Foursquare, Tumblr, and Control Group — who will build and teach a new curriculum of programmer “soft skills” to graduates of public computer science programs in New York (starting with the CUNY system) that will better equip them as professional developers.

The goal is to make sure that anyone in this city with a passion for technology, no matter who they are or what neighborhood they grew up in, can get the mentoring, training, and support they need to succeed as a developer.

Why are we doing this?

As you probably know, there’s a vast disparity between open tech jobs and qualified developers in today’s market. At last count, there are nearly 5 job openings for every one job-seeking developer. With New York City’s current tech job count teetering at around 300,000 job openings, we need to increase the number of good candidates or a lot of  websites aren’t going to get built. The city needs developers. And this happens to be an area that we know a thing or two about. Our goal is to support and empower developers, no matter where they may be in their programming careers. Despite our well-known belief in remote work, our founder has always been a particular proponent of building great places in New  York for those developers who do want to work in a more communal space. Like many tech companies, we’ve been giving a lot of thought to how we can promote inclusion, both internally and in the tech community as a whole. We don’t pretend to have figured it all out, but this is just one thing we’re excited to share. As always, we welcome any ideas you may have.

Let’s be honest, nobody has made the full swap to the vSphere Web Client yet. I’ve been pushing my colleagues and some customers to start using the Web Client, but it just isn’t up to par with the Windows Client. And I hate to admit but when a host or VM is down and I have to quickly walk through my vSphere environment to check for active storage paths, check VM and host settings, I still use the Windows Client for this.

On the VMware Community there is a thread running that burns down the vSphere Web Client very badly, which in my opinion is not necessary. In other occassions VMware has already shown their listening and improvements to the Web Client are in the works.

Now with vSphere 6 there will be major improvements but instead of completely getting rid of the Windows C# Client, VMware has made some adjustments to the Windows C# Client as well, just to show they are listening and do feel your pain.

The Windows C# Client in vSphere 6 will be able to edit VMs of all hardware levels but can only edit v8 and lower features. In other words, using the Windows C# Client on a VM with HW Level 11, you can still change RAM and CPU settings (all v8 and below features) but you can’t change vSphere Flash Read Cache settings since this is a v9 and up feature.

Source: vSphere 6 – Clarifying the misinformation. Also see the comments section.

Microsoft had a series of major announcements on January 21. Most of them around Windows 10. Did you miss it? Let me summarize for you.

1. Microsoft revealed 1.7 million active Windows Insiders are taking part in the Insider program – getting early access and providing critical feedback.
   
2. Microsoft will be updating Cortana for the phone and will bringing Cortana over to the desktop. You can see hints of this in the Insiders build of W10.
   
   1. Users will be able to talk to Cortana, w/o buttons “Hey Cortana!”
   2. Cortana will synchronize across user devices
   3. Cortana will interact with the Photos app
3. Microsoft-built apps like Mail and Maps are getting the Windows 10 treatment. New, better, even more OneDrive integration, and so on.
   
   1. Apps are called Universal Apps
   2. Apps will run “eventually on the Xbox as well”
   3. Many apps will run on Android and iOS mobile devices
   4. “Present your PowerPoint presentation right from your phone”
   5. In the new Mail app you can left swipe to delete
   6. In the new Mail app you can right swipe to flag
   7. The Photos app syncs with any phone
   8. The Photos app has a collection view
   9. The Photos app automatically applies Auto-Enhance
   10. The Photos all de-dups redundant photos
   11. The Photos app auto-groups photos by date and location
4. Microsoft’s next generation internet explorer (Spartan) is another part of Windows 10. It will including inking, sharing, collaboration, and more.
   
   1. The new browser is a Universal App
   2. Users can annotate with a pen, finger, or keyboard
   3. Spartan includes a Reading List, synced and offline
   4. Cortana is a built-in part of Spartan
5. Microsoft’s Office suite is coming. See Word, Excel, PowerPoint, OneNote, and Outlook as touch-first apps that light-up on Windows 10. 
6. Xbox Live is extending the console gaming experience to the PC. Play on your Xbox. Play on your PC. Play on both with cross-device multi-player.
   
   1. Cross-device includes friends, chat, achievements, and clips
   2. Fable was demoed as the first game to enable this feature
7. Microsoft announced more for Xbox: the ability to play multi-player over local Wi-Fi, record gameplay on the PC, and introduced DirectX 12.
   
8. Microsoft revealed the Microsoft Surface Hub, the rebranded PPI; it’s a 55-84”, 4k business-class touch-enabled whiteboard for conference rooms.
   
   1. The Surface Hub supports multi-point inking and touch
   2. The Surface Hub has a custom version of OneNote and Skype
9. Microsoft revealed Microsoft HoloLens, a wearable Windows 10 computer that is the world’s first holographic computing platform.
   
   
   
   
   
   1. The HoloLens is a stand-alone device, no wires, phone, PC needed
   2. The HoloLens has see-through HD lenses, spatial sound, advanced sensors, and a new Holographic Processing Unit (HPU)
   3. The HoloLens will be available in the Windows 10 timeframe
10. Microsoft demonstrated for the first time the Action center in Windows 10, the universal Notification center phone users have today.
    
11. Microsoft continued by indicating the Windows 10 holographic APIs are available on every build of Windows 10. Every single one.
12. Microsoft announced that Windows 10 will be a free upgrade for qualified Windows 7 & 8.x. & Phone 8.x devices that upgrade in the first year.
1. This will not include Windows Enterprise, however, most enterprises using this SKU get unlimited upgrades through software assurance.
13. Microsoft continued that they will keep all devices current for their supported lifetime with new features, security and functionality.
14. Finally, Microsoft reiterated the upcoming Build conference in San Francisco at the end of April – the place to learn more about Windows 10.
    

These are the kind of magical moments that we live for.

Pretty exciting stuff.

// End

I recently received a pair of Gen8 blades for my enclosure, and it is time to change out my Gen6 and 7 blades for Gen8. Now, as with every upgrade, a fair amount of planning must occur in order to start this upgrade. I consider it a hardware upgrade, and while it should be straightforward, one cannot simply swap the blades. So much for the easy way.

What follows are the steps necessary to get Gen8 (or Gen9) blades into an existing enclosure.

Blade 1:

1. Read all readmes associated with your blades. You will find that there are minimum requirements for Virtual Connect and Onboard Administrator (OA) firmware.
2. Ensure you have the proper offline support pack ISO (what used to be the firmware CD) from HP. This now requires you to have a proper entitlement (either care pack or warranty).  Other firmware for your enclosure is free still, but your blade BIOS is not.
3. Upgrade your Onboard Administrator firmware. This is the first step; without it, the blade will not even be recognized.
4. Now, look at the blade. If you are using mezzanine slots, such as for an FC HBA, ensure the FC HBA is in the slot previously used by the older blade.
5. Ensure you have new disk trays. Gen6 and Gen8 use different disk tray formats. While the drives are the same, the disk trays are not. You can order the disk trays separately.
6. Ensure your Onboard Administrator and Virtual Connect firmware and your management tool versions match the requirements of the blade. (Hence, my OA upgrade done first.)
7. Remove the old blade.
8. Take the drives out of the old blade, and change out their disk trays needed by the Gen8 blades. Four screws per disk, two disks, done.
9. Insert the disks in the same order in which you pulled them out. (Left on Gen6 to bottom of top of Gen8; right on Gen6 to bottom on Gen8.)
10. Insert the new blade into the appropriate slot.
11. Let the ILO come on. Once registered, it will boot the blade. Mine did not boot due to a drive issue (covered later). However, this allowed me to shut down the blade and configure the ILO appropriately. I accessed the ILO through the OA, which did not require a password, as I use SSO. The only change I made was to fix the IP and add any necessary users.
12. Log in via the ILO, and start the remote console.
13. Mount the HP Service Pack for ProLiant ISO image via the virtual media. I used version 2014.09.0.
14. Boot the blade. Enter F11 when appropriate and do a one-time boot from CD-ROM.
15. Upgrade the firmware using the HP Service Pack. I used the automatic update.

Now the blade is prepared unto itself. However, it is not yet ready to boot. I had a red screen from the blade, because the boot volume was not found. Oops. Well, not really. When going from an HP-210i to a 220i, the array disappeared, and my system would not boot properly. The fix is as follows:

1. Boot the blade once more, but this time, hit F5 when appropriate to enter the HP ACU to manage your disks.
2. Select the two drives, and create a new array (you do not lose data).
3. Ensure you pick the same RAID level as you had before (in my case, mirrored). This should be automatically selected, but please double check everything.
4. Voilà! The boot volume is now ready. Exit out of the ACU, and reboot the blade

At this point, my blade booted properly into ESXi. But the local datastore was not available. The solution to this was to mount the datastore with a different identifier, just like you would mount a mirror volume or storage snapshot volume. I then rebooted my ESXi host, which allowed me to rename the volume back to its original name.  This is required, because one of my HP StoreVirtual nodes sits on that disk, and I need that to restore redundancy.

However, the VM could not be added into vCenter due to the location of the VMFS volume’s now being incorrect, since we gave the datastore a new identifier or UUID. I tried many options but was unable to fix the problem by editing the .vmx file by hand. Instead, I just recreated the VM. Since it is part of my HP StoreVirtual, if something goes wrong, it is just easier to recreate one of the VSAs in use.

The vSphere host is working fine, as is my StoreVirtual VSA after a forty-eight-hour restripe of the storage array. Now it is time to ensure my host profiles are up to date. I find I often have to resync my host profiles after any upgrade of vSphere and hardware. However, the first time I attempted to check compliance, I received the following error:

Failed to execute command to configure or query coredump partition.

The fix for this was already documented by a fellow vExpert at Virtual Potholes. However, being the security-conscious person that I am, I implemented my solution using the vCLI off the VMware Management Assistant (vMA) using the following commands (the vCLI from Windows or Linux is also possible to use):

esxcli -s vCenterIP -h vSphereHost system coredump partition get
esxcli -s vCenterIP -h vSphereHost system coredump partition set -u
esxcli -s vCenterIP -h vSphereHost system coredump partition set -e true -s

Now I was able to check the host profile, and I found it wanting. Anything related to the older partition was incorrect, as were some other issues. It was time to fix what I could by applying the profile, which had become possible now that the reason for the error was gone. However, due to disk issues, it was just easier to recreate the profile once all the security items were fixed in the old profile—once TSM was enabled, etc. So, now I have a Gen8-specific host profile with a modified storage profile, per this post.

Blades 2 and above:

1. Now, look at the blade. If you are using mezzanine slots, such as for an FC HBA, ensure the FC HBA is in the slot previously used by the older blade.
2. Ensure you have new disk trays. Gen6 and Gen8 use different disk tray formats. While the drives are the same, the disk trays are not. You can order the disk trays separately.
3. Remove the old blade.
4. Take the drives out of the old blade, and change out their disk trays needed by the Gen8 blades. Four screws per disk, two disks, done.
5. Insert the disks in the same order in which you pulled them out. (Right on Gen6 to bottom of top of Gen8; left on Gen6 to bottom on Gen8.) Yes, I switched the order from my first blade, just to see if that would fix the weird array issue I had (and it did, so order is important).
6. Insert the new blade into the appropriate slot.
7. Let the ILO come on. Once registered, it will boot the blade.  I accessed the ILO through the OA, which did not require a password, as I use SSO. The only change I made was to fix the IP and add any necessary users.
8. Log in via the ILO and start the remote console.
9. Mount the HP Service Pack for ProLiant ISO image via the virtual media. I used version 2014.09.0.
10. Boot the blade. Enter F11 when appropriate, and do a one-time boot from CD-ROM.
11. Upgrade the firmware using the HP Service Pack. I used the automatic update.
12. Apply the host profile (fixing anything necessary; in my case, the Advanced Option ScratchConfig was incorrectly set, and the KB#1033696 had the fix).

Now that my systems are at Gen8s and my StoreVirtual VSA is once more synced at Network Raid-10, it is time to upgrade the hosts using a rolling upgrade approach via VMware Update Manager.

The key to making this work is to ensure your StoreVirtual VSA is redundant before you switch out any other blades that contain StoreVirtual components. If you do that prematurely, you will have data loss. Secondly, find the correct order in which to place your disks so that you will not have to recreate the array. Granted, to get everything correct, it took properly powering off my StoreVirtual VSA a few times. You cannot power these off from within vCenter but must use the HP StoreVirtual Centralized Management Console; otherwise, the Network RAID-10 device metadata on each VSA will get corrupted, forcing you to reinstall that VSA and rebuild the RAID, which can take up to forty-eight hours for three TBs.

The post vSphere Upgrade Saga: Exchanging HP BL460c G6 with BL460c G8 appeared first on AstroArch Consulting, Inc.

Read more of this story at Slashdot.