2018 Learning and Blogging Plan

Every year I usully start with some grandiose vision of what I plan to achieve and generally dont follow through with it. This failure is caused by failing to breakdown goals and for having measurable achievements and regular reviews.

This year I plan on fixing all of these mistakes by using a simple notepad to collect and store all of my plans alongside using my diary both physical and online to enable me to at a glance know whats steps I need to be taking to reach these goals. Too many times using an online tool results in me getting bog down with another task and not focusing on achieving my goals. The use of a physical notepad allows me to focus and gather my thoughts way better without any distractions.

My plans for 2018 is to learn more C++. I have found the more C++ I have learnt the better I have become in many other areas of my DBA role. I plan on doing a lot more C++ problem solving on Project Euler and some other Competitive coding site to enable me to further grow and refine my skills.

This year I want to truely ensure that I use PowerShell in my daily activities to automate and make my life easier as a DBA. I have a dream of going to work and simply running a script to do daily my routine tasks which would allow me to surf hacker news all day. This might be a complete fantasy but I think I can get very close to it. Alongside PowerShell I want to master the Pester framework so that I can learn and integrate a TDD approach to coding rather than my current haphazard style.

As a DBA my bread and butter is SQL Server and this year I plan on learning more about Performance Tuning as I think that this year I can make a massive difference to our internal platform by being able to make our SQL queries run suer efficiently.

My low priority learning goal is to learn more about Windows Server, Hyper-V & Docker. This will help me with my day to day work but I doubt it will allow any quantum leap in my ability to do my DBA work.

I plan on blogging about these 4 areas but I dont want to commit to doing more than a fortninghtly post as I want to ensure regular content without making this goal a distraction from my learning goals.

The regular reviewing is already underway with a series of appointments in my calender with my wife to make sure that I am staying focused on achieving these goals and makng sure that my 2018 plans are a success.

Quick introduction to SQL, databases and relational theory

Many people find it daunting when they think about Databases and SQL. I know I found it very scary in the beginning and my early experiences with Database developers left me feeling very small and I thought that maybe I was not good enough to succeed in this field.

However, I was luckily given a quick 30 minute overview from one of the senior developers and he gave me a whole load of terms to research and understand. This gave me the foundation of my Database career and I hope that I can provide the same information to whomever reads this post.

What is a database?

A database is an organised collection of data. Databases are used to store all sorts of information like academic records, medical history, sports statistics and many many more areas.

Why use databases?

The main reasons for using databases are
• Data consistency and ability to enforce data rules
• Ability to easily retrieve large amounts of data
• Ability to manage security
• Ability to open up and share the data to more users

In the past one of the main criticisms about databases has been that they were slow but improvement in both hardware and software have eliminated this issue.

What are tables, rows and columns?

A table is similar to a spreadsheet where the data is held in an organised and structured way which is comprised of columns and rows.

Each column contains a set of similar types of data such as post codes, dates of birth or colours

A row is simply data or information for the columns. For example, if the column is “Grades” each row may be comprised of “A-E”.

What is null?

A null is a missing or unknown value. It is not a zero, empty text string or one or more blank spaces.

Nulls occur when a value is not added to a column when the record is made.

What is normalisation?

Normalisation is the process of organising data in tables and columns to both reduce the amount of  data redundancy and to improve the data integrity in the database.

What is a primary key?

These are a unique attributes which identifies each row. These are generally a number increasing in a sequential manor but they can be comprised of other data. The primary key will identify that record throughout the database and helps maintain data integrity. An example of a primary key is a Customer ID.

What is a foreign key?

When you want two tables to have a relationship with one another normally this is done by replicating the primary key into another table which thus creates a parent-child table relationship. By enforcing the relationship the referential integrity is maintained.  A classic primary and foreign key example is Customer and Order details.

What are relationships?

These are how you connect information between tables and they compromise of three types

  • One to one
  • One to many
  • Many to Many

What is a view?

A view is a virtual table which is comprised of one or more base tables. It is not a physical table as it retrieves information from base tables.

What is an index?

An index is an object which helps speed up the retrieval of data. It is specified on one or more columns and there are quite a few different types of indexes. A simple way of thinking about an index is to think about the index or contents page of a book which helps direct you to the correct page without searching through each page individually.

 

