In this issue:

Tech News : General Interest

Tech News : Security

Hardware News

Blogs : Administration

Blogs : Career

Blogs : Events

Blogs : High Availability/Disaster Recovery

Blogs : Integration Services/ETL

Blogs : Performance and Tuning

Blogs : PowerPivot

Blogs : Replication

Blogs : Security and Auditing

Blogs : Software Development

Blogs : T-SQL

Database Weekly - www.databaseweekly.com

The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

SQL Server News for 2012-08-13

SQL Storage Compress Compress live data by 73% 
Red Gate's SQL Storage Compress reduces the size of live SQL Server databases, saving you disk space and storage costs. Learn more.
SQL Source Control No source control system needed to evaluate SQL Source Control
The evaluation repository makes it easy to try SQL Source Control. Get started with the 28-day free trial.
SQL Monitor Once in a while you come across a tool you just can't live without
…I can't imagine managing a large SQL environment any other way - Aaron Kolysko, Monsoon Commerce. Get started with SQL Monitor today - download a free trial
Editorial - Avoiding Stored Procedures

I ran across this piece from a developer on why he avoids stored procedures and thought it made some good arguments. The primary thrust of the piece is that ORM tools (Object-Relational Mapping) have evolved to handle most of the requirements of many applications. They also save a ton of development time since so many stored procedures written are simple CRUD type operations. 

In many cases I agree. Having developers write stored procedures is silly and a waste of time. Procedures that select a few fields, or that update a table based on a primary key are mind-numbingly simple to write, but they take time to get in place. That's time a developer isn't spending thinking about the application and logic. Plus with any ORMs and tools like LINQ, you can write one line of code and let the ORM handle all of the work of getting or storing the data. Good points, and in many cases that's correct. If you do mostly CRUD type work, this is a good reason to perhaps avoid stored procedures and let a tool do the work for you.

Unless you use a different tool. There are plenty of tools, most of them free, that will generate that CRUD code for you. A few templates or snippets will handle the front end side of the call as well, building code to call stored procedures. If you're actually typing this stuff over and over, you are wasting time.

My brain started to wander when I saw "A database should be limited to the role of a persistence layer" which is silly sounding when you move beyond CRUD operations. It completely shut off when I saw "your stored procedures would need to be re-written in order to migrate to MySQL, Oracle or another database" since I think this rarely happens. If it does for you, fine, but the vast majority of apps never leave their initial database.

There are benefits in ORM tools, but you need to understand how the ORM works, what it's strengths and weaknesses are. Blindly following the basic pattern for your state-lookup-data-editing dialog for all reporting screens is a sure way to cause yourself some problems. Allowing the ORM to define your relational database, without spending some time thinking about the benefits of good database design and proper modeling is asking for performance problems, or even integrity issues. 

ORM tools are just that tools. Used well, they can perform admirably, but just as I don't use a hammer to drive a screw into wood, don't depend on your ORM handling everything database related for you in an efficient manner.

» Join the debate, and respond to today's editorial on the forums


The Weekly News

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

Forecast for systems administrators: Cloudy - When Donald Roper found himself in the job market earlier this year, he quickly learned how high the bar had been raised in his profession. A senior systems administrator with 28 years in IT, an MBA and seven certifications to his name -- including one in virtualization -- he discovered that wasn't always enough....(more)

6 hottest IT certifications - IT professionals have always sought out certifications to give them a leg up in their career advancement efforts. But anecdotal evidence suggests that in today's job market, having a broad array of certifications is even more important for giving job-seekers a needed edge....(more)

'Shadow IT' on the rise in businesses - Business divisions are bypassing the IT department, making their own decisions to buy cloud-based application services or use mobile devices, raising the specter of so-called "shadow IT" that's outside the knowledge or control of the CIO and the IT staff....(more)

Top 5 cities for big data jobs - Corporate data stores are growing exponentially, nearly every tech vendor is positioning their products to help handle the influx of data, and IT departments are scrambling to find the right people to collect, analyze and interpret data in a way that's meaningful to the business. On the employment front, the big data deluge is creating a hiring boom across North America. Modis, an IT staffing firm, identified five cities in particular where big data is driving job growth....(more)

NotInMyBackYard Scours Web for Your Personal Data - Finally, consumers have the power to scour the Internet and find out who might have access to their personal information. ...(more)

Tech News : Security

Global Payments: Data breach cost a whopping $84.4 million - Global Payments, which back in the spring reported a data breach in which information associated with an estimated 1.4 million payment cards was stolen, has revealed that expenses associated with investigations, fines and remediation has hit $84.4 million....(more)

Hardware News

The Intel SSD 910 Review - The increase in compute density in servers over the past several years has significantly impacted form factors in the enterprise. Whereas you used to have to move to a 4U or 5U chassis if you wanted an 8-core machine, these days you can get there with just a single socket in a 1U or 2U chassis (or smaller if you go the blade route). The transition from 3.5" to 2.5" hard drives helped maintain IO performance as server chassis shrunk, but even then there's a limit to how many drives you can fit into a single enclosure. ...(more)

