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 »
Among many different things that can affect SQL Server performance, some are more significant than others. In addition, some changes can be relatively easy to implement, but others are quite painfully: Read more »
SQL Server Data Tools – a free standalone download or and add-in to visual studio comes in different flavors and versions. Although this blog post uses Visual Studio 2012 and SSDT stand-alone the principles are example are also valid on Visual Studio 2013.
Read more »
In the world of information, auditing serves an important purpose. It helps to provide an assurance that the data involved is accurate and safe. The level of assurance of course depends on any number of factors including the level of trust in those performing an audit (or collecting the data for the audit), the frequency the data is collected, and the types of data collected.
Any time the collection of data is this important to a process you can bet that a DBA is going to be involved. We get asked to create and run queries to pull data (frequently ridiculously complex queries, and rarely some simple ones). You could almost make it into a joke. Read more »
We use cryptography every day: on the internet, mobile devices, ATM machines, and in almost every aspects of our digital life. In a nutshell, cryptography is about data scrambling and hiding, depending on the implementation and user-specific needs. Read more »
© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy