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.
Vendors/3rd Party Products
SQL Monitor Custom Metrics: WriteLog Wait Time - During a transaction, data is written to the log cache so that it’s ready to be written to the log file on commit, or can be rolled back if necessary. When the log cache is being flushed to disk, the SQL Server session will wait on the WriteLog wait type. If this happens all the time, it may suggest disk bottlenecks where the transaction log is stored....(more)
Blogs : Administration
Automate Registering and Maintaining Servers in SQL Server Management Studio (SSMS) - When you have a lot of SQL Server instances to manage and servers are retired and new ones are added, it becomes a headache to manually maintain these registered servers in SQL Server Management Studio (SSMS). In this tip we look at how to use a control file that lists the structure to use within SSMS and a PowerShell script that will automatically maintain the list of registered servers within SSMS. ...(more)
What does it mean to be a SQL Server DBA - In many respects, a SQL Server DBA wears numerous hats and responsibilities may vary from one organization to the next. Although the implementation, technology, responsibilities, etc. may vary, there are a handful of common character traits that I have observed in a number of SQL Server DBAs that I respect and admire...(more)
What Counts for a DBA – Moments - ”Do one thing every day that scares you” – Eleanor Roosevelt. For the DBA, embracing the sentiment doesn’t mean jumping off the metaphorical cliff. Pick up a copy of SQL Server Express Edition (free), and try out new features. One day you may need another employer who has learned to store data in a modern database server and expects all of the newfangled goodies to actually be used rather than avoided....(more)
Blogs : Analysis Services / BI
R and Meta-Analysis - roadly speaking, a meta-analysis is any statistical analysis that attempts to combine the results of several individual studies. Not only has meta-analysis become a fundamental tool in medicine, but it is also becoming popular in economics, finance, the social sciences and engineering. ...(more)
The 10 worst big data practices - It's a new world full of shiny toys, but some have sharp edges. Don't hurt yourself or others. Learn to play nice with them
When your sentence begins, "We will use Mongo to analyze ...," stop right there and think about what you're doing. Sometimes you really mean "collect for later analysis," which might be OK, depending on what you're doing. However, if you really mean you're going to use MongoDB as some kind of sick data-warehousing technology, your project may be doomed at the start....(more)
Blogs : Backup and Recovery
How to Back Up Terabytes of Databases - When I’m building a SQL Server to hold multiple terabytes of databases, this backup question is the very first one we have to address – even before we talk about the speed of end user queries.
Otherwise, we could end up designing a server with all local solid state drives, which is very inexpensive and satisfies end user performance goals – but we can’t back the data up fast enough...(more)
Blogs : Big Data
Sentiment Analysis with Microsoft APS and StreamInsight - Sentiment analysis is the process of analyzing customer comments and feedback from Facebook, Twitter, Email, and more. The purpose of the analysis is to understand the overall sentiment the customer is trying to convey. ...(more)
Oracle hopes to make SQL a lingua franca for big data - Big Data SQL is a software tool from Oracle that can run a single SQL query against Oracle’s own database as well as Hadoop and NoSQL data stores. The software is an option for Oracle’s Big Data Appliance, which incorporates Cloudera’s Hadoop distribution, but you also have to buy big into Oracle’s technology stack, however.
SQLite: Small. Fast. Reliable. Choose any three. - SQLite does support multiple concurrent connections, and therefore it can be used with a multi-threaded or multi-process application. The catch is that when SQLite opens a write transaction, it will lock all the tables. By using write-ahead logging (WAL), available since version 3.7.0, you can gain additional speed when performing concurrent operations....(more)
20th Century DBMS success and failure - In the minicomputer world of the 1970s and 80s, hardware vendors lacked the power of DB2, and independent DBMS vendors thrived. Indeed, Oracle and Ingres rode to success on the back of Digital Equipment Corporation (DEC) and other minicomputer vendors, including the payments they got to port their products to various platforms. The big competitive battle was Oracle vs. Ingres.
Blogs : Computing in the Cloud
Create and Configure a File Share using Azure Files - Azure Files lets administrators create standard Server Message Block (SMB) file shares, which is the type of file shares that would be created if you set up a shared folder on Windows Server in the cloud without provisioning a file server running in a virtual machine.
The fallacy of distributed transactions - In certain circumstances, it is possible that the queue got both Prepare & Commit messages, while the database got just a Prepare message. The timeout has expired, and the database has rolled back the transaction. In other words, as far as the queue is concerned, the transaction committed, and the message is gone. As far as the database is concerned, that transaction was rolled back, and never happened....(more)
HIPAA Compliance and Security Top Cloud Adoption Concerns for US Healthcare Providers - Over 80 percent of healthcare organizations are currently using cloud services, mostly in SaaS form, according to a recent report by the Healthcare Information and Management Systems Society (HIMSS). Hosting of clinical applications and data, health information exchange and backups were the most used cloud services for healthcare organizations....(more)
Blogs : Data Mining
Can You Trust Your data analytics Algorithms? - How much faith should you put into those data analytics algorithms, and how can you be sure they’re not misleading you? They’re not simple questions, but through the use of algorithmic differentiation techniques, data scientists can get more precise answers....(more)
Blogs : Database Design, Theory and Development
Calculating and Verifying Check Digits in T-SQL - A lot of numbers that we use everyday such as Bank Card numbers, Identification numbers, and ISBN codes, have check digits. As part of the routine data cleansing of such codes we must check that the code is valid- but do we? Dwain Camps shows how it can be done in SQL in such a way that it could even be used in a constraint, and keep bad data out of the database....(more)
Blogs : Developer Tools
Quick Tips–SQL Prompt Aliases for Every Table - Recently I’ve run into a few people that weren’t aware of some of the ways in which it can help you. Aliases are used to make code more readable, and shorten the amount of code that one needs to write. Typically we use these to give a short name to a table. SQL Prompt can automate aliases for me....(more)
Blogs : DMO/SMO/Powershell
Software Carpentry: (Draft of) Windows Scripting Tutorial using PowerShell - PowerShell is modern, powerful and installed on all modern Windows operating systems by default. I took these notes for the 2013 Bath shell scripting session. Rather than sit on these notes any longer, I’ve decided to just publish what I have so far in case they are useful to anyone....(more)
Blogs : High Availability/Disaster Recovery
Should Cloud Be Part of Your Backup and Disaster Recovery Plan? - The online backup systems typically are schedule-based; however continual backup is a possibility. Depending on the requirements of the system and application, the backup is updated at preset intermittent levels; with the aim of efficient time and bandwidth utilization. ...(more)
Blogs : Integration Services/ETL
SSIS: Value does not Fall Within the Expected Range - Every now and again I find myself working with SSIS for one reason or another. Every now and again I find myself fighting with SSIS because it provides less than useful error messages. This is one of those error messages that can be a pain until you understand what can be causing it. ...(more)
Blogs : NOSQL
Theory of Constraints: the rise of NoSQL - EAV can be good for quick development but for production it’s completely unreadable thus impossible to debug and the performance is atrocious. Instead of using EAV one could give each developer a virtual database and let them work as fast as they can and then let them test out merging there changes in virtual databases before merging them in to trunk which is itself a virtual database that can be branched from and merged into.
Virtual databases take up almost no storage and can be made in minutes with a developer self service interface....(more)
Blogs : Performance and Tuning
Examining OLEDB Waits - Generally we will see OLEDB waits when we make a call to an OLE DB provider and we are waiting on our data.
OLEDB waits happen a lot in environments that use linked servers (there are some exceptions, we’ll get into one specific one later in this article)....(more)
TPC-H: Data And Query Generation - The TPC council ships two utilities that can be used to generate the data and query for the TPC-H schema: DBGEN and QGEN.
The utilities are written in C to make them portable between platforms. Unfortunately, the code is rather old and I dare say so: not particularly pretty.
In an attempt at making TPC-H more accessible, I have created a new GIT repo on Bitbucket that should make it a easier to build TPC-H. ...(more)
State of the UNION - Correct now, optimize later. is one of the most important developer mantras and Scott K. followed it to a fault. He was on a team of programmers debugging a C# package management application, which used Microsoft SQL for revision tracking. Make sure it works right the first time; you can always tease out more performance after launch.
But if your program takes ten minutes to extract a C# package, as Scott discovered, you might want to optimize sooner rather than later...(more)
Execution Plan Details - I wouldn’t say it’s common knowledge that you should look at execution plans when tuning queries, but it’s not exactly uncommon knowledge either. But, people tend to get focused on just looking at the graphical part of the plan and there’s just not enough information there. Let’s take a look at a query:...(more)
Comparing Estimated and Actual Execution Plans in SQL Server - The estimated execution plan is designed to show what SQL Server would most likely do if it were to execute the query. Using statistics, it estimates how many rows may be returned from each table. It chooses the operators it will use to retrieve the data – scans or seeks. It decides how to best join the tables together – nested loops, merge joins, hash joins. It is a reasonably accurate guide to what SQL Server will do....(more)
Blogs : Replication
Performance Tuning SQL Server Transactional Replication: A Checklist - SQL Server transactional replication is a blessing and a curse. It’s a great developer tool that lets you scale out data to multiple servers, even using Standard Edition. But as your business picks up, your datasets get larger, and your customers grow more demanding, replication can start to fall behind. You need to learn how to tune it to keep up....(more)
Blogs : Security and Auditing
“Severe” password manager attacks steal digital keys and data en masse - It is standard advice to use a software-based password manager to ease the password fatigue that comes from choosing and securing dozens of hard-to-guess passcodes that are unique to each site or service. A research paper scheduled to be presented at a security conference next month underscores the hidden dangers of selecting the wrong products....(more)
CNET user database stolen by Russian hacking group, auctioned for $600 - Of all the sites you'd expect to get hit by a security exploit, a website dedicated to computers and technology is not one of them. But CNET learned the hard way on Monday that editorial focus is no substitute for proper security....(more)
Why '123456' is a great password - Researchers say the simplest passwords are useful within a strategy that saves the hardest-to-remember credentials for the most critical sites and services. Companies are making a website or corporate network less secure if they require employees to use complex passwords that are difficult to remember and have to be changed every three months...(more)
Blogs : T-SQL
Yes, Virginia, you can have too much white space - White space is a wonderful way to format your code. However it can, and frequently is, taken to extremes. I’ve heard “There is no such thing as too much white space” and I disagree. Strongly....(more)
Articles : Administration
Questions about SQL Server Data Types You were Too Shy to Ask - Although SQL Data Types seem to cause a lot of grief for database developers and can be tricky in their use, we seem to be expected to know all about them, and so it is embarrassing to ask questions about them in forums. Rob Sheldon continues in his mission to answer all those questions that we hesitate to ask....(more)
Articles : Development
MSBuild support for Schema Compare is available - Schema compare is one of the most important Visual Studio SQL Server tooling components. As of our July release the schema compare functionality is available via MSBuild. It can be run from the command line or as an integrated part of automated project build systems to detect changes and generate reports....(more)
The Myth of Incremental Development - In the agile world there is a common notion that incremental delivery is a desired approach. Many taught rapid release, even multiple releases a day.
You need to know what DONE looks like in units of measure meaningful to the decision makers. Those units start with Measures of Effectiveness and Measures of Performance.
Each of those measures is probabilistic, driven by the underlying statistical processes of the system. These mean you must be able to estimate not only cost and schedule, but how that cost and schedule will deliver the needed system capabilities measured in MOEs and MOPs....(more)
On Identifying the Real Stakeholders - From my own past experience, the major struggle in development is in finding out who the customer really is, and in identifying the key people in the organisation who really understand the business processes. It’s not always as easy as you might imagine....(more)