There are many ways to process your SSAS Tabular Model. This can be achieved in SSIS using the Analysis Services Execute DDL Task or manually, through Management studio GUI but to have a little fun & make the task more flexible I’m going to script this with ASSL/TMSL & build a notification round it. We can then schedule this as a step in a SQL agent job, call it from SSIS or PowerShell.
Read more »
From time to time, we may encounter the following scenarios when dealing with data processing:
Read more »
It’s not often that I write negative articles surrounding SQL Server’s latest release but ever since we upgraded one of our BI boxes to run SQL Server Reporting Services 2016 (SSRS 2016), I have picked up on some frustrations from my team when using the upgraded Report Manager portal due to the unavailability of features that used to exist in versions prior to SSRS 2016. I have since realized that in spite of the many exciting features and improvements introduced in SSRS 2016, there is a downside to this latest version of reporting services that is likely to leave many administrators frustrated.
Read more »
Importing and exporting CSV files is a common task to DBAs from time to time.
Read more »
Scaling out Reporting services to multiple nodes, in itself, is a relatively simple process. It’s when we come to solve problems and investigate performance that we begin to see there is a lot more going on under the hood that’s not clear through implementation. There are also some foundational elements that have changed in SQL 2016 with next to no guidance from Microsoft on the changes.
Read more »
SQL Server Always On Availability Groups are an enterprise-level high-availability and disaster-recovery feature introduced the first time in SQL Server 2012as an alternative to database mirroring. A set of user databases that fail over together forms the availability group. These availability databases are hosted by the availability replicas and can be readable- writable at the primary replica and up to eight sets of secondary replica databases that can be configured to be read-only databases. The availability groups fail over due to the availability replica’s level issues and not the ones caused due to database level issues such as data loss or database corruption.
Read more »
With the introduction of SQL Server 2016, Microsoft introduced many new features which had taken SQL Server another step forward and they made sure that it stands in front among many major Relational Database Systems.
One such feature which has been lacking but desperately required was the native support towards JSON.
Read more »
What makes SQL Server 2016 one of my favorite SQL Server release since the release of SQL Server 2012 is the increased number of exciting new features that have been introduced. Whilst my article Top 5 New SQL Server 2016 DMVs for DBAs reviewed some of these new features albeit from a DBA point of view, in this article I continue to review SQL Server 2016 but from the perspective of an SSRS administrator in relation to the changes in report subscriptions.
Read more »
SQL Server 2016 SP1 is released as announced by Microsoft. It comes with a bunch of new features and enhancements as a result of customer and community feedback. In this article, I will introduce you to these new features and enhancements.
Read more »
Since its initial release that was part of SQL Server 2005, Dynamic Management Views (DMV) changed – mostly for the better – the role of database administration (DBA) within SQL Server-based environments. They improved the efficiency of DBAs regarding database server monitoring, issue diagnoses, and subsequent performance optimisation. As a result of this positive reception, it has become customary that some of the highly anticipated items in every release of SQL Server includes discovering what new DMVs will be added. In this article I take you through my favourite top 5 DMVs to come out of SQL Server 2016.
Read more »
In this article I’ll cover all aspects of a new SQL Server 2016 feature, Temporal Tables (System-Versioned), including:
Read more »
In the previous article, in this series, How to create and configure a Sunburst chart in SQL Server 2016 Reporting Services, we have explored new chart types in SQL Server 2016; the Sunburst chart and the Tree Map chart, that are used to display hierarchical data. The Sunburst chart is a way of presenting relational datasets together in a compact form. The Tree Map Chart is also a way of showing hierarchical data.
In this article, we are going to show how to create a Tree Map chart and its comparison with the Sunburst chart.
Read more »
SQL Server 2016 introduces a new set of configurations that can be applied at the database level to control its performance and behavior. These configurations were previously applicable only at the SQL instance level before version SQL Server 2016, With the ability to configure settings at the database level, many databases that are hosted in the same instance can now be isolated from each other, with each database has its own customized configurations. The database level configurations allow us also to set different database configurations for the Always On Availability Groups primary and secondary databases to meet the different types of workloads.
Read more »
SQL Server tries always to generate the most optimized execution plan for each stored procedure the first time that the stored procedure is executed. The SQL Server Engine looks at the stored procedure passed parameter values when compiling the stored procedure, the first execution, in order to create the optimal plan including the parameters and keep that plan for future use in the plan cache. This parameter analysis process is called the Parameter Sniffing.
Read more »
Dynamic Data Masking is a new security feature introduced in SQL Server 2016 that limits the access of unauthorized users to sensitive data at the database layer.
As an example of the need for such a feature is allowing the applications developers to access production data for troubleshooting purposes and preventing them from accessing the sensitive data at the same time, without affecting their troubleshooting process. Another example is the call center employee who will access the customer’s information to help him in his request, but the critical financial data, such as the bank account number or the credit card full number, will be masked to that person.
Read more »
Microsoft SQL Server 2016 introduces several significant new features, and enhances some existing ones in reporting services. We have summarised these new features in the previous article. We’ve also explored how to configure the SQL Server Mobile reports in my earlier article.
Read more »
Developers who work in both SQL and .NET world must have experienced the differences between Visual Studio IDE and SQL Server Management Studio IDE.
Being a full-stack developer myself, I always felt that SSMS lacked a lot of basic features when compared to the functionalities which Visual Studio IDE provided out of the box. With SSMS being tightly coupled with SQL Server Core engine, the IDE had quite a technical debt associated with it over a period of time.
Read more »
SQL Server 2016 came with many new features and enhancements for existing ones, that concentrate on the aspect of SQL Server security. One of the new security features introduced in SQL Server 2016 is Row-Level Security. This feature allows us to control access deeply into the rows level in the database table, based on the user executing the query. This is done within the database layer, in complete transparency to the application process, without the need to manage it with complex coding at the application layer.
Read more »
SQL Server 2016 introduces a new type of table that is designed to keep the full history of data changes, where row validity is managed by the system. This new table type is called a System-Versioned Temporal Table. In earlier SQL Server versions, user tables would enable you to hold only the most recent copy of the row, without being able to query the value before the UPDATE or DELETE operations. Using a Temporal Table, you will be able to query the recent state of the row as usual, in addition to the ability to query the full history of that row, which is fully managed by the SQL Server Engine, as you can’t define the rows validity period explicitly.
Read more »
When a new row is inserted or an existing one is updated in your database, the SQL Server Database Engine will keep that change in the buffer pool in the memory first, without applying each change directly to the database files for IO performance reasons. These data pages located in the buffer pool and not reflected yet to the database files are called Dirty Pages. The SQL Server Database Engine uses a special kind of processes to write these dirty pages to the data and log database files periodically. These processes are called Checkpoints. The Checkpoint creates a mark that is used by the SQL Server Database Engine to redo any transaction that is committed, written to the database transaction log file without reflecting the data changes to the data files due to an unexpected shutdown or crash. Also, this recovery point that is created by the Checkpoint will be used to roll back any data changes associated with uncommitted transaction, by reversing the operation written in the transaction log file. In this way the SQL Server Engine will guarantee the database consistency. The time that is taken by the SQL Server Database Engine to redo and undo the transactions is called the Recovery Time. All information about the Checkpoints will be written to the database boot page to identify till which point the database files are synchronized with the buffer pool when the system wakes up after crash or shutdown.
Read more »
SQL Server 2016 provides great enhancement capability features for troubleshooting purposes. Some of the important features are:
Read more »
SQL Server Management Studio a graphical interactive that allows you to interact with the databases hosted on your servers. SSMS provides you with the ability to write, edit, execute, analyze and monitor your SQL queries. It also helps database administrators answer important questions about the SQL query performance, such as why a query is slow or why an index is not used. The answer to these questions can be found simply by tracking the Query Execution Plan.
Read more »
PowerShell is Windows Shell that can be used to automate tasks in Windows, Exchange, Azure, SQL Server and more.
Read more »
SQL Server Trace Flags are special switches that are used to customize and control specific behaviors of the SQL Server Engine. Trace Flags can be defined in two forms; Session Trace Flags that are activated and visible at the current connection level only, and Global Trace Flags that are enabled and visible at the SQL Server Instance level and applied to all connecting sessions in that SQL Server. Global Trace Flags should be enabled globally in order to take effect, where some Trace Flags that can be either Global or Session Trace Flags can be enabled in the appropriate scope, and its effect will appear on the defined level.
Read more »
One of the exciting things to look out for in a release of SQL Server are the new built-in T-SQL functions that are usually introduced. Some of the reasons for the new functions may be because SQL Server is catching up to other competitors, attempting to stay ahead of the competition or simply responding to SQL Server feature-requests from the Microsoft Connect site. In this article, I take you through my top 5 T-SQL functions released in SQL Server 2016.
Read more »© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy