Prashanth Jayaram

How to configure SQL Server 2017 on Linux with mssql-conf and other available tools

December 13, 2017 by

Configuration 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.

  1. mssql-conf
  2. Transact-SQL
  3. SQL Server Management Studio
  4. PowerShell
Read more »
Daniel Calbimonte

How to build a cube from scratch using SQL Server Analysis Services (SSAS)

December 11, 2017 by

Introduction

I am a DBA consultant and several times, in the past, I have been asked for a good tool to generate fast reports to get information about the company like total sales, sales per year, month, week etc

SSAS is a Business Intelligence (BI) technology that Microsoft bought from Panorama Software in 1996. After the acquisition, the technology was upgraded and evolved and now it is part of the tools that come with SQL Server. This tool is an extremely powerful tool to create analytical reports fast. It works with cubes that are designed to generate reports with optimal performance using aggregated information stored in the cube.

Read more »
Esat Erkec

How to use parallel insert in SQL Server 2016 to improve query performance

December 8, 2017 by

Introduction

In the first part of this article, we will discuss about parallelism in the SQL Server Engine. Parallel processing is, simply put, dividing a big task into multiple processors. This model is meant to reduce processing time.

  • SQL Server can execute queries in parallel
  • SQL Server creates a path for every query. This path is execution plan
  • The SQL Server query optimizer creates execution plans
  • SQL Server query optimizer decides the most efficient way for create execution plan

Execution plans are the equivalent to highways and traffic signs of T-SQL queries. They tell us how a query is executed.

Read more »
Marko Zivkovic

How to export data from SQL Server to a Flat file

December 7, 2017 by

In this article, we will illustrate how to export SQL Server data into a Flat file, by using the SQL Server Import and Export Wizard. We will also explain some of the problems that may appear during the process of exporting SQL Server data into a Flat file.

To start the SQL Server Import and Export Wizard, use one of the following ways form the How to import/export data to SQL Server using the SQL Server Import and Export Wizard page.

Read more »
Timothy Smith

How to return data use, index compression, and row information with PowerShell

December 6, 2017 by

Background

We recently inherited a database environment where we’re facing significant data growth with limits on the sizes we can allow our databases to grow. Since we maintain multiple development, QA and production environments and these environments must be sized appropriately. We set a few standards about tables that exceed certain sizes – rows, data or both, or have certain growth patterns and our standards force compression at thresholds we set for our different environments (including using page, row, or clustered columnstore index compression) We’re looking for how we can get information about data and compression in tables and options we have so that we can quickly determine candidates that don’t currently match our best practices design we’ve set for our environments.

Read more »
Robert Seles

Ola Hallengren’s SQL Server Maintenance Solution – Installation and SQL Server Backup solution

December 5, 2017 by

Database administrators tend to use various scripts or applications, to make the daily SQL Server database maintenance task easier.

Some more experienced administrators prefer to design and use their own scripts for these tasks. The scripts are usually designed to fit the requirements imposed by the specific environment. After the scripts are thoroughly tested, they often get included in a maintenance plan, or SQL Server agent job to automate their execution. Such solution is usually optimal for some demanding environments like high traffic servers or databases that are still in development. Designing these solutions usually take time, and require an experienced DBA skilled in T-SQL or PowerShell scripting.

Read more »
Prashanth Jayaram

An introduction to a SQL Server 2017 graph database

December 4, 2017 by

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 »
Ayman Elnory

How to design SQL queries with better performance: SELECT * and EXISTS vs IN vs JOINs

December 1, 2017 by

Quite often developers put the responsibility of the server performance on the shoulders of DBAs. But to be honest, as a developer, it is your responsibility to write code which is efficient and optimal. However, when writing code, the developer often lacks the knowledge and/or experience about SQL Server to meet this requirement.

In this series, I will be discussing various aspects of query designing, keeping performance and scalability in mind. We will be addressing the most common concepts which we often see that negatively impact SQL code written by developers.

Read more »
Craig Porteous

How to manage Power BI dataset refresh failures

November 30, 2017 by

As I covered in a previous post How to connect to (and query) Power BI and Azure using PowerShell, Power BI can be difficult to manage and administer, unlike on-premises BI solutions. One such concern that will often require quick action is the failure of a dataset refresh.

