Best products from r/SQLServer

• MDX Solutions by George Spofford and others - I consider this one a bible of sorts for MDX. Lots of explanations and in the MDX reference section LOTS of practical examples. A little dated as it's for 2005 but still very very useful. For $10 used on Amazon, don't pass it up.
  
• Practical MDX Queries by Art Tennick - A much smaller but very dense "learning by example" reference for MDX. I love this book. Used for $17 on Amazon.
  
• MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli - A really, really good book on MDX. Really great examples.
  
• Expert Cube Development with SSAS Multidimensional Models by Chris Webb and Marco Russo - I don't have this book but considering the authors I have to think it's an awesome book from a great publisher (Packt).
  
• There's also a great reference site called MDXpert which covers most if not all MDX functions and syntax although not in terribly great detail.
  
  The MDX language hasn't really changed all that much, so don't worry too much about getting an older reference for 2005/2008.
  
  You're not going to find an "all-in-one" reference for OLAP and MDX. You'll need more than one reference just because there's so much to cover. And my advice is don't assume that any SQL knowledge you have will translate to OLAP/MDX. It's a totally different animal. It took me years to master and I had 10+ years of experience with SQL Server and Oracle when I started learning OLAP/MDX....which didn't mean squat.

Welcome to the DBA club! Administrating both Oracle and SQL Server is quite the learning curve - good luck, and remember that being conservative is always the best bet until you're familiar with the platform you're on. Just don't lose any data, and you'll be fine :)

I haven't read any SQL Administration books for quite a while, but I've heard nothing but good things about Rod Colledge's book SQL Server 2008 Administration in Action, and Rod's a great, genuine guy as well.

Here's an interview with Rod discussing the book, and provides links to a couple of sample chapters.

