Over the past few months we have covered a lot of ground in our little “get togethers”. We have seen a few of my favorite tips and tricks. In today’s discussion we are going to have a look at a few gotcha’s upon which I have banged my head many times.
Read more »In continuation of the previous post “Buffer Pool Extension (BPE) – Introduction to the Buffer Pool”, we will keep diving into the Buffer Pool Extension, this time, talking about another important factor behind this feature – the memory. No, we will not review how the memory works literally, we are talking about the great SQL Server 2014 new feature: The In-Memory OLTP, or the known HEKATON.
Read more »In our SQLShack discussions over the past few weeks, we have dealt with a few of the more conventional SQL Server data manipulation techniques. Today we are going to be a bit more avant-garde and touch upon a subject dear to my heart.
With Power Bi becoming more and more important on the business side within major industries worldwide, it is not surprising that sooner or later every SQL programmer is going to have to learn and be able to ‘talk’ DAX.
In this article we are going to have a look at the a few of the more important ‘constructs’ and produce production grade queries for data extraction and for reports; enabling the reader to ‘hit the ground running’.
Read more »SQL Server transaction log is one of the most critical and in the same time one of the most misinterpreted part. While being neglected, it can easily become a bottleneck to our SQL Server environment. We need to have this in mind and to take care of our transaction logs in order to streamline the performance of our queries and increase log’s throughput.
Read more »When I configured my first Always-On Availability Group, I setup a Windows Cluster and started with SQL Server Management Studio’s New Availability Group Wizard, scripting out the steps along the way. This entire process took only a matter of minutes. The minimum required steps for configuring the cluster and getting a database into an Availability Group (AG) are very few. This process, however, is deceptively simple. What some don’t realize is that the majority of work required for setting up an AG needs to occur in the planning phase, before a server is even requisitioned. Let us examine the questions that you should ask before implementing an AG.
Read more »As a person who has always enjoyed finding new and innovative ways to perform tasks more efficiently and effectively with SQL Server, I have endeavored to document some of the more ‘innovative’ ways and means of doing these things in our SQLShack ‘get-togethers’.
Today, we shall be looking at one of my favourites, the ‘Data Access Layer’, not to be confused with ‘Data Access Layers’ from the Visual Studio world.
Read more »A few months back, I encountered an interesting challenge at a client site. For those of you whom have read my previous article entitled “Excel in loading multiple workbooks into SQL Server“, you will know that the challenge centered around loading the data from multiple spreadsheets into our SQLShack financial database.
Now, one of the enterprises business rules was that the loading of this data was NOT to occur before that last of the daily spreadsheets arrived in the common data repository.
Read more »
In my first article, we talked about how we can make our SQL Server installation include every single patch and hotfix we plan to install. That process is called slipstreaming and it’s a huge timesaver, so if you missed it, jump
here
and take a look at it. I seriously believe it could be a game changer for you and how you do some of your work.
Read more »
Far too often we encounter clients that are really too keen to establish all inclusive reports for decision making purposes. While this is super (in principle) oft times these folks will inform you that all the data that is within the tables should be present within the report, and this is not always feasible nor practical.
After much thought I came up with an alternative to permit these folks to have their ‘cake and eat it’ and yet not render a cluttered report.
Read more »Introduced on SQL Server 2014, the Buffer Pool Extension came to build up a new layer of cache, helping to maintain the “hot” pages accessible with less effort. With the introduction of Hekaton, the BPE has also an important role. In this article we will explore all the aspects of this new feature as well as understand how it works.
With the release of SQL Server 2014, earlier this year, some new features were released, with a special remark to the In-memory OLTP (Hekaton). Together with that new feature, new challenges were brought. The available memory is, of course, one of the main challenges. More ahead, we will understand better why.
Read more »This article is for people who need to replicate the SQL Server Databases. SQL Server includes several types of replications to synchronize the databases across different SQL Servers.
Sometimes we need to replicate the data to have a Backup. Sometimes we need to synchronize Servers that are located in different cities or countries.
In this article, we will introduce you to the SQL Server Replication Theory and then we will show a step-by-step tutorial to replicate a Table using the Merge replication.
Read more »One of a database designers’ worst nightmares is having to design a database for business analysts and data stewards whom insist upon creating their own reports, using Excel as a GUI. The reason that I mention this is that user created reports often open up “Pandora’s box”; with many of these folks creating their own ‘miss-information’ due to a lack of understanding of the underlying data. A few weeks back I had the ’fortune’ of working on such a project, which prompted an ‘ah-ha’ moment. I decided to design the backend SQL Server database using the Business Intelligence Semantic Model (BISM) and to employ the super set of tools provided by Microsoft Power BI, with Excel as a GUI. The end results were wildly accepted by the user community and once you see how easy this is to apply, you will be ‘chomping on the bit’ to employ the same techniques on your own user driven projects.
Read more »This Article is for people with experience using T-SQL and SQL Server Management Studio, but without experience in SQL Server Integration Services (SSIS).
In this article, we will introduce you to the SSIS world and then we will show how to combine different SSIS task to create combined backup tasks. Read more »
A few months ago, I was working on a few SQL Server reports for a client. The one request that I had received (from this client) was to ensure that the finished reports were as ‘all encompassing’ as possible, as they wanted to conduct a considerable amount of Business Analytics, via the reports.
Knowing this, I decided (where possible) to attempt to construct the reports so as to enable the firm to do their ‘what if’ scenarios with a minimal amount of time and effort.
The screen shot below is a sample of the final report AND we are going to look at the steps necessary to create this report. Stay with me!!!
Read more »In the process of doing the round of PASS SQL Saturday’s, I have often been asked how to load data into SQL Server from SharePoint. Naturally and at first glance it should be a ‘no brainer’ however the gotcha is normally related to a security issue. Over the past few days, I have been working on just such an issue. But wait, I am getting ahead of myself!
Read more »This is the third post in the series about database development and testing using SQL Server Data Tools and Team Foundation Server.
Post 1: Continuous Integration with SSDT and TFS
Post 2: Unit testing with SQL Server Data Tools
You can run test in Visual Studio to test the quality of your build. In VS 2012 and 2013 there are 5 kind of tests that are available for the user Read more »
MS SQL Server 2005 and later versions include the Database Snapshot feature to have snapshot of the database for reports, as a copy in different periods.
The Database Snapshot can be created multiple times and it can only be created using the T-SQL.
In this article, we will show how to create a Database Snapshot, how to see the snapshot created in the SQL Server Management Studio (SSMS), how to recover objects dropped or data removed using the snapshot. Read more »
In the first part we started discussion about choosing the right table structure and described the heaps — in my opinion a mostly overused option. Now let’s see better alternatives.
Read more »A year or so ago, I worked for an online web grocery software house located in the northern United States. At that time I had my ‘baptismal’ exposure to ‘genuinely dirty data’. Granted most of the data entry was done manually and many times from offshore. The point being that I could not fathom just how many ways there were to spell the brand name of a major cereal manufacturer. Why is this such an issue? The answer is fairly straight forward. Imagine the scenario that you are trying to ascertain the dollar value of breakfast cereals sold in the country from the local supermarket standpoint all the way up to national sales. Imagine this utilizing a SQL Server Multi-dimensional cube. The ‘eagle – eyed’ reader will recognize that the results will not aggregate correctly should our aggregation attributes have a plethora of different ways of being spelt. Read more »
Now that Karla’s restore process is mature, it is time to pre-stage the means of reporting on these events. Two major actions have been taken for each database tested. First a restore operation occurred. The restore operation validates that the backup file is well formed and that there is nothing wrong with the data which would cause a failure. Next CheckTables was executed to thoroughly inspect for data corruption and any consistency errors. Each of these two actions will have their own methods of providing evidence of successful completion. Recall the requirements set by Karla, in part 1 of this series, for the reporting piece. Read more »
Karla is a production database administrator and she has a lot in common with you. Being responsible for database backups and recovery, she has implemented a well-structured automated backup system. Maybe she’s using Ola Hallengren’s Maintenance Solution, custom T-SQL stored procedures, or a set of PowerShell scripts. She has heard the saying, “a DBA only needs one thing, either a backup or a resume, but never both,” and is confident that she won’t be dusting off the resume any time soon. Read more »
This article on SQL Unit Testing is the second part on the series about SSDT and database development
Part I: Continuous integration with SQL Server Data Tools and Team Foundation Server
In December 2012, a great addition was made to SSDT: The ability to do SQL unit testing.
Read more »A few month back, I found myself in a position where the client wanted a ‘monitoring tool’ to utilize on a daily basis to ascertain the status of SQL Server Systems and to continually monitor disk space capacity. Being a typical Monday morning quarter back and utilizing my favorite SQL Server Tool, SQL Server Reporting Services, I came up with the following ‘ah-ha’ solution. Read more »
© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy