RSS

Mini Data Lab – SQL Server 2019 on Docker Part 3

Mini Data Lab – SQL Server 2019 on Docker Part 3

Back for more I see!! Well glad you’ve come back to round three where we are going to make a few more minor adjustments to our container and run our first shell script.

Make sure your container is running by executing the following command in your command prompt window: docker ps
If you don’t see the name of your container you can simple run this command docker start NAMEOFYOURCONTAINER in the command prompt.

Read the rest of this entry »
 

Tags: , , ,

Mini Data Lab – SQL Server 2019 on Docker Part 2

Mini Data Lab – SQL Server 2019 on Docker Part 2

So here we are back for Part 2 (I highly recommend you read Part 1 if you haven’t yet). In this post we are going to start configuring our containers and our SQL Instances to make them a little more functional and useful. In the first post, we really just created the containers with all the default settings. That means that all the storage is going to be inside the container. If it is deleted, the storage goes with it. So instead, to make the container more robust and upgradable, we are going to map some local storage on my host machine.

Let’s first take a look at the way I have my disk/folder structure laid out. Again, this is on my personal computer so it’s not a best practice for production and more suitable for development environments.

For each container, I’m creating a separate folder with the MSSQL paths that I need to put my databases, transaction log, and backup files on. Additionally, under the DockerMount folder I have a folder called sqldockershared (which I will put some shared content in later).

Read the rest of this entry »
 

Tags: , , ,

Mini Data Lab – SQL Server 2019 on Docker Part 1

Mini Data Lab – SQL Server 2019 on Docker Part 1

Recently, with the help of a colleague at work, I’ve started to dabble a little with containers. I had a customer that requested some specific code to be tested, and I realized that I didn’t have my own local instance of SQL running (always good to have a local one). I decided to try to make this process easier instead of going the traditional route of creating a Virtual Machine and also to help me learn a new technology. In these series of posts, I’m going to document my process of creating a Mini Data Lab for SQL Server on my desktop using Docker. It is intended to be for beginners and in no way is an article for best practices or production deployments.

Read the rest of this entry »
 

Tags: , , ,

Azure Security Group Automation [Guest Post]

Azure Security Group Automation [Guest Post]

Mission Statement

In this blog post by our guest author (@islamtg) we are going to discuss how to automatically update an Azure AD Security Group. This can be useful for something like Power BI where we have a security group that has specific permissions on a service. Through this will will try to address the following topics:

  • Eliminating the manual task of adding users to a security group.
  • Eliminating the process of manually cleaning up inactive users and invalid (or no longer valid) emails addresses.
  • Create an email clean up list that can be sent to the admins on a daily basis which can then be automated to clean up bad emails.
  • Creating an automated process for multiple security groups.
    • Create a read only security group on a particular service, a server admin security group.
    • Add users to a group that is assigned a specific license.
Read the rest of this entry »
 
Leave a comment

Posted by on December 16, 2020 in Database Administration, Other

 

Tags: , , , , , ,

The Power of DevOps and Deploying Power BI reports to different Environments [Guest Post]

The Power of DevOps and Deploying Power BI reports to different Environments [Guest Post]

We are very happy to publish our very first Guest Post by Islam El-Ghazali (@islamtg)!

Mission Statement

We are trying to create a source control versioning process for our Power BI Reports, to allow us to publish our reports and revert to previous versions.

  • Eliminate the step of saving multiple Power BI Reports on the machine as v1, v2, …, etc.
  • Allow us to do continuous integration and development for the reports on multiple workspaces, i.e. DEV, QA, PRD with minimal effort.
  • Use Azure DevOps to make this a more automated process while have control over what gets pushed to prod.
Read the rest of this entry »
 
3 Comments

Posted by on November 4, 2020 in power bi

 

Tags: , , , , , , ,

A COVID19 Story with Azure Databricks

A COVID19 Story with Azure Databricks

Before I get to my story, I would like to say that you can download the notebooks I created and ran in Azure Databricks here: https://github.com/thesqlpro/blog/tree/master/notebooks

The source of my data was: https://covidtracking.com/data

I chose not source my data directly from Maryland’s State Government site because the format was not easy to use. The official Maryland Government provided data basically has each day as a column and had the rows as Zip Codes — not as easy as the data provided from the site above. So there may be few discrepancies between the data on a day to day basis, but the totals are identical. You can read about their methodologies of retrieving data from various official State Government websites and the quality of each.

