For someone new to SQL Server Analysis Services, a decision must be made on whether to use a Multidimensional Cube or Tabular Model. Both types can be installed on the same server, but they must be different instances. That could mean additional license costs. But, with the developer’s edition, both can be installed and reviewed before a production decision is made. Data Source connections are the first step to review with both versions.Read more »
Before jumping into creating a cube or tabular model in Analysis Service, the database used as source data should be well structured using best practices for data modeling. Some might say use Dimensional Modeling or Inmon’s data warehouse concepts while others say go with the future, Data Vault. No matter what conceptual path is taken, the tables can be well structured with the proper data types, sizes and constraints.Read more »
As you may already have figured out, the default settings in SQL Server are not always the best. Such is the case when you are working with new user databases; usually you get a single data (*.mdf) and transaction log (*.ldf) file. The data file resides within the PRIMARY file group; the only one we have so far, and it will store all of our databases objects, system objects, user tables, user stored procedures and all other objects. In some cases this default configuration may be good enough for you, but let us cover why would you prefer a different configuration in your production environment.Read more »
Database deployments are critical tasks that can affect negative in on performance in production. In this article we’ll describe some performance related best practices for database deployments.Read more »
Consolidating SQL Server databases and instances is a practice which, under certain criteria, allows organizations to benefit as they can reduce licensing costs, and if properly designed, hardware costs.Read more »
From time to time, I’ve run into replication issues in inherited environments that I did not architect and some of these environments experienced errors in replication because of how it was constructed from the beginning. In this tip, we look at some of the basics in replication architecture and then at solving some of these problems. Some of the replication issues I’ve seen are caused by misunderstanding what is impossible and possible with replication.Read more »
A while ago I discovered that you can conveniently connect to a local instance of SQL Server by typing one of the following names:Read more »
Nowadays many customers are spread globally and need to manage the requirement for users to connect from/to any location, perform an activity (insert, update, delete), and the databases should be kept synchronized across multiple sites.Read more »
More often than not (at least in places I’ve worked at), the job of installing, configuring and subsequent administration of Team Foundation Server (TFS) is performed by different individuals to those administering enterprise applications such as Windows Server, SQL Server, SharePoint etc. This is because TFS, unlike other enterprise applications, often has to be administered from both the server side (i.e. TFS Administration Console) as well as the client side (i.e. using Team Explorer in Visual Studio) – it is thus not surprising that a TFS administrators may once had been a developer. The benefit of having a TFS administrator with a software development experience is that it may be easy to get developer-buy-in into the tool. However, the disadvantage to this is that developers usually get accustomed to doing things in a certain way – like connecting to SQL Server using (local) convenience names.Read more »
One of the more difficult challenges we face when analyzing data is to effectively identify and report on boundaries. Data sets can contain any number of significant starting and stopping points that may indicate significant events, such as missing data, important business events, or actionable changes in usage. Regardless of the use case, knowing how to quickly locate and manage data boundaries is extremely useful. Knowing how to design solutions that can effectively avoid these scenarios can also be helpful in the long run.Read more »
Working as a DBA, the simplest task requested from you is installing SQL Server. As a start you will check the server’s hardware and software specs to make sure that you can start the installation, checking which services will be installed and the security authentication type required. After this pre-installation plan, you will start the normal installation process.
After completing the installation, you need to apply specific configurations on the server to make sure that the server can host production databases and the users can connect to it successfully.
In this article, I will describe the common configurations that you should apply after the SQL Server installation.Read more »
This article is a continuation of a guide where we are checking all the available backup preferences for a database contained in an Availability Group.Read more »
Since the AlwaysOn Availabiliy Groups feature was introduced, we got new options to make the backups strategy more complete, but also more complex. Taking an advantage of secondary replicas, we can offload both, the FULL and even the Transaction Log backups from the Primary Replica to the Secondary, leaving the Primary replica dedicated to serve the production application.Read more »
In Part 1 of this article, we built a case for the benefits of documenting our schema via the use of a stored procedure, as well as the ways this information can be used for DML operations on a relational database. Below, we will continue where we left off by speeding up our SQL queries and then putting everything together so that we can demo a usable script that will hopefully make your life easier!Read more »
In a typical OLTP environment, we want to maintain an acceptable level of data integrity. The easiest way to do this is through the use of foreign keys, which ensure that the values for a given column will always match that of a primary key in another table.
Over time, as the number of tables, columns, and foreign keys increase, the structure of that database can become unwieldy. A single table could easily link to thirty others, a table could have a parent-child relationship with itself, or a circular relationship could occur between a set of many tables.Read more »
Here we are with the last part of this series of articles! In this article we are going to see two curiosities:
- How does in-memory OLTP work with Availability Group?
- Can I migrate from Log Shipping or Database Mirroring to Availability Groups?
So, let’s start!Read more »
In continuation to the Availability Groups series, here you have another curiosity coming to make your job easier and help you to provide a solution to your customers 🙂
This time we will be talking about listeners. Basically, we will be talking about the limitation of one listener per Availability Group. If you never tested / tried this, this is what happens when you already have a listener in the Availability Group, and try to create another one:Read more »
We all love Availability Groups! Since its introduction in the SQL Server 2012, some things changed. In the beginning it was seen as “just a replacement for the database mirroring”, but when we discovered that this would support readable secondary’s, the possibility of having a listener and get rid of the shared storage – even being based on a Failover Cluster – we saw that Availability Groups is a special feature. Read more »
We have already configured our Availability Group, now we need to make it flexible and accessible. It’s time to check on how to create a listener in order to make a single access point for you AG!
In continuation to our previous article, we are going to pass to another phase of this setup, as we already have our database in sync and safe, or highly available, depending of the chosen mode/architecture.Read more »