SQL Server 2016 Administration MCSA Underway

AvgITGeek

About 3 months ago I took a position as a DBA after a couple of years away. I've always loved working with SQL Server and querying the data so I've been studying off and on for the SQL 2012 MCSA for a bit. Yeah, I passed the Administering SQL Server 7.0 exam way back in the day. Anyhow, I decided to bin my attempts for 2012 and go for 2016 as we will be moving away from 2012 in the next couple of years so I might as well get clued up. I have PluralSight, Udemy, the MS Press 70-764 and 70-765 books and plan to augment that with a bunch of research/technet as well as my home lab. I'm planning on taking a week per chapter to get things down, some chapters may be quicker as I'm already familiar with a lot of admin tasks but I've never used Encryption for example.

This is my own place to keep accountable but if you'd like to chime in, please do, I'd welcome it!

Zorodzai

Best of luck, you got this !

AvgITGeek

Thanks @Zorodzai !

Today so far, I've got about 2 hours in the Exam Reference and some Microsoft Docs: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-and-database-encryption-keys-database-engine?view=sql-server-2016 Now planning on hitting up the Udemy video course for about an hour or so. May get some sample commands in. Not sure. Exhausting day at work, very busy and TIL that Database Mail stores every message sent in MSDB and if you use Database Mail a lot, your MSDB can get fairly large over time. Apparently the guy I replaced didn't know that either. Time for some cleanup!

For all of you DBAs out there that don't know:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sysmail-delete-mailitems-sp-transact-sql?view=sql-server-2017

AvgITGeek

Today's time was spent reading MS Docs on Transparent Data Encryption and Always Encrypted. Also found a good article from Red-Gate on Always Encrypted with a little walk through that I'm going to do in a bit. After that will be re-watching a few Udemy videos on encryption.

AvgITGeek

Ran through a couple of installations all from GUI. I'll spend some time tomorrow for command line installs. Some more encryption labbing tonight but nothing too heavy. Maybe about an hour or so. Looong day at work and I'm pretty tired.

AvgITGeek

Still at it. VirtualBox is having problems with cloned parent disks on my computer. I've deleted everything and starting my VM's from scratch. I miss Hyper-V. VirtualBox sucks. I really don't wan't to spend the $99 to upgrade to Win 10 Pro since I only have 30GB free on the SSD this computer shipped with. I should buy a > 300GB SSD and be done with it. I need to open my current box to make sure I have the right connectors and I just don't have the physical energy to get down and do all of that.

AvgITGeek

Nothing tonight. Going to relax and chill after this long week. I'll probably fire up one of my SQL servers and putz around but nothing really serious

AvgITGeek

After mowing the lawn and cleaning one of my gutters... Spent about 3.5 hours finishing up the Encryption chapter in the book (and submitting errors to the errata folks) and hands on with Transparent Data Encryption, creating keys, certificates, backing up keys and certificates and encrypting backups along with reading various MS docs. Jotted down a couple of topics that were not covered like moving and restoring encrypted databases. Will hit that up tomorrow. I figured Encryption might go a little long as I never used it before. May spend a couple of more days on it so I can get the syntax down.

AvgITGeek

Didn't get much accomplished yesterday as I was picking my father up at the airport and then spending time with him.

Today: More on Transparent Data Encryption and the various keys and processes surrounding them. Hit up the appropriate system catalogs and DMV used to gather information. Some syntax notes so I can differentiate between what is happening based on the commands issued.

AvgITGeek

Ran through the commands to create Keys and Certificates along with the backup/restore from backup commands (keys and certificates). Started watching videos on SQL Server Logins and was really focused on the TSQL ways of creating logins with different options like with asymmetric keys and certificates. I got a major query request thrown my way at the end of the day which has to be completed by Friday (of course) and it involves shipping records which I have no clue about as of yet. I don't even know how an order is shipped yet so the evening will be cut short so I can start researching the tables in question and link them back to order line items to see if I can make some sense of them before I walk into customer service tomorrow morning to have them walk me though the process.

AvgITGeek

