Study your Data

From SQL Guatemala, we wish you a happy new year 2018.  We hope this new year brings you joy and zero: production outages, corrupted databases, corrupted backups, full disks, and failed nodes hehehehe! Also we wish for you all your desired certifications, your new software licenses and tools you need, no dumb users with sa privileges, and of course... a salarial raise! Best wishes for all...

A very interesting feature was recently added to SQL Server Management Studio 17.4 (SSMS): The new SQL Vulnerability Assessment. What is it? SQL Vulnerability Assessment or VA, is a lightweight, easy to use tool to identify and help to remediate potential security vulnerabilities, these rules are based on Microsoft's best practices. This reports executes at database level. VA is included on SSMS 17.4 and it works for SQL Server 2012 and later. Using VA From the Management Studio,connect to the SQL Server database instance. Right click on the database to analyze and select Tasks > Vulnerability Assessment > Scan for vulnerabilities... Select the path to store the report and click OK. After a moment, the report is show. You can now see all the vulnerabilities encountered with a brief explanation and a suggested fix for each one: Failed rules Passed rules   Baseline You can also approve results to be marked as passed using the approve as

To check the available advanced options configured at instance level in SQL Server, you need to use the  sp_configure system stored procedure. The default behavior, for security and stability purposes, is to show only the "least" advanced parameters, so, if you execute the stored procedure, it will show something like this: EXEC sp_configure It doesn't show all the configuration options To display all of the available options, you have to run the "show advanced options" command: EXEC sp_configure 'show advanced option' , '1' ; CAUTION: Be extremely careful enabling this command, and remember to disable right after using it, because any user have access to the sp_configure command, and you will expose advanced configuration parameters to an untrusted user. After this, you have to execute a RECONFIGURE command for the changes to take effect. Note: RECONFIGURE and RECONFIGURE WITH OVERRIDE have the same effects for dis

I have recently obtained the certification Microsoft Certified Solutions Associate: SQL Server 2012/2014. I began with this path almost 2 years ago, back then i wrongly thought that the MCP will be enough for me, and then I decided to get out of my comfort zone and look forward to finish the certification to the next step, and here we are, after a long struggle and a lot of blood and tears, I made it :) Perhaps you are wondering, "hey this is not a a big deal, so why the post?" because it was, at least for me, so freaking difficult! Let me explain why: The old format had 3 required exams When i started my MCSA certification path, the SQL Server 2016 certification path was still too recent, also my skills on that area, so I decided to take the 2012/2014 instead, who has 3 required exams: Querying Microsoft SQL Server 2012/2014 Administering Microsoft SQL Server 2012/2014 Databases Implementing a Data Warehouse with Microsoft SQL Server 201S2/2014 And now for

I am sharing today a SQL Script to quickly check on an instance which databases are in mirrored and their status. select db.name, db.state_desc, dm.mirroring_role_desc, dm.mirroring_state_desc, dm.mirroring_safety_level_desc, dm.mirroring_partner_name, dm.mirroring_partner_instance from sys.databases db inner join sys.database_mirroring dm on db.database_id = dm.database_id where dm.mirroring_role_desc is not null order by db.name     I hope you find this script useful!

In the new Project deployment model for SQL Server Integration Services, a new SQL Server database is used to provide a central management and deployment of packages: SSIS catalog What it does? Is an special database called SSISDB where you store projects, packages related to the projects, environments (for example dev, test, QA and Prod) and variables related to each environment (for example a connection string). It also stores the execution history for further analysis. How to access it First of all, you need to install it since it is not added by default. If you don't have it installed   Open the SQL Server Management Studio, and then locate the Integration Services Catalogs folder, and then right click in the folder, and select the Create Catalog option.   After that, a new window will appear, just enter the password you want, and the catalog will be created. If you have it already installed   Just expand the folder and the SSISDB will be

One useful sentence when you need to add data to an existing table from an external source or database, and you are not sure if some of the data exists or not, is the MERGE statement. This statement can significantly reduce the code needed to perform validations and insert data from source to destination, or updating it if already exists, even you can choose to delete data instead.   The general form is: MERGE INTO <target_table> USING <table_source> ON <merge_search_condition> [ WHEN MATCHED [ AND <clause_search_condition> ] THEN <merge_matched> ] [ ...n ] [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ] THEN <merge_not_matched> ] [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ] THEN <merge_matched> ] [ ...n ]; Let us watch an example of use, using the AdventureWorks database, I have created a test table with fewe