SQLServerWiki

“The Only Thing That Is Constant Is Change”

Adding Admin Login to SQL Server

Posted by database-wiki on June 14, 2016

> Try to download and install SQL Server Management studio express and try to login
the customers Windows Account.
> We should be encountering the error à Login failed error 18456.

— Follow the following steps to resolve the issue,

1> Start SQL in single user mode
C:\program files\microsoft sql server\MSSQL.1\MSSQL\Binn\sqlservr.exe -s ACT7-m
-c

or

sqlservr.exe –m (or sqlservr.exe –f) {default instance}

2> Open another CMD window and connected to the instance by,

C:\sqlcmd -S <computer-name>\ACT7 -E

or

sqlcmd -S.  {default instance}

1>Create login [Windows Account] from windows <Eg. ComputerName\Username>
2>go
1>Sp_addsrvrolemember ‘Windows Account’,’sysadmin’
2>go
exit

Posted in SECURITY | Leave a Comment »

HDFS

Posted by database-wiki on June 12, 2016

What is Big Data?

A huge volume of structured, semi-structured and unstructured data that has the potential to be mined for information is considered as big data.

Big data is not a technology it’s an evolving problem.

The problem consists of three components:

a.) The extreme volume of data generated in a short span. (rate at which the data is being generated. For ex, 100’s of GB per hour)

b.) The wide variety of types of data.

c.) The velocity at which the data must be processed.

Although big data doesn’t refer to any specific quantity, the term is often used when speaking about petabytes and exabytes of data, much of which cannot be integrated easily.

Because big data takes too much time and costs too much money to load into a traditional relational database for analysis, new approaches to storing and analyzing data have emerged that rely less on data schema and data quality. Instead, raw data with extended metadata is aggregated in a data lake and machine learning and artificial intelligence (AI) programs use complex algorithms to look for repeatable patterns.

The analysis of large data sets in real-time requires a platform like Hadoop to store large data sets across a distributed cluster and MapReduce to coordinate, combine and process data from multiple sources.

What is Hadoop?

Hadoop is an open source software project that enables the distributed processing of large amount of data sets across clusters of commodity servers.

What components make up Hadoop 2.0?

H1

Hadoop Distributed File System (HDFS) – the Java-based scalable system that stores data across multiple machines (on the cluster) in Hadoop without prior organization.

YARN – resource management framework for scheduling and handling resource requests from distributed applications. (YARN is an acronym for Yet Another Resource Negotiator.)

MapReduce – a software programming model for processing large sets of data in parallel.

What is HDFS?

HDFS is Hadoop Distributed File System, which is responsible for storing data on the cluster in Hadoop. Files in HDFS are split into blocks before they are stored on the cluster.

The default size of a block is 128MB. The blocks belonging to one file are then stored on different nodes. The blocks are also replicated to ensure high reliability.

What are Hadoop Daemons?

Hadoop Daemons are the process that runs in the background. Hadoop has three such daemons. They are NameNode, DataNode and Secondary NameNode. Each daemons runs separately in its own JVM.

H2

Note: In hadoop 2.0, there is no concept of job tracker and task tracker. When a client submits a job, yarn controls the execution of the job.

  • NameNode – It is the Master node which is responsible for storing the meta-data for all the files and directories. It has information such as the blocks that make a file, and where are those blocks located in the cluster. (The role of Master node is to assign tasks to the Slaves.)
  • DataNode – It is the Slave node that contains the actual data. It reports information of the blocks it contains to the NameNode in a periodic fashion. The role of Slave node is to process the tasks that’s assigned by NameNode. The DataNode also send block report for the master to valid it.

The NameNode should run all the time and a failure will make the cluster inaccessible as there would be no information on where the files are located in the cluster.The DataNode report their status to NameNode using heartbeat. The default   heartbeat interval is 3 seconds and If the DataNode does not send heartbeat for 600 seconds (10 minutes) then the NameNode considers that DataNode as dead.

  • Secondary NameNode – The file system change in the NameNode are not updated in the FSImage instead the changes are stored in edit log. The edit log is updated from the in memory FSmetadata. The secondary NameNode periodically merges changes in the edit log with the FSImage so that it doesn’t grow too large in size. It also keeps a copy of the FSImage which can be used in case of failure of NameNode. This merge activity happens every 60 minutes or if the combined size of edit log exceeds 256MB.

Blocks are used:

For horizontal scalability.

For parallel processing as the block are widely spread across DataNodes.

For avoiding space constraints.

Replication is used:

To avoid data loss. By default 3 copies of the same block is stored in 3 different DataNodes.

To handle network failure multiple copies are needed.

Different operations in HDFS 2.0:-

Writing a 2TB file on the cluster:

H3

  1. The HDFS client requests to write crimes_2015-16.csv file on Hadoop cluster by calling ‘Create()’ method of DistributedFileSystem object.
  2. DistributedFileSystem object using RPC call to connect to NameNode and initiates new file creation. Here no blocks are associated in file creation process in the NameNode. The NameNode checks if the file crimes_2015-16.csv already exist in the Hadoop cluster and HDFS client has the required permissions to create new file. If file already exists or client does not have sufficient permission to create a new file, then IOException is thrown to client. Otherwise, operation succeeds and a new record for the file is created by the NameNode.
  3. Once new record is created in NameNode, an object of type FSDataOutputStream is returned to the client. Client uses it to write data into the HDFS.
  4. FSDataOutputStream contains DFSOutputStream object which looks after communication with DataNodes and NameNode. While client continues writing data, DFSOutputStream continues creating packets with this data. These packets are en-queued into a queue which is called as DataQueue.
  5. There is one more component called DataStreamer which consumes this DataQueue. DataStreamer also asks NameNode for allocation of new blocks and NameNode responds with desirable DataNodes to be used for replication.
  6. Now, the process of replication starts by creating a pipeline using DataNodes. In our case, we have chosen replication level of 3 and hence there are 3 DataNodes in the pipeline.
  7. The DataStreamer streams the packet into the first DataNode DN-1 in the pipeline. Generally packets are in 4MB size.
  8. Once the packet is written in DN-1, it is forwarded to DN-2 and DN-4.
  9. Another queue called ‘Ack Queue’ is maintained by DFSOutputStream to store packets which are waiting for acknowledgement from DataNodes.
  10. Once packets are replicated successfully the acknowledgment packet is sent to DFSOutputStream.
  11. DFSOutputStream removed the packet from the ‘Ack Queue’. In the event of any DataNode failure, packets from the ‘Ack queue’ are used to reinitiate the operation.
  12. When write operation is completed by HDFS client, it calls close() method which results in flushing remaining data packets to the pipeline followed by waiting for acknowledgement.
  13. The above process is repeated till the end of file is encountered. Once final acknowledgement is received for a block, file write operation successful is communicated to the name node. Name node saves HDFS metadata information like block location, permission, etc in files called FsImage in the disk, in-memory FSmetadata and EditLog. Note that all the blocks are written serially.
  14. The secondary NameNode queries for edit logs in regular intervals and applies to its FsImage. Once it has new FsImage, it copies the FsImage to the NameNode. The NameNode uses this FsImage for the next restart to reduce the startup time.

Reading a 2TB file from the cluster:

H4

  1. The user provides the filename crimes_2015-16.csv to the HDFS client. HDFS Client initiates read request by calling ‘open()’ method of DistributedFileSystem object.
  2. DistributedFileSystem object connects to namenode using RPC and check if the file exist and HDFS client has got access to read the file. If yes, metadata information such as the locations of the blocks of the file is provided by the NameNode.
  3. In response to this metadata request, addresses of the DataNodes having copy of that block, is returned back. The NameNode provides the location for first few blocks only.
  4. Once addresses of DataNodes are received, an object of type FSDataInputStream is returned to the client. FSDataInputStream contains DFSInputStream which takes care of interactions with DataNode and NameNode. In step 4 shown in above diagram, client invokes ‘read()’ method which causes DFSInputStream to establish a connection with the first DataNode with the first block of file.
  5. Data is read in the form of streams wherein client invokes ‘read()’ method repeatedly. This process of read() operation continues till it reaches end of block which is block b0 in our case. Once the end of block b0 is reached, the HDFS client does a CRC32 checksum validation of the block. If the checksum validation completes the DFSInputStream closes the connection. If the checksum fails mean if the block is corrupt, then the HDFS client will try to access the block b0 in the other replica set.
  6. The DFSInputStream move to the next block in the DataNodes.
  7. The process is repeated till the last block b15 on DataNode DN-4 is read by DFSInputStream.
  8. Once client has done with the reading, it calls close() method.

There are three kinds of failures:

  1. Complete failure:

While writing a particular block the data streamer request the NameNode for block location. The NameNode responds back with set of DataNodes for data pipeline.If the HDFS client is not able to communicate with all the 3 DataNodes after 4 retires a complete failure is reported to NameNode. The NameNode flushes the previous entry of DataNodes and provides a new set of DataNodes to the HDFS client. We call this a complete failure because HDFS client hasn’t even initiated the write operation and since failure has happened in the connect establishment phase itself.

  1. Partial failure:

When a write operation has been initiated for the data pipeline of 3 DataNodes and when a write fails for one of the DataNode the HDFS client waits for a specific time and reports the error to the NameNode as partial failure based on the replication factor meaning if the replication factor is set to 1 or 2 – no error will be reported as we have 2 copies written successfully. If the replication factor is greater than 2 then the error will be reported as we have only two copies of the block that being successfully written. Here the process moves forward and the copying of the failed block happens when metadata validates the block scan report from all the DataNodes. Writing of failed block will be done later to satisfy the replication factor thats been set.

  1. Acknowledgement failure:

If the block has been written successfully but sending acknowledgement has failed for one of the DataNode then NameNode considers this as a partial failure. The HDFS client reprocess the duplicate block (renames the failed block id) by seeking new allocation locations from NameNode. The NameNode provides fresh site of DataNodes for the pipeline and the process of write operation starts once again till the success acknowledgement message is received from all the DataNodes. As soon the write is complete all the DataNode will scan all the blocks and send the block report to NameNode. The NameNode validates and finds if there is any block that does not have metadata associated with it. The NameNode sends a command to the DataNode to remove the file that does not have relevant metadata in the name node. In this way the orphaned blocks as a result of acknowledgement failures are removed.

High Availability for NameNode:

We can implement high availability for NameNode by adding additional NameNode to solve single point of failure (SPOF) of NameNode. The additional NameNode will be passive. If the active NodeNode is dead then the zookeeper failover controller changes the status of passive NameNode to Active based on fencing and lock mechanism.

The metadata between the two nodes are kept in sync by: 1. Quorum of Journals (recommended) 2. Shared Disk. (Not recommended)

  1. Quorum of Journals:

H5.png

Prior to Hadoop 2.0, the NameNode was a single point of failure (SPOF) in an HDFS cluster. Each cluster had a single NameNode, and if that machine was unavailable, the cluster on the whole would be unavailable until the NameNode was either restarted or started on a separate machine. In a classic HA cluster, two separate machines are configured as NameNodes. At any point, one of the NameNodes will be in Active state and the other will be in a Standby state. The Active NameNode is responsible for all client operations in the cluster, while the Standby is simply acting as a slave, maintaining enough state to provide a fast failover.

In order for the Standby node to keep its state coordinated with the Active node, both nodes communicate with a group of separate daemons called ‘JournalNodes’ (JNs). Every edit log modification in the Active node is logged in the JournalNodes. The Standby node is capable of reading the amended information from the JNs, and is regularly monitoring them for changes. As the Standby Node sees the changes, it then applies them to its own edit log. In case of a failover, the Standby will make sure that it has read all the changes from the JounalNodes before changing its state to ‘Active state’. This guarantees that the namespace state is fully synched before a failover occurs.

To provide a fast failover, it is essential that the Standby node have to have the updated and current information regarding the location of blocks in the cluster. For this to happen, the DataNodes are configured with the location of both NameNodes, and send block location information and heartbeats to both.

It is essential that only one of the NameNodes must be Active at a time. Otherwise, the namespace state would deviate between the two and lead to data loss or erroneous results. In order to avoid this, the JournalNodes will only permit a single NameNode to a writer at a time. During a failover, the NameNode which is to become active will take over the responsibility of writing to the JournalNodes.

  1. Shared Disk:

Here the metadata is stored in a network storage disk share by both the Active and Passive NameNodes. If the shared disk fails the entire cluster goes down and hence this option is not recommended.

The above two solution only solve the SPOF problem in NameNode. The NameNode unavailability due to overwhelming requests is solved by Hadoop federation.

HDFS is a fit when,

  • Files to be stored are large in size.
  • Your application need to write once and read many times.
  •  You want to use cheap, commonly available hardware.

And it is not a fit when,

  • You want to store a large number of small files. It is better to store millions of large file when compare to billions of small files.
  • There are multiple writers. It is only designed for writing at the end of file and not at a random offset.

Installation of Hadoop.

Pseudo Distributed Cluster Installation Mode:

Apache Hadoop v2.7.1

Linux Operating System (Ubuntu 12.04)

  1. Download the latest stable version of Apache Hadoop tarball distribution.
  1. Download Java1.7 JDK tarball. Consider the architecture 32 bit (i386, i586, i686),  64bit (x86_64) before downloading.
  1. Assuming that the downloaded tarballs are present under the home directory of the user. Extract the tarballs

~$ tar -xvf hadoop-2.7.1.tar.gz

~$ tar -xvf jdk-7u79-linux-x86_64.gz

  1. After extracting, set up the environment variables in ~/.bashrc file

~$vim .bashrc

export JAVA_HOME=/home/balajimani/jdk1.7.0_79

export HADOOP_PREFIX=/home/balajimani/hadoop-2.7.1

export HADOOP_HOME=${HADOOP_PREFIX}

export HADOOP_CONF_DIR=${HADOOP_PREFIX}/etc/hadoop

export HUE_HOME=/home/balajimani/hue

export PATH=$JAVA_HOME/bin:$HADOOP_HOME/bin:$HADOOP_HOME/sbin:$HUE_HOME/build/env/bin:$PATH

After appending these lines, save and close the file.

  1. For these variables to be set for the current shell, source the file.

~$ source .bashrc

Check whether the changes have been applied properly

~$ java -version

~$ hadoop version

  1. Next, edit the hadoop configuration files.

 ~$ cd $HADOOP_CONF_DIR

~hadoop-2.7.1/etc/hadoop$ vim hadoop-env.sh

 export JAVA_HOME=/home/balajimani/jdk1.7.0_79

~hadoop-2.7.1/etc/hadoop$ vim core-site.xml

<configuration>

<property>

<name>fs.defaultFS</name>

<value>hdfs://localhost:8020</value>

</property>

<property>

<name>hadoop.proxyuser.balajimani.hosts</name>

<value>*</value>

</property>

<property>

<name>hadoop.proxyuser.balajimani.groups</name>

<value>*</value>

</property>

</configuration>

~hadoop-2.7.1/etc/hadoop$ vim hdfs-site.xml

<configuration>

<property>

<name>dfs.namenode.name.dir</name>

<value>/home/balajimani/name</value>

</property>

<property>

<name>dfs.datanode.data.dir</name>

<value>/home/balajimani/data</value>

</property>

<property>

<name>dfs.webhdfs.enabled</name>

<value>true</value>

</property>

</configuration>

~hadoop-2.7.1/etc/hadoop$ vim yarn-site.xml

 <configuration>

<property>

<name>yarn.resourcemanager.hostname</name>

<value>localhost</value>

</property>

<property>

<name>yarn.nodemanager.aux-services</name>

<value>mapreduce_shuffle</value>

</property>

</configuration>

~hadoop-2.7.1/etc/hadoop$ cp mapred-site.xml.template mapred-site.xml

~hadoop-2.7.1/etc/hadoop$ vim mapred-site.xml

<configuration>

<property>

<name>mapreduce.framework.name</name>

<value>yarn</value>

</property>

</configuration>

~hadoop-2.7.1/etc/hadoop$ vim httpfs-site.xml

<property>

<name>httpfs.proxyuser.balajimani.hosts</name>

<value>*</value>

</property>

<property>

<name>httpfs.proxyuser.balajimani.groups</name>

<value>*</value>

</property>

~hadoop-2.7.1/etc/hadoop$ vim slaves

Localhost

After adding all these entries into their respective configuring files, the Hadoop is set to start in Pseudo distributed mode.

  1. To enable password-less login thru ssh

~$ ssh-keygen

~$ ssh-copy-id -i ~/.ssh/id_rsa.pub localhost

 This procedure avoids prompting for password, when starting the daemons.

  1. Format the namenode before starting the daemons.

~$hdfs namenode –format

This formats the dfs.namenode.name.dir location and creates the necessary files and folders required for namenode.

Note: Steps 7 and 8 are one-time procedures.

  1. Start the cluster

~$ start-dfs.sh

~$ start-yarn.sh

Alternatively, to start all the daemons

~$ start-all.sh

  1. To check for the daemons, use jps (java process status)

~$ jps

  1. To Stop the cluster

~$ stop-yarn.sh

~$ stop-dfs.sh

To stop all the daemons in one go

~$ stop-all.sh

Note: To stop or start daemons individually.

~$hadoop-demon.sh <start | stop> <namenode | datanode>

~$yarn-daemon.sh <start | stop> <resourcemanager | nodemanager>

~$mr-jobhistory-daemon.sh <start | stop> historyserver

HUE INSTALLATION:

Note: HUE is a GUI used for file manipulation in HDFS.

Hue Prerequisites: Already installed while installing OS

~$ sudo apt-get update

~$ sudo apt-get install gcc g++ libxml2-dev libxslt-dev libsasl2-dev libsasl2-modules-gssapi-mit libmysqlclient-dev python-dev python-setuptools libsqlite3-devant libsasl2-dev libsasl2-modules-gssapi-mit libkrb5-dev libtidy-0.99-0 libldap2-devlibssl-dev

The first command will update your ubuntu repositories and the next will install the necessary packages for installing Hue.

Let us begin with the assumption that hue tarball (hue-3.8.1.tgz) is already downloaded from the vendor site and is present under the home folder of the “balajimani” user.

  1. Extract the contents of the tarball

~$ tar -xvf /home/balajimani/hue-3.8.1.tgz

  1. Enter the folder and install using the following command

~$ cd /home/balajimani/hue-3.8.1

~$ PREFIX=/home/balajimani/ make install

The above command will run for some time. Proceed with the next step after the completion of the above command.

Configure Hue:

~$ cd $HUE_HOME/desktop/conf/

~$ vim hue.ini

Edit the following parameters

[desktop]

server_user=balajimani

server_group=balajimani

default_user=balajimani

default_hdfs_superuser=balajimani

Start HUE:

~$ nohup supervisor &

NAMENODE UI: http://localhost:50070

H6

YARN UI: http://localhost:8088

H7.png

HUE UI: http://localhost:8888

H8

HDFS commands:

