SQL DBA Internals

SQL Server 2008 end of support

2018-12-21T14:02:00.000-06:00

2018 is about to end. In the case you didn't know or haven't heard, the extended support for SQL Server 2008 (and R2) reaches to an end in 2019.

This beloved version (lets be honest, is like windows XP for the database world) was launched on 8/6/2008, so that means that is a 10 years old technology, with the same time as the backberry and much other devices that almost no one uses this days, which were great, but trought be told, compared with Today's gadgets they are way behind although it marked a milestone to what we have now you would preffer something better for you, isn't it?

So what does the end of support means?

Basically it means, that Microsoft wouldn't offer any type of support for that version of SQL Server, which can be translated to:

No new Service packs will be released
No new cumulative updates will be developed

And what does that means to me? Why should I care?

If I depend of any type of regulation or my environment holds any type of certification (PCI-DSS, HIPPA, etc.) it means that I would be flagged as "non-compliant" because it requires that any software that I use is still with support of the company that provide it.

It means, that I won't be getting updates anymore, if any new vulnerability is found for this product, it is most likely that I won't be getting a patch to remediate it. Also, all the repositories will eventually be removed, so even if the package I need was developed and for some reason I didn't applied, and then I look for it, it is most likely that I won't be able to find it, like is now the case for SQL 2005.

So what options do I have?

Extended support: You can pay for extended support, this applies only if you have Software Assurance or Enterprise agreement and is only if you need to raise a ticket with Microsoft asking for support but not to resolve bug fixes and this extended support is Expensive, really really expensive.
Azure: Microsoft can offers the extended support for up to 3 years, if you move your SQL Server as a VM in Azure, with that you can still get support without paying the extended support fee like if you stay on-premises.

Upgrade: The best option is that you upgrade your environment, if you really need to stay on-premises, you can plan a migration in your own data center, however you can also plan to upgrade and take the new benefits of Azure in the different flavors it has to offer (Azure SQL, Azure Managed instance or VM with SQL Server)

Does it really worths the effort?

Absolutelly, the newer versions not only run faster but it also offers more features to secure your data, it also integrates monitoring tools and auto-tunning plus now you can run it in all different platforms not only with Windows.

So if you don't have your migration planned already, what are you waiting?

Tools to connect to a SQL database

2018-06-27T23:12:00.000-06:00

Today I'm getting back to basics, whether you are new or veteran with the database engine you need a tool to get into, write queries, do some monitoring, tuning, etc.

If you have been using SQL for at least 5 years or any version older than SQL Server 2016 you will mostly only know one tool, as it was included with the installation media, however there are other tools available that you can use with which you can achieve the same goal, and at the same time offer some other benefits. In this post I will be writing about the top 3 and that are developed by Microsoft that you can use, in another posts I'll evaluate some others but for now lets use focus on this 3.

SQL Server Data Tools (SSDT) - SQL Server Data Tools, was introduced as an add-on with Visual Studio 2012 meaning that it has been here for a while now, it offered a lot of features that you can only find with third party tools at an extra cost such as schema and data comparison between two databases, it also offered connectivity with a Team Foundation Services Server (TFS) among other features. In later versions you begin to be able to develop Reporting Services reports with SSDT, so it removed the need of installing the Software Development Kit within your database server or sharing some installation media with other teams for them to develop or update SSRS Reports. With the latest version you are able to connect to SQL Servers from 2005, Azure SQL, Azure SQL Data Warehouse and SQL Server 2017 on Linux. It doesn't requires any license to be installed. If you are more familiar with Visual Studio, it is easier for you to adapt to this one or even better, you can add this package to your installation and work from there without the need on having a separate tool to do the database part. You can see the official documentation and download it from here: https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017

SQL Operations Studio - SQL Operations Studio is a multi-platform tool you can use to connect to databases, if you are bored and tired of the limitation that SQL Server Management Studio offered or that there was no Data Tools for Linux, let me tell you that this is your tool, you can run Operations Studio on Windows, macOS and Linux. With this tool you can do almost all the administrative and development work you used to do with SSMS, there are still some items missing from SSMS, however most of the ones you used on a daily basis are here. It also includes a lot of reports you are used to see in SSMS with richer format. It doesn't requires any license to be purchased. It is a light weight file compared with the traditional management studio. You can download it for free here: https://docs.microsoft.com/en-us/sql/sql-operations-studio/download?view=sql-server-2017

SQL Server Management Studio (SSMS) - As I said at the beginning of this post, if you have been using SQL Server for at least 5 years, or if you have installed any SQL Server version older than SQL 2016, you might noticed that with SQL Server 2016 there was a big change with the installation media, and is that it didn't include the option of "Management tools" instead it was in a different section in the welcome screen of the setup and when you click on it, it route you to download the media, this because of a lot of different reasons (Performance, Security and starting SQL 2017 multi-platform). This tool continues to be the most utilized tool to manage and interact with databases whether you are running your database on-premises or cloud. The good thing of this change is that if you are not installing a SQL engine in your desktop you don't need the entire media that included the setup files for the SQL engine, instead you just download the latest version and there you have it, also in the past you could only download the express version for free which was very limited or download the media containing the Development version for SQL, that is no longer the case, with the current release format you will have all the features supported. It has improved a lot from the versions we used to know, as now you can develop your own monitoring reports with SSRS and import those into SSMS and have that visibility from there, for example if you have a ad-hoc query to monitor your SQL Servers you can convert it as a report and integrate it with your SSMS and you will no longer need to open a new query window and run it, instead you just run your report and presto!

You can download the latest version from here: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

Hope that this quick review help you get a better idea and start your desire to test different tools.

Thanks for reading!

WSFC Lost Quorum votes due to network problem

2018-06-05T16:24:00.000-06:00

Problem:

Early in the morning I received emails requesting for support as the applications were failing to connect to one of the production servers that are configured with Always On, I asked for an specific error and the customer sent me this:

---------------------------

Error while updating the backup server list: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

---------------------------

After checking with one of the IT managers he confirmed that one of the database servers was going through some networking issues, however that didn't explain why the SQL server didn't automatically failover to the other node that was up and running, as both servers are part of the same WSFC as required to configure Synchronous availability groups with automatic failover configured.

So after getting into the available replica the troubleshoot began.

The first thing I noticed was:

After also checking the availability group dashboard which also confirmed the error state I went to the Failover cluster manager and noticed the following errors:

Also noticed that the node that was under the issues was showing that the Failover cluster state was stopped. Obviously if the server was not able to be reached through the network the cluster wouldn't be able to show it as healthy or running. But because lf that and the fact that there was no third witness the entire cluster state was failed and due to that the availably group and Listener were down as well, so what was needed? getting the cluster up and running even if one node is missing, but how would you do that? Well you need to start the cluster without the quorum validation, with that you will have the WSFC running and in return the availability group and listener will be up as well.

But wait, I'm a DBA that means that my experience with managing clusters at the windows level is minimal. If that is the case you have 2 options call IT to get the assistance you need or do it yourself, In my case was the second, if you choose the same, in the following URL are the required steps
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/force-a-wsfc-cluster-to-start-without-a-quorum?view=sql-server-2017

In my particular case the steps with the windows interface didn't work but the PowerShell ones did.
This got my cluster running, however my Availability group still had as primary the failed server, so I still had to failover the group to the one working, I achieved that by forcing the failover with this command:

ALTER AVAILABILITY GROUP <DAG NAME> FORCE_FAILOVER_ALLOW_DATA_LOSS

I know, it is scary when you see "Allow data loss" however, I had Synchronous commit set, so worst case scenario, I would be loosing the transactions that weren't committed at the time the primary server went offline so in the end that wasn't even written in the Primary database and it was safe to roll them back if needed.

That brought my databases online and the applications could resume working.

After fixing all the network issues and noticing that you are able to connect to your secondary replica execute this command to restart the synchronization, make sure that you update it for each of your databases.
ALTER DATABASE database_name SET HADR RESUME

And bam, you are all set, your cluster and Database availability group is back to the state it was before the failure.

As a lesson learned, add a witness to a file server, so that you don't loose your Quorum and prevent this issue to happen.

Thanks for reading!

Script to review replications

2018-04-18T16:20:00.000-06:00

This script is from my library and I use it to review what is going on with my transactional replications, try to remove statements that didn't get purged for whatever reason. This is my last resource before getting the need to reinitialize or drop and recreate my publications.

use distribution
go

/*Query to get your articleID*/
select top 100 * from Distribution.dbo.MSarticles

/*Use this when you have the xac_seqno that is failing or your last that was replicated*/
select * from MSrepl_commands Where xact_seqno >= 0x0005AEF70000004A000900000000

