Data extraction is a pivotal part of any business process particularly when it comes to running reports and facilitating business decision-making. In the article, How to configure OData SSIS Connection for SharePoint Online, I covered data extraction off a SharePoint Online list using SQL Server Integration Services (SSIS). You would have noticed in the aforementioned article that getting SSIS to successfully integrate with SharePoint Online lists can be a laborious exercise, especially if you haven’t installed the correct SharePoint SDK files. Thus, in business environments where business and Power Users have more control of data extraction processes, SSIS could get complicated for an ordinary business user to operate. Therefore, given the nature of our source data and the platform in which it resides, ETL architects and developers alike may need to find alternative ETL tools to SSIS. This brings me to Microsoft Flow which could be one possible alternative to using SSIS for data extraction. Microsoft Flow is part of Office 365 applications and just like SharePoint Online, is a cloud-based application that is freely available, easier to operate and effortlessly integrates with – amongst other applications – SharePoint Online. The aim of this article is to demonstrate the convenience of extracting data from one SharePoint Online list to another using Microsoft Flow.
Read more »Technologies
How to configure OData SSIS Connection for SharePoint Online
April 13, 2018As data warehouse developers, we often have to extract data from a myriad of source systems. Thus, whilst some source systems readily integrate with our ETL tools there are instances whereby we need to install additional drivers and software addons in order to successfully connect and extract data from other source systems. Microsoft SharePoint Online is one such source system that I recently had to extract data from and its connectors are by default not part of the standard SQL Server Integration Services (SSIS) package template. As SSIS developers we often don’t have solid background on environments such as SharePoint, thus figuring out which version of SharePoint Software Development Kit to install in order to enable successful connection from SSIS can sometimes be a frustrating experience. In this article, I try to alleviate some of that frustration by sharing some of my recent experiences relating to getting data out of a SharePoint list using SSIS.
Read more »A concrete example of migration between an Oracle Database and SQL Server using Microsoft Data Migration Assistant
April 12, 2018Introduction
Intended audience
This document is intended for application developers and database administrators who plan to migrate an Oracle Database to Microsoft SQL Server.
Context
In the previous article, we saw how to install and prepare to migrate one or more Oracle Schema(s) and their objects to SQL Server. Now it’s time to go through the migration process…
Read more »Migrating an Oracle Database to SQL Server with Microsoft Data Migration Assistant – Installation Process and Short Overview
March 27, 2018Intended audience
This document is intended for application developers and database administrators who plan to migrate an Oracle Database to Microsoft SQL Server.
Context
Nowadays, most IT professionals are aware that Oracle Database is a very good database solution, but it’s not inexpensive, to say the least. If you want to partition a table in Oracle, it’s an option that you must pay for, which is the same for advanced security options like Database Encryption or Dynamic Data Masking. In contrast, SQL Server comes with these options out of the box when you use the Enterprise Edition.
Read more »Replace bridge tables in a Data Warehouse with SQL Server 2017 graph database
March 8, 2018Just 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 »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 »