HP unveils all-SSD option for 3PAR storage array - Hewlett-Packard is extending its 3PAR enterprise storage offerings with an all-SSD version of the P10000 array. The all-SSD (solid-state disk) version, announced on Monday and available immediately starting at $350,000, is designed to speed up applications in cloud and virtualized data centers. It also can eliminate manual data-tiering functions in some cases and reduce the physical footprint of storage, according to HP....(more)

Blogs : Administration

Recovering from out-of-disk space conditions for tempdb - Another potential problem that you need to tackle as a DBA is running out of disk space on the hard disk that contains tempdb.This is because SQL Server makes extensive use of tempdb when: ...(more)

Blocking in SQL Server by SPID -2 (Orphan distributed transactions) - Blocking in in SQL Server by SPID -2 happens due to Orphan DTC transaction, for instance whenever a data source connected in MSDTC is rebooted abruptly when a transaction is active, MSDTC does not recognize that one of its destination has been rebooted and keeps the transactive forever wiating for an acknowledgement, Hence the transaction is neither marked in doubt nor completes. This is when you might have blocking in your SQL server where your lead blocker is -2 and You cannot kill this spid using normaill Kill -2 or you cannot resolve the transaction in MSDTC as the trasaction is active. To clear Orphan MSDTC transaction you must KILL UOW associated with the orphaned distributed transactions by querying the session ID column in sys.dm_tran_locks dynamic management views as below,...(more)

Cost Savings and Backup Compression - Let me set the scene, one of our internal IT SQL Servers which stores a whole host of performance metrics has over the last few months’ experienced tremendous growth as we have started to monitor more metrics on more servers. A routine we have in place to collect and report on a multitude of server and database information including database sizes and growth highlighted this to me along with a significant decrease in free space on the backup volume. We use SQL Server to back up our databases as opposed to a third party product as any benefits we may gain are outweighed by the cost....(more)

How to Find Bad Page Splits - I was working with a friend last week, doing SQL related teaching and tune ups. Earlier this week I had received an email from them regarding unusually high page splits they were receiving after deploying new code, upwards of 800 per second!...(more)

How to Data Compress Varchar(MAX) - I talk a lot about compression. I’ve blogged a pretty decent amount on it as well. One of the things that often confuses people is what can and cannot be compressed. There is a list of data types that can be Row Compressed. That list is different between each SQL Version. Page compression on the other hand works at the binary level, it is data type agnostic....(more)

Blogs : Career

SQL Server DBA Interview Questions Video - Our clients often bring us in to interview their candidates for junior and senior positions. Whether you’re a junior, senior, or expert candidate, we’ve got questions that help us figure out whether you’ve really got the goods – or just faking it. In this 30-minute session, we’ll spend 10 minutes asking you our favorite junior, senior, and expert questions. Think fast: some answers will be multiple choice, and some questions will be open-ended. Will you make the cut?...(more)

Blogs : Events

#SQLPASS Wants You – to Help First Time Summit Attendees - I’ll never forget my first time, but here’s the weird part: I did it alone. In 2007, I went to my first PASS Summit conference in Denver. I’d never been to a professional conference before, and my boss, Don Duncan, was the one who actually suggested it to me. He said it all matter-of-factly: “Are there any conferences for what you do? Do you wanna go?” Uh, okay, sure. ...(more)

SQL Saturday US Map - Apparently, SQL Saturday isn’t popular in the west. See for yourself. I wanted to see what cities frequently host SQL Saturday and ended up creating this dashboard. There are some interesting findings. Look at how the events are primarily confined to only (right) half of United States. There are several states that haven’t yet seen a SQL Saturday!...(more)

Blogs : High Availability/Disaster Recovery

CRM Reporting and SQL 2012 AlwaysOn Availability Groups – Better Together - A common issue that I have come across in CRM deployments, particularly for Enterprise customers, is deadlocking on the SQL server resulting in a degraded performance of our CRM environment....(more)

PowerShell and AlwaysOn - Gotcha - Exception setting "ConnectionString": "Keyword not supported: 'applicationintent'." - Here is the an issue I saw come across an alias that is a gotcha! I’m running into a problem connecting to an AlwaysOn read-intent secondary and I was wondering if someone could help me out. I have the .Net Framework 4.5 installed and the newest SQL Client install for SQL Server 2012. Running this command from the server where SQL Server 2012 is installed works fine: ...(more)

Blogs : Integration Services/ETL

SSIS: Transfer SQL Server Object using SMO - we often face a situation to move SQL server object beween servers or databases, when you have constant table list we can always use "Transfer SQL Server Objects Task" with the SSIS, One of my customer had a requirement of scheduling migration process frequently and would be supplying the table list dynamic every time using a meta data table. ...(more)

Blogs : Performance and Tuning

You’re Doing it Wrong: 5 Factors That Affect Database Performance - You didn’t mean to be that way, I know. But the truth is things happen. When they do I am brought in to find out what is happening, what has gone wrong. I see repeating patterns in the database issues I am asked to investigate. I keep track of them as I see them and have been able to place them into some general buckets for you here....(more)

Blogs : PowerPivot