/*use this to review your errors*/
select
*
From MSrepl_errors

/*use this to list the commands to be replicated*/
sp_browsereplcmds '0x0005AEF70000004A000900000000', '0x0005AEF70000004A000900000000'

/*Once that you have the commands to be deleted, add them inside the IN*/
DELETE from MSrepl_commands where command_id=1 and xact_seqno in (0x0005ADE2000001E00009)
and [command] like '%MSins%'

Offline installation of SQL Server 2017 on Linux

2018-02-28T21:18:00.000-06:00

SQL Server is now compatible with different Linux distributions, and there is a lot of excitement about it.

How ever one of the main questions when ever you say SQL Server 2017 is now supported in Linux is, Have you test it? how do you install it?

By looking in the web you can find a lot of post and videos related to it, however most of them are based with Azure VM's (one great post I found is from a good friend that you can find here) or there is also the assumption that you will have an internet connection from your server, but what if not? What if you are a financial institution that is PCI-DSS compliant and the direct connection to the web is blocked or simply your network administrator doesn't want to allow you access to the Microsoft repositories from the network your server is running or you created your VM and the connection is not shared with your host?

Well, let me tell you there is an option, you are not deemed to fight against the network engineers to enable you the access and here I'll show you the steps.

Pre-requesites:

A machine (VM, physical, etc) with Linux

Red Hat Enterprise Linux (7.3 or 7.4)
SUSE Linux Enterprise Server (v12 SP2)
Ubuntu (16.04)
Docker (1.8+)

The latest version of SQL Server 2017 downloaded you can get it here and copied onto your Linux machine.
A user to run the install (different than root)

Installation Steps

Connect to your Linux machine. You can use Putty or the SSH client of your preference
Locate the folder where you have the installer, in my case I put it on "/home/SQLInstall/
execute this command: Sudo yum localinstall <name of the installer> in my case the command looked like Sudo yum localinstall mssql-server-14.0.322.28-2.x86_64.rpm .After executing the command it will ask for the password of the user you are connected with to confirm the permissions and the execution command, then it will start the examination of the media and validate all the dependencies, that will look just like this:

4. If you look at the bottom line, it stops at the Question if the size of the package and the size matches, if the software is the one you would like to install etc, if everything is correct you will then need to Write the letter "y" to confirm.

5. After confirming and hitting enter, the process will start to deploy the files in the system, however you will need to pay attention to the following message:

It says "complete!" but there is a remark "Please run 'sudo.... to complete the setup...' " so go and execute the command, otherwise SQL Server won't be installed as it only exported the files but didn't actually installed the engine and you will see why in the next step.

6. After executing that command, it will ask you to choose the version you would like to install

So make sure to enter the right version, In my case I choose 2, as it won't support a production environment and is only for demo purposes.

7. After selecting the version, it will ask you to agree with the license terms and you will need to write "Yes" if you accept it or "No" if you don't.

8. Then it will ask you to configure the "sa" password.

9. After configuring the password the installation will succeed. However one of the problems you will face after getting it installed, is that you can't connect from outside of the server itself, so what you need to do is to enable the port through the firewall, and you can do that by running the following commands:

sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent

sudo firewall-cmd --reload

The first command opens the port 1433 and the second command restarts the firewall service (sort of speak)

10. After doing all of the above steps and rebooting the server, you should be able to connect to your SQL Server instance, Congratulations!

Hope this steps lets you get up and running and in the case you run into any issue, let me know and I'll be glad to help and learn with you.

Thanks for reading!

P.D.: If by any chance you don't know how to get Red Hat installed, I'm preparing another post with those steps.

I've been chosen to speak at SQL Saturday Guatemala

2018-01-19T18:35:00.000-06:00

I'm thrilled to announce that one of my proposals has been chosen to be presented in Guatemala's SQL Saturday that will be held on February 3rd at the Francisco Marroquin University (UFM).

If you will be in town and are an enthusiast of SQL Server, you should definitely plan to attend, did I mention is Free?

You can check the agenda and register here there are a lot of great speakers, some of them are Data Platform MVP's.

The session I'll be presenting is:

"Implementing Row Level Security (RLS)"

I'll be presenting what it is, what you need and how to implement it, as well as some scenarios where you can benefit with it.

So if you haven't register, what are you waiting?

See you there!

Configure Always on AG with SQL 2017

2017-12-04T07:57:00.002-06:00

One of the new features that we have with SQL 2017 is that you no longer need a Windows cluster to enable the AlwaysOn feature with SQL server (remember we discuss the requirements to configure that here those are valid for versions 2012 to 2016) , if you want to review what's new in SQL 2017 you can check it here.

In this post I'll be showing you how to configure an Always On availability group with SQL Server 2017, most of the steps are the same for the older versions and I'll be telling the differences on each of the steps so lets get started.
First I'll amuse you already have SQL Server installed in two servers as stand alone instances, that is a requirement for this post.

1. Enabling AlwaysOn: In order to do that, you need to go to SQL Server Configuration Manager, and on the SQL Server Services, hit properties over the MSSQLSERVER

2. In the properties window, look for "AlwaysOn High Availability", if you are running with an older version, the checkbox "Enable AlwaysOn Availability Groups" won't be availabile until you make the machine where SQL Server part of a cluster but just part of the windows cluster, the SQL Server instance remains as an Stand Alone type. Once that you have the checkbox enabled, check it, and click apply and Ok. Enabling this feature requires a Service restart so take that in account. Also, you need to do this in all the SQL instances that will be part of the Availability Group.

3. Go to SQL Management studio, connect to any of the SQL instances that will be part of the group, look for the "Always On High Availability" folder, expand it and you will see a folder with the name "Availability Groups" do a right click on it and select "New Availability Group Wizard…"

4. In the availability group wizard, configure the name you want for your availability group.

a. Cluster Type (New with SQL 2017): you have 2 options here (the official documentation shows 3), External or None, if you choose External, it means that you will joining this availability group to an external cluster, E.g. if you have a windows cluster on your primary datacenter that is on a different network or you want to add it to a Linux cluster.
b. Database health level detection (New with SQL 2017), this will enable a constant validation of the databases that are part of the availability group that if anything goes wrong with any of the databases it will trigger an automatic failover
c. Per Database DTC support (New with 2016 SP1) Allows the Distributed Transaction Coordinator through the availability group, feature that was not available in older versions of SQL (2012 and 2014)

If you don't have a cluster created and just like this scenario, choose NONE

5. Select the databases that will be part of the availability group. Requisites for a database to be considered:
a. Be on Full recovery model
b. Have a full backup

6. In here you will be choosing the replicas that you want to add to your group. Important features here:

