Just like in Santa’s Bag of Goodies, every release of SQL Server often has something for everyone – be it enhancements to DMVs for the DBAs, new functions for T-SQL developers or new SSIS control tasks for ETL developers. Likewise, the ability to effectively support many-to-many relationships type in SQL Graph has ensured that there is indeed something in it for the data warehouse developers in SQL Server 2017. In this article, we take you through the challenges of modelling many-to-many relationships in relational data warehouse environments and later demonstrate how data warehouse teams can take advantage of the many-to-many relationship feature in SQL Server 2017 Graph Database to effectively model and support their data warehouse solutions.
Read more »Technologies
How to plot a SQL Server 2017 graph database using PowerBI
January 9, 2018In the article How to plot a SQL Server 2017 graph database using SQL Server R, I highlighted the lack of built-in graph data visualisation as one major limitation of the SQL Server 2017 graph database feature. In the same article, I went on to suggest making use of SQL Server R as one workaround that could be utilised in order to successfully plot and visualise diagrams out of SQL Server 2017 graph database objects. However, whilst 3rd party graph database vendors such as Neo4j provide an interactive and hyperlinked graph diagrams that allows you to – amongst other things – easily drilldown and identify node-relationships as indicated in Figure 1, the graph plotted using SQL Server R is not very interactive in fact it is simply a static image file as shown in Figure 2.
Read more »How to plot a SQL Server 2017 graph database using SQL Server R
January 3, 2018A few years ago, one common business case I came across in my professional career that required modelling of data into a many-to-many entity relationship type was the representation of a consultants and their projects. Such a business case became a many-to-many entity relationship type because whilst each project can be undertaken by several consultants, consultants can in turn be involved in many different projects. When it came to storing such data in a relational database engine, it meant that we had to make use of bridging tables and also make use of several self-joins to successfully query the data.
Read more »How to implement a graph database in SQL Server 2017
December 19, 2017Introduction
Graph database
A graph database is a type of database whose concept is based on nodes and edges.
Graph databases are based on graph theory (a graph is a diagram of points and lines connected to the points). Nodes represent data or entity and edges represent connections between nodes. Edges own properties that can be related to nodes. This capability allows us to show more complex and deep interactions between our data. Now, to explain this interaction we will show it in a simple diagram
Read more »How to configure SQL Server 2017 on Linux with mssql-conf and other available tools
December 13, 2017Configuration of any system plays a vital role in its working efficiently. However, configuring a system needs not necessarily be a daunting task. There are several tools that help with the process.
SQL Server on Linux provides a wide range of options for configuration, management and administration.
- mssql-conf
- Transact-SQL
- SQL Server Management Studio
- PowerShell
An introduction to a SQL Server 2017 graph database
December 4, 2017The graph database is a critically important new technology for data professionals. As a database technologist always keen to know and understand the latest innovations happening around the cutting edge or next-generation technologies, and after working with traditional relational database systems and NoSQL databases, I feel that the graph database has a significant role to play in the growth of an organization. Not only are traditional database systems generally inefficient in displaying complex hierarchical data, but even NoSQL lags a little. We usually see a degradation in performance with the number of levels of relationship and database size. Also, depending on the relationship, the number of joins may increase as well.
Read more »5 tips for working with SQL Server databases to support SharePoint
November 23, 2017The SharePoint primer
If you are reading this, odds are you already work with SharePoint. So, isn’t reading a primer a waste of your time? Not really. As with all things tech, and more to the point Microsoft’s enterprise technology offerings, complete mastery is elusive. For instance, your SharePoint deployment should support sharing, collaboration, and content management depending on your organizational needs. But are you getting the most out of your deployment? And by the way, in how many ways can users collaborate?
Read more »How to set up SQL Server Log Shipping on Linux
November 13, 2017Log shipping is a high-availability configuration that perhaps most of us are familiar with. It’s one of the oldest techniques wherein we ship transaction logs from a Primary database to a Secondary database. Log Shipping is still a vital feature used in case of applications that use warm standby for Disaster Recovery. We can see many articles which discuss the process of configuring Log shipping using T-SQL or SSMS.
Read more »Using SSIS ForEach Loop containers to process files in Date Order
August 3, 2017One positive thing to come out of my recent project that involved rewriting one of the Data Marts from our Data Warehouse environment was a confirmation of my suspicions with regards to the behavior of SQL Server Integration Services’ (SSIS) ForEach Loop Container. You see, I have long suspected that the ForEach File Enumerator type in SSIS’s ForEach Loop Container does not process time stamped text files in an order that could be deemed correct to the human eye. For instance, Figure 1 shows a list of text files containing data relating to Marital Statuses of FIFA 2016 Ballon D’Or nominees.
Read more »Refreshing SQL Server Containers automatically with Watchtower
July 25, 2017Introduction
I’ve been working with SQL Server in containers for a while now and one of the challenges that I have is ensuring that the databases within them are kept at the correct version. This is important because you don’t want your development teams testing against databases that have an out of date schema (or reference data).
Read more »How to migrate MySQL tables to Microsoft Azure SQL database
May 18, 2017Introduction
Today, it is a common practice to migrate our local data to Azure. Sometimes, we migrate files, sometimes databases and sometimes virtual machines. All the service in the cloud including Azure help a lot to simplify the administrative effort and reduce costs.
Read more »How to install SQL Server on Ubuntu
May 18, 2017Introduction
With an increasing number of new servers running on Linux, and even a third of Microsoft’s Azure virtual machines running Linux, they have made a choice to bring SQL Server to Linux in order to increase their market share and attract new customers.
Read more »Migrate an SQL database from Windows to Linux using backup-and-restore and SMB Protocol
May 16, 2017In an attempt decouple systems, we may modularize them. In most cases, we would need to move databases from one server to another—sometimes, even move them from Windows to Linux.
Read more »Installation of SQL Server vNext CTP on Linux Distribution CentOS 7
May 10, 2017It was a paradigm shift in December 2016, when Microsoft made their SQL Server database available for Linux; it was the first time in history that Microsoft ever designed SQL Server to run on a non-Windows operating system. SQL Server vNext was released for public preview so the user community could test and deploy SQL Server on a Linux operating system. Microsoft took a major step in diversifying the database technology into the non-windows platform for the first time.
Read more »How to connect and use Microsoft SQL Server Express LocalDB
May 9, 2017Microsoft SQL Server Express LocalDB is intended for developers, it is very easy to install and doesn’t require any complex configuration task to create an instance or to use the database. The Introduction on the installation of Local DB be found on “How to install Microsoft SQL Server Express LocalDB” article.
This article will show different ways of connecting to LocalDB and how to create and use an instance of LocalDB.
Read more »How to install Microsoft SQL Server Express LocalDB
May 9, 2017Microsoft SQL Server Express LocalDB, a solution primarily intended for developers, is a lightweight version of SQL Server Express. It is very easy to install and set up. The installation copies a minimum set of files which are necessary to start SQL Server Database Engine. LocalDB supports the same T-SQL language and has the same limitations as SQL Server Express.
Read more »How to migrate MySQL tables to SQL Server using the SQL Server Migration Assistant (SSMA) and SSIS
April 25, 2017Introduction
In this article, we will demonstrate how to migrate MySQL tables to SQL Server using the SQL Server Migration Assistant (SSMA) and SSIS. We will give a brief introduction to MySQL and create a table. If you do not have any experience in MySQL, this tutorial will teach you your first steps. After that, we will migrate the created table and existing tables. We will also mention common problems when we migrate tables.
Read more »Running SQL Server Containers on Windows Server 2016 Core
April 21, 2017SQL Server & Containers
There’s a lot of buzz around containers at the moment but not so much in the SQL Server world which I find odd as, to me as a SQL Server DBA, the technology has a lot of benefits that are worth exploring especially when it comes to development environments.
Read more »SQL Server vNext – Does Microsoft love Linux?
April 21, 2017Introduction
In the 90s and at the beginning of the 21 century, Linux and Windows were decidedly working at cross purposes
However, starting on 2009, Microsoft started to contribute with code in the Linux Kernel. On 2013, Azure supported Virtual Machines in Ubuntu and later came the support to .NET in Linux, Visual Studio for Linux, PowerShell in Linux, support to monitor Linux server to monitor in SCOM (System Center Operation Manager) and now, the support for SQL Server that is on the previous version when this article was published and it is planned to be released this year (2017). Microsoft is a Platinum member of the Linux Foundation now.
Read more »How to use Windows hosted file shares to support SQL Server containers
February 23, 2017In a previous article, Automate Delivery of SQL Server Production Data Environments Using Containers, we introduced SQL Server containers for delivery of production data environments to development and QA teams. In this article we look at the methods used for working with SQL Server data, and use of file shares to support delivery of production databases with containers.
Read more »Automate Delivery of SQL Server Production Data Environments Using Containers
January 12, 2017There has been a lot of buzz about Docker and containers this year, and earlier this Fall Microsoft released container support in Windows Server 2016. WinDocks is a team of former Microsoft engineers, and we released an independent port of Docker’s open source in March of 2016. Full disclosure, I am a co-founder at WinDocks.
In this article we’ll take a look at the basics of containers, and the most popular use by SQL Server DBAs.
Read more »Using SSMA v7.1 for Oracle to migrate into SQL Server
December 21, 2016Introduction
This is a second article that is a continuation of the Microsoft SQL Server Migration Assistant (SSMA) v7.1 for Oracle. In my first article, I wrote an overview of the tool and how to install it. In this second article I will write about how to use it to migrate data and objects from an Oracle database to a SQL Server database.
Read more »Microsoft SQL Server Migration Assistant (SSMA) v7.1 for Oracle
December 12, 2016Introduction
SQL Server Migration Assistant (SSMA) is a free supported tool from Microsoft that simplifies and automates database migration processes (assessment analysis, necessary conversions, data migration and tests) from Microsoft Access, MySQL, Oracle, Sybase and DB2 to SQL Server, Azure SQL DB and SQL Server vNext (at the time I’m writing this article, SQL Server vNext stills in CTP1 version, so being a technical preview version it should not be used for production migrations).
Read more »SQL Server 2016 – PolyBase tutorial
October 21, 2016Introduction
This article will teach you how to install PolyBase and will show you a simple example to start.
PolyBase is a new feature in SQL Server 2016. It is used to query relational and non-relational databases (NoSQL). You can use PolyBase to query tables and files in Hadoop or in Azure Blob Storage. You can also import or export data to/from Hadoop.
Read more »Link a SQL Server to an Oracle database
August 15, 2016There are some cases in the wild where we want to get back data from an external database without additional coding in an application and just write a T-SQL query that will do all the job for us. That’s why Microsoft provides the « linked server » feature.
Read more »