In past chats, we have had a look at a myriad of different business intelligence techniques that one can utilize to turn data into information. In today’s “get together” we are going to try to pull all these techniques together, rationalize our development plans, and moreover, look at some good habits to adopt or for the want of better words utilize SQL Server Reporting Services Best Practices.
Read more »
There has always been some debate as to whether or not there are real benefits to be gained from accessing the information in the transaction log. This article will endeavor to answer that question by looking at the following:
With the advent of the Information Era, data is being collected on a massive scale. Information Technology systems have made access to this data faster and easier. It has also made it easier for data to be misused.
Read more »
The purpose of security auditing is to identify all attacks, unlawful or malicious activities which might be taking place on your server. Criminals have become increasingly inventive and as a DBA you might not have considered or even be aware of all activities which may be putting your data at risk.
Read more »
In sequence of the first article about the server memory importance, we will check another In-Memory OLTP key point: The Checkpoint files. Check what wrong can happen, and how to avoid this!
Read more »
With the introduction of the in-memory technology, we need to think about what are the new challenges to keep the service up and running. In this article I will explore one of the three key points to monitor in order to maintain your SQL Server healthy.
Last week I spoke at my local SQL Server user group, in Lisbon. The session was nice and the good feedback received encouraged me to write about this and share information that is not too spread over the internet (so far…): Monitor environments using In-Memory OLTP.
Read more »
In the previous posts
Deployment to several databases using SQL Server Data Tools and TFS using a Custom Workflow file
Deployment to several databases using SQL Server Data Tools and Team foundation Server
Continuous integration with SQL Server Data Tools and Team Foundation Server
I have been mostly writing about the interaction between SQL Server Data Tools and Team Foundation Server. Microsoft provides a hosted version of Team Foundation Build Service called Visual Studio Online. The configuration and functionality is mostly the same than what I have previously been writing about but there are some specifics things that we need to be aware when using Visual Studio Online Build Service.
Read more »
When you have created a series of Azure VMs – for example for doing demos – you might want to be able to start it and shut it down easily. For convenience purposes but also for keeping the cost down while you’re not using the Azure VMs. In this case Azure Automation comes handy.
Read more »
Introduced on SQL Server 2014, the new brand feature In-Memory OLTP a.k.a “Hekaton” is a Main-Memory Database Engine. Developed by Microsoft Research & Paul Larson (Database Research Group at MSFT) this feature have the ability to manage and held tables entirely In-Memory. In this Series we will pass through for all the new concepts and components that makes this New Engine the most excited acquisition of the new SQL Server Version (120).
Read more »
SQL Server has two ways of encrypting data. One way is by protecting data on the table, record or column level, and the other way is by protecting data “at the rest”. One of the best crypto features in the database world today is known as a Transparent Data Encryption.
Read more »
In the last chapter, we explained how to create a Microsoft Azure Account and how to have a Microsoft Azure Portal.
This time, we will show how to backup our SQL Server Databases to a Microsoft Azure. We are assuming that you already have an Azure account.
Read more »
The cloud is a buzzword in the IT world. Oracle, Amazon and Microsoft with Microsoft Azure are offering Cloud Services to the public. Most of the companies plan to have part of their environments in the cloud to reduce the maintenance and security effort.
Read more »
In past chats, we have had a look at a myriad of different Business Intelligence techniques that one can utilize to turn data into information. In today’s get together we are going to have a look at a technique dear to my heart and often overlooked. We are going to be looking at data mining with SQL Server, from soup to nuts.
Read more »
In the previous blog post : Deployment to several databases using SQL Server Data Tools and Team foundation Server I illustrated how it is possible to use TFS and a batch file to deploy a database to several SQL Server instances or to deploy several SQL Server databases to several instances. The main way to achieve that in the previous post was using a batch file. For more information about this technique please have a look at that blog post.
In this post on the other hand I will demonstrate how the same functionality can be achieved using a Windows Workflow Foundation file (xaml) deployment file and Team Foundation Server.
Read more »
Do you know that you can store system and user databases in a fileshare? Even for a clustered instance this is an option now. In this article we will check how to implement this solution.
On the latest versions of SQL Server new possibilities to assign storage to a clustered instance became available. In this article, we will talk about one of these options, which is also an option for standalone instances by the way: The network-attached storage – SMB Fileshare.
Read more »
In an earlier “get together”, we had a quick look at the DAX language and how to construct useful queries. In today’s conversation we shall be concentrating on utilizing the knowledge that we obtained from the earlier article and seeing how these queries may be utilized for “multiple value” query selection criteria (against a tabular database).
Enough said, let us get started!
A few years back, a client asked me to implement a quick and dirty “security mechanism” to control what data the myriad of users were able to view within their reports. There were numerous tables with multiple columns and all departments (within the enterprise) had their data within these tables.
SQLShack Industries has tasked us with creating a similar quick and dirty “security mechanism”. We shall attack this challenge by creating the necessary stored procedures (to extract the required data) and then utilize these stored procedures to render and consume the data within our reports.
Read more »
You already understand the benefits of the BPE and how to deal with the feature, now it’s time to better understand how it works.
Here we are, the last article of this Buffer Pool Extension series. We came from the explanation of what is the Buffer Pool, made an introduction of the new In-Memory technology and saw on what the Buffer Pool Extension can help in keep a stable environment, and finally, the part 3 showed how to implement the Buffer Pool Extension in your SQL Server 2014 system.
Read more »
Sometimes, it is necessary to move the database from one server to another. The problem is that usually the logins and the database users are mapped. If that is the case, when you backup a Database and restore a database in another server, the logins are lost. In this article, we will show different alternatives to solve this problem.
Read more »
This third part of the BPE series shows the basics of Buffer Pool Extension and know how to implement and troubleshoot it.
During all the Buffer Pool Extension (BPE) series we have been explaining base concepts related to this new technology, as well as its benefits. We understood how the Buffer Pool works and the main benefits of implement the Buffer Pool Extension together with In-Memory technology. I recommend you to read the previous articles, if you are not familiarized with those concepts, so everything into this article will make more sense 🙂
Read more »
In the previous article, we have examined the physical structure of the transaction log and discussed WAL algorithm. Now I will take you on a journey through the most common reasons for experiencing a log bottleneck in your systems.
Read more »
In the previous post : Continuous Integration with SSDT and TFS I have described some techniques to allow continuous integration / deployment using SQL Server Data Tools and Team Foundation Server. The solution described works great for deploying to a single instance of a database.
Read more »
Capacity management is one of the most important tasks you have to perform as a DBA. Usually we rely on threshold alarms for the disks volumes and the databases, however I find it very suitable for me to take actions in advance – this way I am sure the SQL Servers under my supervision will have the needed space to operate without issues. And yes, there is the auto-grow, however it’s a safety mechanism which role is to ensure that even in critical situations you can have your database operational, however this does come with a negative performance impact which I try to avoid for my key production systems.
Read more »
In this new article, we will talk about the system views/tables/functions and how to solve common questions using them.
The system views are views that contain internal information about a Database.
The master database for example contains information about the SQL Server itself, while the msdb database contain information about the SQL Server agent and each database has its own system views/tables.
In this article we will show how to get the list of tables, views, stored procedures, how to get a list of tables of all the databases, how to find a table in multiple datatabases, how to get the list of users, logins, mapped logins, how to detect a fragmentation in a table and more.
Read more »
Deciding what method to use to collect audit information can be a task in and of itself. Depending on your needs there are a number of different ways to collect the information. There are settings such as Login Auditing and Common Criteria Compliance that are fairly simple to implement but only collect very specific sets of information. There are also coding solutions such as Triggers and Audit Columns that are very flexible but can be tricky to get right. Then there are Profiler Traces but those have been deprecated and will be removed in a future version of SQL Server. Their replacement, Extended Events, has become easier and easier to use in recent versions and can be used to collect a huge variety of information. However Extended Events does not include any Audit specific information. If you are setting up an auditing solution in SQL Server you are probably going to want to consider using SQL Server Audits. Audits are based on Extended Events and so have all of the benefits of Extended Events but also include the audit specific events. Audits have a different interface than Extended Events and a different set of T-SQL Commands. Fortunately they are very easy. Read more »
© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy