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
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 »
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 »
IntroductionA year or so ago, I was working on a project that revolved around daily data loads (from various asset management groups within an enterprise) into the main SQL Server data repository. Each group completed and published its own daily figures within their own Excel Work Books. These Excel workbooks were then placed in a common directory and then loaded into the Corporate SQL Server database. Let us have a look at how this may be achieved. In short, we are going to create one package that will process all the spreadsheets within the given directory. 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 »