SQL Server 2008 end of support

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?

1. 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.
2. 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.

3. 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?

Configure Always on AG with SQL 2017

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:

1. Initial Role: You will set the role that each replica will have once that the group becomes available
2. Failover Mode: Manual or Automatic
3. Availability Mode: Synchronous commit or Asynchronous commit
4. 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.
5. The endpoints tab will show you the URL and ports each replica is set to.
6. Backup preferences tab: in here you can configure if you want to take the backups from the primary or the secondary servers.
7. 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.
8. 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) 

7. 

1. 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.
2. 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
3. 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.
4. 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.
5. Failover Mode: Manual or Automatic
6. Availability Mode: Synchronous commit or Asynchronous commit
7. 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.
8. The endpoints tab will show you the URL and ports each replica is set to.
9. Backup preferences tab: in here you can configure if you want to take the backups from the primary or the secondary servers.
10. 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.
11. 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

1. 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.
   
   
   2. 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.
   1. Configure the port you want the listener to listen to, this needs to be different than the endpoints and than the usual 1433 that the SQL instance will be listen to.
   2. Configure the IP: make sure that its an available IP in the domain, also make sure that you choose the ip from the same subnet that one of your replicas is running.
    
   
   
   
   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

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.

1. Go to Azure Active Directory 

2. In the "Name" field type in the give name of the user
3. Type the fully qualified name name@domainname.onmicrosoft.com
4. Configure the Profile details
5. It won't let you change the details so you can leave it that way
6. Configure the Groups it will have access to
7. Select the role it will have in the active directory the options are (Regular user, Global Admin, Limited Admin)
8. 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.
9. 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

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!

Configuring a Subscription to an Azure SQL

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

What's new in SQL 2017

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.

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
7. 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.
8. 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.
9. 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

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:

1. Two sets of IPs: You will need one IP for the Windows failover cluster and one for your Listener.
2. 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.
3. 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.
4. 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.

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

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)

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)

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.