Apr 29

When’s too Late to Sort Your SQL Backup strategy?

By Charles | Business

[h2_heading]Do you have a SQL backup strategy in place?[/h2_heading]

You have at most, between now and the next disaster situation to sort out your backup strategy, the catch however is that you will never know when that disaster will strike. So you really only have one option. Sort your SQL backup strategy now once and for all.

SQL database backup

I personally can not sleep knowing that a SQL backup strategy is either non existent or worse not fit for purpose. False hope is no hope at all.

[h2_heading]Are there any rules for a backup strategy?[/h2_heading] There are certainly things to remember here are three of the things I think about.

  1. If you do not have 3 seperatly stored copies of your data, you do not have any data
  2. If you do not regularly do test restores from backups, you do not have any backups
  3. The business leads when it comes to determining acceptable data loss / recovery times

So what do I mean by 3 separate copies. Well you have your production data which is one copy of your data but that’s not really a backup it’s your live system. A local backup to a backup drive would be considered one copy. Doing a flat file backup of these backup drives would be a second copy. This might be to disk on another SAN. The third copy would be having that SAN replicate to another SAN offsite or taking a copy and putting them on tape which are then stored offsite or by a third party. These 3 copies must be in place for every single days backups and at least one copy must be off site.

Regularly test restoring a copy of your systems from your backups. If you do not know that you can restore your backups and use the restored data then you might as well not take the backup in the first place. Taking a backup is just half the battle and can give you a false sense of security as you check the backup software status and it says everything is green so you believe it. Everything continues on until a production system requires a restore to roll back a failed change, when you find out the backup cannot be restored due to an error. I have been there and it is not a nice place to be. It resembles Azkaban and you feel like you are receiving a kiss from a Dementor. You end up having to go back to an even earlier backup and lose some data then try to recreate what has been lost. Do not end up in this position just test your restores its much easier.

Contrary to popular belief the DBA does not choose the data loss and recovery times of a system. Both the RPO (Recovery Point Objective) and RTO (Recovery Time Objective) must be decided by the BUSINESS. The DBA will then implement a backup strategy to enable the recovery inside both the RPO and RTO.

Hopefully after reading this you won’t have trouble sleeping. As always if you would like to discuss a SQL backup strategy contact us.

Apr 28

I want to move to the cloud but don’t know how

By Charles | Architecture

[h2_heading]I want to move to the cloud[/h2_heading]

Ever since the popularisation of tablets and smartphones the move to the cloud has seemed inevitable. Users want on demand access to all of their data across all of their devices at all times. Owning and even storing their data themselves has become less of an issue. Users do not mind it being hosted on public servers as long as it is secure and available from anywhere. These shifts in user behavior have allowed the rise of streaming services such as Netflix and Amazon which allow access to subscription services with vast catalogs of content to be viewed.

Business users have also been moving in the same direction. Long gone are the days when you hire an IT person to install, configure and manage an expensive Exchange server with all your users data stored on site. Cast into the abyss are the days of configuring, patching and troubleshooting issues which have zero ROI in those very early days when you need to make money the most.

There are a few obvious reasons for this move. The old approach has two single points of failure.

  1. The broadband line into your business (unless you have a backup line which is of course recommended)
  2. Only having one server increasing the chance of an outage as there is no server level redundancy

Businesses need resilient systems without the massive overheads of disaster recovery, redundancy and the time needed to maintain these systems. To have these highly available system on site you would need two Exchange servers which unsurprisingly would be twice the price. Which brings us to the benefits of the cloud.

[h3_heading]The cloud and the 3rd wave of computing[/h3_heading]

This shift to the cloud is also known as the 3rd wave of computing. The first being Mainframes and the second being client/server. The cloud delivers just about everything you need to get up and running quickly without having to worry about redundancy, disaster recovery or patching. You just say how many users you want to use the system and provision them. So where should I start?

The first step to move to the cloud for businesses is usually with email as part of Office 365. You get your email hosted by Microsoft in the cloud in almost the same way that web hosting companies have offered hosted email for decades. Some Office 365 subscriptions give you extras like the latest version of the Office suit of apps included. This can really help with licensing or SAM (Software Asset Management) which if not tracked can get out of hand very quickly.