Hadoop file system (fs) shell commands are used to perform various file operations like copying file, changing permissions, viewing the contents of the file, changing ownership of files, creating directories etc.

The syntax of fs shell command is

hdfs dfs <args>

Hdfs dfs Shell Commands

hdfs dfs ls:

The hadoop ls command is used to list out the directories and files. An example is shown below:

balajimani@ubuntu:~$ hdfs dfs -ls /user/balajimani

Found 2 items

drwxr-xr-x   – balajimani balajimani          0 2016-05-29 04:14 /user/balajimani/.Trash

-rw-r–r–   3 balajimani balajimani          6 2016-05-29 04:41 /user/balajimani/hello.txt

The above command lists out the files in the balajimani directory.

The second column of file hello.txt indicates the number of replicas and for a directory, the second field is empty.

hdfs dfs lsr:

The hadoop lsr command recursively displays the directories, sub directories and files in the specified directory. The usage example is shown below:

balajimani@ubuntu:~$ hdfs dfs -lsr /user/balajimani

lsr: DEPRECATED: Please use ‘ls -R’ instead.

drwxr-xr-x   – balajimani balajimani          0 2016-05-29 04:14 /user/balajimani/.Trash

drwxr-xr-x   – balajimani balajimani          0 2016-05-29 04:14 /user/balajimani/.Trash/Current

drwxr-xr-x   – balajimani balajimani          0 2016-05-29 04:14 /user/balajimani/.Trash/Current/tmp

-rw-r–r–   3 balajimani supergroup            0 2016-05-29 04:14 /user/balajimani/.Trash/Current/tmp/hue_config_validation.2028913943167022256

-rw-r–r–   3 balajimani balajimani          6 2016-05-29 04:41 /user/balajimani/hello.txt

The hdfs dfs lsr command is similar to the ls -R command in unix.

hdfs dfs cat:

Hadoop cat command is used to print the contents of the file on the terminal (stdout). The usage example of hadoop cat command is shown below:

balajimani@ubuntu:~$ hdfs dfs -cat /user/balajimani/hello.txt

hello

hdfs dfs chgrp:

hadoop chgrp shell command is used to change the group association of files. Optionally you can use the -R option to change recursively through the directory structure. The usage of

hdfs dfs -chgrp is shown below:

hdfs dfs -chgrp [-R] <NewGroupName> <file or directory name>

hdfs dfs chmod:

The hadoop chmod command is used to change the permissions of files. The -R option can be used to recursively change the permissions of a directory structure. The usage is shown below:

hdfs dfs -chmod [-R] <mode | octal mode> <file or directory name>

hdfs dfs chown:

The hadoop chown command is used to change the ownership of files. The -R option can be used to recursively change the owner of a directory structure. The usage is shown below:

hdfs dfs -chown [-R] <NewOwnerName>[:NewGroupName] <file or directory name>

hdfs dfs mkdir:

The hadoop mkdir command is for creating directories in the hdfs. You can use the -p option for creating parent directories. This is similar to the unix mkdir command. The usage example is shown below:

balajimani@ubuntu:~$ hdfs dfs -mkdir /user/balajimani/hadoopdemo

balajimani@ubuntu:~$ hdfs dfs -lsr /user/balajimani

lsr: DEPRECATED: Please use ‘ls -R’ instead.

drwxr-xr-x   – balajimani balajimani          0 2016-05-29 04:14 /user/balajimani/.Trash

drwxr-xr-x   – balajimani balajimani          0 2016-05-29 04:14 /user/balajimani/.Trash/Current

drwxr-xr-x   – balajimani balajimani          0 2016-05-29 04:14 /user/balajimani/.Trash/Current/tmp

-rw-r–r–   3 balajimani supergroup            0 2016-05-29 04:14 /user/balajimani/.Trash/Current/tmp/hue_config_validation.2028913943167022256

drwxr-xr-x   – balajimani balajimani          0 2016-06-03 07:31 /user/balajimani/hadoopdemo

-rw-r–r–   3 balajimani balajimani          6 2016-05-29 04:41 /user/balajimani/hello.txt

The above command creates the hadoopdemo directory in the /user/balajimani directory.

balajimani@ubuntu:~$ hdfs dfs -mkdir -p /user/balajimani/dir1/dir2

balajimani@ubuntu:~$ hdfs dfs -lsr /user/balajimani

lsr: DEPRECATED: Please use ‘ls -R’ instead.

drwxr-xr-x   – balajimani balajimani          0 2016-05-29 04:14 /user/balajimani/.Trash

drwxr-xr-x   – balajimani balajimani          0 2016-05-29 04:14 /user/balajimani/.Trash/Current

drwxr-xr-x   – balajimani balajimani          0 2016-05-29 04:14 /user/balajimani/.Trash/Current/tmp

-rw-r–r–   3 balajimani supergroup            0 2016-05-29 04:14 /user/balajimani/.Trash/Current/tmp/hue_config_validation.2028913943167022256

drwxr-xr-x   – balajimani balajimani          0 2016-06-03 07:32 /user/balajimani/dir1

drwxr-xr-x   – balajimani balajimani          0 2016-06-03 07:32 /user/balajimani/dir1/dir2

drwxr-xr-x   – balajimani balajimani          0 2016-06-03 07:31 /user/balajimani/hadoopdemo

-rw-r–r–   3 balajimani balajimani          6 2016-05-29 04:41 /user/balajimani/hello.txt

The above command creates the dir1/dir2 directory in /user/balajimani directory.

hdfs dfs copyFromLocal:

The hadoop copyFromLocal command is used to copy a file from the local file system to the hadoop hdfs. The syntax and usage example are shown below:

Syntax:

hdfs dfs -copyFromLocal <localsrc> URI

Example:

Check the data in local file

balajimani@ubuntu:~$ cat sample.txt

Kill Bill 1

Kill Bill 2

pulp fiction

Now copy this file to hdfs

balajimani@ubuntu:~$ hdfs dfs -copyFromLocal sample.txt /user/balajimani/dir1

View the contents of the hdfs file.

balajimani@ubuntu:~$ hdfs dfs -ls /user/balajimani/dir1

Found 2 items

drwxr-xr-x   – balajimani balajimani          0 2016-06-03 07:32 /user/balajimani/dir1/dir2

-rw-r–r–   3 balajimani balajimani         37 2016-06-03 08:14 /user/balajimani/dir1/sample.txt

balajimani@ubuntu:~$ hdfs dfs -cat /user/balajimani/dir1/sample.txt

Kill Bill 1

Kill Bill 2

pulp fiction

hdfs dfs copyToLocal:

The hadoop copyToLocal command is used to copy a file from the hdfs to the local file system. The syntax and usage example is shown below:

Syntax

hdfs dfs -copyToLocal [-ignorecrc] [-crc] URI <localdst>

Example:

balajimani@ubuntu:~$ hdfs dfs -copyToLocal /user/balajimani/hello.txt dir1

16/06/03 08:26:26 WARN hdfs.DFSClient: DFSInputStream has been closed already

The -ignorecrc option is used to copy the files that fail the crc check. The -crc option is for copying the files along with their CRC.

hdfs dfs cp:

The hadoop cp command is for copying the source into the target. The cp command can also be used to copy multiple files into the target. In this case the target should be a directory. The syntax is shown below:

balajimani@ubuntu:~$ hdfs dfs -cp /user/balajimani/hello.txt /user/balajimani/dir1/hello.txt

16/06/03 08:31:15 WARN hdfs.DFSClient: DFSInputStream has been closed already

balajimani@ubuntu:~$ hdfs dfs -ls /user/balajimani/dir1

Found 3 items

drwxr-xr-x   – balajimani balajimani          0 2016-06-03 07:32 /user/balajimani/dir1/dir2

-rw-r–r–   3 balajimani balajimani          6 2016-06-03 08:31 /user/balajimani/dir1/hello.txt

-rw-r–r–   3 balajimani balajimani         37 2016-06-03 08:14 /user/balajimani/dir1/sample.txt

balajimani@ubuntu:~$ hdfs dfs -cp /user/balajimani/hello.txt /user/balajimani/sample.txt hdfs://namenodehost/user/balajimani/dir2

-cp: java.net.UnknownHostException: namenodehost

Usage: hdfs dfs [generic options] -cp [-f] [-p | -p[topax]] <src> … <dst>

balajimani@ubuntu:~$ hdfs dfs -cp /user/balajimani/hello.txt /user/balajimani/sample.txt hdfs://localhost/user/balajimani/dir2

16/06/03 08:39:51 WARN hdfs.DFSClient: DFSInputStream has been closed already

16/06/03 08:39:51 WARN hdfs.DFSClient: DFSInputStream has been closed already

balajimani@ubuntu:~$ hdfs dfs -ls /user/balajimani/dir2

Found 2 items

-rw-r–r–   3 balajimani balajimani          6 2016-06-03 08:39 /user/balajimani/dir2/hello.txt

-rw-r–r–   3 balajimani balajimani         37 2016-06-03 08:39 /user/balajimani/dir2/sample.txt

hdfs dfs -put:

Hadoop put command is used to copy multiple sources to the destination system. The put command can also read the input from the stdin. The different syntaxes for the put command are shown below:

Syntax1: copy single file to hdfs

balajimani@ubuntu:~$ hdfs dfs -put test.txt /user/balajimani/dir2

balajimani@ubuntu:~$ hdfs dfs -ls /user/balajimani/dir2

Found 3 items

-rw-r–r–   3 balajimani balajimani          6 2016-06-03 08:39 /user/balajimani/dir2/hello.txt

-rw-r–r–   3 balajimani balajimani         37 2016-06-03 08:39 /user/balajimani/dir2/sample.txt

