SQL Server 2016 Administration MCSA Underway

AvgITGeekAvgITGeek 70-410, 70-411Posts: 314Member ■■■■□□□□□□
edited June 11 in SQL Server exams
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!

Comments

  • ZorodzaiZorodzai Posts: 346Member ■■■■■■■□□□
    Best of luck, you got this !
  • AvgITGeekAvgITGeek 70-410, 70-411 Posts: 314Member ■■■■□□□□□□
    edited June 12
    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:
  • AvgITGeekAvgITGeek 70-410, 70-411 Posts: 314Member ■■■■□□□□□□
    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.
  • AvgITGeekAvgITGeek 70-410, 70-411 Posts: 314Member ■■■■□□□□□□
    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.
  • AvgITGeekAvgITGeek 70-410, 70-411 Posts: 314Member ■■■■□□□□□□
    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.
  • AvgITGeekAvgITGeek 70-410, 70-411 Posts: 314Member ■■■■□□□□□□
    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

  • AvgITGeekAvgITGeek 70-410, 70-411 Posts: 314Member ■■■■□□□□□□
    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.
  • AvgITGeekAvgITGeek 70-410, 70-411 Posts: 314Member ■■■■□□□□□□
    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.
  • AvgITGeekAvgITGeek 70-410, 70-411 Posts: 314Member ■■■■□□□□□□
    edited June 18
    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.
  • AvgITGeekAvgITGeek 70-410, 70-411 Posts: 314Member ■■■■□□□□□□
    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.
  • AvgITGeekAvgITGeek 70-410, 70-411 Posts: 314Member ■■■■□□□□□□
    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.
  • AvgITGeekAvgITGeek 70-410, 70-411 Posts: 314Member ■■■■□□□□□□
    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.
  • AvgITGeekAvgITGeek 70-410, 70-411 Posts: 314Member ■■■■□□□□□□
    edited June 24
    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.
  • AvgITGeekAvgITGeek 70-410, 70-411 Posts: 314Member ■■■■□□□□□□
    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.
Sign In or Register to comment.