Skip to content

Category: SQL Server and Azure

These posts are about the Microsoft Data Platform, including SQL Server and Azure products and services. Topics include T-SQL, SQL Server Integration Services (SSIS), SQL Server Data Tools (SSDT), SQL Server Management Studio (SSMS), Azure Data Studio (ADS), Azure Data Factory (ADF), Azure Databricks, Azure SQL Databases, Azure SQL Data Warehouse and Azure Data Lake Storage.

Preparing for and taking exam 70-461 (Querying Microsoft SQL Server 2012)

Microsoft Certified Solutions Associate (MCSA): SQL ServerOne of my goals is to become a Microsoft Certified Solutions Expert: Business Intelligence. I decided to start from scratch and take the three exams required for Microsoft Certified Solutions Associate (MCSA): SQL Server 2012 to learn as much as possible. It has taken me a lot longer than I thought it would and I’m not there yet, but I’m one step closer to my goal: I passed the 70-461 exam (Querying Microsoft SQL Server 2012) in January :)

Preparing for the exam
My plan was to work my way through each chapter in the Training Kit book. I read each chapter and answered the quick check questions, lesson review questions, and case scenarios out loud to myself. I did all the exercises, but none of the suggested practices. Instead, I tried to use what I had learned at work. For example, I rewrote some of our queries to use the new window functions, and it was easier to understand how FOR XML worked when I queried data I was already familiar with.

Continue reading →

Something New Learned (T-SQL Tuesday #60)

T-SQL TuesdayT-SQL Tuesday #60 is hosted by Chris Yates (@YatesSQL) and is about sharing something new you’ve learned recently.

For the past five years I’ve worked as a data warehouse and business intelligence developer. My job is all about providing high-quality data, visualizations, and make sure end users have the right data and reports to do their jobs. I don’t have to worry about performance tuning down to milliseconds, I don’t need to know anything about hardware, and administrators take care of backups and security. I don’t have to or need to know any of these things to do my job, and that is exactly why I want to learn about them.

I’ve spent five years learning and there is still so much more to learn about business intelligence. However, this year I’ve started attending sessions on topics I know close to nothing about, and it really is making me a better developer.

At SQLSaturday #337 Oregon I attended a session by Argenis Fernandez on Securing Your SQL Server Instance Without Changing Any Code. In theory it’s not relevant for me at all, and I have to admit that I hadn’t even heard about some of the things presented before that session. But do you know what I had heard about? Vendor software that requires SA accounts to run. And what did I learn? Rename the SA account, create a new account named SA, and voila! The vendor software can now use the “SA account” that you’re in total control of. It might be 101 material for many, but for me it was something new and useful.

At Redgate SQL in the City I attended another security session by Bob Pusateri, this time about Passive Security for Hostile Environments. Again, something that in theory was not really relevant for me, but it was probably the session I learned the most from! I knew about triggers, auditing and extended events, but it was the first time I saw a demo of event notifications. It was really useful to see the features compared and learn more about when they should and shouldn’t be used.

While I still feel like I have so much more to learn, I also realized that I actually know quite a lot that I can teach to others. And so do you! Share something you’ve learned recently, something you’ve known for ages, or something you think “everyone knows”. There’s always someone out there like me who will learn something from you :)

Starting my journey to MCSE: Business Intelligence

MCSA Training KitsOne of my goals for 2014/2015 is to learn enough to become a Microsoft Certified Solutions Expert (MCSE): Business Intelligence.

I can upgrade my MCITP certification, but I decided to start from scratch with the SQL Server 2012 exams because it’s a great way for me to learn new features while my company is still using SQL Server 2008. I’m also sure it will be very useful for me to step out of my comfort zone of data warehouse development / architecture and dive into the scary world of database administration.

The Microsoft Certified Solutions Associate (MCSA): SQL Server 2012 will remain in market without any alterations and passing the three required exams will be my goal this year. The MCSE exams will be updated with SQL Server 2014 topics and I aim to pass them later this year or early next year.