This post is in no way intended to attack anyone, be part of a political movement, promote any agendas political/financial/social, or support any causes out there except one: highlight how data and statistics can be used to tell stories. As data professionals, we need to give importance to the quality of data and the quality of data reporting. Basically this is a lesson in data visualization and telling stories with data.

Read the rest of this entry »
 
1 Comment

Posted by on June 3, 2020 in Other

 

Tags: , , , , , , , ,

Introduction to Azure Databricks Presentation – Video Recording

Introduction to Azure Databricks Presentation – Video Recording

I presented an introduction to Azure Databricks on May 22, 2020 to one of our local SQL Server User Groups here in the Washington DC area. It was done online due to the Covid19 restrictions on gatherings. The good that came out of doing it online was that it was recorded and published on YouTube. Here is the video, I hope you enjoy learning!

Code can be found at my new GitHub Repo:

https://github.com/thesqlpro/blog

 
Leave a comment

Posted by on May 28, 2020 in Other

 

Tags: , , , , , , , ,

Introduction to Azure Databricks


So…  I’ve been away from Blogging and Vlogging for a while. It’s been an interesting couple of years.  I’m trying to get back into things now and the first item of business is a Databricks Intro session that I will be presenting next week.

Hope to see you all there and hope to continue contributing to the Data Community.

Friday 22nd May 11:30 AM East US Time – Details below

NOVA SQL User Group – Weekly Friday Meetup Webinar

Friday, May 22, 2020, 11:30 AM

Online event
,

32 Members Attending

Hello Everyone! Our third Friday webinar will be about Azure Databricks. We will walk through the architecture, configuration and capabilities of Azure Data Bricks. Session title: An Overview of Azure Databricks https://azure.microsoft.com/en-us/services/databricks/ Sessions speaker: Ayman El-Ghazali, Microsoft Senior Cloud Solution Architect BIO/…

Check out this Meetup →

 
Leave a comment

Posted by on May 15, 2020 in Other

 

SQL Server 2016: A Comprehensive Introduction – Arabic


It has been over a year since I have blogged, I know… that’s a long time!  I have not retired, but rather focused on other parts of my life and career and I hope to get back to blogging.  One of my major focuses for the last year was a new course on SQL Server 2016 in Arabic, a training course that’s almost 15 hours long.  The team at MySkill4Afrika (Microsoft) and the Microsoft Virtual Academy have helped in the production of this course and I want to thank them for their tremendous efforts.

Previously, two courses were launched one for SQL 2012 and one for SQL 2014 BI via my personal YouTube Channel. With more than 150,000 views and more than 500,000 minutes watched we decided that it was worthwhile to start investing in a new course; hence the development for this latest series.

Just so you understand, all of the recording was done in Microsoft’s Technology Center in Malvern, PA (just outside of Philadelphia).  The content editing was done by myself as I am the only person with both the technical and linguistic knowledge in the group.  The rest of the editing/formatting/cleaning up, etc was done by our team in South Africa. The supervision was under our team in Egypt. The final walk-through was done by my brother, a SQL PFE also working for Microsoft.  It was truly a worldwide effort and I’m proud to present it to the SQL Community.

Click Here for the Content

Course Title “SQL Server 2016: A Comprehensive Introduction – Arabic

  1. An introduction to SQL Server 2016 – Arabic
  2. Foundations for Data: Install and Configuration – Arabic
  3. Foundations for Data: SQL Server Internals– Arabic
  4. Foundations for Data: Data Files, FileGroups and TempDB – Arabic
  5. Foundations for Data: Transaction Log Files – Arabic
  6. Development Foundations: Development Basics – Arabic
  7. Development Foundations: Indexing Fundamentals – Arabic
  8. Server Administration Foundations: Backup and Recovery Fundamentals – Arabic
  9. Server Administration Foundations: Security Architecture Basics – Arabic
  10. Advanced Administration Topics: Auditing & Transparent Data Encryption – Arabic
  11. Advanced Administration Topics: Row Level Security & Always Encrypted and Dynamic Data Masking – Arabic
  12. Advanced Administration Topics: AlwaysOn – Arabic
  13. Advanced Development Topics: Table Partitioning – Arabic
  14. Advanced Development Topics: Temporal Tables – Arabic
  15. Advanced Development Topics: Query Store – Arabic
  16. Advanced Development Topics: Locking and Concurrency – Arabic
  17. Advanced Development Topics: Columnstore Indexes – Arabic
  18. Advanced Development Topics: In-Memory OLTP – Arabic
  19. Advanced Development Topics: Stretch Database – Arabic
 
2 Comments

Posted by on April 26, 2017 in Other, SQL Server 2016, Videos

 

Tags:

PolyBase: I now Pronounce you SQL and Hadoop


 

Are you interested in Hadoop? With SQL Server 2016 just around the corner, a powerful new feature is being included called PolyBase.  To summarize what it is, just think about it as a marriage between SQL Server and Hadoop.  It is a way to store and query data on HDFS using SQL Server.

HDFS is a distributed file system that works differently than what we’re used to in the Windows OS side of things; the general principle is to use cheap commodity hardware that replicates data in order to account for availability and to prevent loss of data. With that in mind, it makes a great use case to store a lot of data cheaply for archiving purposes or can be used to store large quantities of data that been to be processed in large quantities as well.

For more information please visit: https://msdn.microsoft.com/en-us/library/mt143171.aspx

Now if you want to try it out for yourself, make sure you install the PolyBase Engine (from the SQL Server setup) and feel free to try the modified code sample below.

Read the rest of this entry »

 
6 Comments

Posted by on April 27, 2016 in Other, SQL Server 2016

 

Tags: , ,

SQL Server 2016: What’s the best new feature?


With SQL 2016 around the corner, and my blog without a post this year, I thought I would write about some of the new features.  However, I’m not sure which feature I like the most to talk about! So I’m going to summarize a few of the new features I have been exposed to and see which ones my readers like the best.

Stretch Database:

The idea is simple, what if you could “stretch” your database between your local on-premise server and Azure? That would be fantastic of course!  It’s a wonderful new feature in which you configure your database to push certain “cold” data into the Cloud (specifically Azure) while your “hot” data sits on your local on premise server.  This is an excellent way for archiving data.  But how do the users access the “cold” data? Simple, they just keep querying the same way the have been! From a logical perspective, this is very similar to Table Partitioning where the DB engine understands where the data is stored and the application does not have to change the way it behaves with the database.  Just keep in mind, querying older data will result in longer wait times for the data to be retrieved from Azure.

Always Encrypted:

In today’s world, security is a very big concern. And what is the gold that the technology thieves are after? Your DATA! Nothing else is more important than your data.  Now how does this feature differ from Transparent Data Encryption? Well in a lot of ways. First, TDE encrypts the entire database at rest. So if someone pulls the Database off the server, or it’s backup, they cannot access it without the proper certificate. If they are able to gain access to your server while the database is online, they can read all of the data right off of there as plain text.  Access is usually not a problem for expert hackers.

Now Always Encrypted works in a different way. Data is encrypted, column by column, using a certificate that is not stored inside the SQL Server Engine. Ok so you may think, well TDE encrypts the entire DB not just column by column. Yes, but it does not store the data encrypted as Always Encrypted does. Also, in most cases you do not need to encrypt the entire database, you just need to encrypt the sensitive data. Additionally, with SQL 2014 and above, you can can encrypt the backups without having the need for TDE. So when your backups are moved between locations, they are encrypted.

Let’s not get away from the main point here. Now with Always Encrypted, the certificate to Encrypt and Decrypt the data is used on the client side. So Encryption and Decryption is done outside the DB engine which allows it to scale a lot better.  Encryption can also be done deterministically instead of randomly.  Let me explain that a bit more.  If you have a unique ID like a national ID (let’s use 123456789 for example) you can query against that because the hash used to encrypt it is deterministic. So if you were to store the value twice in the same table (or a different table using the same certificate) you would get the same hash.  This improves query performance tremendously.  Deterministic encryption would not be wise for a column that has few values however, such as gender, because someone can quickly figure out the pattern in the data.

So to sum up this feature, those without access to the certificate cannot decrypt the data (including your DBAs). Data is encrypted even when the database is opened, unlike TDE, so if someone was to gain access to your system they would still need the certificate to decrypt the data. You can use different certificates for different columns, even within the same table, which can further increase security.