SQL Server Books Online (the manual) is fantastic (always, always install it!), and, other than the SQL Server Internals series and quick Google searches, the only resource I use day-to-day (mostly because I'm guaranteed to have access to it, no matter where I am).

I recommend using the local version of Books Online. The web version is great if you want to search it via Google.

The only other resource I can suggest is a good mentor. If you have an experienced DBA at work, try and soak up anything they can tell you. If not, I highly recommend you find one, locally (at a user group or SQL Saturday), or online.

My blog is advertised on my stack overflow account and github, all three are circular and refer to each other. I've kept my reddit account separate on purpose for identifiable reasons.

I'm still toying with the idea of posting occasional blog posts or creating a reddit account to link to my other social medias to help build a base. At the moment, the blog is really intended for employers and myself and it is not based on trying to accumulate or direct web traffic.

So while I'm happy to share my information, I don't want to link it from this account.

> It's incredibly generous and secure of you to actually share your notes and progress.

The notes are usually pretty barebones. Here's an example of the kind of information in my google sheet:

5/22/18

• Read SQL Server Pro Internals
  
  • PG 200-240
    
• Worked on Stairway to XML Series on SQL Central
  
  • Stairways 3 and 4
    
    5/23/18
    
• Read Exam Ref 70-762 Developing SQL Databases
  
  • PG 300-324 (Need to find additional articles on temporal tables.)
    
• Temporal Database practice
  
  • 40 minutes in World Wide Importers
    
    Then if I have done any work / notations, I add them to a folder and upload them to my github.
    
    If you are interested in other examples of how I did things, let me know specifically and I can get you a scrubbed / masked version. Here's my list of recommended resources that appears in my 762 study guide though:
    
    Exam 70-762
    
    Exam policies and FAQ
    
    Course 20762B: Developing SQL Databases
    
    https://www.mssqltips.com/sqlservertip/4690/study-materials-for-developing-sql-databases-exam-70762/
    
    https://www.amazon.com/T-SQL-Fundamentals-3rd-Itzik-Ben-Gan/dp/150930200X
    
    https://www.amazon.com/T-SQL-Querying-Developer-Reference-Ben-Gan/dp/0735685045/ref=pd_lpo_sbs_14_t_0/138-3143892-2208566?_encoding=UTF8&psc=1&refRID=E5CA9HQ5WEXP7RPBE1B1
    
    https://www.amazon.com/Pro-Server-Internals-Dmitri-Korotkevitch/dp/1484219635/ref=sr_1_1?s=books&ie=UTF8&qid=1520887778&sr=1-1&keywords=sql+server+internal+pro

It's hard to say. A lot depends on where you are located. Another problem is that you're wearing many hats... but perhaps not very deeply. Again it's hard to say without getting into lots and lots of specifics of exactly what you're doing every day.

In smaller companies its common to see one person taking on multiple roles. In larger companies... not so much.

Part of your challenge will be to specialize. Do you want to do QA 5 days a week? Work with DB's 5 days a week? Manage software rollouts? Project management?

Besides deciding where you might want to focus will be the challenge of increasing your depth of skill in that particular area. This is necessary, especially for more senior jobs at much bigger companies (eg. you're probably not ready to manage the rollout of software to 1000's of desktops).

You might also benefit from The Rules of Work. I really liked it and it made a ton of sense to me.

Getting your resume in shape and start interviewing for new work might not be a bad idea. Even if you're not hired the experience of going on interviews and seeing what sort of questions are being asked would be useful. You've got a job so you're under less pressure... which should let you enjoy the process more. If your current company finds out you're interviewing just explain "you're exploring options for more responsibility".

Asking for more responsibility and more pay is also not a bad idea (with an eye to your company hiring a replacement for you). But it's a small company... what jobs/positions are going to be available? Getting some certifications might not be a bad idea (DBA or even go for a PMI project management one).

On project management... having a widely varied background can be a good foundation. You can talk to business folks and talk to technical folks and figure out what each is saying (and what either side is not hearing).

I found that the examples given in the data mining chapter of the Training Kit book for SQL Server 2008 BI (70-448) was pretty good.

Data mining in SQL server as I see it is about building models that taken some values can either predict an unknown value or find patterns within itself.

The way you make your model is you start by asking yourself what you want to either predict or what type of data you think might hold more information than you see by just looking at it.

The two different types are called directed data mining and undirected data mining.

The directed approach is usually what people will ask you to help them with because they already have an idea of what they want to solve.

OK so where to start, let's say for example you have some data from your factory that grinds coffee beans. Your factory has a system that has information about imports, deliveries, warehousing, production (packeting, weighting, other controls), your employees, your scheduling of deliveries and all that.

Now what you need to solve is a way to early detect problems with keeping your delivery on schedule.

So maybe your model will look like:

Given when our beans show up, how our production capability is at this time of day, and who works here at that time. What are the chances the product will be in the warehouse on time?

Next you have to pick an algorithm and place your information in it, the different algorithms need to be studied individually for one to understand the results.

Then when your model is done you train it using some of the information you already have.

Then you test your model by using it's training to predict the rest of the information you already have. You then look at how well the model predicted those values to see if the model is any good. If it's only slightly better then a blind guess that means that either your algorithm doesn't work for this problem or worse you are trying to solve something that can't be solved, maybe you need to add or remove information used or to use a bigger test sample?

OK so if you are still with me so far, let's assume you like your model and want to use it. You can deploy your model on your SSAS server and use it like a service or a function. I don't really remember what this step looks like but the idea is that you hold all the parameters and want to find out the probability that a delivery gets delayed (and later you want to know what to change in order to improve the odds of making a dead line).

SELECT odds_of_deliverydelay_using_awesome_Model_X(today,bob is working, beans are ok, yadayada)

Also, I googled around a bit and found this which looks promising:
http://msdn.microsoft.com/en-us/sql10r2byfbi-trainingcourse_sql10r2byfbi09_unit.aspx

Let me know how you are doing and if anything of what I just wrote made any sense. :)

I loved this book when i was first learning SQL. One downside is that it will sometimes give you the roundabout way to do something that might be correct for DB2 but SQL Server has built in functions and such that do the same thing. Now it's been over 10 years since i used it so i'm sure (i would hope) they've updated it to include that stuff.

But the nice part was it taught you how this stuff is happening which gives you a better understanding. When you can think like the DB engine you write better queries.

This is the real answer.

