A place where share my experience on SQL Server and related technologies
When data distribution is heavily skewed, cardinality estimation (how many rows the query optimizer expects each operator to process) can be wildly incorrect, resulting in poor quality query plans and degraded performance. You've probably seen the advice to update all statistics if a query plan looks wrong – but is that the right advice? In many cases, no! These are "sledgehammer" approaches, and while they might solve some problems (usually parameter sniffing problems), they don't solve the actual problem. In this session, you'll learn a generalized yet tailored-to-the-table way to solve query plan quality problems for very large tables (VLTs). Topics will include creating, using, and updating filtered statistics; using forced parameterization and templatized plan guides; and understanding stored procedures and how they can leverage filtered statistics.
Recently I had the opportunity to read the book SQL Server 2012 Reporting Services Blueprints written by Marlon Ribunal and Mickey Stuewe and published by Packt Publishing.
First, I have to tell you that I am not a Reporting Services guy, I have much more knowledge on other topics such as Database Development, Performance Tuning, and so on. When I have heard about the opportunity to read this book I have thought it was a great opportunity to increase my knowledge on SQL Server Reporting Services, but the time is always short, so I needed a practical book that goes to the point quickly.
If I would describe you the book with only one adjective, absolutely I could tell you that this book is concrete and easy to read. There are many pictures in the book and the authors guide the reader, step-by-step, from the Definition of the Data Source for an SSRS Project up to the Reporting Services Best Practices and Transactional Replication topics.
The book covers all feature of SQL Server Reporting Services and provides you all information you need to start develop database reports with SSRS in a week. If you want to look inside the book you can download a sample chapter here and this is the table of contents:
- Chapter 1: Let's Breakdown the Numbers
- Chapter 2: Making a Good Impression
- Chapter 3: Getting Some Actions
- Chapter 4: Everything Looks Better with Charts
- Chapter 5: Location, Location, Locations!
- Chapter 6: Let's Get Analytical!
- Chapter 7: Sir, We're Ready to Deploy!
- Chapter 8: Beyond the Horizon!
- Appendix A: SSRS Best Practices
- Appendix B: Transactional Replication for Reporting Services
My preferred chapters are the 2, 4 and 5.
This book cannot missing in your digital or physical library!
The session Building Your T-SQL Tool Kit: Window Function Fundamentals by Christina E. Leo (@christinaleo) has been published on channel SQLPASS TV.
Have you pulled a script to identify duplicates from a blog post but couldn't quite get it to work because you weren't sure what that ROW_NUMBER() function was doing? Maybe you heard talk about creating running totals without using subqueries, but you got frustrated when the groups weren't totaling correctly. Or maybe, you've never even heard of window functions. All are good reasons to attend this all-demo session, which demystifies this versatile T-SQL tool. First, we'll break apart the OVER clause, the key to understanding how window functions work. Then we'll expand on each group of functions that can use the OVER clause: ranking, aggregate, and analytic functions. Finally, we'll look at real scenarios where this tool works and talk about performance considerations. When you leave, you'll have the fundamentals you need to fully develop your mastery of window functions.
SQLPASS TV channel has been published the session Automate Your ETL Infrastructure with SSIS and PowerShell that Allen White (@SQLRunr) has done during the PASS Summit 2013.
Much of your ETL process flow consists of packages that are very similar in structure, capturing data from a single source and transferring that to a single destination. Creating the individual packages can be tedious, and it’s easy to miss something in the process of generating the same basic package over and over again. BI Markup Language makes it easy to build new packages, and PowerShell makes creating the BIML scripts easy. In this session, we’ll see how you can use PowerShell to generate dozens of SSIS packages doing similar tasks from a defined set of ETL sources.
Enjoy the session and thanks to Allen White!
Let me point out the upcoming live events scheduled for December 2013 organized by PASS Business Intelligence Virtual Chapter.
Create and Load a Staging Environment from Scratch in an Hour with Biml
Date: Thursday 12 December Noon PST / 3 PM EST / 8 PM GMT
Speaker: Scott Currie
Business Intelligence Markup Language (Biml) automates your BI patterns and eliminates the manual repetition that consumes most of your SSIS development time. During this hour long presentation, Scott Currie from Varigence will use the free BIDSHelper add-in for BIDS and SSDT to introduce Biml and use to automatically generate large quantities of custom SSIS packages. The session will be largely demonstration driven, and reusable sample code will be distributed for you to use in your own projects. Using a live-typing approach, Scott will start from scratch and by the end of the session create a full-blown staging environment. This will include the creation of *hundreds* of target table creation scripts, data load packages, data scrubbing rules, logging, and more. The best part is that you can freely reuse the code in your own environment just by changing the connection strings - or make small changes to implement your own data load patterns.
Inferred Dimension Members within MDS and SSIS
Date: Monday 16 December 3 PM PST / 6 PM EST / 11 PM GMT
Speaker: Reza Rad
Combining Master Data Services with Data Warehouses, will cause some challenges in ETL Scenarios. In this session we will go through a demo of Inferred Dimension Members implementation with SSIS considering the fact that MDS keeps the single version of truth for the dimension record. In this session you will learn how we will write back new record's data into MDS entity as an Inferred member. The staging structure of Master Data Services and Batch Processing will be used for this. Then you will learn what is the best practice to add the inferred record into Data Warehouse dimension. Updating the existing dimension member also would consider the Inferred member and apply SCD types only if this is not an inferred Member.
Guerrilla MDS/MDM The Road To Data Governance
Date: Thursday 19 December Noon PST / 3 PM EST / 8 PM GMT
Speakers: Ira Whiteside and Victoria Stasiewicz
Ira and Vic's session "Guerrilla MDS" will be a walk-through of a real-world implementation for a master data model (MDM) and metadata mart utilizing SSIS, MDS and POWER BI EXCEL add-ins as well as applying proper data quality techniques. We will walk through in detail the processes necessary for utilizing the complete MDS functionality as follows: creating entities attribute, relating entities the domain based attributes, staging leave table, updated entity content, apply business rules, create subscription view and set up security. Source code for all samples and PowerPoint will be made available.
From Red-Gate web site you can download, for free (plus a trial of SQL Monitor), the eBook Fundamentals of SQL Server 2012 Replication written by Sebastian Meine (@sqlity).
Data replication is traditionally considered a complex feature and generally we get discouraged easily, already during the preparation of the test environment. Why the implementation of a SQL Server Replication is considered a complex activity? Which problems could you meet? The most common problems that you could encounter are related to the database schema that it has to be thought for replication scenarios and there could be problems related to the replication agents' permissions. Finally, you may encounter problems related to the architecture of the network and the Internet bandwidth especially if you want to implement a web replication or if you have to use an ftp area for data exchange. To do a good job, we need different skills.
This book cannot miss in your digital library! And obviously you can buy the paperback on Amazon.
Many of my clients need to make data that lives on one server available on another server. There are many reasons for such a requirement. You might want to speed up cross-server queries by providing a local copy of the data. Or you might want to make the data available to resource intensive reporting queries without impacting the OLTP load, maybe even with an intentional delay so you're always reporting against complete days only. Finally, you might be looking to implement high availability. In all these situations, SQL Server Replication is a viable option to look at when planning for the implementation of such a requirement.
With this book, I want to introduce you into the vast world of SQL Server Replication and show you its most important strength and weaknesses. After working through the exercises, you will be able to make an informed decision whether replication is the right feature to use and which type of replication is the most advantageous in your situation. You will also know when to stay away from replication and use other features such as simple log shipping or the new "Always On" feature set.
Fundamentals of SQL Server 2012 Replication provides a hands-on introduction to SQL Server replication. The book begins with a short overview that introduces you to the technologies that make up replication. In the following chapters, the book will walk you through setting up different replication scenarios. All hands-on exercises are designed with security best practices in mind. When you're finished working through the exercises, you will be able to implement your own multi-server replication setup while following the principle of least privilege.
Enjoy the book!
Let me point out the upcoming live events, dedicated to Business Intelligence with SQL Server, that PASS Business Intelligence Virtual Chapter has scheduled for November 2013.
The "Accidental Business Intelligence Project Manager"
Date: Thursday 7th November - 8:00 PM GMT / 3:00 PM EST / Noon PST
Speaker: Jen Stirrup
- You've watched the Apprentice with Donald Trump and Lord Alan Sugar. You know that the Project Manager is usually the one gets fired
- You've heard that Business Intelligence projects are prone to failure
- You know that a quick Bing search for "why do Business Intelligence projects fail?" produces a search result of 25 million hits!
- Despite all this… you're now Business Intelligence Project Manager – now what do you do?
In this session, Jen will provide a "sparks from the anvil" series of steps and working practices in Business Intelligence Project Management. What about waterfall vs agile? What is a Gantt chart anyway? Is Microsoft Project your friend or a problematic aspect of being a BI PM? Jen will give you some ideas and insights that will help you set your BI project right: assess priorities, avoid conflict, empower the BI team and generally deliver the Business Intelligence project successfully!
Dimensional Modelling Design Patterns: Beyond Basics
Date: Tuesday 12th November - Noon AEDT / 1:00 AM GMT / Monday 11th November 5:00 PM PST
Speaker: Jason Horner, Josh Fennessy and friends
This session will provide a deeper dive into the art of dimensional modeling. We will look at the different types of fact tables and dimension tables, how and when to use them. We will also some approaches to creating rich hierarchies that make reporting a snap. This session promises to be very interactive and engaging, bring your toughest Dimensional Modeling quandaries.
Data Vault Data Warehouse Architecture
Date: Tuesday 19th November - 4:00 PM PST / 7 PM EST / Wednesday 20th November 11:00 PM AEDT
Speaker: Jeff Renz and Leslie Weed
Data vault is a compelling architecture for an enterprise data warehouse using SQL Server 2012. A well designed data vault data warehouse facilitates fast, efficient and maintainable data integration across business systems. In this session Leslie and I will review the basics about enterprise data warehouse design, introduce you to the data vault architecture and discuss how you can leverage new features of SQL Server 2012 help make your data warehouse solution provide maximum value to your users.
There is a day of the year in which an MVP is "expiring" and in that day he hopes to receive the renewal notification email for this important professional recognition. The MVP Award Program is the way used by Microsoft to say "Thanks for your commitment"... thanks for sharing your technical knowledge with others, thanks for helping others in the worldwide technical communities.
Today is the expiration day of my MVP Award, but is also the renewal day because I have received the long awaited email "Congratulations 2013 Microsoft MVP!" I begin in this way my fourth year in the MVP Award Program for SQL Server. It is an honor for me to be part of this magic group of people that every day share their technical knowledge with others, with passion!
In the past, someone asked me "How can I becoming an MVP?" or "What did you do for becoming an MVP?" My answer was: "Learn a technology very well and keep the pace because it will change quickly; help others about using this technology, not for money but only for a Thanks and put passion in all of this!". This is my point of view of the page Becoming an MVP.
Thanks very much to all of you; I will do my best to bring in a responsible way this great recognition!
Congrats to new MVPs and to all MVPs renewed:
As a friend of mine tells "The first time that something happens is a fortuity, the second time that it happens again is a probability and the third one is a certainty!"
Therefore, this year, SQL Saturday in Italy is a certainty! From October to December in Italy there will be three SQL Saturday events!
The first one: October 8th 2013, Milan
The second one: November 9th 2013, Verona
The third: 2013 December 13th, Ancona
All the CFPs are still open, if there are one or more arguments related to SQL Server, that you take care or that you are interested in, send your session proposals! It could be a great opportunity to visit Italy.
See you there!
From Red-Gate web site you can download, for free, the eBook SQL Server Backup and Restore written by Shawn McGehee.
One of the most important activities under DBA responsibility is to check backups and maintain an efficient recovery plan. In this book, Shawn McGehee explains how to build an efficient recovery plan; because after you have known that your database is in "Suspect Mode" or is corrupt and some data were lost, there is no worst news of that tells you that no valid backups are available for database recovery. It is not enough to take a backup, you must ensure that it is also possible to perform the restore!
This book cannot miss in your digital library! And obviously you can buy the paperback on Amazon.
The duties and responsibilities of a Database Administrator (DBA) make for a long and dynamically changing list, ranging from offering query tuning advice, to cutting stored procedures, all the way through to system process design and implementation for high availability. A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error or disaster. This means that if a database, for whatever reason, gets corrupted, dropped, or otherwise becomes unusable, then it is the DBA's responsibility to restore that database to the state it was in before the problem occurred, or as close as is possible.
Of course, this doesn't mean that a DBA is required to restore a database each and every day, just that if disaster does strike the DBA must be prepared to deal with it, regardless of when or why it occurs. If a DBA isn't prepared, and significant data is lost, or databases become unavailable to end users for long periods of time, then that DBA probably won't be in their job for too long. This is why a good, and tested, SQL Server backup and restore plan must be on the top of every administrative DBA's list of tasks.
In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool. Capturing backups using SSMS or simple scripts is perfectly fine for one-off backup operations, but any backups that form part of the recovery strategy for any given database must be automated and you'll also want to build in some checks that, for example, alert the responsible DBA immediately if a problem arises. The tool of choice in this book for backup automation is Red Gate's SQL Backup Pro. Building your own automated solution will take a lot of work, but we do offer some advice on possible options, such as PowerShell scripting, T-SQL scripts and SQL Server Agent jobs.
Enjoy the book!
The sessions of the event 24 Hours of PASS: Summit Preview (which was held on last July 31) were recorded and now they are available for online streaming!
If you have missed one session in particular or the entire live event, you can view and review your preferred sessions; you can find all details here.
This edition of 24 Hours of PASS wants to be a sneak taste of what you can expect from the next PASS Summit that this year will be in Charlotte (NC) from 15 to 18 October 2013.
In this post, we will learn how to install the NetFx3 feature (.Net Framework 3.5) on Windows Server 2012 (R2). This feature is required by SQL Server 2012.
Recently I have had the opportunity to setup a new Windows Server 2012 (R2) virtual machine in which I must install a new SQL Server 2012 instance. SQL Server 2012 Service Pack 1 installation wizard requires NetFx3 Windows feature enabled, if that feature is not enable, the SQL Server 2012 setup wizard will end with the error shown in the following picture.
Picture 1 – Error while enabling Windows feature: NetFx3
How to install NetFx3 on Windows Server 2012 (R2)
First, we have to open the Server Manager Tool of Windows Server 2012 (R2) and then we have to click on the Add Roles and Features item in the Manage menu as shown in the following picture.
Picture 2 – Add Roles and Features item in the Manage menu
If we have not already specified that we want to ignore the preliminary operations, it will show the Before you begin section of the Add Roles and Feature wizard as shown in the following picture.
Picture 3 – Before you begin section of the Add Roles and Features wizard
Click Next button, the wizard will shows the Installation Type section where we have to select the Role-based or feature-based installation option that it has used to configure this server by adding roles, role services and features. Picture number 4 shows the Installation Type section.
Picture 4 – Installation Type section of Add Roles and Features wizard
Click Next button, it will show the Server Selection section; in this section we have to select the Server on which to install NetFx3 feature, so click Select a server from the server pool and over the grid, select the server as shown in the following picture.
Picture 5 – Server Selection section of the Add Roles and Features wizard
Click Next button again, the wizard will shows the Server Roles section as you can see in the following picture.
Picture 6 – Server Roles section of Add Roles and Feature wizard
No selections are required in this section because we have to add a feature (NetFx3 is a Windows feature) and not a Server Roles. Click Next button to go to the Features section that shows all the Windows features that you can install on the selected server. As you can see, .Net Framework 3.5 Features has not installed yet; click in the related check box as shown the following picture.
Picture 7 – Features section of the Add Roles and Feature wizard
Click Next button, the wizard will shows the Confirmation section.
Picture 8 – Confirmation section of the Add Roles and Features wizard
As you can see, at the top of the window shown in the picture number 8 there is a warning message. It informs you that for one or more installation selections are missing source files on the destination server, so we have to specify an alternative source path. Click the Specify an alternative source patch link, the wizard will shows the window in the following picture.
Picture 9 – Specify an alternative source path for NetFx3 source files
The required source files for the .Net Framework 3.5 are in Windows Server 2012 (R2) DVD (or in the related ISO file); in this virtual machine, the DVD device is associated to the letter "F". Therefore, you can specify this alternative path "F:\Sources\SxS". Click OK button to return in the previous window (picture 8), so click Install button to start the installation process. The Results section will show the installation status, at the end the status will be Completed successfully as shown in the following picture.
Picture 10 – Result section of Add Roles and Feature wizard
The NetFx3 (.Net Framework 3.5) feature has been installed in your system. Now we can retry to install SQL Server 2012 Service Pack 1, the error shown in the picture 1 has been fixed.
By default, on Windows Server 2012 (R2) the NetFx3 (.Net Framework 3.5) feature is not installed because is already installed the .Net Framework 4.5; however some applications may require the NetFx3 feature and in this post we have learned how to install it.
This test has been done in a VMware virtual machine. Thanks to VMware Gurus Program because it donated to MVPs a license of VMware products.