For any business that does not want to worry about installing, configuring, patching, maintaining and troubleshooting server issues this is by far the best option.

  • All upfront hardware costs are removed so you no longer have to worry about buying outdated physical servers living in a server room on site.
  • Users can be added and removed as necessary allowing you flexibility to focus your resources on the services you need and use.
  • Monthly subscription payments improve business cash flow by spreading costs over the year.
  • Business is not dependent on your broadband connection. So if it does go down users can still get email on mobiles devices and from home.

The cloud does not end here it is only the start. You can have your CRM systems hosted in Microsoft Dynamics CRM, your project management stored in BaseCamp and your accounts and payroll stored in FreeAgent. The best thing is that most cloud suppliers build their APIs so you can connect all of these systems together giving you a full view of your business.

If you would like to discuss the advantages a move to the cloud can bring to your business and even have a free trial of Office 365 we would love to help you.

Apr 19

Which is best – DBA or use Managed SQL Service?

By Charles | Business

[h2_heading]Hire a DBA or use Managed SQL Service[/h2_heading]

Reaching the point of having to decide if you need to hire a DBA (Database Administrator) or use a managed SQL service shows two things. That you realise how important your data is to your business and that you are clued up to find the right balance between cost and quality of service needed.

There are pros and cons to both options but I hope to provide a balanced view to help you find out what will best serve your needs. All businesses are of course different so you will need to look at your own needs and judge which you think is a better fit for yourself. So hire a DBA or use Managed SQL Service lets start by taking a look at what they both give you.

[h3_heading]What do you get when you hire a DBA?[/h3_heading]

DBA’s are guardians of your data. We spend a lot of time saying no to requests to access a live system in order to protect the availability of your most precious applications.

  • Backups and restores – 40% of our time is spent managing backups and restores. Full differential and Log backups all need to be completed as required and checked daily. Seems like a lot and it is but we are talking about your data here. Your past and current customers including any issues they have experienced and what and when they bought from you. Your prospective customers and revenue pipeline. Literally your business in a backup file.
  • Security – we are gatekeepers, keeping out those that do not need access and allowing those that do the ability to view and report on KPI’s.
  • Maintenance – updating statistics and indexes, keeping applications running smoothly. Every application stores data and we make sure it takes less time to run a report whilst allowing multiple concurrent users to enter new orders and make changes.
  • Hours – obviously you get the DBA’s skills for approx. 40 hours a week plus any overtime for out of hours emergencies.
  • Cost – £35,000 to £55,000 outside of London depending on experience. Everyone can do the day to day stuff but a DBA really shows their worth in an emergency. You might think that emergencies shouldn’t happen and whilst that might be true, they still do, just as cars should not break down if you treat them well servers should not fail but we live in the real world and planning for redundancy does reach a cost vs benefit point where to save a large amount of money is worth the little bit of down time.
  • Training – there is an ongoing cost for keeping your staff trained and you will also need to have someone to cover when they are on training or on holiday. That could be around six weeks of cover needed every year so the cost will add up.
[h3_heading]What do you get when you use a managed SQL service?[/h3_heading]
  • All of the above delivered as a service but for a significantly reduced monthly cost.
  • No training costs that is the responsibility of the Managed SQL Service company.
  • No holiday cover needed this is also their responsibility.

One quick and dirty metric I use is do I have 40 hours of work for this individual to do every single week? If the answer to that is a big no then it is going to be more cost effective to outsource the work to a Managed SQL Service.

Do I have systems that require a very specialised skill set? If yes then a DBA with experience in that application might be more appropriate for your company even with the higher cost. This would depend more on how critical that application is to your business and what the cost of the downtime would add up to.

There is a bit of a grey area between which is best for either situation and which is more cost effective. It might simply come down to the current size of your business. If you have a small business with only one application using a SQL back end then it might not be worth hiring a full time DBA. You might be able to get away with a Junior DBA but they might need to juggle the database stuff with some other IT duties to justify the cost otherwise the Managed SQL Service is probably your best bet as you will have a team of experienced DBA’s to monitor, maintain and jump in to action in the case of an emergency.

