Organizations generally start off with relatively simple use of data warehousing. Over time, more sophisticated use of data warehousing evolves. The following general stages of use of the data warehouse can be distinguished:
Data warehouses in this initial stage are developed by simply copying the data of an operational system to another server where the processing load of reporting against the copied data does not impact the operational system’s performance.
Data warehouses at this stage are updated from data in the operational systems on a regular basis and the data warehouse data is stored in a data structure designed to facilitate reporting.
Data warehouses at this stage are updated every time an operational system performs a transaction (e.g., an order or a delivery or a booking.)
Data warehouses at this stage are updated every time an operational system performs a transaction. The data warehouses then generate transactions that are passed back into the operational systems.
In the Data Awareness Programme to students, I have shown below slide to let them know on how many versions have released, so far. As you can see below, the first ever version released was in 1989 and we are in 2014 now. SQL 2016 CTP versions are available but expected to release in beginning of 2016. There were totally 13 releases in last 25 years and the product has become very much stronger than ever. SQL 2016 is going to be a huge one with easy integration to cloud.
In my Data Awareness Programmes, I also spread the available SQL Server features from 2000 to 2014 in below slide formats. This will also give a quick glimpse on all SQL Server releases with available features in one single article. Note: These slides covers only a noticeable features and does not cover every single feature on it.
In DBA Best practise training, I also cover the storage bits. This plays a vital role in IO and performance of every single application or user’s ad-hoc querying. As we know a database should have one MDF (Main Database File), 1 LDF (Log Database File) and can have more than 1 NDF (Secondary Database File). These files when stored across various drives improves the performance of the applications in one way. All data stored in tables are actually getting stored in these files. SQL Server gives flexibility, to store these data in both physical and logical components.
As you can see in below slide, the basic unit of storage is stored in these MDF, NDF via File Group concept, where you dedicate one filegroup location for one database file. Also this slide covers the concept of extents and its types (Mixed & Uniform).
When a bottleneck occurs due to performance problem, every DBA should start with initial analysis on why and where the Performance issue have triggered. I cover these topics in my DBA Best Practises training. This process should have Monitoring Tools, Monitoring Plan and then the Performance Analysis like it says in below slide.
In the Monitoring tools section, I cover the following as it says in the below slide with SQL Trace, Profiler, Statistics IO, Trace Flags, System Stored Procedures, DMV & DMF, DBCC, DTA, Performance / Activity monitors.
In Monitoring Plan, I cover mainly with a performance baseline, where a DBA should have clear understanding on the environment, client applications, database applications, network architecture, available system resources, etc.,
We all work with Data everyday, but is everyone required to access every single data on a daily basis? Mostly it is a BIG NO. Because we don’t have to see what happened, 3 years ago but may be used to compare and that too bit rarely.
When your company ask for current week, last week, this month, last month, this quarter is what we talk about latest data and this becomes the hot topics and this is referred to HOT data.
In the below picture, I have given a simple example from Jan to Dec. Let’s say we are in Dec 15 and the data showing up for Oct, Nov, Dec is going to be much more latest data compared to Jan, Feb, Mar. The below picture gives you an ideal scenario on how you can segregate the Hot, Warm & Cold data.
Also HOT data is very heavily accessed but comparing the same with cold data is very less accessed. Also the data in recent months changes quite frequently compared to the very earlier months.
What does every single installed SQL Server Instance Maintenance covers? Below slide gives information on Monitoring Disk Space usage, Adding/Removing users, Moving objects for better performance, Reviewing and verifying system / error catalogs, to check database integrity, Server monitoring, adding / removing servers, Apply patches, etc.,