While everyone else is talking about which reporting tool to implement, you still need to learn about the architectural structure of the environment (extraction, staging, cleaning, conforming, loading) and then how to structure the final fact and dimension tables.

The great thing is that once you get your "back room" in order, the SQL structures should be able to plug into any sort of presentation layer you want to have, whether it's SSAS, SSRS, Tableau, or PowerBI.

I'd recommend The Kimball Group Reader, Relentlessly Practical... to give you a pretty good starting base on just about anything related to data warehousing.

I would also recommend asking for a data analyst. As a developer/engineer, you should be building structures and processes that will allow for a specialist to get the data the business needs. Instead of business clients bugging you incessantly forcing you to context switch, they will instead call upon the analyst to tweak the reports and conduct the deep-dives on questions they have.

EDIT: the cookbook parent might be talking about is Data Warehouse Design Solutions. They talk about common designs based on several common industries and different reporting levels as your CxO suite will view things at a higher level than your individual specialized department heads.

Moving your Access tables up should be pretty straightforward, Microsoft offers SQL Server Migration Assistant (SSMA) for download.

As for learning MS SQL administration, it is a life long process but these resources should get you up to speed quickly:

• Administering Microsoft SQL Server 2012/14 Databases
  
  
• SQL Server 2012/2014 Security Best Practices. Take this with a grain of salt: you will not implement every strategy in here.
  
  There's a slew of other resources, but these will cover the fundamentals the fastest. Since this is a new role for you, ask your company if they will provide training opportunities.
  Edit: fixed link

Grab a copy of dimensional modeling book from the Kimball group. This one deals with MS technology specifically.

To get a better understanding of data visualization theory and techniques I suggest reading all the material from Stephen Few and Edward Tufte. Few's book on dashboard is fantastic.

Network and meet people in the industry through events thrown by The Data Warehouse Institute. Download free datasets online and use those to gain insight into subjects you're interested in as well as to practice the skills you learn as you play around with SQL Server.

I can add more later but I have to go for the day. Feel free to post more specific questions in this thread and I'll try to answer.

I really enjoyed the CBT Nuggets videos. I happened upon them for free, but they offer $99 a month subscriptions which isn't so bad if you have your own lab to work with.

http://www.cbtnuggets.com/it-training-videos/microsoft

Just search for SQL Server 2012

---
Microsoft SQL Server 2012 T-SQL Fundamentals is awesome too, and possible more helpful that the above info. http://www.amazon.com/Microsoft-Server-2012-T-SQL-Fundamentals/dp/0735658145

You might be able to find the above book in PDF form if you look hard enough...

Hello!

I've been doing a lot of searching for this answer as well and what seems to be the most common answer is the official book.

Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012 (MCSA) (Microsoft Press Training Kit) https://www.amazon.com/dp/0735666059/ref=cm_sw_r_cp_api_FFPuzb4B1KYF5

It's dry but apparently it works

I personally have been using the book and a course on udemy. The course is specifically for the SQL SERVER certification.

Also another common answer seems to be to use multiple resources. Hope this helps some. Good luck

If you do all the practice examples and download the adventure works db, this book will help you achieve your goal. DO NOT, however, rely on it to pass the 70-461 exam because you will not pass. This book boosted my skill set from advanced beginner to high intermediate, but left me woefully unprepared for the Microsoft certified exam. Good luck!

http://www.amazon.com/Training-70-461-Querying-Microsoft-Server/dp/0735666059?ie=UTF8&keywords=sql%20server%20exam%2070%20461&qid=1462299171&ref_=sr_1_1&sr=8-1

First, you need to baseline performance in your database. If that means looking at table level metrics, so be it. You can't identify spikes without one.

Secondly, I'd recommend some reading. I'm a fan of Query Performance Distilled. The author provides a step by step decision tree on resolving performance issues. And if you're nice, he might even answer a few of your questions as he posts here often.

For the language aspect of SQL this book is great:

http://www.amazon.com/Microsoft-Server-Fundamentals-Developer-Reference/dp/0735658145/ref=sr_1_1?s=books&ie=UTF8&qid=1452317305&sr=1-1&keywords=tsql+fundamentals