Dynamic Data Masking:

Please don’t confuse this with a security feature, because it really is not.  This feature is designed to display a “mask” over specific data. For example, for email it can display aXXXXXXX@XXX.com for the email. Now the data is not stored like that, and people with elevated privileges can see the data as clear text.  However, this is very easy to implement and does not require a lot of planning.  It can be used as a “quick fix” to potentially block data that you don’t want others to see on reports and webpages that they may have access to for which you are not aware.  It’s handy to keep not-so-sensitive data blocked, but is not a replacement for something like Always Encrypted.

Row Level Security:

This feature has been around in other database engines, but is new for SQL 2016.  It’s very simple, you programmatically allow for certain users to see certain data in a table.  It’s performance is better than using views or other types of logic to do row-by-row security since the engine is optimized to process the Row Level Security features.You can even block your admins from getting access to the data if they are not supposed to. A small disclaimer, they can always disable the functions that force the security and get by it so make sure you are auditing those actions.  This is a great way to allow for multi-tenancy within a database since you can exclude results to those that do not have permissions.

PolyBase:

I like to say it is a marriage between SQL and Hadoop.  PolyBase allows for you to query using your SQL Server Engine data that is stored in Hadoop.  It also allows for you to store data into Hadoop by creating External Tables and loading data into them.  It’s a great way for SQL Server to stretch out and touch HDFS while allowing for SQL professionals to access data on HDFS using regular T-SQL statements. It’s definitely a great bridge between the two worlds and will allow many data professionals to get closer to understanding and using other technologies outside the SQL Server landscape.

New SSRS 2016 Portal and Mobile Reports:

SSRS has finally gotten a face lift!  The portal looks much cleaner and now allows for two new types of objects to be placed. The first is the KPI (key performance indicator) which is basically just a tile on the Reports homepage that displays a value. It’s useful if you want to have specific information highlighted and readily available.  The other new object you can put up there is mobile reports.  With the acquisition of Datazen, Microsoft has basically added the functionality right into SSRS 2016. They look fantastic on a regular screen, tablet, and even phone.  It’s a great step forward in terms of making reports look great and easy to navigate.  The road map also has PowerBI reports being available to on-premise SSRS 2016 implementations. You can read all about the road map here: https://blogs.technet.microsoft.com/dataplatforminsider/2015/10/29/microsoft-business-intelligence-our-reporting-roadmap/

The Verdict?

My favorite feature is a battle between PolyBase and Always Encrypted. I love all the new features (even the ones I did not list here) but I feel like these two specifically push the boundaries of what a DBMS can do and I love it!

 
12 Comments

Posted by on April 19, 2016 in Other, SQL Server 2016

 

Tags:

Get valuable Virtual Log File (VLF) information for all your databases log files

Get valuable Virtual Log File (VLF) information for all your databases log files

I had this problem where I needed to gather Transaction Log information on multiple databases and check for valuable statistics on them. Running the command [DBCC Loginfo] brings back a number of rows for each Virtual Log File (VLF) in your Log File. It is really hard to do anything useful with that information on a larger scale. Each row returned gives you an estimate for the number of VLFs per Log File. Sure you could use the internal (and hidden) system stored procedure sp_msforeachdb to get the information for all database but it looks horrible. Here try it out for yourself before you read the rest of the post:


exec sp_MSforeachdb 'Use [?] select db_name(); DBCC LogInfo'

So why not make it better? That’s what I thought to myself, and I have recently been playing with storing DBCC command output to tables for analysis. I’ve put some together some code that allows you to capture the output of DBCC LogInfo into a Temp Table and then get some interesting information about the number of VLFs per database and other valuable information; see the comments for more information. Just by storing some of this data temporarily, I was able to write queries against it and discovered a major inconsistency in the size of my VLFs in a Log File that could potentially cause performance issues.

Feel free to create a permanent table for this data and run it on a regular basis to get an understanding of what your system is doing for troubleshooting. I also commented out the date field since I deemed it unnecessary, but if you’re looking for trending it maybe a good option to have that additional data.

Read the rest of this entry »

 

Tags: , , ,