My top tips for new programmers

Over the years I have written quite a lot of code. These are the tips that I wish I had known when I had started out and would have saved me a lot of head scratching and restless nights:

  • Create a “to do” list. Always write down your tasks and keep it updated as you work through the problem. Your memory is limited and you can often forget something which may be critical later.
  • Always use variables and don’t hardcode. Generally this will make your life easier and it makes the application more flexible.
  • Code comments are your friend. Many times I returned to code I have written and never known what a function or piece of code is expected to do. If I had taken some time out to put some comments into the code it would have saved me hours troubleshooting issues.
  • Always break a big problem into a series of small problems. Sometimes a big problem can feel daunting and be more difficult than it seems. Break the problem down into smaller blocks and then solve each block one at a time. It’s okay to skip a block and return to a previous block as it may give you clues to fixing the skipped block.
  • Errors are not a bad thing they tell you where the problem is. This will help you to solve the error more easily and doesn’t mean you’re a bad programmer. Only be worried when something goes wrong and you don’t get an error message.
  • Never reinvent the wheel. It is more than likely someone has faced the same problem as you and it is okay to copy their solution.
  • A difficult problem doesn’t mean you’re a failure. There will be times when you face a problem which you find hard to grasp. It is perfectly acceptable to find things difficult.
  • Know who to contact in your ogranisation to ask questions. When you have very little information and need more details it is infinitely better when you have a list of go to people for all the applications you will be supporting.
  • 99% of your work will never be seen by anyone. Your solution may not necessarily be the most elegant one but try to keep it simple.
  • Prototypes do not have to always end up in production. Building things allows us to learn from coding. What you learnt is better than whether it ends up in production. Sometimes it is better to start again than fix problems with some poorly written code.
  • Read code of more experienced programmers. When you get passed some code don’t just deploy it. Read it and review it. You might learn something or you might pick up a problem which will save everyone time.
  • Don’t stop learning. Programming can change quite rapidly. If you don’t invest in learning new things you might end up encountering it for the first time when you are debugging something in production

Feel free to comment with any additional tips which you may have.

 

Book Review: Learn Windows PowerShell in a Month of Lunches, Second Edition by Don Jones

During December I decided to dedicate myself to learning Powershell for the next few months. Many of the blogs I follow and some of my respected peers have recommended the Learn Windows PowerShell in a Month of Lunches book and ashamedly I have owned the book and never gotten past the first chapters.

The reason I never did do much with Powershell is because I wasn’t sure it was worth my time and wasn’t sure how I could apply it. However, after seeing the great work of Chrissy LeMaire on DBATOOLS and Rob Sewell at DBA Reports I knew that I could make my life a lot easier. I know from their work that they have done a very large amount of work which I did manually could be automated. Therefore I now had sufficient motivation  to start my Powershell journey.

The book is compromised of the following chapters:
1 Before you begin
2 Meet PowerShell
3 Using the help system
4 Running commands
5 Working with providers
6 The pipeline: connecting commands
7 Adding commands
8 Objects: data by another name
9 The pipeline, deeper
10 Formatting—and why it’s done on the right
11 Filtering and comparisons
12 A practical interlude
13 Remote control: one to one, and one to many
14 Using Windows Management Instrumentation
15 Multitasking with background jobs
16 Working with many objects, one at a time
17 Security alert!
18 Variables: a place to store your stuff
19 Input and output
20 Sessions: remote control with less work
21 You call this scripting?
22 Improving your parameterized script
23 Advanced remoting configuration
24 Using regular expressions to parse text files
25 Additional random tips, tricks, and techniques
26 Using someone else’s script
27 Never the end
28 PowerShell cheat sheet 

Chapter By Chapter Review

1. Before you begin

This is a nice short intro into Powershell and shouldn’t take anyone long to finish.

2 Meet PowerShell

This chapter is mainly about customising the GUI/ISE to whatever your preference is and along with first chapter can probably be done in one lunch.

3. Using the help system

I loved this chapter as the key success within Powershell is becoming familiar and efficient with the Help system and this chapter gives you those skills.

4 Running commands 

Finally we are doing something and in this chapter you are introduced to some simple one liners and how they can be constructed.