-rw-r–r–   3 balajimani balajimani         31 2016-06-03 08:44 /user/balajimani/dir2/test.txt

Syntax2: copy multiple files to hdfs

balajimani@ubuntu:~$ hdfs dfs -put test2.txt test3.txt /user/balajimani/dir2

balajimani@ubuntu:~$ hdfs dfs -ls /user/balajimani/dir2

Found 5 items

-rw-r–r–   3 balajimani balajimani          6 2016-06-03 08:39 /user/balajimani/dir2/hello.txt

-rw-r–r–   3 balajimani balajimani         37 2016-06-03 08:39 /user/balajimani/dir2/sample.txt

-rw-r–r–   3 balajimani balajimani         31 2016-06-03 08:44 /user/balajimani/dir2/test.txt

-rw-r–r–   3 balajimani balajimani         14 2016-06-03 08:49 /user/balajimani/dir2/test2.txt

-rw-r–r–   3 balajimani balajimani         13 2016-06-03 08:49 /user/balajimani/dir2/test3.txt

hdfs dfs get:

Hadoop get command copies the files from hdfs to the local file system. The syntax of the get command is shown below:

balajimani@ubuntu:~$ hdfs dfs -get /user/balajimani/dir2/test2.txt test2.txt

16/06/03 09:00:04 WARN hdfs.DFSClient: DFSInputStream has been closed already

balajimani@ubuntu:~$ ls –lrt

-rw-r–r–  1 balajimani balajimani   14 Jun  3 09:00 test2.txt

hdfs dfs getmerge:

hadoop getmerge command concatenates the files in the source directory into the destination file. The syntax of the getmerge shell command is shown below:

hdfs dfs -getmerge <src> <localdst> [addnl]

The addnl option is for adding new line character at the end of each file.

hdfs dfs moveFromLocal:

The hadoop moveFromLocal command moves a file from local file system to the hdfs directory. It removes the original source file. The usage example is shown below:

balajimani@ubuntu:~$ hdfs dfs -rm /user/balajimani/dir2/test2.txt /user/balajimani/dir2/test3.txt

16/06/03 09:01:43 INFO fs.TrashPolicyDefault: Namenode trash configuration: Deletion interval = 0 minutes, Emptier interval = 0 minutes.

Deleted /user/balajimani/dir2/test2.txt

16/06/03 09:01:43 INFO fs.TrashPolicyDefault: Namenode trash configuration: Deletion interval = 0 minutes, Emptier interval = 0 minutes.

Deleted /user/balajimani/dir2/test3.txt

balajimani@ubuntu:~$ hdfs dfs -moveFromLocal test2.txt test3.txt /user/balajimani/dir2

balajimani@ubuntu:~$ hdfs dfs -ls /user/balajimani/dir2

Found 2 items

-rw-r–r–   3 balajimani balajimani         14 2016-06-03 09:03 /user/balajimani/dir2/test2.txt

-rw-r–r–   3 balajimani balajimani         13 2016-06-03 09:03 /user/balajimani/dir2/test3.txt

hdfs dfs mv:

It moves the files from source hdfs to destination hdfs. Hadoop mv command can also be used to move multiple source files into the target directory. In this case the target should be a directory. The syntax is shown below:

balajimani@ubuntu:~$ hdfs dfs -mv /user/balajimani/dir2/test2.txt /user/balajimani/dir1

balajimani@ubuntu:~$ hdfs dfs -ls /user/balajimani/dir1

Found 4 items

drwxr-xr-x   – balajimani balajimani          0 2016-06-03 07:32 /user/balajimani/dir1/dir2

-rw-r–r–   3 balajimani balajimani          6 2016-06-03 08:31 /user/balajimani/dir1/hello.txt

-rw-r–r–   3 balajimani balajimani         37 2016-06-03 08:14 /user/balajimani/dir1/sample.txt

-rw-r–r–   3 balajimani balajimani         14 2016-06-03 09:03 /user/balajimani/dir1/test2.txt

hdfs dfs du:

The du command displays aggregate length of files contained in the directory or the length of a file in case its just a file. The syntax and usage is shown below:

balajimani@ubuntu:~$ hdfs dfs -du hdfs://localhost/user/balajimani/dir2/

6   hdfs://localhost/user/balajimani/dir2/hello.txt

37  hdfs://localhost/user/balajimani/dir2/sample.txt

31  hdfs://localhost/user/balajimani/dir2/test.txt

13  hdfs://localhost/user/balajimani/dir2/test3.txt

hdfs dfs dus:

The hadoop dus command prints the summary of file lengths

balajimani@ubuntu:~$ hdfs dfs -dus hdfs://localhost/user/balajimani

dus: DEPRECATED: Please use ‘du -s’ instead.

183  hdfs://localhost/user/balajimani

hdfs dfs expunge:

Used to empty the trash. The usage of expunge is shown below:

balajimani@ubuntu:~$ hdfs dfs -expunge

16/06/03 09:26:51 INFO fs.TrashPolicyDefault: Namenode trash configuration: Deletion interval = 0 minutes, Emptier interval = 0 minutes.

16/06/03 09:26:51 INFO fs.TrashPolicyDefault: Created trash checkpoint: /user/balajimani/.Trash/160603092651

hdfs dfs rm:

Removes the specified list of files and empty directories. An example is shown below:

balajimani@ubuntu:~$ hdfs dfs -rm /user/balajimani/dir2/test3.txt

16/06/03 09:28:34 INFO fs.TrashPolicyDefault: Namenode trash configuration: Deletion interval = 0 minutes, Emptier interval = 0 minutes.

Deleted /user/balajimani/dir2/test3.txt

hdfs dfs -rmr:

Recursively deletes the files and sub directories. The usage of rmr is shown below:

balajimani@ubuntu:~$ hdfs dfs -rmr /user/balajimani/dir2

rmr: DEPRECATED: Please use ‘rm -r’ instead.

16/06/03 09:29:04 INFO fs.TrashPolicyDefault: Namenode trash configuration: Deletion interval = 0 minutes, Emptier interval = 0 minutes.

Deleted /user/balajimani/dir2

hdfs dfs setrep:

Hadoop setrep is used to change the replication factor of a file. Use the -R option for recursively changing the replication factor.

balajimani@ubuntu:~$ hdfs dfs -setrep -w 2 /user/balajimani/dir1/sample.txt

Replication 2 set: /user/balajimani/dir1/sample.txt

Waiting for /user/balajimani/dir1/sample.txt ……

hdfs dfs stat:

Hadoop stat returns the stats information on a path. The syntax of stat is shown below:

hdfs dfs -stat URI

balajimani@ubuntu:~$ hdfs dfs -stat /user/balajimani

2016-06-03 16:29:04

hdfs dfs tail:

Hadoop tail command prints the last kilobytes of the file. The -f option can be used same as in unix.

balajimani@ubuntu:~$ hdfs dfs -tail /user/balajimani/dir1/sample.txt

Kill Bill 1

Kill Bill 2

pulp fiction

Posted in BigData | Leave a Comment »

Ways to migrate SQL Server User databases from one storage to another.

Posted by database-wiki on April 28, 2016

  1. Copy the files. This would be easy but relatively slow, also requires taking the database offline to move the files. Since uptime is paramount, this is a no-go.
  1. Backup and restore with new name onto the new SAN. This is a very strong candidate, due to its simplicity.

Restoring a backup on the new storage (with a different database name) while the current database remains online.

Create a job to restore the Transaction Log backup (incurred in the meantime) of original database to renamed database.

Cutting over would involve backing up the tail of the transaction log on the old database, restoring it on the new one, and then swapping names.

The cutover would be quick enough to fit in a maintenance window, making this a very attractive option.

  1. BACKUP LAST TRAN LOG On Primary database.

BACKUP LOG Check21DB TO DISK = ‘C:\MSSQL\BACKUP\FTRN\’ WITH norecovery

  1. RESTORE the above LAST LOGs for each database ON renamed database with recovery

RESTORE LOG DBNAME FROM DISK = ‘\\SecondaryServer\C$\MSSQL\Backup\FTRN\xxx.trn’ WITH RECOVERY

Note: This will have an overhead on the instance as we are introducing a duplicate copy of large databases on the same instance which will increase the resource consumption and may have performance impact.

  1. Add and remove files from the filegroup. This approach is quite slow and tedious. SQL Server allows you to add and remove files from filegroups at almost any time,

and this can be leveraged to move data between different storage systems. What makes it awesome is that the operation is completely online with no interruptions to the user,

all types of data (including LOB data) is moved, and it works in any edition of SQL Server.

Note: As always, there are trade-offs. It’s very slow, and LOB data makes it even slower.

Shrinking the data files may introduce fragmentation but can be solved using REBUILD or REORGANIZE, and if speed is not important, it could be the way to go.

  1. Mirroring. Much like arolling upgrade, mirroring can be used.

Steps:

  1. Setup a database mirroring on a secondary instance. Secondary database(s) to be placed on Nimble Storage.
  2. Failover the database(s) to secondary instance.
  3. Remove Mirroring and Setting Secondary Database Online.

ALTER DATABASE [database_name] SET PARTNER OFF

GO

RESTORE DATABASE [database_name] WITH RECOVERY

GO

  1. Detach the Secondary database from secondary instance followed by removing the clustered disk and adding it to primary instance.
  2. Remove or rename the primary database(s).
  3. Attach the database(s) from the Nimble storage.

Note: Unfortunately both primary database and secondary database cannot reside on the same instance so a secondary instance is needed.

  1. Log shipping: As an alternate to mirroring, we can use log shipping.