Visualizing Data with Power View: Pan-Geo goes PowerPivot - Back in June we released our book on Power View, Visualizing Data with Microsoft Power View. Not only does the book contain great information about creating reports with Power View, but it also contains content on building tabular models that can be utilized with Power View. The current release only works with tabular models, so either PowerPivot or Tabular Analysis Services (SSAS). With our book we primarily focused on building out a Tabular SSAS solution that gets used for the majority of the Learn By Doing exercises. Sure there are a few times where a PowerPivot file is referenced or we do some initial modeling in PowerPivot, but most of the work gets done with the tabular solution that gets deployed to Analysis Services....(more)

Blogs : Replication

Initialize a Transactional Replication from a Database Backup - During the last days I'm working on Replication – especially with Transactional Replication. The main idea of Transactional Replication is that you have a so-called Log Reader Agent on your Publisher SQL Server database running, that is analyzing the Transaction Log File and synchronize changes on so-called Articles through a Distribution database down to your Subscription databases. ...(more)

Blogs : Security and Auditing

SQL Server : List Login's Database and Server Level Role Permission - In my last post, we have discussed about the orphaned users and fixing the orphaned users. The next step in the security audit of our server was to review the login's database role membership and server role membership. ...(more)

Don't Use Passwords That Can Be Social-Engineered - A Facebook thread by one of my friends brought this one to mind. He was asking about what the current guidelines were for strong passwords. They vary, based on the capabilities of the system. A lot of the guidance nowadays is on passphrases modified in some manner, usually with a few substitutions. The passphrases create a password that is computationally costly to brute-force and the substitution just adds to the number of combinations that have to be tried per word. ...(more)

Forcing network protocol to connect SQL server - Whenever there is a network issue or a SQL server browser issue we might need to connect SQL server in different network protocols to troubleshoot. We can change the default protocol in configuration manager but it will make the change globally for all connection connecting to the SQL instance, instead we can force the protocol for your connection alone pretty easily by appending the protocol name to the servername in SSMS, SQLCMD or any source connecting to the SQL instance like...(more)

Breaking Microsoft's PPTP Protocol - Some things never change. Thirteen years ago, Mudge and I published a paper breaking Microsoft's PPTP protocol and the MS-CHAP authentication system. I haven't been paying attention, but I presume it's been fixed and improved over the years. Well, it's been broken again....(more)

er Swire Testifies on the Inadequacy of Privacy Self-Regulation - Ohio State University Law Professor Peter Swire testifies before Congress on the inadequacy of industry self-regulation to protect privacy.......(more)

The Importance of Paranoia for the Technical Professional - I recently read a blog post from a technical professional who’s account had been hacked (http://www.wired.com/gadgetlab/2012/08/apple-amazon-mat-honan-hacking/all/) – not because he used poor passwords or unsafe practices, but because the hackers used some social engineering to get around the safety he had put into place. ...(more)

Blogs : Software Development

Why I Avoid Stored Procedures (And You Should Too) - Okay – DBA’s are already upset after reading the title. I understand. Stored procedures have been the standard practice of most professional software developers for more than a decade. But like my recommendations on Flash, there are newer and better options than some old tried-and-true technologies....(more)

Excluding Schemas in SQL Compare - I haven’t been a big schema user in the past, but I see the value of excluding some schemas. CDC for sure, but I know some people have an ETL schema, which is much nicer than having Staging_Customers. I think the exact same structure in ETL.Customers moving to dbo.Customers is a good idea. ...(more)

Blogs : T-SQL

Identity Insert and Table Variables – CONNECT issue - I was running some code the other day and was surprised by the result....(more)

SQL Server : Lost all Administrator Account in SQL Server and Forgot sa Account Password - What will happen if you lost all your administrator account by mistake ? As per the best practice you have disabled the sa login. Those who had login with sysadmin rights left the company or not able to remember their password. Now you do not have any login with sysadmin rights. How do you get the administrative rights back? ...(more)

SQL Server: How Recursive Common Table Expression (CTE) Works - Last Friday evening, I forced to look into a procedure which is using Recursive Common Table (CTE) . The procedure was not returning the result.I was not very comfortable with the recursive CTE as I am not able to visualize the execution of recursive CTE. After struggling for an hour , we managed to fix the issue. Then I thought it will be good to learn how the recursive CTE works. In this post let us see how recursive CTE works....(more)

Rollback TRUNCATE TABLE statement - Whenever I ask a SQL candidate in an interview the difference between DELETE and TRUNCATE TABLE, the first answer I get is...(more)

How to prevent ‘Select *’ : The elegant way - I’ve been doing a lot of work with the “Microsoft SQL Server 2012 Transact-SQL Language Service” recently, see my post here and article here for more details on its use and some uses....(more)

Composable DML cannot insert into a table with a FOREIGN KEY - I have written on here about the benefits of Composable DML (i.e. the ability to insert the data outputted from an OUTPUT clause into a table) before however I discovered a limitation today that I think is worth sharing. Basically, the table that you’re inserting into cannot have a FOREIGN KEY constraint nor can it have a FOREIGN KEY constraint pointing to it. Here’s some code that demonstrates the first of these two restrictions:...(more)


Administrative