If you are a very large company then the chances are you will need at least one DBA, probably two but in very large cases a team of three will be needed. Most likely comprised of a Junior DBA, a DBA and a Senior DBA. This covers you for holidays and training but will only work for companies that have large SQL estates such as several clusters or if you are using enterprise level features on your estate. You also get the benefit of the more experienced DBA’s training the Junior level DBA’s so in the event of someone retiring or leaving to go to another job role you still have trained people experienced with your systems.

Apr 08

SQL Server 2014 Developer Edition now FREE as in BEER

By Charles | Business

[h2_heading]What does SQL Server 2014 Developer Edition now ‘FREE as in BEER’ mean?[/h2_heading]

You like beer right? You know how good it tastes after a long day at work? If that beer was then FREE you might even enjoy it more. Well Microsoft have announced that SQL Server 2014 Developer Edition is now a free download for Visual Studio Dev Essentials members Prior to this pricing revolution it was required that each Dev copy of SQL Server have a developer license. One per concurrent user but that has changed making it much easier to license Test/Dev environments. Remember though that it is only free for dev and testing environments not production instances but all the same FREE is FREE.

Not enough?

Well when SQL Server 2016 is released the developer edition will also be FREE. How cool is that?

[h3_heading]Why am I excited by this?[/h3_heading]

Developers and the next gen of DBAs will be able to get hold of the Developer Edition which has all of the Enterprise Edition features allowing you to run, create, test and even break it. We all learn much more and much faster from breaking and trying things out than you ever will from just following a tutorial. This play time is actually the experience that businesses are looking for and you can gain it before you have even started your first job. The way I got my first job is exactly what I am recommending right now. Download, test and play with the products you are interested in so when you apply for your first job you can show experience with the tools you will be using day to day.

So download SQL Server 2014 Developer Edition, install it, update it, try out every feature then break it and fix it because that experience is what you will be paid for and it’s fun.

Apr 06

SQL Server 2005 is dead, Long live SQL Server

By Charles | Business

[h2_heading]On 12th April 2016 support for SQL 2005 ends[/h2_heading]

What does this mean exactly? Well Microsoft is basically saying if it’s broke, don’t call us. As it has been over a decade since the release of SQL Server 2005 and as all good things must come to an end, Microsoft have announced the end of support for SQL Server 2005. History will show that SQL Server 2005 was a massive step forward from SQL 2000, the previous version. It set records, improved security and came with the new DTS replacement called SSIS.

SQL 2005 was the first production version of SQL I worked with so there is a bit of sadness as I write this, although the longer you work with databases the more older versions you seem to find hidden under desks and forgotten in server rooms.

If you are still using SQL 2005 and many still are, it is your absolute last chance to move away from it (before support ends) to SQL 2014 or the soon to be released SQL 2016 which both offer much better data analytic options such as improved SSIS, SSRS and SSAS versions and features. Honestly the latest features we have available today offer more power and flexibility than ever before allowing for some exciting new applications to be written. Five years ago no one really knew about the cloud and today your SQL Server can connect and fail over into it at the click of a button.

Hopefully you have already moved away from SQL 2005 but if you have not it really is time to take a step into the present.

If you have an instance or a few of SQL 2005 and you need some help migrating to a later version of SQL Server, need help re-writing SSIS packages and SSRS reports or just want to know what the latest SQL Server versions offer Contact Us it’s what we like to discuss the most.

Dec 12

Why you need a Proper Object Naming Convention.

By Charles | T-SQL

[h2_heading]What’s in a name?[/h2_heading]

We have all been there. Called in to look at an application having performance issues. One of the first things we do is take a look through the objects to familiarize ourselves with what data is stored in which entities only to find a lot of objects with the word TEMP in them.

Having a good naming scheme might seem like a trivial issue but when you have hundreds of tables and a vast number of them include the word TEMP this can mean different things to different people. Some SQL developers might read TEMP as the table is a temporary backup copy of a current table, needed only for a short time. Some BI developers might read the word TEMP as the data in the table is temporary and used as a staging table but the table object itself is needed for the application to work.