Steps:

  1. Setup a Log Shipping on a secondary instance. Secondary database(s) to be placed on Nimble Storage.
  2. Changing the secondary as primary:
  3. Ensure all scheduled backups have completed successfully.
  4. STOP/DISABLE LOG SHIPPING BACKUP JOBS.
  5. RUN (LOG SHIPPING) TRAN LOG COPY AND RESTORE JOBS ON Secondary until the last log is applied then DISABLE all copy & restore jobs.
  6. REMOVE THE LOG-SHIPPING FROM PRIMARY SERVER’S MAINTENANCE PLANS for the required Db’s.
  7. Go to maintenance jobs folder ON Primary.
  8. delete destination server information from inside the maintenance job for each DB.

iii.           remove log shipping & delete maintenance job.

  1. Kill all users IN Required database on primary server.
  2. BACKUP LAST TRAN LOG On Primary Server and place them is easy accessible folder Eg.(\\Primary Server Name\C$\MSSQL\Backup\FTRN)
  3. BACKUP LOG Check21DB TO DISK = ‘C:\MSSQL\BACKUP\FTRN\’ WITH norecovery
  4. COPY LAST LOGS TO Secondary Server to say ‘FTRN’ folder (\\Secondary Server \C$\MSSQL\Backup\FTRN)

E.g: xp_cmdshell ‘copy \\PriamryServer \C$\MSSQL\BACKUP\FTRN\*.trn \\SecondaryServer\C$\MSSQL\Backup\FTRN’

  1. RESTORE the above LAST LOGs for each database ON Secondary with recovery

RESTORE LOG DBNAME FROM DISK = ‘\\SecondaryServer\C$\MSSQL\Backup\FTRN\xxx.trn’ WITH RECOVERY

  1. Detach the Secondary database from secondary instance followed by removing the clustered disk and adding it to primary instance.
  2. Remove or rename the primary database(s).
  3. Attach the database(s) from the Nimble storage.

Note: More complicated than mirroring and both primary database and secondary database cannot reside on the same instance.

Posted in CORE ISSUES, DATABASE SPACE MANAGEMENT, Uncategorized | Leave a Comment »

Disk Benchmark testing using SQLIO.

Posted by database-wiki on April 28, 2016

SQL Server I/O characteristics.

OPERATION RANDOM/SEQUENTIAL READ/WRITE SIZE RANGE
Create Database Sequential Write 512KB
Backup Database Sequential Read/Write Multiples of 64KB
Restore Database Sequential Read/Write Multiples of 64KB
DBCC – CHECKDB Sequential Read 8KB-64KB
Alter Index – on -rebuild (Read Phase) Sequential Read 64KB-512KB
Alter Index – on -rebuild (Write Phase) Random or Sequential Write 8KB upto 128KB
Sys.dm_db_index_physical_stats Sequential Read 8KB-64KB
Insert / Update / Delete Random or Sequential Write 64KB-512KB
Select Random or Sequential Read 64KB-512KB
TempDB Random or Sequential Read/Write 8KB-64KB
Transaction Log Sequential Write 60KB

Summary of SQL Server I/O Patterns that Determine SQLIO parameters.

  1. SQL Server only uses one worker thread per logical CPU at a time and this is managed by SOS Scheduler inside SQL Server. So SQLIO Param.txt file should be configured such that we use the number of logic CPUs  as number of threads.
  2. SQL Server performs data writes periodically, when Checkpoint runs (or under memory pressure when Lazy Writer kicks in). Data modifications are done in memory and those changes flushed to disk by a single system thread that executes Checkpoint (or Lazy Writer). Therefore it makes no sense to configure more than 1 thread in Params.txt when testing writes. In addition, SQL Server writes up to 32 pages – 256 KB – at a time. Therefore, it makes sense to use 256 KB as write size. One exception is Eager Writes, which are performed by minimally logged operations. Those would be 8 KB (or perhaps 64 KB in size).
  3. SQL Server performs data reads continuously. Most of the reads are done in 8 KB pages (singe page reads). Reads-ahead reads are done in 64-page (512 KB) chunks. Reading full extents – 64 KB in size- are also somewhat common. Therefore these three sizes are the only read sizes.
  4. Transaction Log writes vary in size depending on workload (transaction performed) but typically do not exceed 60 KB. Therefore, choosing a size to test for log writes is difficult, but as a test, I better we choose 8 KB (which will cover both eager writes and log writes).
  5. SQL Server is designed to maximize sequential I/O as much as possible (both reads or writes). Random and Sequential I/O play a smaller role in today’s SAN systems because of large storage caches, optimized read/write mechanisms, multiple spindles, etc.

Based on these patterns, below are the list of commands that I have used.

Random Read Tests

Similar to single-page reads (8 KB) in SQL. Use as many threads as logical CPUs in the Param.txt. 

In our case 48 CPU’s and we are using 90 GB files for testing: 

O:\SQLIOMP\Disk1\testfile.dat 48 0x0 92160

sqlio -kR -s7200 -frandom -o8 -b8 -LS -FparamMT.txt > Reads8KRandom8Oustanding.txt

Similar to extent reads I/O 64KB; use as many threads as CPUs in the Param.txt

sqlio -kR -s7200 -frandom -o8 -b64 -LS -FparamMT.txt > Reads64KRandom8Oustanding.txt

Similar to Read-Ahead in SQL; use as many threads as CPUs

sqlio -kR -s7200 -frandom -o8 -b512 -LS -FparamMT.txt > Reads512KRandom8Oustanding.txt

Random Write tests

8 KB Writes – similar to single-page writes in SQL, which are rare, or potentially similar to Log Writes though log write sizes vary . Also Eager Writes may be similar; use as many threads as CPUs in the Param.txt

sqlio -kW -s7200 -frandom -o8 -b8 -LS -FparamMT.txt > Writes8KRandom8Outstanding.txt

256 KB Writes similar to Checkpoint in SQL with a realistic outstanding I/O count 100. This will really push the I/O subsystem, but is realistic and can happen. Use 1 (max 2 threads) in Param.txt – similar to checkpoint.

In our case 48 CPUs and we are using 90 GB files for testing: :

O:\SQLIOMP\Disk1\testfile.dat 1 0x0 92160

sqlio -kW -s7200 -frandom -o100 -b256 -LS -FparamST.txt > Writes256KRandom100Outstanding.txt

256 KB Writes similar to Checkpoint in SQL with a possible realistic outstanding I/O count 200. This will really, really push the I/O subsystem, but could happen. Use 1 thread in Param.txt – similar to checkpoint

sqlio -kW -s7200 -frandom -o200 -b256 -LS -FparamST.txt > Writes256KRandom200Outstanding.txt

Sequential Read Tests

sqlio -kR -s7200 -fsequential -o8 -b8 -LS -FparamMT.txt > Reads8Ksequential8Oustanding.txt

sqlio -kR -s7200 -fsequential -o8 -b64 -LS -FparamMT.txt > Reads64Ksequential8Oustanding.txt

sqlio -kR -s7200 -fsequential -o8 -b512 -LS -FparamMT.txt > Reads512Ksequential8Oustanding.txt

Sequential Write tests

sqlio -kW -s7200 -fsequential -o8 -b8 -LS -FparamMT.txt > Writes8Ksequential8Outstanding.txt

sqlio -kW -s7200 -fsequential -o100 -b256 -LS -FparamST.txt > Writes256Ksequential100Outstanding.txt

sqlio -kW -s7200 -fsequential -o200 -b256 -LS -FparamST.txt > Writes256Ksequential200Outstanding.txt

SQLIO parameter reference:

  • -kW and -kR: means we’re testing writes or reads
  • -t8 and -o8: means 8 threads with up to 8 outstanding requests at once.  SQLIO isn’t CPU-bound at all, and you can use more threads than you have processors.  The more load we throw at storage, the faster it goes – to a point.
  • -s7200: means the test will last 120 minutes.(2 hrs)
  • -b8 , -b64, -b512: the size of our IO requests in kilobytes.  SQL Server does a lot of random stuff in 8KB chunks, and we’re also testing sequential stuff in 64KB chunks.
  • -frandom and -fsequential: random versus sequential access.  Many queries jump around randomly in the database, whereas things like backups, bulk loads, and table scans generally work sequentially.

A sample of SQLIO is shown below.

8KB random reads using 90 GB file with 8 outstanding I/O request:

sqlio v1.5.SG
using system counter for latency timings, 2539062 counts per second
parameter file used: paramMT.txt
file O:\SQLIOMP\Disk1\testfile.dat with 48 threads (0-47) using mask 0x0 (0)
48 threads reading for 7200 secs from file O:\SQLIOMP\Disk1\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
size of file O:\SQLIOMP\Disk1\testfile.dat needs to be: 96636764160 bytes
current file size: 0 bytes
need to expand by: 96636764160 bytes
expanding O:\SQLIOMP\Disk1\testfile.dat … done.
using specified size: 92160 MB for file: O:\SQLIOMP\Disk1\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 15504.09
MBs/sec: 121.12
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 24
Max_Latency(ms): 4887
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 13 21 7 4 3 3 2 2 2 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 32

sqlio v1.5.SG
using system counter for latency timings, 2539062 counts per second
parameter file used: paramMT.txt
file P:\SQLIOMP\SQLIO_01\testfile.dat with 48 threads (0-47) using mask 0x0 (0)
48 threads reading for 7200 secs from file P:\SQLIOMP\SQLIO_01\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
size of file P:\SQLIOMP\SQLIO_01\testfile.dat needs to be: 96636764160 bytes
current file size: 0 bytes
need to expand by: 96636764160 bytes
expanding P:\SQLIOMP\SQLIO_01\testfile.dat … done.
using specified size: 92160 MB for file: P:\SQLIOMP\SQLIO_01\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 18325.52
MBs/sec: 143.16
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 20
Max_Latency(ms): 58819
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 3 3 3 3 5 6 7 19 13 6 4 2 3 2 1 1 1 1 1 1 1 1 1 1 13