Tonight was about SQL Server logins and users. Videos on both along with Microsoft documentation for the TSQL syntax to manage them. Also touched on cross server authentication (or Linked Servers) which excites me as we have a linked server to our production database which is an OpenEdge Progress DB. I had to create a new Linked Server on our test SQL server that pointed to our clone of the Progress production server so I can test all of the SQL Server stored procedures and every query in those stored procedures. Yeah, ERP upgrade testing is starting with me being here 3 months. Fun times but hey, I'm learning this thing from front to back, inside out which makes me happy. Next up will be Partially Contained Databases.

AvgITGeek

Very light day again. Spent about 1 hour on videos on Partially Contained Databases and Dynamic Data Masking and installing my second SQL Server. During any downtime tomorrow I'll be hitting up Microsoft documentation on Dynamic Data Masking.

AvgITGeek

Today was spent going through Microsoft documentation on DDM and Partially Contained Databases. I'm not going to spend a whole lot of time with the latter other than reviewing how to migrate user accounts. Being that DDM is new, I reckon MS will test heavily on it. Then started on Server/Database fixed roles which really haven't changed much over the years. I'll follow that up with Auditing which isn't included in the Udemy course I'm watching to I'll have to search around for more information than what is covered in the certification guide. I expect to get a bunch of labbing done this weekend. My second Windows server is now ready to have SQL Server installed so I'm going to do that tonight while I watch my Pittsburgh Pirates sink up the place.

AvgITGeek

More Server/Database roles and custom roles. Also ran through some schema videos and documentation. Switched over to PluralSight and started a video series on Auditing and will more than likely, stay and watch some of the videos on encryption. I really like having two video sources to choose from. Also checking out Triggers, Extended Events and Policy Based Management from the PluralSight course.

AvgITGeek

Being that I've found a PluralSight course specifically on SQL Security, I've circled around and spent about an hour going back over encryption. Both Always Encrypted which is new with SQL Server 2016 and TDE. The prior DBA put a bunch of databases on C: and we were running out of disk space so I was spending the day moving those database files to their rightful spots with the other database files like they should have been to begin with. This took a little time to accomplish. While that was going on, I spent some time poking around in Extended events and very briefly with SQL Auditing. Auditing I'm going to revisit in this current PluralSight course and the last bit of the chapter in the study guide.

nachodba

You're making great progress!  When are you planning to take your exam?

AvgITGeek

@nachodba I haven't even begun to think about scheduling. I want to get through the book and video resources and then I'll have a better idea.

I finished chapter 1 which ended with SQL Server auditing and then refreshed myself with Common Criteria Compliance and good ol C2 audit mode. Enough to remember what they are and how to enable. Finished up the Auditing PluralSight video and just to hear it again, started up the module on Principals, Securables and server/database roles. That will do it for tonight.

AvgITGeek

Got home from work last night at 5:45 to a completely dark neighborhood. Severe storms knocked out the power but on the other side of the main drag 9 houses down, they were fine. FML. Power out until 02:00. I was able to read by faint outside light about 40 pages in the next chapter of the exam guide on Backup and Recovery.

Nothing more tonight. Didn't sleep too well last night so I'm going to bed early. I'll resume tomorrow with videos on the subject and one last video on Schema best practices.

AvgITGeek

About an hour of videos on Backup tonight with Microsoft documentation. During the day while waiting for user information I did some more reading on Indexing and some articles from Brent Ozar on his database monitoring stored procedures.

AvgITGeek

Nothing substantial the past couple of days. Work had me making some modifications to existing reports which had be busy bouncing between departments trying to figure out what the data they were wanting meant and how to link the tables together to isolate the data. Also Crystal Reports is new to me so there is that added learning curve but at the end of the day, I was able to complete the three changes they requested and have some happy co-workers!

Spent about an hour on Backup and backup terminology along with copy only backups, documentation on LSNs and TSQL procedures for the different backup options available. Tomorrow I'm anticipating getting into the restore process and log shipping.

AvgITGeek

Well, didn't get to log shipping. Did about an hour with restores both database and logs and all of their options. Read some documentation on that as well. Really trying to get a handle on the TSQL side of things and even Powershell. I've been dabbling more with the PowerShell dbatools cmdlets found here: https://dbatools.io/ than I have with the SQL PowerShell cmdlets. I would advise all dbas and want to be dbas to pick those tools up.

