SQL Server Premium Assurance (Database Weekly 12/12/2016)

The Complete Weekly Roundup of SQL Server News In this issue: Vendors/3rd Party Products - [Free Webinar: Redgate DLM Demo] - [SQL in the City Streamed less than a week away!] - [Deploying schema changes to multiple databases using the SQL Compare command line] - [Managing multiple database versions] T-SQL - [NOLOCK] - [Redgate need your help with a survey] - [Actual Chats: RIGHT OUTER joins in the wild] The Lighter Side - [Virgin Galactic returns to flight with its VSS Unity spacecraft] - [A slow Atari 2600 emulator is now inside Minecraft—and it’s pretty cool] SQL Server News - [SQL Server + Python — What’s new] - [SQL Server + Java: What’s new] Software Development - [DevOps Introduction] Security news and thoughts - [Scaling securely at the frontend] - [Cryptography Professor To Audit Open Source Software Used By Most VPN Services] - [Companies Are in Short Supply of Cybersecurity Talent] - [The Future of Credit-Card Security May Involve Blinking Numbers] - [Network Monitoring: Your First Line of Defense] - [Thieves can guess your secret Visa card details in just seconds] - [VIEW SERVER STATE permission was denied on object 'server', database 'master'.] - [Error: The server principal "IncidentManagement" is not able to access the database "DatabaseName"] - [This Week in Data – Episode 15 (Why People Are Flocking to SQL Server)] Reporting Services - [What’s new in Mobile Reports in SQL Server 2016 SP1] - [Migrating reports between SSRS report servers simplified with new PowerShell commands] R Language - [R is better than M for difficult data wrangling tasks in Power BI] - [Stylometry: Identifying authors of texts using R] - [Multivariate Variable Analysis using R] - [Configuring R on SQL Server 2016] Product Upgrades and Releases - [How to monitor backups and other SQL Agent jobs using SQL Monitor] - [SQL Server Interpreter for Apache Zeppelin 0.6.2] - [Announcing SQL Server Management Studio – 16.5.1 Release] - [Released: Public Preview for Microsoft Azure SQL Database Management Pack (6.7.11.0)] - [NetflixOSS: Announcing Hollow] PowerPivot/PowerQuery/PowerBI - [Custom Functions Made Easy in Power BI Desktop] - [Power Query, R is here to help you!] - [Format measures as dates in Power BI] - [Implement Non Visual Totals with Power BI security roles] - [Power Query, Power BI And The “Allow Data Preview To Download In The Background” Option] - [Checking your Wait Stats via Power BI] - [Power BI Custom Visuals Class (Module 28 – Sankey)] Polybase/HDInsight - [Reading Polybase Execution Plan Details] Performance Tuning SQL Server - [TIL: Starting a recurring SQL trace] - [Extended per-operator level performance stats for Query Processing] - [What keeps clearing my query stats?] - [Does Truncate Table Reset Statistics?] - [Transaction Commit latency acceleration using Storage Class Memory in Windows Server 2016/SQL Server 2016 SP1] - [Improve query performance on memory optimized tables with Temporal using new index creation enhancement in Sp1] - [Optimize For Unknown] - [Developers Choice: Query progress – anytime, anywhere] - [When Did SQL Server Last Update That Statistic? How Much Has Been Modified Since? And What Columns are in the Stat?] Microsoft News : General Interest - [Microsoft R Server 9.0 now available] Microsoft News - [Introducing SQL Server Premium Assurance] Hardware News - [Micron Infuses 8TB 5100 Series SSDs With 3D TLC NAND] - [Extending Home Networks - A Comparison of G.hn, HomePlug AV2 and Wi-Fi Mesh] - [HGST Ultrastar SS200 SSD: Up to 7.68 TB, 1.8 GB/s, Dual-Port SAS 12 Gbps] HA/DR/Always On/Clustering - [Unable to connect to SQL Server AlwaysOn Listener on Azure due to misconfigured subnet mask] - [SQL SERVER – FIX Error – Cluster Network Name showing NETBIOS status as “The system cannot find the file specified”] - [Adding database back to Always On High Availability Group] Events to attend - [Query Tuning Training in Early 2017: Boston and Cleveland!] - [Learn More About the SSIS Catalog] - [SQL in the City is coming next week] ETL/SSIS/ELT - [Cleaning Up SSISDB] - [Broken References in the SSIS Catalog] DocumentDB/Key-Value NoSQL Databases - [NoSQL .NET Core development using an local Azure DocumentDB Emulator] Database Design, Theory and Development - [SQL SERVER – GENERATE AND USE TIME / DURATION DIMENSION] - [Domain vs. (Data) Type, Class vs. Relation (UPDATE)] - [Be Careful with Derived Data] Data Privacy - [Court: License plates must be displayed, so by definition they aren’t private] Data Mining/Data Analysis - [How to Dive into Data Lakes and Not Drown] Computing in the Cloud - [How to Choose the Right Cloud Provider and Solution] Columnstore Indexes - [Data Channel - Interview 01 - Andreas Wolter on Columnstore] Career Growth - [SQLskills holiday gift to you: all 2015 Insider videos] - [On Being Disruptive] - [Top 10 Free SQL Server eBooks] Backup and Recovery - [TDE Recovery] Azure SQL Database - [Setting up replication from on-premise SQL Server to Azure SQL DB] - [Clustered Columnstore Index in Azure SQL Database] Azure SQL Data Warehouse and Data Lake - [SQL On The Edge #11 – Azure Data Lake Fundamentals] Analysis Services / BI on the MS Stack - [How Much RAM Do I Need For My SSAS Tabular Server?] - [How Much RAM Do I Need For My SSAS Tabular Server?] Administration of SQL Server - [INTRODUCTION TO BACKUP ENCRYPTION IN SQL SERVER] - [SQL Server Quickie #42 – Recovery Model FULL] - [SQL SERVER – Errors When Disk Space is the Reason] - [SQL SERVER Management Studio – Taking DB Offline Made Easier] - [SQL Server Diagnostic Information Queries for December 2016] - [Should I Install Multiple Instances of SQL Server?] - [Moving Tasks with Copy-SqlJob] - [SQL Server & Containers – Part Four] [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 2016-12-12 [SQL Monitor] SQL Monitor - Always have the answers to SQL performance issues SQL Monitor keeps an eye on your SQL Servers 24/7, so you don't have to. It helps you proactively monitor the performance of your SQL Servers, saving you time, and makes sure you always have the answers to tough performance problems. [Find out more.] [SQL in the City] SQL in the City Streamed Register now for Redgate's free virtual event offering a wealth of SQL Server sessions for you to livestream on December 14 and 15. [Find out more and register.] [DLM Whitepaper] Get the advantages of DevOps for your database Reduce risk, speed deployments, deliver value fast. Database Lifecycle Management brings the advantages of source control, continuous integration, and release management to your database. [See how with this free whitepaper.] Editorial - Faster Versions and More Support When I started working with SQL Server, the versions came a strange paces. Three years between v4.2 and v6.0, but less than 1 before v6.5. Then two years to v7, one more to SQL Server 2000, but five until SQL Server 2005. After SQL Server 2008, we've moved to a 2-3 year cycle, but I can see that accelerating a bit more as SQL Server vNext looks like it might come in 2017, a year after SQL Server 2016. Since much of the code is released to Azure first and exercised there, I could see us getting on premise releases coming every 1-2 years in the future. Overall, I like this pace. We can see changes coming to the product on a regular basis and enhancements that I can choose to implement or not. We no longer have to wait years to get an enhancement or evolution of a feature. We always have the choice to upgrade. Many of us won't, but that's OK. Perhaps we'll use new features in new applications, and learn how they might make the case to, or not to, upgrade existing instances. There's a flip side to the rapid releases and enhancements to the platform. Many of our organizations expect a database server to last for years. We invest plenty of resources in building these database servers, and I think many managers and organizations expect they will be in service for at least five, and maybe ten, years or more before they are decommissioned or perhaps upgraded. I know there are still plenty of SQL Server 2005 and 2008 instances (probably not 2000) that are being used on a daily basis with no plans to replace them. This means that as the developers and DBAs that work with SQL Server, we will likely be supporting a wide range of versions in the future than ever before. Even if you started a company two years ago with a SQL Server 2014 instance, I'd guess you might have a few more now, perhaps some SQL Server 2016 instances. In two years you might have 2014, 2016, and 2017 (assuming vNext becomes SQL Server 2017). In five years, perhaps you'll have five versions to support. I know there are people in that situation today with 2014/2012/2008R2/2008/2005 instances in production. There was an announcement this week from Microsoft. They are now offering [SQL Server Premium Assurance], which will give you 6 years of support after the 10 you ca now get with Extended support. Certainly there are restrictions and costs, but I expect that there will be some large organization that think it's worth paying this to continue to use older versions as long as possible. After all, this is a platform, which implies some level of stability. That doesn't solve the knowledge issue and need to keep staff around that understand the platform, but with all the education and knowledge available from places like [SQLServerCentral], perhaps supporting multiple versions isn't a bit deal. Steve Jones from [SQLServerCentral.com] » [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. Vendors/3rd Party Products [Free Webinar: Redgate DLM Demo] - Learn how to use Redgate’s DLM solution to improve your database change management process in this free webinar on December 13th. Microsoft Data Platform MVP, Grant Fritchey, and Redgate’s Arneh Eskandari, will demonstrate how Redgate’s tools plug into TFS, TFS Build & Microsoft Release Management so that you can build, test and deploy your database alongside your application code. ...[(more)] [SQL in the City Streamed less than a week away!] - There's still time to sign up for Redgate's free virtual event. Register now to join the live stream on December 14 or 15, when you can hear from a range of top speakers including Data Platform MVPs Steve Jones and Grant Fritchey....[(more)] [Deploying schema changes to multiple databases using the SQL Compare command line] - Feodor Georgiev explains how SQL Compare Pro makes it possible to automate the process of synchronizing a source and target database. By calling SQL Compare from a simple batch script, he shows how to make it work for a whole list of targets, one after another....[(more)] [Managing multiple database versions] - Sometimes, it's necessary to have different versions of a database running in production. But how do you establish a single source of truth in source control? Alex Yates from DLM Consultants shows how to use object filters in SQL Compare to build multiple database versions from the same source. Read the article....[(more)] T-SQL [NOLOCK] - NOLOCK, some say it’s a fast option for queries and will never cause blocking, I say it’s quite dirty and MAY cause blocking. I used to think that a NOLOCK (which is the same as read uncommitted) hint actually meant...[(more)] [Redgate need your help with a survey] - Redgate is running a short survey to better understand the tools you're using for database development. There are eleven questions, and three $50 Amazon gift cards up for grabs as a thank you for responding....[(more)] [Actual Chats: RIGHT OUTER joins in the wild] - Colleague: I think I just encountered my first Right join *shocked* ...[(more)] The Lighter Side [Virgin Galactic returns to flight with its VSS Unity spacecraft] - It has been a long road back from a fatal 2014 accident for Virgin Galactic, the splashy spaceship company founded by Sir Richard Branson to bring the masses into space. After its VSS Enterprise crashed into the Mojave Desert during a test flight, killing vehicle co-pilot Michael Alsbury, the company has had to redesign some key safety systems and rebuild its spacecraft. It revealed the VSS Unity in February....[(more)] [A slow Atari 2600 emulator is now inside Minecraft—and it’s pretty cool] - The world's first functioning machine emulation of a game console has arrived within Minecraft. However, it's not exactly playable. The emulator renders Atari 2600 games at a refresh rate so slow, you can only see its games, including Donkey Kong and Space Invaders, animate by using a time-lapse camera....[(more)] SQL Server News [SQL Server + Python — What’s new] - Python is one of the most popular and fastest-growing languages used today. Pyodbc (Python-SQL Server Connector) is an open source Python module maintained by Michael Kleehammer that uses ODBC Drivers to connect to SQL Server. It allows you to connect from the platform of your choice to SQL Server on-premises and in the cloud. Pyodbc is also cross platform and can be installed using pip....[(more)] [SQL Server + Java: What’s new] - Java continues to be one of the most widely used programming languages for a variety of application scenarios and industries. The Microsoft JDBC Driver for SQL Server is used to connect Java applications to SQL Server, whether SQL Server is hosted in the cloud or on-premises, or provided as a platform-as-a-service....[(more)] Software Development [DevOps Introduction] - For the longest time, I thought DevOps meant the developers were the ones who took over server maintenance from an operations team, be it a web admin or DBA. The only way for that to happen was to be up in the cloud. ...[(more)] Security news and thoughts [Scaling securely at the frontend] - High volume web sites that offload scale to the frontend using techniques like leveraging edge caching with a partner content delivery network (CDN) see many benefits, including better performance and a much simpler, more resilient, and potentially cheaper infrastructure to maintain....[(more)] [Cryptography Professor To Audit Open Source Software Used By Most VPN Services] - Cryptography and network security professor Matthew Green has been invited by Private Internet Access, a major U.S. VPN service provider, to audit the open source OpenVPN software used by most VPN services and users....[(more)] [Companies Are in Short Supply of Cybersecurity Talent] - What do the FBI, Trump?EU?s hotel chain, Sony, and JP Morgan Chase all have in common? They are all companies that were hacked in 2014 and 2015, each one a reminder to the rest of us that no one is immune to the threat of criminal hackers. Just this October, a cyber attack disrupted PayPal, Twitter, Spotify, and multiple other websites....[(more)] [The Future of Credit-Card Security May Involve Blinking Numbers] - Your credit card may start winking at you soon in an effort to keep your data safe. French digital security company Oberthur Technologies, which maintains a significant design and production operation in Los Angeles, has developed a digital display powered by a micro-thin battery. ...[(more)] [Network Monitoring: Your First Line of Defense] - What’s your first line of defense? In the military, it’s the troops on the front line. On the football field, it’s the defensive line. And in the data center, it’s the network monitoring system....[(more)] [Thieves can guess your secret Visa card details in just seconds] - Thieves can guess your secret Visa payment card data in as little as six seconds, according to researchers at Newcastle University in the UK. Bad actors can use browser bots to distribute guesses across hundreds of legitimate online merchants....[(more)] [VIEW SERVER STATE permission was denied on object 'server', database 'master'.] - I had to create few users for the first time and give access to execute views from user database. I have created logins for them and gated access to select from views and tables at object level but see below error when they select from views....[(more)] [Error: The server principal "IncidentManagement" is not able to access the database "DatabaseName"] - Error: The server principal "IncidentManagement" is not able to access the database "DatabaseName" under the current security context. for conection string:...[(more)] [This Week in Data – Episode 15 (Why People Are Flocking to SQL Server)] - Welcome back to another episode of This Week In Data! If you missed our previous episodes, you can watch them here. In this week’s episode, we discuss why companies are deciding to move to SQL Server....[(more)] Reporting Services [What’s new in Mobile Reports in SQL Server 2016 SP1] - In our last post around SQL Server 2016 SP1, we promised to do a post about some enhancements we’ve made to mobile reports in the latest release. We’ll highlight two major items we’ve updated and also provide the full change list of updates and fixes for mobile reports that many of you have asked for to date....[(more)] [Migrating reports between SSRS report servers simplified with new PowerShell commands] - I recently needed to replicate all of the reports on one report server to a different server. I started downloading each report one at a time to a file system folder, moving them to the other machine and then uploading each RDL file....[(more)] R Language [R is better than M for difficult data wrangling tasks in Power BI] - Yep, I said it! In most cases I believe that it is true. R has been around for over 20 years so it is much more mature than M. It has close to 10K packages many of which are meant just for data wrangling. If you don’t agree with me now I think by the end of this post it’s a good chance that you will think otherwise. This blog is written for those with little exposure to R so I will keep it simple. I am going to start with a very basic file import example and I will end with a somewhat simple but slick multiple file import example....[(more)] [Stylometry: Identifying authors of texts using R] - Few people expect politicians to write every word they utter themselves; reliance on speechwriters and spokepersons is a long-established political practice. Still, it's interesting to know which statements are truly the politician's own words, and which are driven primarily by advisors or influencers....[(more)] [Multivariate Variable Analysis using R] - So far I’ve worked on simple analytical techniques using one or two variables in a dataset. This article is a sort of a summary – about various techniques we can use for such datasets, depending on the type of variable in question....[(more)] [Configuring R on SQL Server 2016] - As people look to incorporate R code into their SQL Server environment there are a lot of questions surrounding how it works. As R is very memory intensive, is it possible that people running R code will prevent other processes from running?...[(more)] Product Upgrades and Releases [How to monitor backups and other SQL Agent jobs using SQL Monitor] - How many times, when reading a report of some highly public case of data loss or corruption, do you inevitably stumble across a line like: ‘Unfortunately the backup job had not been running for the last three months, and no one noticed’?...[(more)] [SQL Server Interpreter for Apache Zeppelin 0.6.2] - I’ve updated the code-base to Apache Zeppelin 0.6.2 and I’ve also finished a first simple-but-working support to autocomplete (you can activate it using CRTL + .). Right now the autocomplete is based on the keywords specified here:...[(more)] [Announcing SQL Server Management Studio – 16.5.1 Release] - Today, we are pleased to announce the latest generally-available (GA) quality release of SQL Server Management Studio (SSMS) 16.5.1. This update fixes a couple issues listed below....[(more)] [Released: Public Preview for Microsoft Azure SQL Database Management Pack (6.7.11.0)] - We are working on significantly updating the Management Pack for Azure SQL Database. Your feedback on this public preview will improve the quality of the final release. Please install the bits from the download location below and send us your feedback. You can comment on this post or send specific feedback to sqlmpsfeedback@microsoft.com. We are looking forward to hearing from you....[(more)] [NetflixOSS: Announcing Hollow] - “If you can cache everything in a very efficient way, you can often change the game”...[(more)] PowerPivot/PowerQuery/PowerBI [Custom Functions Made Easy in Power BI Desktop] - I have written a lot about Power Query M scripting language, and how to create custom functions with that. With recent updates of Power BI Desktop, creating custom functions made easier and easier every month....[(more)] [Power Query, R is here to help you!] - Microsoft Power BI has an embedded ETL tool called Power Query that is great for basic data transformation. You can handle many data transformation tasks with just a few clicks. But when your data transformation tasks become complex you often have to result to writing custom M code. M is a relatively new programming language and is not that intuitive. The community of M developers is small so finding help is limited in comparison to finding help with other programming languages....[(more)] [Format measures as dates in Power BI] - If you want to format a measure as date in Power BI, you need an expression returning a date/time data type. This is different from what you can do in Power Pivot and in SSAS Tabular, where you can format any numeric expression using a date and/or time format, and the conversion is implicitly done to perform such a visualization....[(more)] [Implement Non Visual Totals with Power BI security roles] - This article describes how to implement non-visual-totals with security roles in Power BI and Analysis Services Tabular, which by default show only visual totals of measures in the model....[(more)] [Power Query, Power BI And The “Allow Data Preview To Download In The Background” Option] - Recently I was asked by a customer to do some tuning on an Excel workbook with a lot of Power Query queries in it. Although all of the data used in the queries was coming from tables in the workbook itself and the data volumes were small, there were fifty Power Query queries and clicking Refresh All resulted in a large, prolonged spike in CPU and memory usage by Excel....[(more)] [Checking your Wait Stats via Power BI] - Wait stats is my go to thing, however I do get bored just querying it via a table so I decided to broaden my horizons and see how “analytical” I could get with it via Power BI....[(more)] [Power BI Custom Visuals Class (Module 28 – Sankey)] - ...[(more)] Polybase/HDInsight [Reading Polybase Execution Plan Details] - A few days ago, I made mention of how the Remote Query in a Polybase execution plan contained interesting details....[(more)] Performance Tuning SQL Server [TIL: Starting a recurring SQL trace] - Today I learned… How to set up a recurring Server-side SQL trace that runs every hour for 10 minutes....[(more)] [Extended per-operator level performance stats for Query Processing] - Back in March we announced the availability of per-operator level performance stats for Query Processing (exposed in Showplan XML) with SQL Server 2014 SP2 and SQL Server 2016, such as CPU and I/O....[(more)] [What keeps clearing my query stats?] - I was building out a blog post about cardinality today and noticed that sys.dm_exec_query_stats was getting cleared my my Surface Pro 3 dev instance about every 30 to 60 seconds....[(more)] [Does Truncate Table Reset Statistics?] - Short answer: the SQL Server optimizer will know that the table was truncated, but statistics might not update when you expect....[(more)] [Transaction Commit latency acceleration using Storage Class Memory in Windows Server 2016/SQL Server 2016 SP1] - SQL Server 2016 SP1 adds a significant new performance feature, the ability to accelerate transaction commit times (latency) by up to 2-4X, when employing Storage Class Memory (NVDIMM-N nonvolatile storage). This scenario is also referred to as “persistent log buffer” as explained below....[(more)] [Improve query performance on memory optimized tables with Temporal using new index creation enhancement in Sp1] - With the introduction of the Temporal feature in SQL 2016 and Azure SQL Database, there is an ability to time travel through the state of data as it was at any given point of time. Alongside In-Memory OLTP, Temporal on memory optimized tables allows you to harness the speed of In-Memory OLTP, and gives you the same ability to track history and audit every change made to a record....[(more)] [Optimize For Unknown] - Hello Dear Reader! It's been a while. I was working with a friend and we came across an interesting problem. They had a large amount of skewness/data skew. This led to some performance issues for them. The way this manifested itself was in a set of queries that ran quickly, normally within seconds....[(more)] [Developers Choice: Query progress – anytime, anywhere] - One of the most important actions when a performance issue hits, is to get precise understanding on the workload that’s executing and how resource usage is being driven. The actual execution plan is an invaluable tool for this purpose....[(more)] [When Did SQL Server Last Update That Statistic? How Much Has Been Modified Since? And What Columns are in the Stat?] - Whether I’m working as a DBA, a consultant, a teacher, or just answering questions in my inbox, I always end up needing a script to inspect statistics one way or another....[(more)] Microsoft News : General Interest [Microsoft R Server 9.0 now available] - Microsoft R Server 9.0, Microsoft's R distribution with added big-data, in-database, and integration capabilities, was released today and is now available for download to MSDN subscribers. ...[(more)] Microsoft News [Introducing SQL Server Premium Assurance] - IT departments are in a fundamental state of change, with the drive for digital transformation creating many net new applications and moving others to the cloud. At the same time, companies must also maintain stable and secure operation of their existing applications databases. To provide better flexibility in managing these diverse priorities, Microsoft is announcing two new offerings to help you run applications longer without disruption: Windows Server Premium Assurance and SQL Server Premium Assurance...[(more)] Hardware News [Micron Infuses 8TB 5100 Series SSDs With 3D TLC NAND] - Micron is shaking things up a bit with its latest 5100 Series SSDs, which not only tip the scales with 8TB of storage but also bring a blistering 74,000 random write IOPS to bear....[(more)] [Extending Home Networks - A Comparison of G.hn, HomePlug AV2 and Wi-Fi Mesh] - Over the last decade or so, we have seen a rapid increase in the number of devices connecting to the home network. The popularity of IoT has meant that even devices that are not mobile require communication over the Internet, but, their placement might be far away from the primary router in the house....[(more)] [HGST Ultrastar SS200 SSD: Up to 7.68 TB, 1.8 GB/s, Dual-Port SAS 12 Gbps] - Western Digital has introduced a new family of Ultrastar SS200 SAS SSDs that wed high-performance with capacities up to 7.68 TB as well as relatively high endurance. The drives are aimed at mixed-use and read-intensive workloads that require not only maximum throughput, but also reliability. To guarantee the latter, the Ultrastar SS200 uses the company's Guardian technology....[(more)] HA/DR/Always On/Clustering [Unable to connect to SQL Server AlwaysOn Listener on Azure due to misconfigured subnet mask] - When it comes to troubleshooting, the more you know the better troubleshooter you are (in my option). Sometimes knowing more actually gets in the way. We repeatedly get calls where customers couldn’t connect to AlwaysOn Listener on Azure because of the misconfiguration of the subnet mask for the listener IP....[(more)] [SQL SERVER – FIX Error – Cluster Network Name showing NETBIOS status as “The system cannot find the file specified”] - Over a period, I have learned that fixing an issue is easier if we know which log we should look at and what we should search on google. Here is the situation which I was into few days back. In this blog post we will learn how to fix the error Cluster Network Name showing NETBIOS status as “The system cannot find the file specified”....[(more)] [Adding database back to Always On High Availability Group] - Summary: If databases in secondary replica are out of sync with primary then below is the workaround which helps to fix....[(more)] Events to attend [Query Tuning Training in Early 2017: Boston and Cleveland!] - A couple of days ago in Washington DC I presented the final 2016 delivery of my Tuning Your Biggest Queries seminar. The class went really well (at least from my perspective!) and it was a great end to a busy 2016 training schedule....[(more)] [Learn More About the SSIS Catalog] - Join Kent Bradshaw and me for three free webinars about the SSIS Catalog! SSIS Academy: Using the SSIS Catalog is Tuesday – Thursday, 27-29 Dec 2016....[(more)] [SQL in the City is coming next week] - Next week is the 2016 version of SQL in the City and you can register now to attend. It’s free and doesn’t require any travel. You can watch the event live from the comfort of your office chair....[(more)] ETL/SSIS/ELT [Cleaning Up SSISDB] - I really appreciate what MS has done w/ the SSIS Catalog. We have built-in logging at a level that wasn’t possible in prior releases, but that comes at a cost. The default retention is pretty high (365 days) and MS chose to handle cleanup using cascading deletes. This sort of coding makes life easier for the developers, but almost always performs poorly....[(more)] [Broken References in the SSIS Catalog] - It’s possible to create a broken reference in the SSIS Catalog. What’s a “broken reference?” Let’s begin by explaining SSIS Catalog References. I wrote a lengthy explanation of SSIS Catalog References and Environments in SSIS Catalog Environments – Step 20 of the Stairway to Integration Services at SQL Server Central . ...[(more)] DocumentDB/Key-Value NoSQL Databases [NoSQL .NET Core development using an local Azure DocumentDB Emulator] - I was hanging out with Miguel de Icaza in New York a few weeks ago and he was sharing with me his ongoing love affair with a NoSQL Database called Azure DocumentDB. I've looked at it a few times over the last year or so and though it was cool but I didn't feel like using it for a few reasons:...[(more)] Database Design, Theory and Development [SQL SERVER – GENERATE AND USE TIME / DURATION DIMENSION] - Recently, I was working on a backups dashboard in PowerBI to do some analytics on the health of our database backups across the board. We set out to understand some basic things about our backups...[(more)] [Domain vs. (Data) Type, Class vs. Relation (UPDATE)] - "Our terminology is broken beyond repair. [Let me] point out some problems with Date's use of terminology, specifically in two cases....[(more)] [Be Careful with Derived Data] - We often perform database reverse engineering as part of our consulting work. We have found that it’s common for databases to contain derived data. Derived data is data that can be computed from other base data. Often, the storing of derived data is a mistake and it would have been better if developers instead computed derived data on the fly....[(more)] Data Privacy [Court: License plates must be displayed, so by definition they aren’t private] - A Virginia state court has decided that a license plate number is not personal information—essentially validating law enforcement’s use of license plate readers in the commonwealth....[(more)] Data Mining/Data Analysis [How to Dive into Data Lakes and Not Drown] - Kyvos Insights has a question for Enterprise Analytics executives: How are your Data Lakes working out for you? For many that embarked on journeys to stock up Hadoop based Data Lakes, with everything from structured transaction records to non-relational data such as log files, Internet clickstream records, sensor data, social streams, and so on, the answer has been...[(more)] Computing in the Cloud [How to Choose the Right Cloud Provider and Solution] - Once you’ve tackled the challenge of understanding if you should move to the public cloud, you’re faced with the next big question: which cloud provider and solution will meet my business’ needs?...[(more)] Columnstore Indexes [Data Channel - Interview 01 - Andreas Wolter on Columnstore] - Welcome to the first ever interview of DataChannel. I am honored to have Mr.Andreas Wolter, a German MVP, MCM, MCSM for the first interview. Detailed profile of Andreas provided below....[(more)] Career Growth [SQLskills holiday gift to you: all 2015 Insider videos] - As we all wind down for the 2016 holiday season, we want to give the SQL Server community a holiday gift to say ‘thank you’ for all your support during 2016, and what better gift than more free content?!...[(more)] [On Being Disruptive] - Being disruptive is a powerful way to conduct business. Steve Jobs and Bill Gates insisted that computers belonged not just in business offices but in the homes of regular joes, and built businesses around a need that had not fully manifested itself. ...[(more)] [Top 10 Free SQL Server eBooks] - Looking for more resources on SQL Server? Explore these top 10 SQL Server ebooks. From Introducing Microsoft SQL Server 2016 to SQL Server Execution Plans, in this guide you will find ebooks covering the most trending topics on SQL Server....[(more)] Backup and Recovery [TDE Recovery] - This week’s TSQL Tuesday is being held by the mighty Kenneth Fisher – Backup and Recovery- see this link, how can I not get involved with this one?...[(more)] Azure SQL Database [Setting up replication from on-premise SQL Server to Azure SQL DB] - For performance reasons we are looking for a way to split our write-operations from our read operations. A good way to do that is by duplicating the database onto another server (on-premise) or platform (Azure SQL DB). When you look at the options (Availability Groups (AG), database mirroring, clustering, replication, Azure sync groups, etc.) the easiest way to quickly duplicate a database and keep the data up-to-date at the same time is SQL Server replication....[(more)] [Clustered Columnstore Index in Azure SQL Database] - Columnstore index is the preferred technology to run analytics queries in Azure SQL Databases. We recently announced general availability of In-Memory technologies for all Premium databases. Similar to In-Memory OLTP, the columnstore index technology is not available in databases in the Standard and Basic pricing tiers today....[(more)] Azure SQL Data Warehouse and Data Lake [SQL On The Edge #11 – Azure Data Lake Fundamentals] - It is no secret that we are generating increasing amounts of data every day. Hundreds of apps on our phones that we carry with us every day, amass a collection of data from connected devices. Hundreds and thousands of different computer systems enable the operations of every single company in the planet....[(more)] Analysis Services / BI on the MS Stack [How Much RAM Do I Need For My SSAS Tabular Server?] - Much of my client work these days is focused on performance… making slow Analysis Services servers run faster. The good part about these types of engagements is that there’s usually (not always) a good amount of low-hanging fruit and I get to play the hero that swoops in to save the day. The bad part about these types of engagements is that many of the performance issues could have been avoided (or at least mitigated) had the server been given enough memory the start....[(more)] [How Much RAM Do I Need For My SSAS Tabular Server?] - Much of my client work these days is focused on performance… making slow Analysis Services servers run faster. The good part about these types of engagements is that there’s usually (not always) a good amount of low-hanging fruit and I get to play the hero that swoops in to save the day....[(more)] Administration of SQL Server [INTRODUCTION TO BACKUP ENCRYPTION IN SQL SERVER] - One of the most essential need of every Database Administrator is a backup file. This file acts as a life savior when any disaster occurs either on machine or on the server. When talking about SQL server, such backup files are very much essential in order to have a duplicate copy of the server data....[(more)] [SQL Server Quickie #42 – Recovery Model FULL] - Today I have uploaded SQL Server Quickie #42 to YouTube. This time I’m talking about the Recovery Model FULL in SQL Server...[(more)] [SQL SERVER – Errors When Disk Space is the Reason] - In the past, I have written a number of blogs when it comes to working with errors pertinent to disk space running out. In this blog, I try to consolidate the common messages you are going to see in the Error logs when the database engine is not able to get the space that it needs from the disk subsystem....[(more)] [SQL SERVER Management Studio – Taking DB Offline Made Easier] - I was playing around with SQL Server Management Studio 2016 and when I was about to take the database to offline mode, I was presented with a neat checkbox to “Drop All Active Connections” as part of the dialog box....[(more)] [SQL Server Diagnostic Information Queries for December 2016] - This month, I have added a separate set of queries for SQL Server vNext. Right now, they are almost identical to the SQL Server 2016 queries, but I will make changes to surface new functionality in SQL Server vNext over time. I have also added support for new functionality in SQL Server 2016 Service Pack 1....[(more)] [Should I Install Multiple Instances of SQL Server?] - Instance stacking is the technique of installing multiple instances of SQL Server on the same Windows instance. For example, you might have a VM or server named SQLPROD1, and have:...[(more)] [Moving Tasks with Copy-SqlJob] - In this post, I want to look at Copy-SqlJob, which will allow me to easily move jobs from one instance to another....[(more)] [SQL Server & Containers – Part Four] - In this final part of my series on SQL Server & Containers I’ll detail an option to run earlier versions of SQL Server in containers....[(more)] --------------------------------------------------------------- Administrative To be removed from this list, please click [here] Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can [change your profile] or follow the instructions on the daily newsletter. --------------------------------------------------------------- This transmission is ©2015 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. Contact: [webmaster@sqlservercentral.com]