sqlio v1.5.SG
using system counter for latency timings, 1948244 counts per second
parameter file used: paramMT.txt
file H:\SQLIOMP\SQLIO_01\testfile.dat with 40 threads (0-39) using mask 0x0 (0)
40 threads reading for 7200 secs from file H:\SQLIOMP\SQLIO_01\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
size of file H:\SQLIOMP\SQLIO_01\testfile.dat needs to be: 96636764160 bytes
current file size: 0 bytes
need to expand by: 96636764160 bytes
expanding H:\SQLIOMP\SQLIO_01\testfile.dat … done.
using specified size: 92160 MB for file: H:\SQLIOMP\SQLIO_01\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 191786.53
MBs/sec: 1498.33
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 1
Max_Latency(ms): 113060
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 60 12 4 6 12 4 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Converting the output of SQLIO to a more readable output using SQLIOResult2Excel.ps1

Code for SQLIOResult2Excel.ps1 (From the internet)

<–Begin

## @The MTC Blog

# SQLIO Result Parser by Chai Swaddipong
# Feel free to pass along/improve

# Parsing was tested with the results from sqlio v1.5.SG; results displayed in Excel2013
# Start Excel application
$xl=New-Object -ComObject “Excel.Application”

#Constant
$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]
$xlDirection=[Microsoft.Office.Interop.Excel.XLDirection]

# Create a workbook
$wb=$xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(“Sheet1″)

$cells=$ws.Cells

$cells.item(1,1)=”SQLIO Test Results from ” + $args[0]
$cells.item(1,1).font.bold=”True”
$cells.item(1,1).font.size=18

#Location of the header text
$row=3
$col=1

#insert header text

“Threadcount”,”Outstanding IO”,”Testfile Path”,”Datafile size”,”Duration”,”IO Pattern”,”Bandwidth(MB/s)”,”IOPs”,”Average latency(ms)” | foreach {
$cells.item($row,$col)=$_
$cells.item($row,$col).font.bold=$True
$col++
}

# Loop through lines of the result file

ForEach ($line in Get-Content $args[0] )
{

# Remove leading spaces
$line -replace ‘^\s+’, ”

# How many threads? Read or write test?How long the test runs? What is the test file?
if ( $line -match “^\d+ threads \w*” )
{
$TestDetails = $line.Split(” “)
$ThreadCount = $TestDetails[0]

if ( $TestDetails[2] -match “reading”)
{
$ReadOrWrite = $TestDetails[2].replace(“reading”,”read”)
}
else
{
$ReadOrWrite = $TestDetails[2].replace(“writing”,”write”)
}

$TestDuration = $TestDetails[4]
$TestFilePath = $TestDetails[8]
}

# IO size for the test? Sequential or random IO pattern?
if ( $line -match ” \w* (sequential|random)”)
{
$IoToTest = $matches[0].split(” “)
$ioSize = $IoToTest[1]
$RandomOrSeq = $IoToTest[2]
}

# Size of the datafile?
if ( $line -match “\w+: \d+ \w+ ” )
{
$DataFileSize = ($matches[0].Split(“:”))[1]
$FieldCount++
}

# IOs per thread
if ( $line -match ” \d+ outstanding” )
{
$QueueDepth = ($matches[0].Split(” “))[1]
}

# Bandwidth attained
if ( $line -match “MBs/sec” )
{
$Bandwidth = ($line.Split(“:”))[1]
}

# Average latency attained
if ( $line -match “Avg_Latency” )
{
$AverageLatency = ($line.Split(“:”))[1]
}

# Throughput attained
if ( $line -match “IOs/sec” )
{
$Throughput = ($line.Split(“:”))[1]
}

# End of each run of SQLIO. Write out results
if ($line.startswith(“histogram”) )
{
write-host “Writing to Excel–>” $ThreadCount $QueueDepth $TestFilePath $DataFileSize $TestDuration $ioSize $RandomOrSeq $ReadOrWrite $Bandwidth $Throughput $AverageLatency
$row++
$col=1
$cells.item($row,$col)=$ThreadCount
$col++
$cells.item($row,$col)=$QueueDepth
$col++
$cells.item($row,$col)=$TestFilePath
$col++
$cells.item($row,$col)=$DataFileSize
$col++
$cells.item($row,$col)=$TestDuration
$col++
$cells.item($row,$col)=$ioSize+$RandomOrSeq+$ReadOrWrite
$col++
$cells.item($row,$col)=$Bandwidth
$col++
$cells.item($row,$col)=$Throughput
$col++
$cells.item($row,$col)=$AverageLatency
}

}

#Sort on Bandwidth
$objRange = $ws.range(“A4:I$row”)
$objRange2 = $ws.Range(“G4”)
[void]$objRange.Sort($objRange2)
$chart=$ws.Shapes.AddChart().Chart
$chart.chartType=$xlChart::xlBarClustered

$start=$ws.range(“G3”)

#get the last cell
$Y=$ws.Range($start,$start.End($xlDirection::xlDown))
$start=$ws.range(“F3”)

#get the last cell
$X=$ws.Range($start,$start.End($xlDirection::xlDown))

$chartdata=$ws.Range(“G$($Y.item(1).Row):G$($Y.item($Y.count).Row),F$($X.item(1).Row):F$($X.item($X.count).Row)”)
$chart.SetSourceData($chartdata)

$chart.ChartTitle.Text = “Bandwidth”
$ws.shapes.item(“Chart 1”).top=60
$ws.shapes.item(“Chart 1”).left=600
$ws.shapes.item(“Chart 1”).width=600
$ws.shapes.item(“Chart 1”).height=300

#Sort on IOP
$objRange = $ws.range(“A4:I$row”)
$objRange2 = $ws.Range(“H4”)
[void]$objRange.Sort($objRange2)

$chart=$ws.Shapes.AddChart().Chart
$chart.chartType=$xlChart::xlLine

$start=$ws.range(“H3”)

#get the last cell
$Y=$ws.Range($start,$start.End($xlDirection::xlDown))
$start=$ws.range(“F3”)

#get the last cell
$X=$ws.Range($start,$start.End($xlDirection::xlDown))

$chartdata=$ws.Range(“H$($Y.item(1).Row):H$($Y.item($Y.count).Row),F$($X.item(1).Row):F$($X.item($X.count).Row)”)
$chart.SetSourceData($chartdata)

$chart.ChartTitle.Text = “IOPs”
$ws.shapes.item(“Chart 2”).top=360
$ws.shapes.item(“Chart 2”).left=600
$ws.shapes.item(“Chart 2”).width=600
$ws.shapes.item(“Chart 2”).height=300

#Sort on average latency
$objRange = $ws.range(“A4:I$row”)
$objRange2 = $ws.Range(“I4”)
[void]$objRange.Sort($objRange2,2)

$chart=$ws.Shapes.AddChart().Chart
$chart.chartType=$xlChart::xlLine

$start=$ws.range(“I3”)

#get the last cell
$Y=$ws.Range($start,$start.End($xlDirection::xlDown))
$start=$ws.range(“F3”)

#get the last cell
$X=$ws.Range($start,$start.End($xlDirection::xlDown))

$chartdata=$ws.Range(“I$($Y.item(1).Row):I$($Y.item($Y.count).Row),F$($X.item(1).Row):F$($X.item($X.count).Row)”)
$chart.SetSourceData($chartdata)

$chart.ChartTitle.Text = “Average Latency”
$ws.shapes.item(“Chart 3”).top=660
$ws.shapes.item(“Chart 3”).left=600
$ws.shapes.item(“Chart 3”).width=600
$ws.shapes.item(“Chart 3″).height=300
#Show time!!
$xl.visible=”True”

End–>

Command to run:

PS C:\Users\bmani\Desktop\SQLIO\SQLIO-HUS150-64GB-Disk1\SQLIO> .\SQLIOResult2Excel.ps1 C:\Users\bmani\Desktop\SQLIO\SQLO-HUS150-64GB-Disk4\SQLIO\Reads8KRandom8Oustanding.txt

8KB random reads using 90 GB file with 8 outstanding I/O request:

Threadcount Outstanding IO Testfile Path Datafile size Duration IO Pattern Bandwidth(MB/s) IOPs Average latency(ms)
48 8 O:\SQLIOMP\Disk4\testfile.dat 92160 MB 7200 8KBrandomread-HUS150 48.64 6226.01 61
48 8 P:\SQLIOMP\SQLIO_04\testfile.dat 92160 MB 7200 8KBrandomread-Nimble 455.38 58288.99 6
40 8 H:\SQLIOMP\SQLIO_04\testfile.dat 92160 MB 7200 8KBrandomread-Pure 1473.68 188631.44 1

PS1

Hope this help!

 

 

Posted in GENERAL PERFORMANCE | Leave a Comment »

Troubleshooting PLE issue:

Posted by database-wiki on April 11, 2016

Problem Description:
===================
We have observed that SQL Server PLE value has hit all-time record low in one of our environment.

Analysis:
========
Currently we have 16 gigs of memory in the server out of which 12 gigs has been allocated to SQL Server instance.

This is not to say that the instance is under memory pressure. If that’s the case, you will see the below error in the SQL Server error log and it’s a critical issue that needs immediate attention.