5 Working with providers 

In this chapter you learn all about PSproviders.

6 The pipeline: connecting commands 

The pipeline is a critical component of Powershell and is explained in an eloquent way.

7 Adding commands 

Now we start dooing some real stuff with Powershell and I found the Lab at the end particularly enjoyable.

8 Objects: data by another name 

A key concept within Powershell is everything is an object and it is explored in this chapter.

9 The pipeline, deeper 

More awesome Pipeline stuff.

10 Formatting—and why it’s done on the right 

Finally I learned how people were making there reports with Powershell and now I had that knowledge.

11 Filtering and comparisons 

Does what it says on the tin.

12 A practical interlude 

I loved this chapter as it was a series of challenges which helped give me confidence with my Powershell knowledge.

13 Remote control: one to one, and one to many

After this chapter I was a little scared about how easy it would be to hack on a large scale using Powershell.

14 Using Windows Management Instrumentation 

WMI had always scared me and now it doesn’t after using it and its newer CIM implementation.

15 Multitasking with background jobs 

Does what it says on the tin.

16 Working with many objects, one at a time 

Finally we get to the FOREACH command.

17 Security alert!

After this chapter some of my earlier hacking concerns were a little alleviated.

18 Variables: a place to store your stuff

I liked this chapter as I finally feel like we are properly starting to script.

19 Input and output

I wasnt too sure about this chapter as it contained quite basic information  and probably coulkd have been added to another chapter.

20 Sessions: remote control with less work

I understand how sessions work but I am not sure it needed a whole chapter.

21 You call this scripting? & 22 Improving your parameterized script

Finally we are doing some really cool stuff and it is quite surprising how easy it is to create scripts for Auomating Administration tasks.

23 Advanced remoting configuration

I must admit I read the chapter and quickly forgot what was contained in this chapter as I doubt I will need to remember all that detail.

24 Using regular expressions to parse text files

My hatred of regular expressions has now extended to another programming language.

25 Additional random tips, tricks, and techniques

A nice few tidbits of information.

26 Using someone else’s script

A great chapter which is a must read.

27 Never the end

A nice little send off into the big bad world with some Powershell.

28 PowerShell cheat sheet 

A nice little reminder of things which has been covered.

In conclusion this book was a great start to my Powershell journey and I am now working through the Learn PowerShell Toolmaking in a Month of Lunches.

2017 Blogging Plan

This year I am going to get back to blogging regularly and this time I really mean it. I have neglected to blog regularly due to procrastination and not being happy with most of the blog posts I had written. I will try to blog at least every fortnight and will, subject to my work and family, try to blog more often. This schedule should be easy to maintain and not become a burden and lead to me going on another blogging sabbatical (hiding he he).

This year I plan on levelling up my PowerShell, C# skills and learning more about Cyber Security.

I have seen some of the cool stuff some people are doing with PowerShell and would love to automate everything in my life. This will free me up to do more Production DBA work and enable me to increase my project workload. I hope that I can try and produce some scripts which I can add to the DBATools.io project and give them back something for their awesome community work.

My C# journey is mainly so I can increase my programming expertise and I want to try and take part in some competitive programming and undertake some programming challenges. Currently my knowledge level is insufficient to do this but I want to learn more and hope that learning design patterns, algorithms and object orientated programming will let me fill in these gaps and allow me to have some programming fun.

Lately I have become interested in Cyber Security as I want to understand both the methodology used by hackers and the people preventing them. I find the mindset, creativity and folklore around all of this to be fascinating and hope that I can learn a few things along the way.

I hope that my audience will enjoy my 2017 journey and will help keep me accountable.

Thanks for your support.

Why you should learn programming

I remember when one of my friends got a summer job where he was asked to remove certain special characters from filenames and replace it with an underscore. He complained it was boring and tedious but it paid well so he stuck at it.

He was expected to do a certain amount of files per hour and he was determined to hit that target as soon as he could so that he could spend the remainder of the hour surfing the internet. The internet at that time was still a rarity and he decided he needed to find a quicker way to complete his work so that we could free up more time.

After much searching and lots of testing he wrote a simple program which renamed the files for him. Unfortunately for him some of the code he cut and pasted without properly understanding its function looped through all the directories on that particular drive completing weeks of work into a few hours. He was shocked at the speed, efficiency and most devastating of all the prospect of losing his job.