AvgITGeek

Spent another 30 minutes finishing up the Backup and Restore Udemy course and then went in and was creating databases and backing them up via TSQL. I've never done a restore of the master database so I thought I'd run through that while I watched the video. OK, I stopped all of the services, set the MSSQL service to start as single user. Restarted that service only, logged in through SSMS and whenever I tried to restore master database I kept getting an error saying there was already an administrator logged in. I set it to single user mode and shut down all other services. How could there be another administrator logged in? I spent way too much time on this. Hit up a MS doc on restoring the master database and as it turns out, you need to use sqlcmd to do it. I felt like such an idiot! Sure enough, by opening up SSMS I was using that single administrator login. Yeah, I won't forget this anytime soon! I love learning!

Also, the MERGE function is completely kickass! A super way of keeping a table up to date from a source or to keep a table in sync. If you haven't used it already, you need to!

AvgITGeek

Only review on the TSQL for creating databases, tables, backups and restores. Read a great article by Gail Shaw on transaction log maintenance which is a really good read!

yuddhidhtir

Is the Microsoft books good? Any other Sql 2016 exam book which I can refer ?

AvgITGeek

@yuddhidhtir The book for this exam (70-764) is only 400 pages. The Errata is lacking quite a bit. There was one exercise where the TSQL was for a completely different database yet the screenshot showed the correct TSQL. It gives you the basics but you will have to do a lot of research on your own to be even ready to take the exam.

yuddhidhtir

That's the same review of others about the book in the internet. I was looking for a book specific for the 2 SQL administration exams. I can only find Microsoft books. Earlier whenever i looked for Microsoft exam books  there used to be plenty to buy. Although its been 3 years since the release but still very few books, in fact just Microsoft books are available.

8 years ago, when i gave exams i used the double shot offer which helped me pass exchange in second attempt. So are you using the same , 2 attempts in exam? 

AvgITGeek

While this book isn't the best, it does go into all of the objectives and tells you flat out to do more research. I'm probably going to use my SQL Server 2012 Administration book to go through some exercises. There are really only a couple of new features in 2016 that I'm really spending a bunch of time with.

I hope they still have the second chance program. It helped me with my 70-411.

Have you tried any of the Kaplan (formerly Transcender) practice exams? They were the gold standard back in the day and Boson doesn't seem to have any for this track.

yuddhidhtir

I have not started the certification preparation yet  so not thinking about practice tests. Never knew the name changed, i remember  on this forum itself everyone used to recommend the transcender tests.
Currently I am going through You tube videos mostly about SQL server 2012, the book - Learn SQL in 10 mins. 
Plan is to buy SQL 2012 administration used book, then the Microsoft book  of SQL 2016,and then the Udemy course. Will start a thread here as well.

AvgITGeek

On Udemy, look for: Microsoft SQL Server 2016 Certification (70-764) by CubeTech Academy, LLC. It is $29.99 but very good. I bought one prior course and about an hour in I wanted a refund because it was just so light on everything. This one is really good and they have downloadable content for most of the modules that contain links to relevant topics. I highly recommend it. Going through the 2012 admin book isn't going to be a waste of time at all. Like I said, I really am planning on using my MS Press 2012 admin book for a bunch of the foundational things that the book doesn't quite cover. Be sure to spend time on both the SSMS way of doing things and the TSQL way.

Yes! Be sure to start a topic here when you start.

Unfortunately, I haven't had much time to do any real sit down studying. It's been all articles here and there and some off the top of my head Microsoft Docs searching this past week.

I've had some new accounting reporting thrown at me the other day and the four people that set up the queries, the VBA behind the spreadsheet that uploads everything and any other steps involved in completing this process are no longer with the company and I still don't know how things in the inventory side of the house tie into the accounting side. The new VP of Finance started on Monday and I have a sit down with them next week to start making sense of all this! Fun times to be sure but this is the way to learn the business though!

yuddhidhtir

Thank you for the suggestion, it really helps.