Initial Role: You will set the role that each replica will have once that the group becomes available
Failover Mode: Manual or Automatic
Availability Mode: Synchronous commit or Asynchronous commit
Readable Secondary: No, Yes - Read Intent Only and Yes. If you choose No or Yes - Read Intent Only, you won't be available to query your replicas, the Read Intent is when you enable that feature that routes the read operations to your available replicas. With the Yes option you will be able to query the databases in your replica.
The endpoints tab will show you the URL and ports each replica is set to.
Backup preferences tab: in here you can configure if you want to take the backups from the primary or the secondary servers.
Listener tab: In this window it lets you set the parameters to configure the availability group listener, however my advice is to do it later, configure your group first and once that is done, configure it later.
Read-Only Routing: tab This lets you configure your read only routing for the read-intent setup, this allows you to load balance the queries so you have only the queries that will Insert, Delete or Update on the primary and all the Select queries routed to your secondary's so they don't consume resources the Primary will use (I'll explain it in another post)

Automatic seeding (Starting SQL 2016): With this type, SQL Server will do everything for you, it will use the default folders configured so make sure you have enough space available on it.
Full database and log backup: Same as the Automatic seeding, however in this case it lets you choose where do you want to generate the backups
Join Only: With this one, you do prepare everything in your replica, important notice, you do a restore with norecovery in your replica and apply at least one log backup. This option is useful with really large databases.
Skip initial data synchronization: Same as before, however in this particular one, you need to restore everything just at the time before initializing the synchronization otherwise it will tell you that there are items pending to be restored.
Failover Mode: Manual or Automatic
Availability Mode: Synchronous commit or Asynchronous commit
Readable Secondary: No, Yes - Read Intent Only and Yes. If you choose No or Yes - Read Intent Only, you won't be available to query your replicas, the Read Intent is when you enable that feature that routes the read operations to your available replicas. With the Yes option you will be able to query the databases in your replica.
The endpoints tab will show you the URL and ports each replica is set to.
Backup preferences tab: in here you can configure if you want to take the backups from the primary or the secondary servers.
Listener tab: In this window it lets you set the parameters to configure the availability group listener, however my advice is to do it later, configure your group first and once that is done, configure it later.
Read-Only Routing: tab This lets you configure your read only routing for the read-intent setup, this allows you to load balance the queries so you have only the queries that will Insert, Delete or Update on the primary and all the Select queries routed to your secondary's so they don't consume resources the Primary will use (I'll explain it in another post)

8. Verify that the validation runs successfully, this warning is because I didn't setup the listener in step 6, but that is fine, every time that I've tried to configure it from the very first page it fails, so my advice is that you configure it later (part of this post).

9. After hitting finish and if none of the steps have failed you will see the screen just like this.

Setup of the listener

The listener will be your single point of entry for your availability group, no matter which server is primary and which secondary, you will only need to configure your applications to use the Listener fqn or the ip and that will give you that High Availability you are looking with Always On.
1. Go to the "Always On High Availability" folder, expand your availability group and look for the "Availability group listener" and right click on it.
1. In the Configuration screen, Configure the domain name you want your listener to respond onto, this will be like another computer in the domain, so be sure that you have permissions to create objects in the active directory, if you don't ask your domain admin to provision that name and grant you permissions over it so you can enable it.
You are all set, you can start configuring your applications to the listener and taking the advantages always On provides.

Create users in Azure AD

2017-11-27T08:04:00.001-06:00

In this post I'll show you how to create new users in your Azure Active Directory so you can use them as service accounts or to grant other users to login to your Servers or services as well as how to enable them so you can use it, I didn't know that and the documentation was not as explicit as you would love, however I consolidate both tasks here.

Go to Azure Active Directory

In the "Name" field type in the give name of the user
Type the fully qualified name name@domainname.onmicrosoft.com
Configure the Profile details
It won't let you change the details so you can leave it that way
Configure the Groups it will have access to
Select the role it will have in the active directory the options are (Regular user, Global Admin, Limited Admin)
Password: Make sure to copy the password as you are not able to retrieve it later this is auto generated and you are not able to change it, and the user is configure to change it at the first logon.
Before clicking create, your window will have to look like this

Alright, you have your account created, however it wont work until you synchronize the password, or in other words, until you enable your user by logging in to the active directory, however, how can you do that? This is something hard to find out there in the web, even when the steps are simple, reason why I'm including it with this post.

1. Navigate to this URL: http://myapps.microsoft.com/

2. When asked for the logon user, use the one you just created

3. Type in the password you copy from the creation window, then it will route you to this other page where it asks you to configure a new password, type in the new password.

And you are done, once that you have successfully updated the password it will route you to this screen meaning that you successfully create and activate the account. Now you are ready to use it to join your machines to the domain, configure it as service accounts, etc.

Hope this and the post from last week lets you get started and working with Azure.

Thanks for reading!

Configure an Azure AD Domain Services

2017-11-20T08:00:00.000-06:00

Problem: I was working on getting a demo on how to setup Always On with SQL Server 2017 without the need of creating a windows cluster, I had my two VM's in Azure and everything was working fine until the creation of the availability group, it failed and it was because the account under which the server was running was the default "NT Service" which is not allowed (not to mention is a concerning security issue) so instead I decided to join both VMs to a domain, as mentioned before, this were Azure VM's so the creation of a traditional domain as you know it on premises was a hard task to do (not to mention the cost for each of the servers) however by looking through I came across a service, Azure AD Domain Services that provides a seamless experience as a traditional domain controller, but as a service (PaaS) which is great because it does almost everything for you so here are the steps as to how to set that up.

All the steps are in this URL: https://docs.microsoft.com/en-us/azure/active-directory-domain-services/active-directory-ds-getting-started follow it thoroughly however, here are some annotations from my own experience:

Task 1: "configure basic settings"

DNS domain name: choose the name of your preference with the add of ".onmicrosoft.com" unless you have something else configured it won't check your name as valid.

Resource group: You can create a new resource group or use an existing one, it won't break anything that you have in your group if you choose "Use Existing"

Task 2: "Configure network Settings"

In here you have the option to either Create a new one or use an existing one, my recommendation, create a new one, in my first attempt I choose an existing one and spent 2 days cleaning everything up as in the middle of the creation it fail.

Task 3: "Configure administrative group"

In this task you create the equivalent of "Domain Administrators" group, so make sure that you add the members you want to be domain admins in your Azure AD (AAD).

After finishing this task, it will start the process on provisioning the resources, once this is done, your domain will be almost ready, before that you still need to configure your DNS. How to know when is done? When you go into the overview of the domain you are able to see the section "Update DNS Server settings for your virtual network"

Task 4: "Update DNS Server settings for your virtual network"

Make sure that you choose "custom" and copy both IPs onto the boxes as shown in the steps, this step is so that you can configure you DNS servers with the ip of the services (that will serve as Domain controller servers) Azure provided for you, take this as a verification step of the process.

With that you are done, you have successfully configured your Active directory as a service and you are good to go and work like if you were in an on premises domain, you will only need to configure your accounts to the services or if you didn't do it in the task #3, you can create an account to manage your domain (Join the machines, create other accounts to configure as Service Accounts, ETC).

In a following post I'll show you how to create and enable users in your Azure AD.

Thanks for reading!

Contained Databases setup

2017-11-08T09:18:00.000-06:00

Last week I wrote about what is a contained database, the benefits and challenges, today as promised I'll show you how to setup one, how to configure a user and how to connect to it so let's get started.

Step 1

After logging in to your SQL Instance, go to the instance properties.

Step 2

In the properties page, select advanced and in the first section choose "Enable Contained Databases = True" With this you will be enabling the feature at the instance level so that you can setup your databases this type.

Step 3

Go to the database that you want to convert to a contained database and click properties, then go to options and in the Containment Type choose Partial.

With the above steps you have now your first contained database, however at this point there is nothing different as if you try to connect to the database with the accounts or users that are already granted at the instance level you will see no difference, for you to see the difference you will need to configure one use or account at the database level as follow.

Step 4

Go to the security folder inside the contained database, go to the Users folder and choose "Create new User", it will prompt you the following window where you will create your user, remember to configure the privileges that you will allow it to have, and always remember the best practice is to follow the least privileged principle.

Ok so you have now your user created, let's try to connect

Step 5.1 connecting to SQL Server as usual

Wait, what happened? We created the user, now it is not able to connect?

Step 5.2 To take advantage of the containment, you will need to select directly the database that you want to connect, you can do this by clicking on the "Options Button" at the bottom right corner of the screen, the logon window will change, so then you will need to go to the Connection Properties tab and in the "Connect to Database" section, you will need to write the Database name, it shows the option to lookup, however if you are not logged in it will tell you that you need to login first and you will end up in a circle where you can't authenticate, so better write down the database name so you can enter it here.

Step 6 Welcome you are now connected directly to your database. Can you see the difference? With an account that is granted at the server level you can see all the databases in the SQL Instance, however when you are connected to your contained database, it shows you only the database that you have permissions, creating an isolation of the environment.

Tests:

Now lest do some tests, imaging that you are sharing the account with someone from the production control team and that person has a little bit of knowledge in SQL, and tries to discover it there are more databases in the instance, the query will only return 3, Master, tempdb and the contained database it has permissions, why this 3? Well, master has the metadata that it needs to work with, if you create temp objects you will need to rely on the tempdb, but that's it.

For the second test, I created another contained database and tried to run queries against, however it doesn't lets me do it, why? It was not mentioned in last week's post but other of the limitations is that you can't run queries across different databases, unless you have the guest account enabled, which is not this case and that strongly not recommend.

Summary: Contained databases is a feature that provides isolation between environments if you are running databases that requires this kind of separation, also by using this feature you don't have to worry about migrating users, roles, etc. when you move your database from one server to another, either because you are migrating, upgrading or recovering your server, also this portability eases the configuration and security administration when you have Always On Availability groups configured for you HA/DR environments.

Contained Databases

2017-11-01T22:50:00.000-06:00

Today's post is in regards a feature that was released with SQL Server 2012, it hasn't gotten the attention or used that it deserves even when it helps solving a lot of problems you face when upgrading or migrating the database servers and is Contained databases.

What is a Contained database?

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. A contained database basically includes all database settings and the metadata within itself thereby resulting in no configuration dependencies on the instance of the SQL Server Database Engine where the database is actually installed. Users will be able to connect to a contained database without authenticating a login at the Database Engine level. This feature really helps to isolate the database from the Database Engine thereby making it possible to easily move the database from one instance of SQL Server to another, this is incredibly useful when you have HA environments. The Contained databases feature is available at the instance level and is not enabled by default.

What type are available?

Contained database feature provides two containment modes:

None – By default each database has its mode set as NONE. This means there is no contained database feature being used.

Partial – With partially contained databases, we can define boundaries between databases and the server, so the metadata will exist inside the databases. It makes SQL Server databases more portable and less dependent on underlying hosts.

Advantages of contained databases :-

1. User authentication can be done at database level, so you only need to be sure to grant the users permissions in your database.

2. Have less dependency on instance than conventional databases. Objects & features of each database can be managed by them self, reduce workload of system database & SQL instance

3. Easier & Faster to migrate databases from one server to another. Errors related to missing users and orphan users are no longer an issue.

4. Contained database users can be Windows and SQL Server authentication users.

5. Contained database user can access only contained database objects. They cannot access system databases and cannot access server objects.

6. This is the preferred mode to be used with HADR (Always On)

7. Maintaining database settings in the database, instead of in the master database increase security & flexibility. Each database owner have more control over their database, without giving the database owner sysadmin permissions.

8. To close collation issues in contained database. New feature catalog collation introduced with contained database. Now database collation works for user objects & catalog collation works for system objects in database. Catalog collation will be same for all contained databases on all SQL instance, also this collation cannot be changed.

Disadvantages and Limitations of contained databases :-

1. A database owner has more control on contained database, User can create contained database users without the permission of a DBA that can lead to security issues & data theft threat

2. Contained databases cannot use replication, change data capture, change tracking, numbered procedures, schema-bound objects that depend on built-in functions with collation changes

3. Before changing containment settings at database level from NONE to PARTIAL , contained databases feature needs to be enabled at instance level, so make sure to enable it before releasing your server and database to production.

4. To connect to a contained database, you need to specify the database name in the default database option tab.

5. Temporary stored procedures are currently permitted. But can be removed from future versions of contained database.

6. Contained database user can access other databases on the Database Engine, if the other databases have enabled the guest account, which can be a security issue.

Changes cross versions:

This feature hasn't gotten any change since it got released with SQL 2012, mostly as the new versions have been focused in improving the performance, security and integrating Artificial Intelligence and Machine learning features, however, personally if you have environments where you need High Availability and every time that you failover your groups you need to resynch your users and principals, this might work for you, however, you will need to remove you database from your availability group if you want to enable this in your database, but we will be working with it in next week's post.

Thanks for reading!

Resources:

http://www.sqlservercentral.com/blogs/mssqlfun/2014/06/23/sql-server-2012-contained-database/

https://www.sqlshack.com/contained-databases-in-sql-server/

https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-databases

Configuring a Subscription to an Azure SQL

2017-10-25T13:17:00.000-06:00

This is my first post where I utilize an hybrid cloud, item that I personally find very interesting and useful so expect more posts like this to come.

Microsoft Azure released at the beginning of this week the availability to add an Azure SQL database as a subscriber for a transactional replication (https://t.co/WlZSmOQhhT), so I was preparing the post to show how to create a subscription to a transactional publication so instead of adding the subscription of an on-premises server I set it up to the Azure version.

The steps to configure a Subscriber are the following:

1. Start the subscription wizard: You will go and look for the "Replication" Folder, expand the folder and go to "Local Subscriptions" in there right click and choose "New Subscriptions…"

2. Choose your publication: the first step is connecting to a SQL Server that is configured as a publisher and the published databases lastly select the publication you want to subscribe, in this case, I'm connecting to the publication created in a previous post

3. Choose the distribution agent location You have two options here, creating Push or Pull subscriptions, what this means? With Push subscriptions, you will rely on the Publisher to send (Push) the replicated data to the subscribers, on the other side, with the Pull subscriptions, the subscribers will go an connect to the Distribution database and download the changes, this second one is particularly recommended with environments that the minimum increase of workload affects dramatically and if you plan to have multiple subscribers you can reduce that overhead in the publisher by configuring Pull subscriptions, however with the Azure SQL release it is recommended (for now) to add it as Push, mainly the reason why is because you don't have (yet) SQL Server agent to configure the agent jobs to run, so you need to use the one from the publisher.

4. Select your subscribers In this step you will be adding the subscribers to the publisher you have chosen, initially you will only see listed the current server where you are connected, however you can add more, in order to do so, you need to click on "Add Subscriber" and select "Add SQL Server Subscriber…"

5. Connect to your Subscriber server After you click in the Add Subscriber button, you will need to connect to the server or database that you will be adding, in this particular case, I'm connecting to my Azure SQL Database as you can see by the URL.

Once that you have connected successfully, you will see that server with the box at the left checked, telling that you will be adding that as a subscriber as well as the database that will receive the replicated data.

6. Configure the agents security: You will then need to configure the accounts that you will be using to connect to the distribution agent, in this particular case as I don't have an Active directory configured I choose to connect using a local account from my VM, important note, remember you will need to choose

7. Select the Synchronization Schedule once that you have chosen the subscriber database, you configure the Distribution agent security, you need to choose the Schedule, you can select "Run continuously" or schedule to be run at a given times, this all depends on how fast you need the updates to be replicated, when using transactional replication I recommend use continuously as you would want each transaction to be replicated immediately to the subscribers.

8. Initialize subscriptions In this step you will specify if your subscription will be initialized after finalizing the wizard steps or if you want to do it later. My recommendation do it Immediately, there are just few scenarios where you need to do that later.

9. Confirm the actions Confirm if you want to execute the commands immediately or you want to generate the scripts so you can review it and run them at a later time.

And you are all set, you just need to validate with the Replication monitor the progress of your initialization process as well as if it is synchronizing the data:

You can see that I had some errors in there, but they were from another test cases, just wondered that Triggers are not yet available in Azure SQL and I included some tables that had triggers before, but if you don't have that set, it would be straight forward.

The steps above applies the same as if you are setting a subscription to an On-Premises server or a SQL VM created in azure.

Thanks for reading

Configuring a Transactional replication

2017-10-25T06:36:00.000-06:00

Hello,

This post is intended to show you all the steps you will need to follow when you setup a transactional replication, in fact the first 5 steps are one time only, assuming this is the first time you will be creating a publication in your database server, if you already have one set (Except for Log-Shipping, Mirroring or Always On) you can skip the first 5 steps.

Step 1: Configuring the Distribution database

As discussed previously (Replication Basics) SQL Server will use this database to keep a track and distribute all the changes that happen in your published objects (tables) to your subscribers, so the first steps are in order to create it. So in order to initialize that wizard, you connect to you SQL Server, and look for the "Replication" folder, over there you do a right click and select the first option "Configure Distribution"

Step 2 Selecting the type of distributor that you will have: You can select the type of distributor that you would like for your database server to use, the default selection is that each server serves as its own distributor, however, there are highly transactional environments where the little overhead can severely impact the performance and for that you will choose to use the distribution services from another server. For this case, we will use the first option (own distributor).

Step 3: Configure the SQL Server agent: By default the SQL Server agent is configure to start manually, if you haven't change that option you will get a message like this, If you already configure it to start automatically you won't see this screen.

Step 4: Configure the snapshot folder You will need to choose a location where SQL Server will be creating your Publication snapshots, remember this are important as from this you will be initializing your subscriptions. This folder can be later reconfigure in the case that you need to do so to improve the performance, but that will be review in another post.

Step 5 choosing the name for the distribution database and the location of the files: In this step you need to configure the name that you would like to give to the database that the distribution agent will be using, the default name is "distribution" however you can choose the name that you like, for instance you can use Customer_distribution to specify the name, this is useful if you will be using a separate server as your distributor. After this screen you will get a summary of the setup and will let you finish the wizard that will create and configure you Distribution database and agent. With this you are all set to start publishing you databases.

Verification: if you like to verify if your database got created properly you can go to the "System Databases" folder and look for the database name you choose in step 5 to verify.

Creating a publication

Every time that you need to create a Transactional replication publication you will need to follow this steps:

1 Choose new publication Go to the replication Folder (same folder you used to configure the distribution database), expand the Tree and over the "Local Publications" hit right click and select "New publication", that will prompt the "New publication Wizard"

2 Choose the database to be published Once that you are on the "New publication Wizard", select the database that you want to publish

3 Select the Publication type As discussed before, there are 4 different types of publications that you can choose from, in this post (Replication Basics) I explain a little bit more each case of use, and in that screen you can see a brief explanation of each one for you to choose, for this particular case we will select "Transactional Publication"

4 Choose your articles The articles are the database objects that you want to get replicated, you can select from tables, views or stored procedures, the only condition is that the table must have a Primary key.

5 Filter table rows You can choose to filter the data that will be replicated, in here you can write a query that will select the conditions the data needs to meet to be replicated, e.g. filtered by a date range, a status, age, etc. You can also leave this blank and will replicate the entire table.

6 Snapshot Agent Each publication has its own snapshot agent, this agent will be the one in charge of generating the schema to be applied on each of your subscriptions, you can choose to generate the snapshot immediately at the end of the wizard or schedule it to run at a given time. You can also choose both, in the case that you would like to reinitialize your subscriptions at a given day of the week although for transactional replications that is not recommended.

7 Agents setup you will need to configure the security for the Snapshot and Log Reader agent. When clicking on "Security Settings" it will drive you to another window where it will ask you to configure the account you will like to use to connect to the distribution agent to a) Generate the snapshot of the data and queue it and b) Read the changes that are made to the published objects.

8 Choose the next action In this step it lets you choose which action you would like to do, if you want to create the publication at that exact moment, or if you like to get the script generated to create and configure and you will be executing it later.

9 Confirmation In this step it shows you the summary of what you have chosen, but it is also an important step, where you will be giving the name to the publication so you can identify later what is it about, this is incredibly useful when you have several replications configure in the same server, or when you have 2 or more publication from the same database that each serves for different purposes (Reports, DR, etc.)

Once you have complete all of this steps and after clicking finish you don't see any error, you are all set, your publication is created and you are ready to start creating the subscriptions.

What's new in SQL 2017

2017-10-11T14:52:00.001-06:00

SQL Server 2017 became General Availability (GA) on 10/2, I've been sharing through the different social media channels the different features and enhancements included in this new version, however I want to consolidate in this posts most of those that you will be seeing in most of the production scenarios with an explanation of each one.

Resumable online index rebuild resumes an online index rebuild operation from where it stopped after a failure (such as a failover to a replica or insufficient disk space), or pauses and later resumes an online index rebuild operation. See ALTER INDEX and Guidelines for online index operations. This is a great improvement specially with index operations where you run out of space or have to pause it due to performance degradation, you can later resume where you left it instead of having to cancel, wait the rollback and then start from the beginning.
A new generation of query processing improvements that will adapt optimization strategies to your application workload’s runtime conditions. For this first version of the adaptive query processing feature family, we have three new improvements: batch mode adaptive joins, batch mode memory grant feedback, and interleaved execution for multi-statement table valued functions. See Adaptive query processing in SQL databases.
Automatic tuning is a database feature that provides insight into potential query performance problems, recommend solutions, and automatically fix identified problems. Automatic tuning in SQL Server, notifies you whenever a potential performance issue is detected, and lets you apply corrective actions, or lets the Database Engine automatically fix performance problems. This feature in particular is one of the few Azure first, you can see this included in the Azure SQL database since at least V12 (current), for more reference See Automatic tuning.
sys.dm_os_sys_info has three new columns: socket_count, cores_per_socket, numa_node_count. This in particular helps you check if your current setup exceeds NUMA boundaries, in the case you exceed it, it will lead to an overcommitted host and in the end will cause you performance issues.
A new column modified_extent_page_count, is introduced in sys.dm_db_file_space_usage to track differential changes in each database file of the database. The new column modified_extent_page_count allows you to build smart backup solution, which performs differential backup if percentage changed pages in the database is below a threshold (say 70-80%) else perform full database backup.
SELECT INTO … ON FileGroup - SELECT INTO now supports loading a table into a filegroup other than a default filegroup of the user using the ON keyword support added in SELECT INTO TSQL syntax. This feature in particular will help when you need to run the first step of an archive solution or if you create a "troubleshoot filegroup" so you move the data directly to it instead of you PRIMARY filegroup where you might be troubleshooting a performance issue or anything else and you want to isolate the problem. See SELECT INTO.
A new DMF sys.dm_db_log_info is introduced to expose the VLF information similar to DBCC LOGINFO to monitor, alert, and avert potential transaction log issues caused due to number of VLFs, VLF size or shrink file issues experienced by customers. This dmv helps you to analyze the current health of your transaction log file that can become fragmented due to either an incorrect design or that the current management of your log is not the adequate. Pro Tip: If you have a large number of VLF's that could be one of the reasons why your replications are failing or some other CRUD operations are performing poorly, so go and check this number.
Cluster-less Availability Groups support added. Remember this post (Introduction to SQL Server Always On) where I explained the Key components of Always on, with SQL 2017 Windows Failover Clustering (WSFC) is no longer needed so you can enable the Always On feature even if the SQL Server is not part of a Windows cluster.
Linux. Did I mention SQL 2017 can run in Linux? Well, this new version is fully supported either in Windows or Linux

The items described above are related with the most frequently used with the database engine but there are more, also in this post I'm not reviewing features such as SSIS, SSAS, SSRS or Machine Learning and in this last one Microsoft made a lot of improvements with it.

There are templates already ready in Azure for you to go an create VM's with it or if you like to create containers, there is one in Docker already with Linux (Ubuntu 16.04)and SQL 2017 (https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker)

For a complete list of the new features you can see this sites:

https://docs.microsoft.com/en-us/sql/database-engine/whats-new-in-sql-server-2017

https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2017

https://blogs.technet.microsoft.com/dataplatforminsider/2017/10/02/sql-server-2017-on-windows-linux-and-docker-is-now-generally-available/

Introduction to SQL Server Always On

2017-10-06T12:07:00.000-06:00

Today I'll write with one of the features I like the most and is Always On. There will be two post of this subject, this first one is an overview of the changes it has gone through, benefits and the scenarios where you can get it implemented, the second will detail the steps on how you can get it implemented.

So I'll try to answer this questions What is Always On? When was it Released? And How can it be used?

What is Always On?

Always On or Always On availability groups (Microsoft) is an integrated high availability (HA) and disaster recovery (DR) solution that provides redundancy within a datacenter and across datacenters to help enable fast application failover during planned and unplanned downtime. Specifically, Always On delivers failover cluster instances, multiple secondary, active secondary, and connection director capabilities (multi-subnet and read-only intent).

In English, this means, Always On provides a highly available and Disaster recovery solution for you database environment. As mentioned before is the next evolution of database mirroring. Database mirroring is relatively easy to set up, and it provides an almost instant failover when it’s used in High Safety mode. However, database mirroring has several important limitations. First, database mirroring is limited to a single failover partner. In addition, database mirroring is capable of failing over only a single database at a time. Database mirroring must be implemented either synchronously or asynchronously you have to choose one or the other. Finally, the databases on the mirroring server are in a state of recovery, so they can’t be directly accessed. To use the data, you must take point-in-time snapshots of the

databases.

This function was introduced with SQL Server 2012 and has received a lot of transformations with the different releases of SQL Server

This are some of the enhancements:

Secondary replicas With 2012 you could only have 4, with 2014 it was increased to 8
You were not allowed to create a replica in Azure, with 2014 that option was introduced
In 2012 and 2014 it was limited to the Enterprise version, in 2016 SP1 you can use "Basic Availability groups since the standard version"
In the previous versions you were limited to servers that belong to the same domain, with 2016 you can combine groups form different domains.
In 2016 Databases with Transparent Data Encryption (TDE) are now supported
If you were using Grouped Managed Service accounts (gMSA) you were not able to configure the availability groups, it is now supported with 2016
In 2016 the Direct Seeding got introduced. Direct seeding takes all the initialization steps for you, it does a full backup and synchronizes the database in the background for you.
In 2014 the read intent was introduced, meaning that all of your read operations will be routed to your replicas so that you have the primary server to run I/U/D (Insert, Update, Delete), In 2016 the engine automatically load balance this across your replicas.

How can it be used?

Always On needs some components to run:

Two sets of IPs: You will need one IP for the Windows failover cluster and one for your Listener.
Windows Failover cluster: You need to configure (create) a windows failover cluster (WSFC) so that you can enable the Always on Functionality in the SQL Server service, why? Because the failover cluster validates if the servers that will be part of the availability groups are running without problems.
Availability Groups: After you create your cluster and install SQL Server as Stand Alone instances, you will have to configure an availability group and add both SQL Server instances, this is where you Always On starts to takes life.
Listener: This will be you single point of entry to you Availability group (AOG) it could be either by the domain name or the IP you will be assigning to it in the setup section, this will always route you to your primary server, unless you configure the Read-Intent routing and it will route your read operations to your replicas.

Think of the an Always On environment as an "Share almost nothing" environment" why? You are only sharing the failover clustering part, other than that you will have independent hardware for each of your SQL Servers. Also when you setup your environment with this type of setup, it reduces the changes (or steps) that you need to take when doing a planned DR because everything is already setup (At least the database part) and you only need to worry if the synchronization was up to date.

Introduction to SQL Server replication

2017-10-03T18:50:00.000-06:00

This time I'll talk about some basis with SQL Server replication, whether you need it for High availability (HA), Disaster Recovery (DR), load balance your applications or any other reasons, you have ever considered one of the different types of replication available with SQL Server, some of the components that come in play when you will it setup as well as a brief description as to how each one works and the benefits. So let's get started:

Requirements:

Distribution DB: This is a system database you need to setup before configuring three types of replication (Transactional and Merge). This database comes into play when you need the changes to be distributed across the different subscribers you have to your publications. This is the database used by the Distribution agents to distribute the pending transactions you can also query this database to troubleshoot any errors shown in the replication monitor.

Publisher: This is the database server supporting your database publications, this applies the same as if you think of a book, you can find from a publisher different books (Publications).

Publications: This are the published databases for the replications in there you configure the articles (Tables) that will be part of the publication.

Subscriber: This are the servers that will receive the data from the publisher, the subscriber can be the same server as the publisher or can be different.

Snapshot: This is a copy sort of speak of the publication, it is used to initialize the subscribers or in the case that you setup that type of replication (Snapshot) it synchrony you set it up.

Replication Types

Snapshot: This type of replication is usually used when you need to move some data for reporting purposes or in the case that you will only read that data to run some extensive analysis and that processing will impact the transactional server.

Transactional Replication: This replication ships (depending on your settings) all the transactions from the publisher to the subscriber, this type of replication is helpful when you have reports that needs to be done online and you can't add more stress to your current production database, this is also useful in scenarios of HA or DR because it ships the data fast to the subscribers. This replication depends of the Distributor database to send replicate the data.

Peer-to-Peer replication: This publication enables multi-master replication. The publisher streams transactions to all the peers in the topology. All peer nodes can read and write changes and the changes are propagated to all the nodes in the topology. With this type each of the servers work as publishers and subscribers and you need to configure a Distribution database on each of the servers. This replication depends of the Distributor database to send replicate the data.

Merge replication: This replication "merges" the changes made at the publisher and the subscribers only after they have been initialized with a snapshot, you need to setup a merge schedule so that they can combine the changes from one site to another. This replication depends of the Distributor database to send replicate the data.

Log Shipping: This type of replication is one of the oldest but most trusted methods for Disaster recovery, what this replication does is it backups the transaction log of your database, copies and restore the file in your subscribers, all this based on the schedule that you setup, the more frequent, the closer to the failure you can restore. The good thing about this, is that it also helps you to keep your log files to a managed size so they don't grow indefinitely as you are constantly running this backups. You won't find this type of replication inside the replication folder in the management tree, instead you will find it on the "Properties" of each of the databases.

Mirroring: Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record (Microsoft). This type of replication is extremely useful when you seek scenarios of HA or DR, because the fail-over time is considerably fast if you do it manually or you can set up to do it automatically. I like to say, this is the preview version of what Always On does now, that is one of the reasons why you will see Microsoft's comment that this feature will be deprecated.

Always On: This is the newest feature in terms of replication that was added to SQL Server, and thus the one that has received a lot of enhancements with each of the versions. It was introduced with SQL Server 2012 and received a lot of improvements with each version even the name has changed, originally it was "AlwaysOn" and in SQL 2016 changed to "Always On availability groups". This takes the functionality described with the mirroring replication and the benefits of the Log Shipping feature where you can have your replicas with Read-Only. An specific post for Always On will be released were we will review all the features as well as how to get it setup. This type is frequently used to support both scenarios HA and DR.

My wish is that you can give a better idea as to which replication will work better for each of the needs you have so you can get the better of what the SQL engine can provide to you.

Thanks for reading

SQL Server database basics

2017-09-16T11:27:00.000-06:00

So today I'll talk about some of the basic items for SQL server that helps to understand the reason of some performance recommendations. This first post will list and give an explanation of each of the components and later on will explain in detail how it works and what are the best practice to each one.

System databases

master - This database is the brain of SQL Server, in here you have the metadata for each object that is created in the SQL Server instance, that includes Logins, SQL Users, Databases, tables, startup parameters, etc.
model - This database has all the default templates for SQL Server, when the SQL instance first starts, it takes the all the setup for the databases from here, for example, every time that you restart your SQL Server instance, the tempdb is created base on a template stored in here.
msdb - This database is used to store all the information related with the SQL Server jobs, every time that a job is created, an entry is inserted here, you can also find the execution log for all those jobs in this database. The maintenance plans are also stored here.
distribution - This database is created and utilized every time that you setup a replication, except in the cases when you use Mirroring or Always On Availability groups, however if you use Transactional or Merge you will use this database to distribute the commands to your subscribers.
ssisdb - This database was introduced in SQL Server 2012, you won't find this database under the "system databases" folder as it needs to be created on demand. This database was introduced in order to provide portability to the SSIS catalog and improve the security, in the past, in a Disaster recovery or High availability solutions, you had to create your packages on each of your sites, whether on the SSIS instance or on disk, with this you can add the database to a mirroring or Always On solution and all your packages will be replicated.

User Databases

The user databases regularly follow this structure:

Data file (mdf) - This is the file where all the data is stored, this is your key file, if this gets corrupted, lost, or whatever, you will be losing all of your data if you don't have a backup, so make sure where this is stored and be sure that you backup your database regularly.
Log file (ldf) - This is the file that supports and stores all the transaction made in the database, this file plays a key role when: a. You setup your recovery model as Full, b. You replicate the database. One common mistake that is made is not to configure t-log backups, if you do so, you gain several benefits,

You can manage the size of your log file,
Prevent that your file gets highly fragmented (vlf's)
You can do a restore in time

Filegroups - By default the databases get created with a Primary filegroup, what does this filegroup? Think of this as an index at the beginning of a book, where you list all the Chapters. The Filegroup plays a key role when your database is becoming larger in time, because you can create an Archive filegroup, assign that filegroup to a different physical file, and move that ancient data into the new group, so that you Primary group remains supporting your daily operations. There are also more benefits you can gain when you setup different filegroups, but that will be discussed in a different post.
Recovery Model - You can have different recovery models set to a database, by default when you create a new database, the template from the model sets as full, however you have the following options:

FULL - This means that it will store all the data in the mdf file, and will write all the transactions in the ldf so that when you restore your database from a backup, it applies rolls forward or back the transactions based on their state in the Log to remain the integrity of the data. This also allows you to do in time restores.
Simple - This model is usually used when you only care of the data that is committed in the database, also some use it as a high performance model, because that millisecond that takes to be written in the log, can create an impact with the highly transaction applications that requires a millisecond response time, or in scenarios where you setup a Dev or QA environment and you don't need to restore in time.
Bulk-logged - With this model there are certain operations that won't be fully logged in the transaction log, thus this won't make it grow, operations such as BULK INSERT, CREATE INDEX, SELECT INTO.

Backups

Full backup - I would say this is the most frequently used type, however most of the times this is missed of failed to be reviewed and leads to loss of data. With this type, you are backing all the data that is stored in your database, at a given time.
Transaction Log backup - As discussed before, this type of backups helps to prevent the transaction log to grow indefinitely and also will allow you to restore in time.
Differential backup - This backup will take all the changes made to the database since the last full backup.

SQL Server Replication snapshot initialization taking long time to be applied

2017-08-25T13:00:00.002-06:00

Hello all,
In today's post I want to share a tip that helped me troubleshoot an issue I was facing and couldn't find much documentation about it. To my friends that read and don't have much knowledge in databases, this entry will look like an estrange language but I promise I'm working in some post to walk you through the database world and understated some of the terms used here.

Issue: Had to reinitialize a subscription from a transactional replication. When doing the initialization with the snapshot, the process was taking a huge amount of time, about 13 hours and it has only applied the snapshot for 12 (out of 24) tables, based on that speed the entire process would have taken about 26-30 hours to complete.

The snapshot was generated in 12 minutes and it was generated taking advantage of parallelism, however I noticed that when the snapshot was getting applied it was processing one file at the time (due to the parallelism of the snapshot generation, it split the snapshot files in 48 chunks for each table) so I started to wonder why the initialization was sequential (Serialized) whereas the generation was in parallel.

By doing a deep research on the web, I found lots of explanations such as this one (https://blogs.msdn.microsoft.com/sqlsakthi/2011/08/07/maxbcpthread-parameter-for-snapshot-and-distribution-agent-in-sql-server-replication/) which explain how the parameter "-MaxBcpThreads" work, however it doesn't explains how to get it implemented. You can find that parameter in the Distribution Agent for the publisher server, however you can't change the value from the user interface (UI) of SSMS.

So I stop trying to find for an specific solution through the web and started to build my own. Time to remove the dust and start managing the replications with T-SQL, so I looked for any stored procedures that would help me change that parameter so I found this "sp_change_agent_parameter" in there you can update the parameters for all the database agents. You can query your current Distributors but using the following query. lets call it Query1 for future reference:
SELECT
[publication] as Publication
,c.srvname as SubscriberName
,b.profile_name as Profile
,[name] as DistributionJobName
,b.profile_id
FROM [distribution].[dbo].[MSdistribution_agents] a
inner join msdb.[dbo].[MSagent_profiles] b
on a.profile_id = b.profile_id
inner join master..sysservers c
on a.subscriber_id = c.srvid
order by b.profile_name

From those results, validate if the publication you want to update is listed there, and take the value from the profile_id column.
Important note before updating this values, in the URL mentioned above, there is an important note: "When applying a snapshot that was generated at the Publisher using the concurrent snapshot option, one thread is used, regardless of the number you specify for MaxBcpThreads."
Meaning that you will need to verify the Sync_method that is configured with your publication, if it is Concurrent, then you will have to update your sync method before updating your Distribution agent properties, you can validate the method with this command "EXEC sp_helppublication @publication = 'YourPublicationName'" and look for the 7th column, this command will need to be executed on your published database. The column value will be a number, you can match each result to this property:
Synchronization mode:

0 = Native bulk copy program (bcp utility)
1 = Character bulk copy
3 = Concurrent, which means that native bulk copy (bcputility) is used but tables are not locked during the snapshot
4 = Concurrent_c, which means that character bulk copy is used but tables are not locked during the snapshot.
source(https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-helppublication-transact-sql)

Once that you have verify and noticed that you need to update the sync method to your publication, you can do it with the following command:

sp_changepublication
@publication = 'YourPublicationName',
@property = 'sync_method',
@force_invalidate_snapshot =1,
@force_reinit_subscription =1,
@value = 'native';
GO

Once that you have that set, you can use the following command to update the value you want for the maximum Bcp Threads where Profile_ID is the one you got from the Query1:

sp_change_agent_parameter
@profile_id = 4,
@parameter_name = '-MaxBcpThreads',
@parameter_value = '4'
go

What would this change do? Enable you to use x number of threads (based on what you configure in @parameter_value) to read the snapshot files and get it apply in parallel. One thing that worth's mentioning, when you check for your active executions (e.g. sp_who) you will notice that some of the parallel threads will get blocked, don't worry about it, is because you are dealing with the same table, you will gain that parallelism when you process various tables at the same time, so in here my advice is to configure that parameter_value to a number close to the half of the files that were generated always taking in account your available processors, you don't want to affect the performance for the other databases when trying to re-mediate one issue.

The result? In my environment I was able to apply the snapshot (24 tables) in 6hrs and 13 minutes, the Published database was a 1.5TB database and the snapshot weighted 185GB, without the change the process ran for more than 13hrs and only 12 tables (50% in number 35% in data) were applied, hope this tip helps you get the snapshot applied faster.

Thanks for reading!

Training and tips to get certified with Microsoft (Part 4 and last)

2017-08-21T15:56:00.000-06:00

In the previous posts I posted some questions that I usually get when we talk about getting certified with SQL Server also listed some resources you can use to train for each of the exams 70-764 and 70-765 with this post I want to close that series and answer the last question Does it gives you any value? sometimes it is also asked as Why did you do it?

In my personal case, getting an MCSA was a goal I wanted to achieve for a very long time which I kept postponing until now. Being certified was not a requirement in my current work, so one of the questions I get is, if it was not required why did you do it? My answer usually was, "I've been working with SQL server for the last 10 years so it was time to certify that knowledge", I must confess that was my initial thought, however after getting into the planning and training, my motivator changed not only I wanted to certify that knowledge, but at the same time every time I read and train more and more about it, I found features that could ease my work that were already there and I was not using it and it was because I encapsulated on the environments I was working on and closed my self to discover and apply those new features that were released in the newer versions of the database engine that I was only reading about.

Between the end of last year and mostly at the beginning of this year, in my current work I started to get a lot of request to migrate to the newer versions, and asking of advice as to which would suit better for their needs, so I started to read and research a lot with regards of SQL 2016 and also some requests with Azure SQL so I had to put my self into a training path to be ahead of the requests I've been getting from my customers and turns out that learning path matched with the learning path to earn the certification, so it became more obvious to get certified.

What I didn't thought at the beginning was how this was going to change my life. I left behind that comfort state where I knew everything about a given version and started to train on a weekly basis in the new version and more specific in the new releases that are launch every week with Microsoft Azure so that DBA that was more than happy in their "comfort zone" changed to an active learner. I put "comfort zone" because it was comfort troubleshooting the performance issues on a daily basis and planning some improvements to address that for good but wasn't really innovating in technology and wasn't catching up with the current trends, so becoming certified looked like an up hill road.

After setting up my own learning path, my habits started to change, stopped spending a lot of time in social media looking at others life and started to invest more time in my training so my explorer history started to fill up with training videos, Microsoft release notes and MVA's training courses. Also by getting and getting more training, I started to look for other certification paths to increase the knowledge and be updated with that technology as well, as I said before, now I'm changing to an active learner which I lost somewhere in the past.

So if you ask me for an answer if being certified gives you any value? Yes and it totally worth's it. Why, first of all, you are investing in you and there is not asset in the world that gives you better ROI (Return of Investment) than you. Second, it certifies to the world that you are prepared and have the knowledge to work with that tool(s) which is every time more demanded in this globalized world and Third, it helps you to start that desire to keep on learning and not loose the achieved pace to be updated.

Lastly, being certified gives you also exposure to the world, why and how? once that you pass one of the exams, it lets you create your Microsoft Professional profile In that site Microsoft updates the certification each professional achieves, in the past Microsoft published the list of certified professionals but they stop doing so years ago, then there was another option, if a potential hiring manager requires you to check for your transcripts and this requires your transcript ID plus an access code, however if you ask me, it is easier, the professional profile shows almost the same information as your transcript, the only different between this and the transcript is the certification id. Also it helps you to keep motivated as you can see how do you stand worldwide, for instance there are only 57 professionals holding this certification, based on the portal, there could be more but it will be caused that those professionals haven't created their profile and thus were left out of the statistic.

So summarizing, getting certified helps you to:
1. Reach your goals and advance in your career.
2. Become and active student.
3. Puts you in the spotlight to the world.

So my advice for you is, stop thinking and go for it!
Thanks for reading.

Training and tips to get certified with Microsoft (Part 3)

2017-08-11T14:19:00.000-06:00

Welcome back, thanks for keep on reading me, and for the ones who read for the first time, welcome.

With this third post I'll keep answering the question Where did you train? addressing in specific the topics for the exam 70-765 "Provisioning SQL Databases"

From both exams this was the one that I enjoyed the most, particularly because most of the topics evaluated were new to me, (Spoiler Alert) this one I'll dare to say is 75% over azure SQL.

The Skills measured are:
1. Implement SQL in Azure (40 -45%)
2. Manage databases and instances (30 - 35%)
3. Manage Storage 30 - 35%)

By no doubt the course that helped me the most was Migrating SQL Server Databases to Azure that I mentioned in the previous post, in that course you will learn how to deploy a Microsoft Azure SQL Database, it will also explain the different options you have to create SQL as a Service (PaaS) or if you want to create an Azure VM (IaaS), also explains you the different storage options, their redundancy etc. That same course and eBook explains you how to secure your SQL Databases, just to mention some other skills that you will acquire with that course.

The other area that is evaluated here is Storage, which you can find it in this course also mentioned in the previous post. Yes at this point you could start noticing that most of the courses are shared among both exams, and I think that was the idea (the reason why it is called MCSA Database Administration) because the database role covers that, be that one who has the expertise not only in troubleshoot slowness, maintain the data in a way it can be retrieved faster, but at the same time be that person who can say, this is the infrastructure I recommend that will satisfy with your needs, specially with how IT is moving (Cloud).

Lastly there are a few skills that aren't covered by those two courses and they belong to the On-Premises instances -For those who haven't heard of that term before, On-premises refers to the Servers (Application, storage, Database) that are provisioned in your own data center not in the cloud.

The first of those topics is:

1. Manage SQL Server instances
Create databases, manage files and file groups, manage system database files, configure tempdb

This are regular DBA tasks and sometimes Dev tasks, but for those that are not familiar with that, you can find some training with this videos:
Create databases: https://www.youtube.com/watch?v=WKWZZcrin5I even if the video is built with SQL 2012, the steps are the same
Manage Files and Filegroups: https://www.youtube.com/watch?v=-8TuJgikCWk

2. Perform database maintenance
Monitor DMVs, maintain indexes, automate maintenance tasks, update statistics, verify database integrity, recover from database corruption

Monitor DMV's: https://www.youtube.com/watch?v=AWFg6KdVoh4 this is an outstanding SQL Pass session you can use to get a bit of a knowledge as to what to use to monitor the Performance. Even if the video is form 5 years ago is a good resource to start getting familiar with it.

This exam has practice test available so you can choose to buy that option when purchasing your exam or not. Personally I choose to buy it as mentioned before it helped me to get familiar with the evaluation method that is used, so is your choose if you want to buy that or not.

In the exam you will see two scenarios, one with questions and multiple choice answers and the second with real case scenarios. Compared with the 764 exam this is shorter and less complicated that the other.

I personally believe that even if this exam is less complex that the other, is the one that gives you the best knowledge for a future career in the newer technologies.

Enjoy the study and if you need any questions I'll be glad to help.

Training and tips to get certified with Microsoft (Part 2)

2017-08-08T08:30:00.001-06:00

In my previous post I answer the first of the regular questions I usually get in regards of the MCSA certification, if you haven't seen it I encourage you to go back and see it. In this post I'll try to explain the second question Where did you train?

Where can you get prepared? By no doubt, the best resource you can get is experience, this will give you the best tools you need to pass this exams, however there are online resources you can use to train.
Lets go exam by exam.

The first one (70-764) evaluates this 4 areas:

Configure data access and auditing (20-25%)
Manage backup and restore of databases (20-25%)
Manage and monitor SQL Server instances (35-40%)
Manage high availability and disaster recovery (20-25%)

In the exam you can see scenarios such as which would be the best options to backup the databases, the steps as to how to recover a database to the most recent state with the minimum data loss, which Azure tiers will be the best where you can be cost effective and at the same time support the different environments described, how to secure the databases, assignment of security roles, etc.

One quick guide I initially found was this https://www.mssqltips.com/sqlservertip/4696/exam-material-for-the-microsoft-70764-administering-a-sql-database-infrastructure/ which gave me a great source of material where to start, however in that post you will find some payed resources as well as some that might be outdated.

There is a good training in O'Reilly specially designed with all the topics covered by the exam, however with most of the topics you are only able to see it at a glance but it provides some tips for when you present the exam.

There are good courses you can take in the Microsoft Virtual Academy.
1. Securing your data in Microsoft Azure SQL Database: https://mva.microsoft.com/en-US/training-courses/securing-your-data-in-microsoft-azure-sql-database-16076?l=ds8WZndSC_3105121157 this one will cover topics such as how to secure and audit SQL Databases in Azure (which is also part of the exam).

You can also find out there tips and tricks as to how to get data restored, but there is one in particular that is not well documented out there and is Piecemeal restore which you can find in this URL: https://www.youtube.com/watch?v=VuUAHI10o4E
This storage training will help you understand key features where you can backup databases not only on premises but Azure SQL as well https://mva.microsoft.com/en-US/training-courses/microsoft-azure-for-it-pros-content-series-storage-17237.

With the Management and Monitor, I recommend you to focus on the configuration for Data Collector, one video you can use to learn how to configure it can be found here although, the video is intended for SQL Server 2012 the steps are the same and Query Store and a good video to learn from it is this.

This two are features included in recent versions which most of the time you are not related to and will help you not only to pass the exam but also in real life scenarios when you need to troubleshoot problematic queries.

With Manage high availability and disaster recovery, do a thorough training with Always On, get familiar with the setup and troubleshooting, my best advice is that you to test it, Create an Azure account and test it, also download the evaluation version of SQL 2016 (or developer) and build your own scenarios, there are tons of videos and documentation in the web in regards of this but you will fully understand it until you get hands on it, so test it!

Also another MVA course that will help you for both exams (764 and 765) is Migrating SQL Server databases to Azure this course is an incredible resounce to get the MCSA, specially the eBook that is given for free as it describes all the steps you need to follow to migrate your on premises database to Azure (SQL Azure or Azure VM) how to get the storage set, your recovery options, etc.

Lastly, in the day of the exam, be relaxed, don't let your nerves to betray you, try to get a good sleep the night before and get on time to the examination center, if you decide to present the exam online, be sure to turn off all the possible distractions, I prefer to go to an evaluation center as it force me to unplug from the world during the time of the exam (Usually 2hrs) and avoid any sort of distractions so that my focus is a 100% on the exam and nothing else.

For this exam in particular there is no practice test yet available or at least in the official site that you can use to practice or if you have never taken a Microsoft exam to get familiar with it, 70-765 does have practice test and that's the reason why I took that before as I wasn't familiar with any of this tests in the past and it let me get comfortable when I was going through it.

I'm already working with the material for the 70-765 post however I'd like to keep each post short enough to give you the tools you need and not get you bored so I'll be publishing it in the next days.

Thanks for reading.

Training and tips to get certified with Microsoft (Part 1)

2017-08-02T14:33:00.001-06:00

In the last weeks, while I was preparing for one of the certification exams, I got a lot of questions from collegues, asking questions such as How do you Schedule and Pay for the exam? Where do you train? How hard is it? Does it gives you any value?

Based on that common questions which I also had in the past and pretty much had to solve it by my self by looking all over, is that I decided to do this as my first blog post.

How do you Schedule and Pay for the exam?
I think the first question would be, are you informed as to which career path do you want to follow? The Microsoft certification program has been constantly changing and last year new paths were rolled out that anyone can follow.
If you want to pursue an SQL Server 2012/2014 MCSA this is the path that you need to follow: https://www.microsoft.com/en-us/learning/mcsa-sql-certification.aspx#cp-section3-head

If you want to pursue the SQL Server 2016 MCSA this is the path that you need to follow:
https://www.microsoft.com/en-us/learning/mcsa-sql-2016-certification.aspx

If you look in detail, you will noticed that for 2012/2014 you require 3 exams to be passed, for 2016 you need only 2, this doesn't means the one for 2016 is easier, it simplifies the process? yes, and is mainly because it is more specialized for each path, you can see there are 3 different areas that you can get certified (Database Development, Database Administration and Business Intelligence Development).

In this post in particular I'll be focusing on the Database Administration path, which is the one I followed, the process would be similar for each one though.

What are the exams that you need to pass?
You need to pass two exams 70-764 and 70-765.

So now, how do you schedule those exams? In both URL's provided above, it gives you an option that says "Take Exam ###" that will redirect you to an URL, in there you will need to select the country where you will be presenting the exam, the cost varies from country to country so be sure that you choose the correct one. Also, there might be times where you can buy offer packs, such as Exam, Exam retake and Practice test, you can look for it in here: https://www.microsoft.com/en-us/learning/offers.aspx?intcmp=lexexampage_belbutton however be sure if you buy that complete package that an practice test is available, for instance, when first looked at the career path there was only practice test available for the exam 70-765 and not for the 764 so first be sure if the practice test for the exam that you will be presenting is available, if that's not the case, you can buy either just the exam or the exam + the retake, When I purchased mines I bought it with the retake, thankfully didn't had the need to use the retake for any of the exams, but it reduces the anxiety when you are presenting it. If you buy any of this options you will receive and email with the coupon code that you will need when scheduling the exam.

Either if you bought the coupons or not, you will be back at this URL: https://www.microsoft.com/en-us/learning/exam-70-764.aspx this one in specific is for the 764 exam but you can only change the exam code in the address line so you are sure you schedule the right exam. Next you will need to click on "Schedule Exam" which will take you to login with your MSDN account. Make sure that you are using your correct address, whether if you would like to have your profile with your personal account or the one created with your corporate account as if you use the incorrect one and pass the exam, but later you want to transfer it to the other account you will need to create a ticket with the Regional support center so your Microsoft IDS can be merged or just transfer what you have earned.

If this is the first exam that you are presenting, you will need to fill in some personal details. Once you are done with those details you then need to click on "Submit" which will transfer you to the Pearson portal which will give you 4 options:
I will be scheduling for a future date at a test center. (You will go to a test center)
I want to take this exam as an online proctored exam. (You will be presenting it at home)
I am taking this exam at a Certiport test center.
I have received a Private Access Code for this exam.

If you choose the Test Center or Certiport test center it will then ask you to select your test center, it will show you the available dates and times for each center near you. Once that you have chosen your center, date and time you will be asked to go to the Checkout page, in there it will ask you to confirm your information, to you agree to the policies and then the payment information, if you purchased any of the options mentioned above and received the coupon information already, is here where you enter the information provided in that email, it won't ask you to pay for anything else as you already buy what you need, lastly you just need to confirm and submit, and you are done and scheduled to present your exam.

Then is just matter to be prepared the best as you can for the exam date.

The other questions will be resolved in the upcoming entries.