The three Training Kit books finally arrived yesterday: Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012, Training Kit (Exam 70-462): Administering Microsoft SQL Server 2012 Databases and Training Kit (Exam 70-463): Implementing a Data Warehouse with Microsoft SQL Server 2012.

Reviews range from “useless” to “excellent”. Based on my experience I can’t rely on these books alone, but hopefully they’ll be a great starting point and will help me identify what I need to work on. My own reviews will be posted as I finish the books and take the exams.

My plan is simply to work my way through each chapter and write down useful suggestions and improvements for my company. It’s easier for me to learn when I can relate the content to my work. I’m most excited about learning more about database administration since that’s not something I do every day, and most curious about the data warehouse part since I’m kind of already supposed to know my way around dimensional modeling and SSIS :)

I passed exam 70-461 (Querying Microsoft SQL Server 2012) in January 2015.
I passed exam 70-463 (Implementing a Data Warehouse with Microsoft SQL Server 2012) in April 2015.
I passed exam 70-462 (Administering Microsoft SQL Server 2012 Databases) in May 2015.

Have you recently taken any of these exams? What is your job and what did you find most challenging?

Business Intelligence Fagdag & SQL Server 2012 Lansering

Quality Hotel 33 huset i dag over 300 personer med minst én felles interesse: Business Intelligence. Årets fagdag ble kombinert med norsk lansering av SQL Server 2012, med presentasjoner av blant annet BI-guru Rafal Lukawiecki, Microsoft og Platon.

Microsoft, Platon, Evry, Avanade, Affecto, Inmeta Crayon, Itera Consulting, Nextbridge, Glasspaper og ikke minst Capgemini var tilstede med stands, og ansatte fra store og små bedrifter hadde møtt opp for faglig påfyll.

Dagen startet med en kikk på eksisterende og kommende trender innen Business Intelligence: Big Data, Cloud, Mobile BI og Self-Service. Petter Stordalen fortalte om BI-visjonen i Choice Hotels og hvordan Microsoft-teknologier brukes for å monitorere sosiale medier og fintune strategier fra dag til dag. Videre fortsatte sesjonene i to spor, overordnet Business Intelligence og et teknisk dypdykk i De Fantastiske 12 i SQL Server 2012.

SQL Server 2012 Fantastic 12

De Fantastiske 12 omhandlet de tre hovedtemaene Mission Critical Confidence, Breakthrough Insights og Cloud on Your Terms.

Brukere stiller stadig større krav til bedre tilgjengelighet, sikkerhet og ytelse. AlwaysOn er et enormt skritt i riktig retning, med enklere failover, clustering og sikkerhet. ColumnStore Indexes kan forbedre ytelsen dramatisk og gjøre spørringer lynraske. (Aggregering av 12 millioner rader på under 2 sekunder er imponerende!) PowerPivot og Power View gjør data enkelt tilgjengelig for analyser.

I SQL Server 2012 er Master Data Services og Data Quality Services integrert, og det har aldri før vært så enkelt å vedlikeholde og kvalitetssikre data. Data administreres av sluttbrukere gjennom kjente grensesnitt som Excel og webapplikasjoner, og ETL-utviklere kan enkelt sjekke og laste data gjennom nye komponenter i SQL Server Integration Services (SSIS).

Hybrid IT tok for seg sømløs integrering og synkronisering mellom løsninger og data lokalt, i private skyer og i den offentlige skyen.

Til slutt ble SQL Server Data Tools presentert som et integrert verktøy for DBAer og utviklere. Det har aldri før vært så enkelt å administrere databaseprosjekter! Kodekontroll, kodekvalitet, utrulling, refaktorering, oppgradering, sporbarhet og versjonering er nå integrert i ett verktøy – som på toppen av det hele er gratis.

I tillegg til faglig påfyll ble det servert en nydelig lunsj og middag – og for mange var dagens to minste høydepunkter self-served softis og popkorn ;)