I'm a fan of SQLZOO.com as well

I ran into a similar conundrum. If you read the 70-461 or T-SQL Fundamentals, they have practice problems that are related. The 70-761 does not contain practice testing material.

Apart from that, it's recommended to do the more official practice tests if you decide you need one. They are supposed to be more difficult than the real test, but I do not have any experience taking the practice tests. I am thinking of trying the 762 practice test though.

I did use one other resource that was a practice test and I'll list it below, but here's a copy from my site that lists my favorite resources from when I studied for my 761.

My favorite resources:

https://www.cathrinewilhelmsen.net/2015/01/28/preparing-for-and-taking-exam-70-461-querying-microsoft-sql-server-2012/

https://www.mssqltips.com/sqlservertip/4644/sql-server-exam-70761-study-material-for-querying-data-with-transactsql/

https://www.amazon.com/T-SQL-Fundamentals-3rd-Itzik-Ben-Gan/dp/150930200X

https://www.amazon.com/Training-70-461-Querying-Microsoft-Server/dp/0735666059/ref=sr_1_1?ie=UTF8&qid=1513628070&sr=8-1&keywords=70%3D461

https://www.amazon.com/Exam-70-761-Querying-Data-Transact-SQL/dp/1509304339/ref=sr_1_1?s=books&ie=UTF8&qid=1513628083&sr=1-1&keywords=70-761

http://www.accelerated-ideas.com/70761-practice-test-questions.aspx#.WeeTWVtSypo

https://www.mssqltips.com/sqlservertip/4015/introducing-json-for-sql-server-2016/

https://www.mssqltips.com/sqlservertip/4073/sql-server-2016-advanced-json-techniques--part-1/

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server

https://www.mssqltips.com/sqlservertip/3680/introduction-to-sql-server-2016-temporal-tables/

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql

https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

This book Microsoft SQL Server 2012 T-SQL Fundamentals (Developer Reference) was really helpful in my learning tsql from scratch a few years back. I recommend it to anyone asking about a good book to learn sql

Edit: I'll add that it's a great starting point for the fundamentals, but it's a never ending learning game. Source: I've been a database engineer for 4 years and still learn new things all the time

Git gud. Learn to speak his language. Read a couple books to learn financial concepts and then you might be able to find some common ground and interact with each other effectively. Personally I found this book to be useful when I was in this exact scenario a couple years back,

https://www.amazon.com/Financial-Intelligence-Revised-Managers-Knowing/dp/1422144119/ref=pd_cp_14_1?pd_rd_w=eSxOL&pf_rd_p=ef4dc990-a9ca-4945-ae0b-f8d549198ed6&pf_rd_r=PJSRYREQ9ZE7C3328JP8&pd_rd_r=55b1bad0-e574-4bce-ac21-917b7ebdcc72&pd_rd_wg=5JvDM&pd_rd_i=1422144119&psc=1&refRID=PJSRYREQ9ZE7C3328JP8

https://www.amazon.com/Training-70-461-Querying-Microsoft-Server/dp/0735666059

Even if you don't take the test this book is well written and it exposes you to useful things you may not otherwise stumble across - like windowing functions.

the MS 462 book, ick.

You absolutely MUST read these books:


1 - Microsoft SQL Server 2012 T-SQL Fundamentals


2 - Inside Microsoft SQL Server 2008 T-SQL Querying

Happy learnig!

Itzik wrote the book

It's good.

That's basically just a list of predicates.

Pick up an Itzik Ben-Gan book, that should help immensely.

This is great, but a little dense as a beginner

T-SQL Querying (Developer Reference (Paperback)) https://www.amazon.co.uk/dp/0735685045/

You can check following blog post which has various topics on SQL Server 2012 categorized at one place: http://sqlwithmanoj.wordpress.com/denali-2012/

Also you can check "Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012" book: http://www.amazon.com/Training-Kit-Exam-70-461-Microsoft/dp/0735666059?tag=651998669-20

older version, but concepts hold over: https://www.amazon.com/SQL-Server-2008-Administration-Action/dp/193398872X

Are you talking about the CD from here?