الزملاء الأعزاء من العالم!
السلام عليكم و رحمه الله و بركاته
أقدم لكم دورة تمهيدية شاملة في SQL Server 2014 BI. وتقدم هذه الدورة التدريبية باللغة العربية، وتهدف إلى المساعدة فى التقديم للمفاهيم الأساسية للمهنيين المتخصصين فى قواعد البيانات عامه وبخاصه SQL Server BI في محاولة متواضعه منى لنشر هذه المعرفة والعلم بين اخوانى الناطقين باللغه العربية.
تهدف هذة الدورة التدريبية الى تلبيه فضول واثاره اهتمام المتخصصين فى مجال SQL Server BI وعلى وجه الخصوص BI Developers كما ان هذة الدورة مبسطه الشرح خاصة فى الدروس الاولى لكى يسهل على المبتدئين فى مجال SQL Server BI متابعتها واضعا فى اعتبارى انه لا يوجد الا القليل من المواد فى هذا المجال باللغة العربية. فقررت بعون الله وتوفيقه ان ايسر هذا العلم بين ايديكم لعل الله ينفع به احد من اخوانى و اخواتى فى اي مكان فى العالم. اسال الله عز وجل ان تنال هذه الدروس اعجاب الجميع و خاصه المهتمين بمجال SQL Server BI ارجو من جميع الأخوة والأخوات ان يذكرونى فى صالح دعائهم
و لاتترددوا فى مراسلتى و ابداء ارائكم و مقترحاتكم البناءة لتطوير هذا العمل الخيرى باءذن الله تعالى
أخوكم
أيمن الغزالى
3/2015 فى واشنطن- الولايات المتحدة الأمريكية
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.
Welcome back for part 3 of my SQL Snack Pack on Table Partitioning! If you have not watched the first two videos, I would highly encourage you to do so.
I hope you’re hungry for another SQL Snack! In fact, this will be one of a series of snacks (dare I call it a SQL Snack pack?). Table partitioning is a fantastic feature that is easy to learn and can significantly improve your OLTP and Data-warehouse environments. It can be a little intimidating because it is tricky to get started with, but once you get the basics down you’ll realize it’s pretty straight forward and a very useful feature to have. I will be providing the code and outline for each of the SQL Snacks related to table partitioning so that you have a chance to practice on your own. Happy partitioning!
This is a special edition for SQL Snacks. It is one (of many hopefully) SQL Snacks that has been recorded with the intention of fulfilling a request by a SQL community member looking to learn more about SQL Server. It is straight forward, very basic, beginner level information about Joins with a few examples.
الزملاء الأعزاء من العالم!
السلام عليكم و رحمه الله و بركاته
أقدم لكم دورة تمهيدية شاملة في SQL Server 2012. وتقدم هذه الدورة التدريبية باللغة العربية، وتهدف إلى المساعدة فى التقديم للمفاهيم الأساسية للمهنيين المتخصصين فى قواعد البيانات عامه وبخاصه SQL Server في محاولة متواضعه منى لنشر هذه المعرفة والعلم بين اخوانى الناطقين باللغه العربية.
تهدف هذة الدورة التدريبية الى تلبيه فضول واثاره اهتمام المتخصصين فى مجال SQL Server وعلى وجه الخصوص DBAs و كذلك Developers كما ان هذة الدورة مبسطه الشرح خاصة فى الدروس الاولى لكى يسهل على المبتدئين فى مجال SQL Server متابعتها واضعا فى اعتبارى انه لا يوجد الا القليل من المواد فى هذا المجال باللغة العربية. فقررت بعون الله وتوفيقه ان ايسر هذا العلم بين ايديكم لعل الله ينفع به احد من اخوانى و اخواتى فى اي مكان فى العالم. اسال الله عز وجل ان تنال هذه الدروس اعجاب الجميع و خاصه المهتمين بمجال SQL Server ارجو من جميع الأخوة والأخوات ان يذكرونى فى صالح دعائهم
و لاتترددوا فى مراسلتى و ابداء ارائكم و مقترحاتكم البناءة لتطوير هذا العمل الخيرى باءذن الله تعالى
أخوكم
أيمن الغزالى
ديسمبر2013 فى واشنطن- الولايات المتحدة الأمريكية
Dear Colleagues of the Database World!
I present to you a comprehensive introductory course in SQL Server
2012. 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. The course is geared
towards those that aspire to become DBAs, Developers, or those just
interesting in learning the basics of SQL Server. 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, Jihad Abouhatab and my brother Islam El-Ghazali for their help and support with this production.
First let’s get things straight, this post is not an encouragement for anyone to spend more time with his/her Database or with Database Snapshots for that matter. If you’re married to your Database, that’s a different story all together and I don’t think I could help you. As database professionals we are always looking to improve the quality of our code and data. This post is intended to show an easy way to use Database Snapshots in order to test new code or change data and revert changes back quickly and easily.
On July 10th 2013, I gave a presentation with the Professional Association for SQL Server (PASS) DBA Fundamentals Virtual Chapter. The topic was Indexing Fundamentals and there were some questions that we did not have time to answer. I have combined similar questions and given one answer to cover the specific topic.
Abstract:
Indexing is an integral part of SQL Performance and Architecture. In this presentation we will cover the basics of Clustered and Non-Clustered Index structure and function. Additionally, we will discuss the B-Tree structure, index density (fill factor), page splits, and performance considerations. Finally, there will be a brief discussion of index maintenance.
**This Session has been rescheduled for July 10th @ Noon EST (16:00 UTC)**
On July 10th 2013 @ Noon EST (16:00 UTC) I will be giving a presentation with the Professional Association for SQL Server (PASS) DBA Fundamentals Virtual Chapter. Entrance is free as always and details can be found at http://fundamentals.sqlpass.org.
Code, Slides, and other information about the presentation will be under the Presentations section of this site afterward. Hope to see you there!
Abstract: Indexing is an integral part of SQL Performance and Architecture. In this presentation we will cover the basics of Clustered and Non-Clustered Index structure and function. Additionally, we will discuss the B-Tree structure, index density (fill factor), page splits, and performance considerations. Finally, there will be a brief discussion of index maintenance.
Ever wanted to know the reason behind the Database color scheme in SQL Server Management Studio?
I have always wondered why all the Databases in management studio were Yellow (Online), Grey (Read-Only), Lighter Yellow with text next to it (something like Restoring), or Yellow with a Red Icon (Offline). Have you ever wondered why there aren’t any more useful colors like Red for when a Transaction Log is Full or Purple when your Statistics are Outdated. I have a theory on this that I have entitled “The Theory that you have to actually work to call yourself a DBA.”
A problem I face often is not knowing the size of a new table that I’m creating. I put some basic code together that pulls the column information from INFORMATION_SCHEMA.COLUMNS and with some basic math gets the approximate size of the table based on the estimated number of rows.
Finally the epic conclusion to my three part series about listing server and database roles. In this post I will show you how to create SSRS report to use for regular tracking or auditing purposes. This is a much easier and cleaner way of looking at the data and you can provide it to other team members or even your manager.
Building upon the code in my previous post, I will be retrieving permissions information from remote servers that I have access to. This was a little more difficult to accomplish because security is different on different servers and I did not want to create Linked Servers with escalated permissions. Read only SQL users was an option, however there is no good way to make sure that these users will have read permissions on all current and future databases. It took a while to over come these challenges but I got it working; obviously since I have this blog post.
It has been a long morning for me. It was supposed to be a standard run for our quarterly data aggregations but one of the data import SSIS packages kept failing. The problem was a conversion problem from varchar type to float and I kept getting the following error:
Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to float.
However, my data was numeric I checked it many times… or was it really 100% numeric?
I’ve run into a road block. As a Microsoft Certified Trainer, I have all the software I need to get hands on with SQL Server. I even have Virtual Hard Drives (VHDs) that I can download directly from Microsoft; there are even publicly available ones HERE. My problem is on the hardware end. Since I’m not a millionaire that can throw money around and buy an entirely new server to put Windows Server on with SQL Server, I had to find another way… and I did 🙂
One big question I have gotten often is “I need data, what kind of reports do you have?” It is a valid question from a user, especially with a server that may gave hundreds of reports. The solution? Create a Reports Catalog Report. I have provided the simple instructions and code to create this report in 15 minutes or less.
A part of every DBA’s job is to justify all those disk space requests. The phrases “I just need it” or “Trust me this database is going to grow one terabyte this year” might not convince your boss.
Many people are under the impression that disk space is cheap since they base that notion off of the shell shocker they saw on NewEgg or the weekly circular from Best Buy. If you are using consumer SATA drives from retail outlets for your production databases please do the following:
1. Update your resume
2. Apply for jobs at least 1000 miles from your current location
3. Hope the next DBA that replaces you has more common sense
We’re in the times of SANs, iSCSI, SAS drives, SSD drives and RAID architecture! You should be forking out almost the cost of your server in disk space. Unless of course losing data is an option where you work.
Now that I’ve bored you or intrigued you (or something in between) I can get to my point. There is a wonderful tool that comes with SQL Server 2008 and later called the Management Data Warehouse (MDW) also known as the Data Collector.
Every wonder who all your orphaned SQL Server users are on your server? Just run the script below and copy the results of the query into a new query window then execute them. Make sure you keep the spacing and the quotes as they are so that the Dynamic SQL generated is free of Syntax errors.
USE master SELECT'USE '+ name+ ' EXEC sp_change_users_login ''Report'' ' FROM sys.databases WHERE name not in ('master','msdb','model','distribution','tempdb') ORDER BY name
But wait a minute, I only know which orphaned users are on my server. I want to actually fix them! Ok, ok relax, I have the solution for that just keep reading.
This is somewhat of a quick or lazy way to truncate and shrink the transaction log because all you have to do is replace the database name and change your size requirements. It’s not the cleanest or best approach I admit, but sometimes I find myself on a development or staging server and I need some space quickly. There is no requirement on the server to preserve historic data, and since it is not highly transactional I don’t care what is on the log file to begin with. This happens often when running data aggregations in our DataMart environment where log files can grow up to 100GB+ with staging data that is not useful after the process is complete.