[h2_heading]Proper Object Naming Convention[/h2_heading]

When naming objects try to think about who might end up looking at the applications back end and chose a name to explain if the object is a temporary backup copy that can be removed after a certain time or if you mean the data in the table is temporary. By adding the word backup and a date or a change request number you can easily identify if something is needed or not which is much easier than just adding TEMP and leaving the next person to figure out what it is that you mean. If an object really is just temporary remember to drop it after it has been used and is no longer needed.

So the next time you are creating and naming objects think about having a Proper Object Naming Convention that you and all the developers use and make sure it is enforced.

Nov 22

Giving back to the local community BRLSI youth activities

By Charles | Business

[h2_heading]BRLSI youth activities[/h2_heading]

Digital Samurai Tablet Donation

I have been volunteering at the BRLSI youth activities as a STEM (Science, Technology, Engineering, Maths) ambassador for over 2 years and I finally heard of an opportunity that would allow me to help more than giving up my Saturday afternoons. BRLSI were after a way to enhance the children’s recording of the experiments by using tablet PCs. Being in IT I thought to myself ‘a problem to solve’ as most IT professionals are actually problem solvers first and IT is just our tool to accomplish that goal.

I looked around and found a decent android tablet which would do what they needed but just handing over a load of new devices would not be particularly helpful. With a few calls and a contact at Mappfia I talked through what we wanted to achieve, what the current method of recording experiments involved and how we could go about delivering a useful app that would get the children to engage more with the process of recording what they think will happen, what actually happens and why it happens in that way.

With the added bonus of being able to take pictures of the apparatus used and how everything was setup the initial trial has been a good learning exercise for both the children and us as to how we all learn and interact with technology as well as problem solve in STEM subjects.

Everyone has read about the shortage of IT and STEM professionals in the industry today and that it is only going to get worse over the next 5 years if we do not tackle it now. I believe the majority of people fall into jobs that they did not expect they would be doing. In IT in particular new technologies can rise up without you knowing whilst you are still at school. Visualization on x64 was very new when I was doing my GCSE’s. A few years ago ‘the cloud’ would have never been heard.

By transferring our skills to the next generation at a younger age we can help them find a career that they will really enjoy earlier. Imagine knowing that maths lessons teaching statistics actually allow you to measure marketing campaigns instead of just learning it to pass an exam! This will allow children to have more time to perfect their chosen skill set and give them the chance to be even better than we are.

Those IT jobs boards asking for 3 years experience on SQL 2016 when the year is 2015 are a bit of a joke now but a graduate could quite easily have 10 years or programming experience by their 18th birthday making the market much more competitive and the industry much more exciting.

Mozart could play a few instruments by the age of 5. Children of 10 are frequently writing mobile apps found on the top 10 most downloaded apps on app stores. Imagine having found a subject you love and having an extra 10 years to learn about it. How much further do you think you could push yourself and your own subject with that extra experience and knowledge under your belt?

We believe that businesses should be offering more of their resources to help those that want to learn. By doing this locally the entire community will benefit and all communities across the country will have some way of benefiting from this partnership. We will certainly be looking at how much more we can help those who want to learn, get ahead and find what they want to spend their lives doing and we will certainly be looking forward to working with those that take a similar path to ourselves.

If you are in Bath or from the surrounding areas why not check out the BRLSI youth activities.

Sep 09

Removing hidden characters stored in SQL table

By Charles | T-SQL

Removing hidden characters

Have you ever received a request to write a query to get the data you need from a table in SSMS (SQL Server Management Studio) only for it to display differently when copied into excel? You probably know what I mean. Rows get split in the middle of a field which then shifts everything after it into the next field. Suddenly you have street names in your postcode field and created dates in your notes field.

You look back at your query and can see that it is displayed correctly in SSMS. You scroll through the whole dataset and see no issues at all. Did it become corrupt during the copy and paste. Does excel have an issue that needs a hotfix to be applied?

Is it broken?

