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?

Offline installation of SQL Server 2017 on Linux

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:

1. A machine (VM, physical, etc) with Linux 
1. Red Hat Enterprise Linux (7.3 or 7.4)
2. SUSE Linux Enterprise Server (v12 SP2)
3. Ubuntu (16.04)
4. Docker (1.8+)
2. The latest version of SQL Server 2017 downloaded you can get it here and copied onto your Linux machine.
3. A user to run the install (different than root)

Installation Steps

1. Connect to your Linux machine. You can use Putty or the SSH client of your preference 
2. Locate the folder where you have the installer, in my case I put it on "/home/SQLInstall/
3. 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.

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

Configuring a Transactional replication

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.

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.