Ahmad Yaseen

SQL Server indexed views

March 17, 2016 by

SQL Server Views are virtual tables that are used to retrieve a set of data from one or more tables. The view’s data is not stored in the database, but the real retrieval of data is from the source tables. When you call the view, the source table’s definition is substituted in the main query and the execution will be like reading from these tables directly.

Read more »
Sifiso W. Ndlovu

Working with ragged right formatted files in SSIS

March 16, 2016 by

In the world of SSIS development architecture, preference should be given to extracting data from flat files instead of non-Microsoft relational databases. This is because you often don’t have to worry about driver support and compatibility issues in your SSIS development/server machine that is often attributed to non-Microsoft database vendors. In fact, I’ve been in several situations whereby we cannot upgrade to another version of SSIS (i.e. BIDS to SSDT) due to the lack of external vendor driver compatibility issues in the newer versions of SSIS.

Read more »
Shawn Melton

Analyzing SQL Server backups

March 8, 2016 by

Introduction

Database backups are important and always something you should have in any environment. Outside of needing them to restore a given database they have some information that can be useful in certain situations. One situation I found them convenient is with consolidation projects. If the databases are online you can obviously go to the source SQL Server instance to gather that information, but as a consultant I don’t necessarily have access to every environment. You may have the same issue if you are being brought into a project and your customer or department manager just wants you to advise on how you would setup the server. One easy request is to have them point you where the backups are stored and ensure you have access to the files.

Read more »
Ahmad Yaseen

How to track changes in SQL Server

March 8, 2016 by

As a part of a Big Data project, we are often asked to find the best way to track the changes applied to the database tables, so that, no requirement is created to load all the huge tables to the data warehouse database at the end of the day, if not all of the data was changed.

Read more »
Minette Steynberg

Using and troubleshooting SQL Server IntelliSense – For SQL Server 2012 or higher

February 29, 2016 by

When I first heard that Microsoft was going to introduce IntelliSense for SQL Server I could hardly contain my excitement. Much to my disappointment the initial version of IntelliSense left a lot to be desired. From issues like just not popping up at all, to not working on remote servers and sometimes picking incorrect entries could make it a bit tedious to use and for those of us who started out on SQL Server before the advent of IntelliSense it was easier just to revert back to good old fashioned typing from memory. I am happy to report however, that there has been great strides in IntelliSense to such an extent that if I suddenly had to live without it, I would be really really sad and my productivity would probably be affected somewhat as well.

Read more »
Sifiso W. Ndlovu

SQL Server pivoting on non-numeric data types

February 19, 2016 by

Introduction

In the article, Multiple Options to Transposing Rows into Columns, I covered various options available in SQL Server to rotating a given row into columns. One of the options included the use of a PIVOT relational operator. The mandatory requirement of the operator is that you must supply the aggregate function with only a numeric data type. Such a mandatory requirement is usually not an issue as most aggregations and subsequent pivoting is performed against fields of numeric data type. However, sometimes the nature of business reporting requests may be such that you are required to cater for pivoting against non-numeric data types. In this article we take a look at how you can deal with such requirements by introducing a workaround to pivoting on non-numeric fields.

Read more »
Ahmad Yaseen

How to configure SQL Server mirroring on a TDE encrypted database

February 19, 2016 by

Securing and encrypting sensitive data stored in your production databases is a big concern, especially the databases storing the organization’s financial data and customers’ confidential information.

SQL Server offers multiple encryption methods in the cell, table and database levels. And in this article, we are interested in a SQL Server database encryption method, introduced in SQL Server 2008, called Transparent Data Encryption (TDE). SQL Server TDE provides encryption on the database file level; it encrypts the database (.MDF), (.LDF), (.NDF), (.BAK), (.DIF), (.TRN) and snapshot files.

The main purpose of this article is showing how we could setup a mirroring site for a database encrypted using SQL Server Transparent Data Encryption. But before starting the demonstration, it is better to introduce TDE first.

Read more »
Joshua Feierman

A DBAs Introduction to Mercurial – Branching and merging

February 19, 2016 by

Introduction

In my previous article, we went over the basics of Mercurial, as well as some arguments why using it is critical for database administrators. Among many reasons, it allows us to easily track history and changes to our scripts, which in turn makes it easier for us to experiment and enhance our toolkit, since we can do so safely without fear of permanently causing damage. In this installment, we are going to go into more depth on the specifics of two feature of Mercurial that, once harnessed, can add significant efficiency to our coding workflows.

Read more »
Ed Pollack

Insight into the SQL Server buffer cache

February 18, 2016 by

When we talk about memory usage in SQL Server, we are often referring to the buffer cache. This is an important part of SQL Server’s architecture, and is responsible for the ability to query frequently accessed data extremely fast. Knowing how the buffer cache works will allow us to properly allocate memory in SQL Server, gauge accurately how databases are accessing data, and ensure that there are not inefficiencies in our code that cause excessive data to be cached.

Read more »
Rajendra Gupta

Peer to peer replication

February 18, 2016 by

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 »
Daniel Calbimonte

How to execute jobs on multiple SQL Servers

February 18, 2016 by

Introduction

In earlier chapters, we explained how to administer multiple servers using the ApexSQL Backup and how to run queries in multiple SQL servers using the SQL Central Management Server. In this new chapter, we will show how to propagate a job from a SQL Server Master Agent Job to a target server.

This feature is called Multiserver Administration. In a multiserver administration, you need a Master Server and one or more target servers. In the master server, you create a copy of the job and then it is copied and executed in the target servers.

Read more »
Page 32 of 45« First...1020...3031323334...40...Last »