The answer of course is no. There is nothing wrong with excel or SSMS they are both doing exactly what they were designed to do. The problem is in the data itself. Applications don’t just store the text you can see. In order to display it correctly they also store hidden characters which are used by the application to remember the formatting of that text. There are quite a few hidden characters that can cause issues. You can google for a complete list of them but the three that seem to cause the most problems are CHAR(9) which is TAB, CHAR(10) which is LF or Line Feed and CHAR(13) which is your CR or Carriage Return.

So what are these mysterious characters, what do they do and most importantly how can I remove them so that they don’t mess with my data export to excel? I’m glad you asked as I recently had to do the same task. As I have seen this issue before it was a quick fix and the BA (Business Analyst) was very pleased with a fast solution. So here it is.

The solution

SELECT REPLACE(REPLACE(REPLACE(fieldname, CHAR(13), ), CHAR(10), ),CHAR(9),”)

FROM YourTable

This simply replaces the TAB, CR and LF from that field with nothing. Essentially removing them from your dataset and allowing you to copy and paste the results into excel for your user.

Note you will need to do this for all the fields that are causing your data to shift fields in your dataset.

Aug 27

New Cloud Shell Account Update Available

By Charles | Affiliate Program

Mauris semper ut purus ut convallis. Curabitur erat neque, vestibulum vel volutpat efficitur, varius lobortis neque. Sed et nisl vel est convallis tincidunt sed at urna. Praesent ullamcorper accumsan nunc, at sodales massa rhoncus id. Morbi mattis bibendum augue id rutrum. Aliquam sit amet sagittis nisi. Praesent sit amet magna viverra, tempor erat vel, volutpat enim. Integer bibendum, turpis finibus fermentum blandit, orci neque tristique erat, eu rutrum tellus erat ut orci. Nam at gravida nulla. Mauris rhoncus magna eu tellus laoreet fringilla. Ut nec ligula sem. Vivamus a lectus at massa sodales fringilla ut ut magna. Donec ac ipsum id nisl tempor efficitur id id nisl. Pellentesque iaculis turpis sed ligula pretium, vel bibendum urna maximus. Suspendisse at pellentesque sem.

Phasellus iaculis convallis risus, id ullamcorper quam. Phasellus tincidunt hendrerit egestas. Cras feugiat semper nisl, eget suscipit erat scelerisque vitae. Suspendisse sagittis laoreet varius. Curabitur eu eleifend lorem. Etiam rutrum tellus eu tortor aliquet, ac fermentum diam aliquam. Morbi pharetra accumsan facilisis. Ut vel pellentesque ipsum. Pellentesque iaculis arcu id lacus euismod convallis. In enim ipsum, lacinia vitae purus ac, mattis pharetra erat.

Integer diam odio, pulvinar sit amet consequat ac, feugiat vel arcu. Nulla sagittis lacus quis ipsum tincidunt, ut lobortis ligula porttitor. Fusce justo enim, pulvinar at commodo non, commodo eu odio. Curabitur mauris tortor, accumsan quis dignissim at, volutpat sit amet lectus. Nunc nulla leo, ornare id elementum ut, sodales sed lorem. Morbi tincidunt arcu tortor, in lacinia dui varius sed. Aenean tellus ipsum, efficitur eget dui id, aliquam aliquam nisi. Vestibulum ullamcorper ante risus, vel venenatis neque semper id. Integer id sem suscipit, interdum massa a, eleifend erat. Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Donec nisl dolor, ultricies id eros vel, blandit condimentum dui. Nulla vel volutpat neque.

Donec eu enim vel nunc rhoncus elementum. Ut facilisis nulla sit amet mi placerat, ac finibus diam malesuada. Suspendisse non fermentum quam, et mattis lacus. Sed ut fringilla odio, non hendrerit magna. Vivamus leo ipsum, vehicula quis scelerisque ut, condimentum sed ante. In eu augue est. Quisque fermentum, eros vel tempus congue, nisi massa tempus neque, at vulputate magna leo nec ex. Nunc sit amet est nisl. Sed non cursus lectus. Nunc porttitor congue mattis. Duis sapien tortor, rhoncus ac iaculis in, tristique ac libero. Aenean ultrices turpis id congue dictum.