If your reports and dashboards all rely on live connection or DirectQuery data sources like Azure SQL Database, Azure SQL Data Warehouse or SQL Server Analysis Services (on-premises or in Azure) then you won’t have to worry about dataset refreshes and this post will just be some interesting reading.

Read more »
Ahmad Yaseen

What to choose when assigning values to SQL Server variables: SET vs SELECT T-SQL statements

November 29, 2017 by

SQL Server provides us with two methods in T-SQL to assign a value to a previously created local variable. The first method is the SET statement, the ANSI standard statement that is commonly used for variable value assignment. The second statement is the SELECT statement. In addition to its main usage to form the logic that is used to retrieve data from a database table or multiple tables in SQL Server, the SELECT statement can be used also to assign a value to a previously created local variable directly or from a variable, view or table.

Read more »
Esat Erkec

R script word-cloud in SQL Server Report Builder

November 28, 2017 by

What is R

R is a very popular data programing language. R is especially used in data analysis, statistics calculations, predictions, data mining and machine learning. R is used by data scientist, economist, genetic scientists, and statisticians. R has very wide usage in real life. Healthcare, finance, marketing, and manufacturing are some of them.

Read more »
Vin Cannarelli

5 tips for working with SQL Server databases to support SharePoint

November 23, 2017 by

The 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 »
Bojan Petrovic

How to configure database mail in SQL Server

November 22, 2017 by

Database Mail, as you would expect from its name, is a solution for sending e-mail messages from the SQL Server Database Engine to users. Using Database Mail, database applications can send e-mail messages that can, for example, contain query results or simply alert a user about an event that occurred in the database.

The process of Database Mail configuration has three main steps. In order to complete this successfully, we need to:

  • create a Database Mail account,
  • create a Database Mail profile,
  • and configure those two to work together
Read more »
Prashanth Jayaram

Data Interpolation and Transformation using Python in SQL Server 2017

November 21, 2017 by

As a continuation to my previous article, How to use Python in SQL Server 2017 to obtain advanced data analytics, a little bit of curiosity about Deep Learning with Python integration in SQL Server led me to write this latest article.

With Python running within SQL Server, you can bring the existing data and the code together. Data is accessible directly, so there’s no need to extract query data sets, moving data from storage to the application. It’s a useful approach, especially considering issues of data sovereignty and compliance, since the code runs within the SQL Server security boundaries, triggered by a single call from T-SQL stored procedures.

Read more »
Dinesh Asanka

SQL Server auditing with Server and Database audit specifications

November 20, 2017 by

Auditing is a key feature in any application or any system as it provides end users with better analysis for administrators. Apart from analysis, auditing can be used as a troubleshooting mechanism too. Apart from organizational reasons, there are compliance reasons for enabling auditing depending on the domain of operation.

Auditing is mainly about answering four questions, i.e. who, when, what and where. However, depending on the situation, it might be decided what questions of the mentioned four should be answered.

Read more »
Daniel Calbimonte

How to use BULK INSERT to import data locally and in Azure

November 16, 2017 by

Introduction

BULK INSERT is a popular method to import data from a local file to SQL Server. This feature is supported by the moment in SQL Server on-premises.

However, there is a new feature that is supported only in SQL Server 2017 on-premises. This feature allows importing data from a file stored in an Azure storage account to SQL Server on-premises using BULK INSERT. This feature will be supported in Azure SQL versions in the future.

In this article, we will show two examples. The first example will show how to use the traditional BULK INSERT statement from a local CSV file to Azure and the second example will show how to import data from a CSV file stored in Azure to SQL Server on-premises.

Read more »
Prashanth Jayaram

How to set up SQL Server Log Shipping on Linux

November 13, 2017 by

Log 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 »
Marko Zivkovic

How to import data from an Excel file to a SQL Server database

November 10, 2017 by

There are many ways to import data from an Excel file to a SQL Server database using:

In this article, steps for importing data from an Excel file to a SQL Server database will be explained using the SQL Server Import and Export Wizard including some of problems that can occur during the processes.

Read more »
Page 1 of 3712345...102030...Last »