SQL Server Replication snapshot initialization taking long time to be applied

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)

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.

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

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.