SQL 2016 Row-Level Security Demo: Users with various clearance levels

SQL 2016 Row-Level Security Demo: Users with various clearance levels

SQL 2016 is right around the corner and one of the new security enhancements promised is Row-Level security for tables.  It’s a great new feature and pretty easy to implement.  I have created a simple demo that gives various users access to data based on specific clearance level to the data.  Feel free to modify the code and play around with it how to see fit.  There are many different ways to setup Row-Level security and this is just one scenario.  One of the things you’ll notice if you go through the scripts below is that the dbo user does not have access to the data after the the security policy is applied.  This is key for many environments where customers do not want administrators to have access to sensitive data.  Of course anyone with good coding skills and the proper permissions could circumvent that, but that’s why we put auditing measures in place 🙂

I’ve broken up the code into three sections.  The first is for setting up the database and permissions.  The second section creates the tables in the database and puts test data in them.  The third section is for the creation of the function and security policy which enables Row-Level Security. After creating the function and security policy, go back to the second section and re-run the select statements to see the security policy in action.  This demo was created on SQL 2016 CTP 2.2.  If you are interested in learning more about Row-Level Security and to see some other demos please refer to this webinar from PASS.

Read the rest of this entry »

 

Tags: , , ,

Working at Microsoft – A Year in Reflection

Working at Microsoft – A Year in Reflection

Last year after starting my new job with Microsoft as a Senior Consultant I wrote an article about my interviewing experience with many companies during my tenure at my last employer (Click here for article).  I realized it is now time to talk about my journey to Microsoft.

It has been a very busy year for me both on the personal and career front.  Having four growing boys is not an easy task to manager; I end up breaking fights regularly over who squirted who first with a water pistol.  Never-the-less it is exciting to be a father!  My year has been an interesting one of challenges and growth career wise. I spent a good number of years and several interviews in order to finally land a job at Microsoft. One of my interviews was with a SQL MCM which went decently well. Although I did not have all the answers for him, he appreciated my enthusiasm and method of trying to solve problems with the knowledge I have.

Read the rest of this entry »

 
1 Comment

Posted by on June 24, 2015 in Other

 

Tags:

SQL Server 2014 BI دورة تمهيدية شاملة في

SQL Server 2014 BI دورة تمهيدية شاملة في

الزملاء الأعزاء من العالم!
السلام عليكم و رحمه الله و بركاته
أقدم لكم دورة تمهيدية شاملة في SQL Server 2014 BI. وتقدم هذه الدورة التدريبية باللغة العربية، وتهدف إلى المساعدة فى التقديم للمفاهيم الأساسية للمهنيين المتخصصين فى قواعد البيانات عامه وبخاصه SQL Server BI في محاولة متواضعه منى لنشر هذه المعرفة والعلم بين اخوانى الناطقين باللغه العربية.
تهدف هذة الدورة التدريبية الى تلبيه فضول واثاره اهتمام المتخصصين فى مجال SQL Server BI وعلى وجه الخصوص  BI Developers كما ان هذة الدورة مبسطه الشرح خاصة فى الدروس الاولى لكى يسهل على المبتدئين فى مجال SQL Server BI متابعتها واضعا فى اعتبارى انه لا يوجد الا القليل من المواد فى هذا المجال باللغة العربية. فقررت بعون الله وتوفيقه ان ايسر هذا العلم بين ايديكم لعل الله ينفع به احد من اخوانى و اخواتى فى اي مكان فى العالم. اسال الله عز وجل ان تنال هذه الدروس اعجاب الجميع و خاصه المهتمين بمجال SQL Server BI ارجو من جميع الأخوة والأخوات ان يذكرونى فى صالح دعائهم
و لاتترددوا فى مراسلتى و ابداء ارائكم و مقترحاتكم البناءة لتطوير هذا العمل الخيرى باءذن الله تعالى

أخوكم
أيمن الغزالى
3/2015 فى واشنطن- الولايات المتحدة الأمريكية

Link to Videos

Dear Colleagues of the Database World!
I present to you a comprehensive introductory course in SQL Server
2014 BI. This course is presented in the Arabic language, and is
intended to introduce core concepts to Database Professionals that are
trying to acquire knowledge in SQL Server BI. The course is geared
towards those that aspire to become BI Developers, or those just
interesting in learning the basics of SQL Server BI. Since there is very
little material in Arabic, I decided to try to use my skills to bridge
the knowledge gap for my SQL Family that communicates in Arabic. I
hope you enjoy the classes and please feel free to share and leave
constructive feedback.