He plucked up some courage and showed his manager what he had done. His manager was astounded at his cleverness and told him if he could setup the process to run daily and then maintain it ongoing he would employ him part time permanently. This led to a fruitful source of income as over the next few years he did very little modification to the system whilst picking up a monthly paycheck.

This is the power of programming and why you should learn programming  as with only an entry level of knowledge anyone can recreate the program my friend had created.

I bet there are dozens of daily tasks which you do which could similarly be automated like

  • moving files
  • downloading files
  • giving custom notifications in response to events
  • manipulating excel sheets
  • importing and exporting data
  • and hundreds of more similar tasks.

Think about how much time you could save and how much you could benefit by automating things in your life. Hopefully the benefit you could receive is enough to answer Why you should learn programming.

What would you like to learn or for me to talk about?

Hi all.

Just a quick blog post to say that I would like to thank everyone who has visited this site, sent me emails or comments on social media. It makes all my efforts worthwhile and further motivates me to continue blogging.

Now I would like to reach to everyone and find out what they would like to learn more about. This helps me to cater for my audience and give me new ideas for future blog posts.

Thanks once again to everyone and have a great day.

 

 

Ruby Challenge update

Hello all.

A quick update on my Ruby Challenge.

I am half way through the One Month Rails course and I am really enjoying learning Rails programming.

I have found Ruby to be a quite intuitive and easy language to pick up. I have already started looking at creating a few simple apps which have always been on my mind.

I am hoping to try and finish the course by the end of next week and hopefully start Michael Hartl’s course.

Hope everyone has a nice weekend.

 

 

 

 

 

How to change the collation of a database in SQL Server?

Have you ever wondered how to change the collation for a database in SQL Server?

Well the other day I received a database from a third party supplier and the first step was to change the Collation of the database. My questioning about why they would supply the database in the incorrect collation setting was met with a wall of silence. Therefore I had to put in my documentation how to change the Collation of the database.

I did it the quick way by right click on database > Select properties > options > Select appropriate collation from dropdown menu next to Collation.

Database properties
Database properties

That was a good enough solution at the time but for my documentation I would always add the T-SQL so that anyone could follow the guide without making mistakes.

Below is an example of how to change the Collation via T-SQL

ALTER DATABASE AdventureWorks2012 COLLATE SQL_Latin1_General_CP850_BIN

Please let me know whether you found this guide useful and leave a message in the comments.

Ruby Rails challenge

I overheard a conversation the other day in the office about some of my colleagues all challenging themselves to do an Iron Man race as they always wanted to do it and they thought it would be fun to do it as well.

This got me thinking about my own dreams and challenges I could set for myself. Whilst an Iron Man competition sounds fun it doesn’t appeal to me and I have thought about what I could do in a short period of time to both experience something new and to learn and grow.

I have always had a niggling desire to create apps and my existing knowledge of  C#, Python, SQL and PowerShell has been primarily geared towards solving administrative tasks rather than creating programs. After a short deliberation in my head I have decided that I will start knocking out a few apps over the next few months and hopefully share any knowledge I have picked up  over that time.

A quick Google around showed that there were two ways to go to fulfil my goal and those were Ruby & Rails or Python & Django. I know there are pros and cons to both options and didn’t want to get stuck in that malaise so I have chosen the Ruby & Rails route.

The two reasons for choosing Ruby & Rails is because its shiny and new for me and there appears to be a growing community of users which I would like to be a part of and the other reason is there is a meetup local to myself for Ruby programmers which gives me less than a month to learn the basics of Ruby programming and to produce at least one rudimentary app.

I have signed up to onemonth.com for their Rails program and will do the Ruby on Rails Tutorial by Michael Hartl on SafaribookOnline.com which should give me a firm foundation and hopefully I will be able to contribute in that meetup.

I will post regular updates about my progress and hope this public announcement will keep me accountable. Also, I probably be posting a whole load of Ruby & Rails related blogs and hope that everyone finds them enjoyable and informative.

Now I want to turn the tables and ask all my readers what they will be doing over the next few months to learn, grow and most importantly have fun.