“A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1086400, committed (KB): 2160928, memory utilization: 50%.”

During the above scenario an activity called workset trimming happens. Where windows signals SQL Server memory to move the pages in buffer pool to the windows page file. This scenario happens as a result of memory contention faced by entire server due to SQL and workset trimming hugely degrades the SQL performance.

As far as PLE issue, SQL Server instance has started to suffocate due to the shortage of contiguous memory in the buffer pool for optimal memory management.

PLE value is generally calculated using the below formula.

PLE = (DataCacheSizeInGB/4GB * 300 seconds)

so the value should ideally be (12GB/4GB) X300 = 900 seconds.

Below query give you the PLE value for the instance.

SELECT [object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Manager%’
AND [counter_name] = ‘Page life expectancy’

After monitoring the instance for some time, this value vacillates between 5, and as high as 200.

This is evident that the SQL server is frequently load and unloads pages from the buffer pool, with all the pages being used, and then a query run that causes a huge amount of different data to be read from disk, displacing much of what’s already in memory and causing a precipitous drop in PLE.

If you see DB1 pages in the buffer pool it’s close to 4 gigs. At any given point if SQL Server cannot find a contiguous space to accommodate 4 gigs worth of pages (Every ETL cycle) It will flush the existing pages of DB1 database file and load the other required pages.

Below is the query that will give you the datapages size in MB for all the databases.

DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE’%Buffer Manager’
AND counter_name = ‘Total Pages’;

;WITH BufCount AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[Database_Name] = CASE [database_id] WHEN 32767
THEN’MSSQL System Resource DB’
ELSE DB_NAME([database_id]) END,
[Database_ID],
db_buffer_pages as [Buffer Count (8KB Pages)],
[Buffer Size (MB)] = db_buffer_pages / 128,
[Buffer Size (%)] = CONVERT(DECIMAL(6,3),
db_buffer_pages * 100.0 / @total_buffer)
FROM BufCount
ORDER BY [Buffer Size (MB)] DESC;

=> Below query give you the which object has got the large chunk of pages in bufferpool.

begin tran t1

select cached_MB,dd.name,s.name,s.index_id,s.type,s.type_desc,s.fill_factor from (
SELECT count(1)/128 AS cached_MB
, name
, index_id
FROM sys.dm_os_buffer_descriptors AS bd with (NOLOCK)
INNER JOIN
(
SELECT name = OBJECT_SCHEMA_NAME(object_id) + ‘.’ + object_name(object_id)
–name = ‘dbo.’ + cast(object_id as varchar(100))
, index_id
, allocation_unit_id
FROM sys.allocation_units AS au with (NOLOCK)
INNER JOIN sys.partitions AS p with (NOLOCK)
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT name = OBJECT_SCHEMA_NAME(object_id) + ‘.’ + object_name(object_id)
–name = ‘dbo.’ + cast(object_id as varchar(100))
, index_id
, allocation_unit_id
FROM sys.allocation_units AS au with (NOLOCK)
INNER JOIN sys.partitions AS p with (NOLOCK)
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
HAVING Count(*) > 128
) as dd
inner join sys.indexes s on dd.index_id=s.index_id and object_id(dd.name)=s.[object_id]
order by cached_MB desc

rollback tran t1

 

=> Pages from clustered index loaded to the buffer pool means we are having a query that does an index scan on the object.

=> Below query is used to get the execution plan for the index on the table with large number of pages in the bufferpool.

DECLARE @IndexName SYSNAME = ‘[PK_DB1]’;
DECLARE @DatabaseName SYSNAME;

SELECT @DatabaseName = ‘[‘ + DB_NAME() + ‘]’;

WITH XMLNAMESPACES
(DEFAULT ‘http://schemas.microsoft.com/sqlserver/2004/07/showplan&#8217;)
SELECT
n.value(‘(@StatementText)[1]’, ‘VARCHAR(4000)’) AS sql_text,
n.query(‘.’),
cp.plan_handle,
i.value(‘(@PhysicalOp)[1]’, ‘VARCHAR(128)’) AS PhysicalOp,
i.value(‘(./IndexScan/@Lookup)[1]’, ‘VARCHAR(128)’) AS IsLookup,
i.value(‘(./IndexScan/Object/@Database)[1]’, ‘VARCHAR(128)’) AS DatabaseName,
i.value(‘(./IndexScan/Object/@Schema)[1]’, ‘VARCHAR(128)’) AS SchemaName,
i.value(‘(./IndexScan/Object/@Table)[1]’, ‘VARCHAR(128)’) AS TableName,
i.value(‘(./IndexScan/Object/@Index)[1]’, ‘VARCHAR(128)’) as IndexName,
i.query(‘.’),
STUFF((SELECT DISTINCT ‘, ‘ + cg.value(‘(@Column)[1]’, ‘VARCHAR(128)’)
FROM i.nodes(‘./OutputList/ColumnReference’) AS t(cg)
FOR XML PATH(”)),1,2,”) AS output_columns,
STUFF((SELECT DISTINCT ‘, ‘ + cg.value(‘(@Column)[1]’, ‘VARCHAR(128)’)
FROM i.nodes(‘./IndexScan/SeekPredicates/SeekPredicateNew//ColumnReference’) AS t(cg)
FOR XML PATH(”)),1,2,”) AS seek_columns,
RIGHT(i.value(‘(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]’, ‘VARCHAR(4000)’), len(i.value(‘(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]’, ‘VARCHAR(4000)’)) – charindex(‘.’, i.value(‘(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]’, ‘VARCHAR(4000)’))) as Predicate,
cp.usecounts,
query_plan
FROM ( SELECT plan_handle, query_plan
FROM ( SELECT DISTINCT plan_handle
FROM sys.dm_exec_query_stats WITH(NOLOCK)) AS qs
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
) as tab (plan_handle, query_plan)
INNER JOIN sys.dm_exec_cached_plans AS cp
ON tab.plan_handle = cp.plan_handle
CROSS APPLY query_plan.nodes(‘/ShowPlanXML/BatchSequence/Batch/Statements/*’) AS q(n)
CROSS APPLY n.nodes(‘.//RelOp[IndexScan/Object[@Index=sql:variable(“@IndexName”) and @Database=sql:variable(“@DatabaseName”)]]’ ) as s(i)
–WHERE i.value(‘(./IndexScan/@Lookup)[1]’, ‘VARCHAR(128)’) = 1
OPTION(RECOMPILE, MAXDOP 1);

=> Query to find the buffer pool usage by objects:

IF OBJECT_ID(‘TempDB..#BufferSummary’) IS NOT NULL BEGIN
DROP TABLE #BufferSummary
END

IF OBJECT_ID(‘TempDB..#BufferPool’) IS NOT NULL BEGIN
DROP TABLE #BufferPool
END

CREATE TABLE #BufferPool
(
Cached_MB Int
, Database_Name SysName
, Schema_Name SysName NULL
, Object_Name SysName NULL
, Index_ID Int NULL
, Index_Name SysName NULL
, Used_MB Int NULL
, Used_InRow_MB Int NULL
, Row_Count BigInt NULL
)

SELECT Pages = COUNT(1)
, allocation_unit_id
, database_id
INTO #BufferSummary
FROM sys.dm_os_buffer_descriptors
GROUP BY allocation_unit_id, database_id

DECLARE @DateAdded SmallDateTime
SELECT @DateAdded = GETDATE()

DECLARE @SQL NVarChar(4000)
SELECT @SQL = ‘ USE [?]
INSERT INTO #BufferPool (
Cached_MB
, Database_Name
, Schema_Name
, Object_Name
, Index_ID
, Index_Name
, Used_MB
, Used_InRow_MB
, Row_Count
)
SELECT sum(bd.Pages)/128
, DB_Name(bd.database_id)
, Schema_Name(o.schema_id)
, o.name
, p.index_id
, ix.Name
, i.Used_MB
, i.Used_InRow_MB
, i.Row_Count
FROM #BufferSummary AS bd
LEFT JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
LEFT JOIN sys.partitions p ON (au.container_id = p.hobt_id AND au.type in (1,3)) OR (au.container_id = p.partition_id and au.type = 2)
LEFT JOIN (
SELECT PS.object_id
, PS.index_id
, Used_MB = SUM(PS.used_page_count) / 128
, Used_InRow_MB = SUM(PS.in_row_used_page_count) / 128
, Used_LOB_MB = SUM(PS.lob_used_page_count) / 128
, Reserved_MB = SUM(PS.reserved_page_count) / 128
, Row_Count = SUM(row_count)
FROM sys.dm_db_partition_stats PS
GROUP BY PS.object_id
, PS.index_id
) i ON p.object_id = i.object_id AND p.index_id = i.index_id
LEFT JOIN sys.indexes ix ON i.object_id = ix.object_id AND i.index_id = ix.index_id
LEFT JOIN sys.objects o ON p.object_id = o.object_id
WHERE database_id = db_id()
GROUP BY bd.database_id
, o.schema_id
, o.name
, p.index_id
, ix.Name
, i.Used_MB
, i.Used_InRow_MB
, i.Row_Count
HAVING SUM(bd.pages) > 128
ORDER BY 1 DESC;’

EXEC sp_MSforeachdb @SQL

SELECT Cached_MB
, Pct_of_Cache = CAST(Cached_MB * 100.0 / SUM(Cached_MB) OVER () as Dec(20,3))
, Pct_Index_in_Cache = CAST(Cached_MB * 100.0 / CASE Used_MB WHEN 0 THEN 0.001 ELSE Used_MB END as DEC(20,3))
, Database_Name
, Schema_Name
, Object_Name
, Index_ID
, Index_Name
, Used_MB
, Used_InRow_MB
, Row_Count
FROM #BufferPool
ORDER BY Cached_MB DESC

=> once you get the execution plan. Check if the plan is optimal meaning if there is any missing indexes leading to page scan.
=> In our case creating the missing indexes increase the PLE value to an optimum level.

Posted in GENERAL PERFORMANCE | Leave a Comment »

The distribution agent may experience high latency when you configure transactional replication with articles that are configured not to replicate changes as parameterized statements.

Posted by database-wiki on April 11, 2016

Symptoms

In Microsoft SQL Server 2005 or Microsoft SQL Server 2008, you set up a transactional replication.

  • The transactional replication contains one or more articles.
  • For one or more of the articles you specify a non default value for the @status parameter (default is either 16 or 24) of the sp_addarticle stored procedure. Or, you specify the value for the @ins_cmd, @del_cmd and @upd_cmd parameter of sp_addarticle stored procedure to SQL

In this scenario, you may find that the Distribution Agent will exhibit high latency when replicating data to the subscriber. Additionally you many notice high values for NETWORKIO wait type on the SPID corresponding to the Distribution Agent process on the distributor.

Note: You can confirm the above symptom by generating the script for your publication and by looking at the value of @status parameter for procedure sp_addarticle for each of the articles in the publication. If this value is less than 16, then you are not using parameterized statements for propagating changes to the subscriber for the specified article and hence this article applies in your scenario.

Cause

Replicating commands without using parameterized statements causes each command in a transaction to be sent individually via RPC. Thus when you have a transaction with multiple commands, distribution agent will require multiple round trips to replicate the transaction to the subscriber thereby increasing the latency. On the other hand, when you use parameterized statements (the default setting), distribution agent groups multiple commands in a single transaction into one or more batches and replicates them to the subscriber thereby reducing network round trips and latency.

Note: In a SQL profiler trace a batch would show up as one pair of RPC Starting and RPC completed event.

Resolution

To resolve the problem, change affected article’s status property to “parameters” using sp_changearticle stored procedure. This stored procedure should be executed at the Publisher on the publication database.

The syntax is as follows:

Exec sp_changearticle @publication='<PublicationName>’, @article='<ArticleName>’,@property=’status’,@value=’parameters’

Note: Running the above procedure does not cause or require re-initialization of the subscription.

Posted in REPLICATION | Leave a Comment »

Distribution agent is skipping transactions at the subscriber.

Posted by database-wiki on April 11, 2016

Symptoms

ErrorCode = ‘20598’
ErrorText = ‘The row was not found at the Subscriber when applying the replicated command.’

Cause

Incorrect steps were followed when setting up subscriber using @sync_type = N’initialize with backup’. As per BOL, this can’t be done using GUI and can only be done using TSQL scripts.

Using TSQL scripts will ensure new commands in the LOG after the backup is taken will be queued in the Distribution DB and Replicated to the Subscriber.

Resolution

To resolve the issue, Create a new publication and set allow “allow_initialize_from_backup” to true on the Subscription options for the publication properties .

If the backup is used, you should use the TSQL to create the subscription. When executing sp_addsubscription you have to mention the backup file used to restore using @backupdevicename. The Dist agent will then execute a “Restore headeronly” command against the backup and will get the lastLSN from the file. The dist agent will then query the msrepl_transactions from that LSN forward and replicate all the changes to the subscriber.

Posted in REPLICATION | Leave a Comment »

How to drop Replication Identity Check Constraint.

Posted by database-wiki on April 11, 2016

Problem Description:

Customer executed sp_changemergearticle to disable Auto Identity Range Management.
One subscriber still had the Identity Check Constraint.

When try to manually remove check constraint get the following error

–TESTING
ALTER TABLE [dbo].[Shippers] DROP CONSTRAINT
[repl_identity_range_259EDBE5_AC01_41C1_ADDB_997EA2098FB2]

Msg 25010, Level 16, State 1, Procedure sp_MSmerge_altertable, Line 284
The constraint is used by merge replication for identity management and cannot be
dropped directly. Execute sp_changemergearticle @publication, @article,
“identityrangemanagementoption”, “none” to disable merge identity management, which
will also drop the constraint.
Msg 21530, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 182
The schema change failed during execution of an internal replication procedure. For
corrective action, see the other error messages that accompany this error
message.
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

Resolution:

Untested and Unsupported Resolution

DISABLE TRIGGER [MSmerge_tr_altertable] ON DATABASE
GO

ALTER TABLE [dbo].[Shippers] DROP CONSTRAINT
[repl_identity_range_259EDBE5_AC01_41C1_ADDB_997EA2098FB2]
GO

ENABLE TRIGGER [MSmerge_tr_altertable] ON DATABASE
GO

Posted in REPLICATION | Leave a Comment »

Quick and easy Script to collect Merge Replication Agent History tables.

Posted by database-wiki on April 11, 2016

Problem Description:
Quick and easy way to collect Merge Replication Agent History tables for SQL 2005.

Resolution:
–Create database to hold a copy of the Agent history tables
Create Database MergeAgentHistory
Go

Select * Into MergeAgentHistory.dbo.MSMerge_history
from distribution.dbo.MSMerge_history
Go

Select * Into MergeAgentHistory.dbo.MSmerge_sessions
from distribution.dbo.MSmerge_sessions
Go

Select * Into MergeAgentHistory.dbo.MSmerge_articlehistory
from distribution.dbo.MSmerge_articlehistory
Go

Select * Into MergeAgentHistory.dbo.MSmerge_agents
from distribution.dbo.MSmerge_agents
Go

Select * Into MergeAgentHistory.dbo.MSrepl_errors
from distribution.dbo.MSrepl_errors
Go

–Backup Database
Backup Database MergeAgentHistory
To disk=’c:\temp\MergeAgentHistory.bak’
Go

Posted in REPLICATION | Leave a Comment »

Steps to aggressively purge replicated transactions from the Distribution database.

Posted by database-wiki on April 11, 2016

Symptoms

Distribution database cleanup agent is not able to purge already replication rows from the Distribution database faster then rows are being added. As result, the distribution database is growing in size.

Cause

To reduce locking contention, the cleanup agent deletes in batches of 2000 and 5000. When a large batch of transaction have been replicated the Distribution cleanup agent may not be able to deletes fast enough to keep up with new rows being added. You may notice the the number of rows in the replication tracking tables growing to over 10+ million.

select count(*) from distribution..msrepl_commands

select count(*) from distribution..msrepl_transactions

Resolution

Distribution Database Cleanup.

Use these steps to aggressively clean-out already replicated data from the Distribution database. These steps will save out all rows since the last replicated transaction from msrepl_transactions and msrepl_commands to temp tables, truncate msrepl_transactions and msrepl_commands, and then re-insert the saved rows. We determine which rows to save by checking the transaction_timestamp value in the MSreplication_subscriptions table at each subscriber for each subscription database..

Reminder –this should work, but be prepared to have to re-initialize the subscriber.

***Steps for Manual Truncation of Distribution Database***

1) Stop all logreader and distribution agents utilizing this distribution server. Use the queries below to identify all Publisher and Subscriber servers.

–returns list of publishers using a distributor

sp_helpdistpublisher

–returns list of published databases

SELECT *

FROM [distribution].[dbo].[MSpublisher_databases]

–Run in each published database

–Returns publication/article/subscriber information

sp_helpsubscription

2) At each subscriber and in each subscriber database, query the msreplication_subscriptions table for the bookmark xactid – you should find only one row per publication in this table at each subscriber. This value indicates what rows have already been delivered to each subscriber.

select transaction_timestamp from msreplication_subscriptions

Here is the value returned for me: 0x00000045000002560003000000000000

3) Determine SMALLEST value of ALL subscribers in ALL subscription databases. Use that value when querying the msrepl_transactions and msrepl_commands tables in the distribution database for rows to preserve. Now, let’s query msrepl_transaction and msrepl_commands, using our transaction_timestamp and see if there are any other commands that have not been propagated to either subscriber

–Be sure to use your smaller transaction_timestamp value here:

select count(*) from distribution..msrepl_commands where xact_seqno>=0x00000045000002560003000000000000

select count(*) from distribution..msrepl_transactions where xact_seqno>=0x00000045000002560003000000000000

4) How many rows do you get back? If around 1 million rows proceed to save those rows. If more, determine which distribution agent is behind (lowest LSN) and see what steps can be used to move that Distribution Agent forward.

5) Save rows returned by the above queries to temp tables before we truncate the Replication tables:

Begin Transaction

select * into distribution..msrepl_commands_temp from distribution..msrepl_commands

where xact_seqno>=0x00000045000002560003000000000000

select * into distribution..msrepl_transactions_temp from distribution..msrepl_transactions

where xact_seqno>=0x00000045000002560003000000000000

–did you get same row counts as in step 3), if yes, then commit the transaction

Commit Transaction

6) To verify you can query the temp tables and make sure that the rows were selected out. Then, perform the following to truncate the two tables in the distribution database:

truncate table distribution..msrepl_commands

truncate table distribution..msrepl_transactions

7) Once these are emptied, insert our rows back in from our temp tables:

insert into distribution..msrepl_commands

select * from distribution..msrepl_commands_temp

insert into distribution..msrepl_transactions

select * from distribution..msrepl_transactions_temp

Now, verify the rows are successfully re-inserted into the two tables. Then start the logreader and distribution agents back up and verify that they are able to successfully synchronize to both subscribers.

Posted in REPLICATION | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 175 other followers