All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.
Tech News : General Interest
NASA’s ex-CTO built a cloud in a box and he wants you to buy it - Private clouds are all the rage for businesses seeking to recreate the capabilities of Amazon's infrastructure-as-a-service cloud within their own data centers. One of the first organizations to build its own private cloud was NASA, which started working on the "Nebula" cloud in 2008. Now the people who built that cloud will sell you one very much like it....(more)
Tech News : Security
Amazon S3 storage buckets set to 'public' are ripe for data-plundering - Using a combination of relatively low-tech techniques and tools, security researchers have discovered that they can access the contents of one in six Amazon Simple Storage Service (S3) buckets....(more)
SQL Server News
Enter Our Daily Sweepstakes to Celebrate the Anniversary of SQL Server 2012! - This month, we are celebrating the one year anniversary of SQL Server 2012, and we’d like to give a little shout out to our community to say, “Happy anniversary!” We can hardly believe that it was just one year ago that SQL Server 2012 was released to manufacturing. When we take a look back at how far we’ve come over the past year since this exciting occasion, we’re amazed by all the fantastic moments we’ve enjoyed with you! From the release of the TechNet Radio SQL Server 2012 and Fantastic 12 of 2012 series last year, to the great events we’ve attended together like PASS Summit and Strata, we are proud to continue this great journey onward with you! ...(more)
Dataguise introduces field-level encryption for Apache Hadoop database - Dataguise says the latest version of its data-protection product enables users to encrypt sensitive data right down to specific fields within an open source Apache Hadoop database. DG for Hadoop 4.3 also makes use of the traditional Dataguise "masking" capability across single or multiple Hadoop clusters to camouflage sensitive data....(more)
Microsoft News : General Interest
Windows Azure Receives G-Cloud Impact Level 2 Accreditation from Cabinet Office for Use across the UK Public Sector - Microsoft’s cloud computing platform, Windows Azure, has been awarded Impact Level 2 (IL2) accreditation, further enhancing Microsoft’s offerings on the current G-Cloud Framework and CloudStore....(more)
Microsoft News : Security
Microsoft's new security patching routine raises concerns - Microsoft just released its first acknowledged security update for a Metro app -- and the new patching method is a disaster waiting to happen...(more)
Hybrid Memory Cube spec makes DRAM 15 times faster - Backed by 100 tech companies, the three largest memory makers announced the final specifications for three-dimensional DRAM, which is aimed at increasing performance for networking and high-performance computing markets. Micron, Samsung and Hynix are leading the technology development efforts backed by the Hybrid Memory Cube Consortium (HMC). ...(more)
Blogs : Administration
sp_Blitz™ Version 18 Out: Lots of Bug Fixes - There’s a new version in town. v18 adds new checks looking for:
•Backups stored on the same drive letters as user databases (Thanks @AlinSelicean!)
•Agent jobs that don’t have failure notifications set up (Thanks @Thirster42!)
•Shows free drive space per drive letter if you pass @CheckServerInfo = 1 in. We don’t consider this a problem – we just show additional info if you use this parameter.
•Added the elevated database permissions check back in (whoops! dropped it accidentally)
Altering Text Columns: Only a Metadata Change? - Say you want to change the type of a text column using the ALTER TABLE … ALTER COLUMN syntax. It is valuable to know how much work SQL Server will have to do to fulfill your request. When your tables are large, it can mean the difference between a maintenance window that lasts five minutes, or one that lasts five hours or more....(more)
Catching SQL Server System Object Changes - Let’s say you get your hands on a brand new version of SQL Server that you haven’t played with before, and you want to know what system objects have changed – DMVs, DMFs, system stored procs, yadda yadda yadda. Sure, you could read the documentation – but ain’t nobody got time for that, and they’re probably leaving out the juicy stuff, right? Here’s what I do (thanks to improvement suggestions from Kendra):...(more)
SQL Server – Saving Changes Not Permitted in Management Studio - SQL Server Management Studio does not allow you to save changes to a table which require table re-creation such as changing data type for a column. When you perform such changes you will run into following error message:...(more)
Quick tip: Using sp_server_diagnostics - In SQL Server 2012 a new procedure sp_server_diagnostics returns a payload of information about server health and performance. The sproc is leveraged by the Windows Failover Cluster service and is designed to run in repeat mode. However the sproc can be run in non-repeat mode and loaded into a temp table. From there we can slice and dice the XML payload as desired....(more)
Evaluate This – Hyper-V Server 2012 - Hyper-V Server is a free operating system specifically designed to just run Hyper-V so basically a cut down core installation of a paid for edition of Windows Server. The cut down bit refers to the fact that only the roles and features needed to run Hyper-V are there. However Hyper-V itself is in no way cut down; for example you can create clusters for running HA virtual machines (up to 64 nodes hosting 8,000 VMs) and each VM can still have up to 64 logical processes as per the DataCenter edition of Windows Server....(more)
New Metric: Long-running job - SQL Monitor’s Job duration unusual alert is a powerful tool for keeping track of most jobs in your SQL Server environment. It works by comparing the running time of any job instance against the median for that job, and identifying when a variation in the job duration could hint at a performance problem. However, if your environment contains big jobs whose durations vary by several orders of magnitudes, it can be tricky to specify what kind of variation is expected and what would be unusual. For these cases, it is possible to go beyond the functionality of the built-in alert with a custom metric....(more)
Blogs : Analysis Services / BI
Query Options for Analytical Models and Transactional Reporting - Requesting both historical and transactional reports is a very common requirement. As they stand, neither Multidimensional nor Tabular are designed to support well transactional (detail-level) reporting with large datasets, as I discussed in my Transactional Reporting with Tabular blog post. There are two query options that might provide some relief with transactional reporting and they both have limitations. ...(more)
LightSwitch and Self-Service BI - Visual Studio LightSwitch has been on my list of Things To Check Out When I Have Time for a while now; my upcoming session on the uses of OData feeds for BI at the PASS BA Conference (which will be a lot more exciting than it sounds – lots of cool demos – please come!) has forced me to sit down and take a proper look at it. I have to say I’ve been very impressed with it. It makes it very, very easy for people with limited coding skills like me to create data-driven line-of-business applications, the kind that are traditionally built with Access. Check out Beth Massi’s excellent series of blog posts for a good introduction to how it works....(more)
Blogs : Backup and Recovery
Windows Azure SQL Database Backups - You can get backups of your WASD databases. But, if you want them to be transactionally consistent, you need to do a little extra work. This video (with bad lighting at the start) shows how to get the job done....(more)
Backup Database with BUFFERCOUNT parameter - If you want to make you backup faster, you can try BUFFERCOUNT parameter with compression backup....(more)
The Case of Anti-Virus filter drive interference with File Stream Restore - I recently worked with a customer on a Database restore issue where the database being restored had 2TB of File stream data. The restore in this case would just not complete successfully and would fail with the error below....(more)
Blogs : Career
How Not To Hire Someone - It’s the end of the college basketball season here in the US. At this time of year I get a little nostalgic for my former life as a basketball coach. I always enjoyed playing in March as that meant our team was doing well and advancing through the playoffs....(more)
How to go the Summit - A recent presentation I gave about Interviewing Tips provided me with a number of great questions that I thought I would expand on. One that I get often looks a lot like this: ...(more)
How I prepared myself for the MCSE certification - Last week I took the final exam to acquire the MCSE – Business Intelligence certification. This blog post describes my preparation for all of the exams. It is not a strict guideline of course, it’s just what suited best for me.
The MCSE certification exists of 5 separate exams, of which the first 3 grant you the MCSA SQL Server certification. These are the following:
Blogs : Computing in the Cloud
Pinching pennies when scaling in The Cloud - Running a site in the cloud and paying for CPU time with pennies and fractions of pennies is a fascinating way to profile your app. I mean, we all should be profiling our apps, right? Really paying attention to what the CPU does and how many database connections are main, what memory usage is like, but we don't. If that code doesn't affect your pocketbook directly, you're less likely to bother....(more)
SQL Reporting – build a business using reports in the cloud - Cloud SAM is a software asset management service created by the Atea group. The service uses RDL and SQL Reporting to provide informative and actionable reports to subscribing customers. The dashboard for this service is shown below. It is actually an RDL report composed of charts, tables, text boxes with calculated data, gauges, and links to drill through reports. The entire web front-end is implemented solely as RDL reports running on Windows Azure SQL Reporting....(more)
So, Is the Cloud Secure or Not? IT Managers Wary - There's a security gap in the cloud era -- or at least a perceived security gap. So says a new survey from AccelOps that asked 176 IT security professionals about their security practices during the RSA Conference 2013.
While 65 percent of organizations are using cloud services, only 46 percent have moved mission-critical applications and data outside the enterprise, due to cloud security concerns. Thirty-nine percent believe their existing Security Information and Event Monitoring tools are not acceptable to support their cloud security and regulatory compliance requirements.
But what can I *do* with Windows Azure? Backups - Maybe you just want to cut to the chase. Windows Azure. What do I *do* with it? Let’s talk about that. One of the quickest, easiest ways to use Azure is in the storage feature, as a backup target. Can Windows Azure backup data, servers, workstations or databases? Yes. Yes it can. Windows Azure storage is replicated three times in one datacenter (on different fault-domains) and then those three are replicated to another geographically separate (but still in the same country region) location, you get six copies of the data automatically. Your data stays in the datacenter you choose, and is replicated within a geo-politically same region. So it’s actually a great target for backups....(more)
Blogs : Data Mining
A Rickety Stairway to SQL Server Data Mining, Part 10.3: DMX Prediction Queries - In the last two installments of this series of amateur self-tutorials on SQL Server Data Mining (SSDM), I discussed how to performed Data Definition Language (DDL) and Data Manipulation Language (DML) tasks with Data Mining Expressions (DMX), the SQL-like language used to administer SSDM. These topics were easier to explain and less critical to know than the material covered in previous posts, like the installments on SSDM’s nine algorithms; furthermore, the syntax for both operations is far simpler and serves far fewer purposes than DDL and DML with T-SQL and Multidimensional Expressions (MDX), the languages used for relational tables and OLAP cubes in SQL Server. Much of the functionality DMX provides can be also be performed faster in the GUI of SQL Server Data Tools (SSDT) and is thus only useful in certain limited scenarios. ...(more)
Blogs : Events
Observing SQL Server Transaction Log Flush Sizes using Extended Events and Process Monitor - In this blog post we’ll take a look at how SQL Server interacts with the transaction log file using Extended Events and Process Monitor. We will see how small transactions can have a negative impact on transaction log IO throughput and how larger transactions can actually be much faster than a lot of small transactions. ...(more)
Pragmatic Works' Master SSIS Workshop coming to NYC - Pragmatically speaking, there’s no way you can learn all there is to know about Microsoft’s native ETL tool, SQL Server Integration Services – more affectionately known as SSIS – in only two days. However, if you’re looking for a 2-day excursion and deep-dive into the tool that no real DBA should be without, Pragmatic Works is offering its advanced 2-day Master SSIS Workshop, coming to NYC this May 7 through May 8th. ...(more)
SQL Server Events in the UK – April 2013 - The bank holiday and work have put me a few days behind schedule, but here is the list out all of the UK events for all you busy people who forget to go over to http://www.SQLServerfaq.com to check what SQL Server events are happening in the UK this month....(more)
Blogs : Excel
Format and customize Excel 2013 charts quickly with the new Formatting Task pane - The new Excel makes creating and customizing charts simpler and more intuitive. One part of the fluid new experience is the Formatting Task pane, which replaces the Format dialog box. The new Formatting Task pane is the single source for formatting--all of the different styling options are consolidated in one place. With this single task pane, you can modify not only charts, but also shapes and text in Excel. ...(more)
Blogs : High Availability/Disaster Recovery
Database Mirroring on SQL Server Express Edition - Like most SQL Server users I'm rather frustrated by Microsoft's insistence on making the really cool features only available in Enterprise Edition. And it really doesn't help that they changed the licensing for SQL 2012 to be core-based, so now it's like 4 times as expensive! It almost makes you want to go with Oracle. That, and a desire to have Larry Ellison do things to your orifices....(more)
How To Survive Any Database Disaster - Database disasters happen from time to time. I have a method that allows you and your business to survive any database disaster.
Blogs : Integration Services/ETL
What Are DQS and MDS About - This is just a very quick information about SQL Server 2012 Data Quality Services (DQS) and Master Data Services (MDS). If you don't have a clue what they are useful for, this blog post should give you the basic idea.
Many companies or organizations do regular data cleansing. When you cleanse the data, the data quality goes up to some higher level. The data quality level is determined by the amount of work invested in the cleansing. As time passes, the data quality deteriorates, and you need to repeat the cleansing process. If you spend an equal amount of effort as you did with the previous cleansing, you can expect the same level of data quality as you had after the previous cleansing. And then the data quality deteriorates over time again, and the cleansing process starts over and over again.
Blogs : Performance and Tuning
Top Six SQL Monitor Metrics for Analysis - Many SQL Server metrics tell their story best when displayed together on the same graph, so you can see how the things you're measuring vary against each other, over time, in order to gain a summary of the stresses on the server. So what six metrics should you choose? Grant comes up with his six favourite dagnostic metrics.
Managing SQL Server Statistics - Accurate statistics about the data held in tables are used to provide the best execution strategy for SQL queries. but if the statistics don't accurately reflect the current contents of the table you'll get a poorly-performing query. How do you find out if statistics are correct, and what can you do if the automatic update of statistics isn't right for the way a table is used?
Optimizer Limitations with Filtered Indexes - Paul White (@SQL_Kiwi) makes some great points about limitations in SQL Server's optimizer, and things you need to watch out for, when it comes to filtered indexes....(more)
DBCC CHECKDB performance and computed-column indexes - It’s no secret that DBCC CHECKDB has some performance quirks based on the schema of the database being checked and various kinds of corruptions. I was recently doing some scalability testing of DBCC CHECKDB for a blog post and discovered quite a nasty performance issue that exists in all versions of SQL Server back to SQL Server 2005. This isn’t a bug, it’s just the way things work....(more)
DBCC CHECKDB scalability and performance benchmarking on SSDs - Back in February I ran a bunch of performance tests of DBCC CHECKDB on SSDs, to evaluate the effects of degree-of-parallelism (DOP) and various options and traceflags, and now I’m finally getting around to presenting the results. Make sure to also read the recent post where I talk about the detrimental effect of computed-column indexes on DBCC CHECKDB performance....(more)
Blogs : PowerPivot
PowerPivot Table Import Wizard cannot find provider - The data source provider list in PowerPivot can often be a source of confusion for users since they equate the fact that a provider appears in the list as the provider being installed and available. Unfortunately, the list of providers is actually a static list of supported data sources for PowerPivot, so the user is still required to install the desired provider to successfully import data into PowerPivot. Thus, the most common fix for a "provider is not installed" error in the import wizard is to ensure you have the proper data provider installed and that the installed provider matches the platform architecture (32-bit or 64-bit) of PowerPivot and Excel....(more)
Blogs : Reporting Services
Setup SSL Access to SSRS 2012 with Active Directory Certificate Services - The objective was to configure SSL access to a SQL 2012 Reporting Services server in Native Mode. The Certificate Issuer was a Certificate Authority created in the domain. On SSRS 2005 this was a piece of cake, go into IIS (version 7), double click on the Server Certificates icon and then select the action on the right "Create Domain Certificate...". If you are used to creating CSR requests in IIS then this is familiar territory. For the Online Certification Authority you select your CA and that's about it. But SSRS on 2012 is something altogether different. Back on SQL 2008 Microsoft stopped using the IIS web server for SSRS and switched to using a web server packaged with SSRS. You can install IIS and do it the old way, I suppose, but then your Report Server becomes dependent on the IIS services, and I wanted to avoid that. I didn't find a good source through all my googling to help with setting up SSL this way so maybe this posting will help others that want to do the same thing and not burn through the better part of a day sorting out the ins and outs of how to get it done....(more)
SQL Server Reporiting Services: Migrating SSRS reports, data sources and subscriptions from one server to another - As part of our SQL Server infrastructure consolidation project, it was decided to consolidate multiple companies SQL Server 2005 / SQL Server 2008 Reporting Services instances to a new dedicated SQL Server 2012 Report Services instance. These existing SQL Server Reporitng Services instances hosts many business critical SSRS reports, data sources and subscriptions. In order to complete this task, I’ve to make sure that, all reports and other artifacts are migrated successfully from the existing SQL Server 2005/ SQL Server 2008 Reporting Services instances to the new SQL Server 2012 Report Services instance....(more)
Blogs : Security and Auditing
The PUBLIC role – Do not use it for database access! - As per Microsoft Books Online and SQL Server Security best practice white paper, it is recommended to periodically review privileges granted to public role, and revoke any unnecessary privileges assigned to this role. This is because public role is a special database role that exists in every user database, and by default, every database user is automatically assigned to this built-in role. This role is similar to Windows NT Everyone group, for example, if you grant privileges to this role, then all members of this role automatically get’s this permission. Due to this reason, when locking down access controls, then, we need to look at each individual user’s privileges as well as the privileges assigned to public role....(more)
5 ways to implement HTTPS in an insufficient manner (and leak sensitive data) - HTTPS or SSL or TLS or whatever you want to call it can be a confusing beast. Some say it’s just about protecting your password and banking info whilst the packets are flying around the web but I’ve long said that SSL is not about encryption....(more)
Blogs : Software Development
DBAs vs Devs: ORMs, Caching & Access to Prod - Developers and database administrators frequently clash on major questions about how to scale application performance. Are they just being stubborn, or is one side really right? Jeremiah and I recently gave a talk on this topic to the AtlantaMDF User group and we wanted to open this debate to the world....(more)
Blogs : SQL Server 2012
Viewing SQL Server Non-Clustered Index Page Contents - In this blog post I’ll take a look at what is actually stored in a non-clustered index and (re)introduce you to some tools that you can use for looking at the contents of a given data or index page. Hopefully, by the end of the examples you’ll have a better idea as to what SQL Server is looking at when it is traversing an index and what is stored in the root and intermediate pages of an index. ...(more)
SQL Server 2012 Common Criteria Certification - In February 2013 Microsoft received the Common Criteria (CC) certificate for SQL Server 2012 SP1 Enterprise Edition (English) x64 (Version 11.0.3000.0) at EAL4+ and compliant with U.S. Government Protection Profile for Database Management Systems, Version 1.3, 24 December 2010. See the actual CC certification screen shot below....(more)
Blogs : T-SQL
Group by Rows and Columns using XML PATH – Efficient Concating Trick - I hardly get hard time to come up with the title of the blog post. This was one of the blog post even though simple, I believe I have not come up with appropriate title. Any way here is the question I received.
“I have a table of students and the courses they are enrolled with the name of the professor besides it. I would like to group the result with course and instructor name.
SQL Server JSON to Table and Table to JSON - One of the surprises that I got from writing for Simple-Talk was the popularity of my article Consuming JSON Strings in SQL Server. I hadn’t really expected it to be so appreciated; in fact I was nervous about posting it at all. It came from a real requirement I had at the time, but I got interested in it in order to show how one could analyse hierarchical data documents iteratively in TSQL. Also, an anonymous troll on StackOverflow had told me it was impossible. ...(more)
Solving Complex T-SQL Problems, Step-By-Step - What should you do if your first, most intuitive solution to a problem ends up scanning the data more than is necessary, resulting in poor performance? Have you missed a new SQL Server feature that can remove inefficiency from your technique? Alternatively, do you need a little help, and some lateral thinking, to open the path to a different approach? Sometimes, the answer is "both".
The TABLE custom type / Using a TVP - Table Valued Parameter - Some Code to demonstrate
1.Creating a custom TABLE type
2.Passing a Table as a parameter (Table Valued Parameter)
In reality my 80s themed example would be far more exciting... ...(more)
UNPIVOT a table using CROSS APPLY - I generally spend part of each day looking through http://www.stackoverflow.com, http://dba.stackexchange.com or http://www.sqlservercentral.com. I read through questions that have already been answered if they look interesting and answer questions where I can. This is a great way to not only keep up my basic skills but to collect new and interesting bits of knowledge. In this particular case I was going through dba.stackexchange and I ran across someone trying to unpivot a question table....(more)
An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3 – Part 6 - This is the sixth part of the series regarding Redundant Indexes. If have not read earlier part – there is quite a good chance that you will miss the context of this part. I quickly suggest you to read earlier four parts. On my production server I personally use embarcadero DB Optimizer for all performance tuning and routine health check up. I will be interested to know what is your feedback about the product....(more)
Resolve Cannot Resolve Collation Conflict Error – SQL in Sixty Seconds #047 - One of the most common errors database developer’s receives when they start working with database where there are different collation used. Collation is a very important concept but it is often ignored. First use the method displayed in this video to resolve your error and right away put your efforts to understand what collation stands for....(more)