As a rule, impersonal information is stored in a public cloud, and the personalized part – in a private cloud. The question thus arises – how to combine both parts to return a single result at a user’s request? Suppose there is a table of customers divided vertically. The depersonalized columns were included in the table located in Windows Azure SQL Database, and columns with sensitive information (e.g., full name) remained in the local SQL Server. Both tables must be linked by the CustomerID key. Because they are located in different databases on different servers, the JOIN statement will not work. As a possible solution, we have considered the scenario, when the linkage was implemented on the local SQL Server. It served as a kind of entry point for the applications, and the cloud-based SQL Server was set up on it as a linked server. In this article, we will consider the case when both, the local and cloud servers, are equal in terms of the application, and the data merging occurs directly in it, i.e. at the business logic level.
Category: SQL Server
SSAS: Discipline, Accuracy, Attention to Details. Part 2 – OLAP Cube
Introduction
In this article, I will continue describing my experience with Microsoft Analysis Services. In addition to the previous article, I would like to write about unconventional solutions that were implemented in the recent project. These solutions got me close with Microsoft Analysis Services, and allowed me to do things that seemed to me impossible before. (more…)
Working with SQL Server in Hybrid Cloud Scenarios
A hybrid cloud is a fairly attractive model when implementing cloud computing in enterprise information systems since this approach combines the advantages of public and private clouds. On the one hand, it is possible to flexibly attract external resources when needed and reduce infrastructure costs. On the other hand, full control over data and applications that the enterprise does not want to outsource remains. However, in such a scenario, we inevitably face the task of integrating data from various sources. Suppose there is a table with customers, which is vertically divided into two parts. The depersonalized part was allocated in a public cloud, and the information personalizing the customers remained in a local database. For holistic processing inside the application, you need to combine both parts by CustomerID. There are various ways to do this. Conventionally, they can be divided into two large categories: data aggregation at the on-premise database server level which, in this case, will be a single sign on for accessing local and remote data, and data aggregation within the business logic. This article will consider the first approach.
Execute Powershell in SSIS Using C#
Introduction
PowerShell is a Shell included in Windows to automate tasks in the operative system and other applications like SQL Server, SharePoint and Internet Information Services. You can create reports, start services, check the hard drive space, verify the RAM and more.
PowerShell can now be installed in Linux, Docker, and Mac.
You can use loops, comparisons, conditionals and more to create powerful scripts to automate tasks like creating Virtual Machines or SQL Servers in Azure.
In this new article, we will learn how to use the script task to invoke PowerShell in C#. The script task in SQL Server Integration Services (SSIS) allows using C# or VB code to extend the SSIS features. (more…)
Transparent Data Encryption and Always Encrypted
If you need to store confidential data in your database, you can use data encryption. SQL Server supports encryption with symmetric keys, asymmetric keys, certificates, and password phrases. I assume that you, the reader, are already familiar with these terms. In this article, I will focus on two out of many encryption options provided by SQL Server:
- Transparent Data Encryption (TDE)
- Always Encrypted (AE)
SSAS: Discipline, Accuracy, Attention to Details
Introduction:
This article is about working with Microsoft Analysis Services and a little bit about the repository on Microsoft SQL Server that SSAS is working with. I had to deal with not quite trivial things and sometimes I had to “jump over my head” in order to complete my task. I had to work between meetings. Sometimes the new functionality was discussed longer than it was developed. Often at meetings, I had to repeat the same thing several times. When I said that it’s hard for me to have a discussion for more than one hour, people looked at me with surprise and misunderstanding. Thanks largely to this situation, these nontrivial things about which I decided to write appeared.
Introduction to Temporary Tables in SQL Server
A temporary table in SQL Server, as the name suggests, is a database table that exists temporarily on the database server. A temporary table stores a subset of data from a normal table for a certain period of time.
Temporary tables are particularly useful when you have a large number of records in a table and you repeatedly need to interact with a small subset of those records. In such cases instead of filtering the data again and again to fetch the subset, you can filter the data once and store it in a temporary table. You can then execute your queries on that temporary table. Temporary tables are stored inside “tempdb” which is a system database. Let’s take a look at how you can use a temporary data in a simple scenario.
Why Multiple JOINs are bad for Query or Do Not Get in the Way of Optimizer
Recently, I came across an application that generated DB queries. I understand that there is nothing new about that, but when application began running slow and I had to find out the reason of the slowdown, I was amazed to find these queries. Here is what SQL Server sometimes has to deal with: (more…)
CombGuid: Generation of SQL Server-friendly Guid Values in .NET Applications
Usage of UUID as a primary key for tables has a bunch of pros, including the option to retrieve IDs for objects created in a client application on its own without calls to the database server. However, usage of UUID as a primary key has a con: GUIDs generated by the client application may be not quite SQL Server-friendly that can lead to the overhead during the addition of a new record. (more…)
Better ALTER than DROP
In this article, I am going to provide a construction for deleting an object before creating it.
