I Need Help Regarding Database Designing And Tuning

Do back-end engineers need to be experts on database design? How do you handle database performance issues when database grows very large?

Physical table design, yes being an expert would be ideal.Getting your physical design correct out of the gate is critical for both data integrity and performance.As your database grows, by having your physical design optimized it will pay dividends down the line. You should be analyzing your database queries and overall performance from the onset of your project and first release. Try to find poorly performing queries before you get yourself trapped in a corner with a large database because now the problem has been magnified.Here are a few tips:Make sure your foreign keys are indexed.Only index additional column(s) as necessary as additional indexes will slow down performance on inserts, deletes, and updates.Don’t use composite primary keys.Learn how to read explain plans.Use a profiling tool that will filter out the poorest performing queries and then tune them.Stored procedures will perform the best however I stay away from them. I only use when I need to (Example: filtering over a hierarchical relationship)Use summary tables as opposed to doing summarization on the fly.Get your software as close to the database as you can from a network perspective.Use the right database vendor. Oracle will perform better than the others when it comes to large databases however for a steep price.Learn how to implement paging in your applications. Easier with thick clients than with web apps. Get your overall solution for this down sooner rather than later.See if you can implement database partitioning.As a full stack developer, I actually hardly ever tune any queries for myself. I just know. This is why it is a benefit to be an expert in database design as it saves me time. I do however find myself assisting others and educating them.

How do I speed up or tune up Microsoft SQL Server 2014 database for better performance?

How big is your budget? How much faster does it have to be?This is basically a very simple question with a very complex answer.What is running slow? A specific query? Multiple queries? All queries?What’s the usage patterns (I have a client where 80–90% of the usage is in 1–2 months of the year. Designing for that is different than if the same load is spread across 12 months.)Generally I’d look at the most used queries (not the slowest running ones necessarily, because if the slowest query takes 10 minutes to run, but is run once a quarter… that’s ok. But if the most used query is run 1 million times a day and takes 3s to run and you can cut that to .3s, you’ll get a MUCH larger return on your time investment.)Once you know your most used queries (google for examples on the DMVs to look for), start to look at your indices.Don’t make the mistake of adding indices willy-nilly. That can actually slow things down (i.e. if you’re doing a lot more inserts than reads, more indices may cause far too much I/O).Sometimes just memory can help too.Or a faster CPU.Or faster disks.Basically there’s no one right answer.And if you want things 2x as fast, it might cost you say $1000 to hire a consultant such as myself. If you want it 10x as fast.. you may want to pay $30K.If you want things 100x as fast, might need to pay $100K in consulting and spend $250K in hardware.It all depends.

I want to be a database engineer. What skills should I have?

I'm coming a bit late to the party here.  The other answers are all pretty good.  I think I can add something because I have worked with Oracle for 20+ years... and the title with my current employer is Senior Database Engineer.  First though -- a story.Many years (and several employers) back, the VP of the company I worked with asked one of the developers to do something. The developer responded that the task wasn't in his job description. The VP told him that this wasn't a problem, and asked the developer to supply a list what he felt his job duties included. The programmer did so and handed it to the VP a few days later.  The VP read it and told the developer it looked good, but needed one addition. At the bottom of the list, he added "And other duties as assigned". Then he told the developer to go do the task he asked him to do a few days earlier.As a couple of the previous posters have noted, the term "Database Engineer" is so broad that it means little. It is the ultimate 'And other database duties as assigned' job title.  When people ask me what I do, I tell them that I am a Oracle DBA/Developer. There are actual tasks associated with both DBAs and Developers.  However, for the last couple of weeks, I've been doing work that's actually more suited to a Data Analyst... basically because there are millions of dollars riding on locating discrepancies between two databases (neither of which are under my control) and getting them corrected.  I also work as a Data Architect, I sometimes do System Admin work on the server running the database, provide information for contract proposals that require information about our IT/IS capabilities, etc.I would not put it past an organization to have an entry-level position with a title of Database Engineer... but it's not the norm. Generally entry level positions will be for a single skill area: developer, DBA, analyst, etc. Expecting entry level candidates to have database knowledge across multiple job roles is not realistic. If you are starting a career, I'd recommend picking one role. As you gain job experience with that role, learn as much as you can about other the tasks associated with other database roles.