Posts

Showing posts from November, 2017

How to check advanced options in SQL Server?

Image
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

Finally I am MCSA

Image
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

Query to determine mirrored database status

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!

SSIS Basics: Integration Services Catalogs

Image
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