Thank you and good luck future SQL Server Professionals world wide!

Special thanks to my friends Mohamed Elsharkawy for his help and support with this production.

 
 

Tags: , , , , , , , , , , , ,

[SQL Snacks Video] Performance Tuning 103 – T-Logs and VLFs


Finally the arrival of Part 3 of my SQL Snack Pack on Performance Tuning! The series is dedicated to help beginners understand how to start performance tuning with SQL Server. The first video was about performing a baseline using the PAL tool.I would highly recommend you review that video as well as my SQL Snack on Instant File Initialization. Also, if you missed part two from yesterday you can review it here.

If you are still interested in learning more about Performance tuning with SQL Server, I will be giving an hour long presentation with the PASS DBA Fundamentals Virtual Chapter on January 6, 2015 (11 am Central Time/Noon Eastern Time).  For more information please visit http://dbafundamentals.sqlpass.org/ and join PASS for a great way to learn more about SQL Server.

Code from Demo below:

Read the rest of this entry »

 

Tags: , , , , , ,

[SQL Snacks Video] Performance Tuning 102 – Files, FileGroups, and TempDB

[SQL Snacks Video] Performance Tuning 102 – Files, FileGroups, and TempDB

Finally the arrival of Part 2 of my SQL Snack Pack on Performance Tuning! The series is dedicated to help beginners understand how to start performance tuning with SQL Server. The first video was about performing a baseline using the PAL tool.I would highly recommend you review that video as well as my SQL Snack on Instant File Initialization. This second video discusses the importance of properly sizing Data files, placement, and how the Proportional Fill-Algorithm works for data insertion. I’m hoping you get some last minute Performance tuning in before 2015 and so I will be posting the third video within the next 24 hours.

Code from Demo below:

Read the rest of this entry »

 

Tags: , , , , , ,

PASS Summit 2014 – It’s not you, it’s me

PASS Summit 2014 – It’s not you, it’s me

After an exciting week at the PASS Summit, I’m back home with my family.  This was my first ever PASS Summit and instead of writing about what was good or what was bad, I’ve decided to write about what I did and did not do and what I think I did right and what I can improve for my next Summit.  I’m also talking about my observations in general and some tips for those planning to attend next year. I hope that this serves as an inspiration to other attendees and non-attendees a like, I really did enjoy my time and gained a lot of insight while there.

Read the rest of this entry »

 
7 Comments

Posted by on November 10, 2014 in Other

 

Tags:

Bon Voyage fellow PASS Summit 2014 Attendees!

Bon Voyage fellow PASS Summit 2014 Attendees!

For the first time ever, I will be a PASS Summit Attendee! I’m very excited about going and I’ll be flying out tomorrow afternoon to attend a Pre-Con tomorrow.  If you would like to connect and meet while I’m there please feel free to Tweet out to me @TheSQLPro. That’s the fastest and easiest way to connect while there.  Safe travels to everyone and I hope to meet a lot of people at the PASS Summit this year. If you’re looking for some tips on what to do and take at the Summit watch this informative and entertaining video from Brent Ozar’s team.

See you there!

 
1 Comment

Posted by on November 2, 2014 in Other

 

Tags:

[SQL Snacks Video] Performance Tuning 101 – Baseline with PAL Tools

[SQL Snacks Video] Performance Tuning 101 – Baseline with PAL Tools

Welcome to Part 1 of my SQL Snack Pack on Performance Tuning! The series is dedicated to help beginners understand how to start performance tuning with SQL Server. This first video describes how to setup a baseline for your system using the PAL tools. It is essential to get a baseline before you start performance tuning so that you can determine how effective the efforts done in trying to tune your SQL Server have been.  The PAL tools at first look a little intimidating but they are really very easy to use and extremely helpful for performance analysis. Enjoy and happy baselining!

A special thanks to Edgardo Valdez for showing me the how to use this tool.

Link to download PAL Tools and Prerequisites: https://pal.codeplex.com/

 

Tags: , , , , , ,

 
%d bloggers like this: