Tuesday, March 31, 2015

Enable DB2 statement monitoring using event trace


C:\Program Files\IBM\SQLLIB\BIN>db2 connect to SAMPLEDB

   Database Connection Information

 Database server        = DB2/NT64 9.7.6
 SQL authorization ID   = ADMINIST...
 Local database alias   = SAMPLEDB

C:\Program Files\IBM\SQLLIB\BIN>db2 get monitor switches

            Monitor Recording Switches
Buffer Pool Activity Information  (BUFFERPOOL) = OFF
Lock Information                        (LOCK) = OFF
Sorting Information                     (SORT) = OFF
SQL Statement Information          (STATEMENT) = OFF
Table Activity Information             (TABLE) = OFF
Take Timestamp Information         (TIMESTAMP) = ON  03/31/2015 15:34:19.915686
Unit of Work Information                 (UOW) = OFF


C:\Program Files\IBM\SQLLIB\BIN>db2 update monitor switches using statement on
DB20000I  The UPDATE MONITOR SWITCHES command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 create event monitor stmon for statements write to file 'C:\temp'
DB20000I  The SQL command completed successfully.


C:\Program Files\IBM\SQLLIB\BIN>db2 set event monitor stmon state=1
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 create table tbl_donghua(id integer)
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 insert into tbl_donghua values (1)
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 select * from tbl_donghua

ID
-----------
          1

  1 record(s) selected.


C:\Program Files\IBM\SQLLIB\BIN>db2 drop table tbl_donghua
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 set event monitor stmon state 0
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2evmon -path c:\temp > c:\temp\db2stmt.sql

Reading c:\temp\00000000.EVT ...

C:\Program Files\IBM\SQLLIB\BIN>db2 drop event monitor stmon
DB20000I  The SQL command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 update monitor switches using statement off
DB20000I  The UPDATE MONITOR SWITCHES command completed successfully.

C:\Program Files\IBM\SQLLIB\BIN>db2 get monitor switches

            Monitor Recording Switches

Switch list for db partition number 0
Buffer Pool Activity Information  (BUFFERPOOL) = OFF
Lock Information                        (LOCK) = OFF
Sorting Information                     (SORT) = OFF
SQL Statement Information          (STATEMENT) = OFF
Table Activity Information             (TABLE) = OFF
Take Timestamp Information         (TIMESTAMP) = ON  03/31/2015 15:34:19.915686
Unit of Work Information                 (UOW) = OFF




5) Statement Event ...
  Appl Handle: 125
  Appl Id: *LOCAL.DB2.150331075101
  Appl Seq number: 00004

  Record is the result of a flush: FALSE
  -------------------------------------------
  Type     : Dynamic
  Operation: Execute Immediate
  Section  : 203
  Creator  : NULLID  
  Package  : SQLC2H23
  Consistency Token  : AAAAABBc
  Package Version ID  : 
  Cursor   : 
  Cursor was blocking: FALSE
  Text     : create table tbl_donghua(id integer)
  -------------------------------------------
  Start Time: 03/31/2015 15:54:26.746074
  Stop Time:  03/31/2015 15:54:26.852143
  Elapsed Execution Time:  0.106069 seconds
  Number of Agents created: 1
  User CPU: 0.015625 seconds
  System CPU: 0.000000 seconds
  Statistic fabrication time (milliseconds): 0
  Synchronous runstats time  (milliseconds): 0
  Fetch Count: 0
  Sorts: 0
  Total sort time: 0
  Sort overflows: 0
  Rows read: 10
  Rows written: 7
  Internal rows deleted: 0
  Internal rows updated: 0
  Internal rows inserted: 0
  Bufferpool data logical reads: 0
  Bufferpool data physical reads: 0
  Bufferpool temporary data logical reads: 0
  Bufferpool temporary data physical reads: 0
  Bufferpool index logical reads: 0
  Bufferpool index physical reads: 0
  Bufferpool temporary index logical reads: 0
  Bufferpool temporary index physical reads: 0
  Bufferpool xda logical page reads: 0
  Bufferpool xda physical page reads: 0
  Bufferpool temporary xda logical page reads: 0
  Bufferpool temporary xda physical page reads: 0
  SQLCA:
   sqlcode: 0
   sqlstate: 00000

42) Statement Event ...
  Appl Handle: 125
  Appl Id: *LOCAL.DB2.150331075101
  Appl Seq number: 00005

  Record is the result of a flush: FALSE
  -------------------------------------------
  Type     : Dynamic
  Operation: Execute Immediate
  Section  : 203
  Creator  : NULLID  
  Package  : SQLC2H23
  Consistency Token  : AAAAABBc
  Package Version ID  : 
  Cursor   : 
  Cursor was blocking: FALSE
  Text     : insert into tbl_donghua values (1)
44) Statement Event ...
  Appl Handle: 125
  Appl Id: *LOCAL.DB2.150331075101
  Appl Seq number: 00006

  Record is the result of a flush: FALSE
  -------------------------------------------
  Type     : Dynamic
  Operation: Prepare
  Section  : 201
  Creator  : NULLID  
  Package  : SQLC2H23
  Consistency Token  : AAAAABBc
  Package Version ID  : 
  Cursor   : SQLCUR201
  Cursor was blocking: FALSE
  Text     : select * from tbl_donghua
45) Statement Event ...
  Appl Handle: 125
  Appl Id: *LOCAL.DB2.150331075101
  Appl Seq number: 00006

  Record is the result of a flush: FALSE
  -------------------------------------------
  Type     : Dynamic
  Operation: Open
  Section  : 201
  Creator  : NULLID  
  Package  : SQLC2H23
  Consistency Token  : AAAAABBc
  Package Version ID  : 
  Cursor   : SQLCUR201
  Cursor was blocking: TRUE
  Text     : select * from tbl_donghua

Sunday, March 29, 2015

Business Intelligence in SQL Server 2014 - SQL Server Data Tools (SSDT)

SQL Server Data Tools for Business Intelligence (SSDT BI), previously known as Business Intelligence Development Studio (BIDS), is used to create Analysis Services models, Reporting Services reports, and Integration Services packages. In this pre-release version of SQL Server 2014, SQL Server Setup no longer installs SSDT BI.

You can download SSDT-BI from the following locations:

• Download SSDT-BI for Visual Studio 2013


• Download SSDT-BI for Visual Studio 2012

Saturday, March 28, 2015

SQL Server: Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself

USE [NORTHWND];
GO
--Create temporary principal
CREATE LOGIN login1 WITH PASSWORD = 'J345#$)thb';
GO
CREATE USER user1 FOR LOGIN login1;
GRANT CREATE SCHEMA to user1;
GRANT CREATE TABLE to user1;
GO
--Display current execution context.
SELECT SUSER_NAME(), USER_NAME();
-- Set the execution context to login1.
EXECUTE AS LOGIN = 'login1';
--Verify the execution context is now login1.
SELECT SUSER_NAME(), USER_NAME();
--Create schema & table
create schema user1;
create table user1.TBL1 (ID INTEGER PRIMARY KEY, Name VARCHAR(200));
GO
SELECT * FROM user1.TBL1;
-- DENY SELECT ON [user1].[TBL1] TO [user1]
-- Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
REVERT;
--Clean up
drop table user1.TBL1;
drop schema user1;
drop user user1;
drop login login1;

Tuesday, March 24, 2015

ORA-15410: Disks in disk group DATA do not have equal size.

SQL> select failgroup,path,os_mb from v$asm_disk;

FAILGROUP                      PATH                                OS_MB
------------------------------ ------------------------------ ----------
                               ORCL:D5GD1                           5119
                               ORCL:D5GD4                           5119
                               ORCL:D5GD2                           5119
                               ORCL:D5GD3                           5119
D2GD1                          ORCL:D2GD1                           2047
D2GD2                          ORCL:D2GD2                           2047
D2GD3                          ORCL:D2GD3                           2047

7 rows selected.

SQL> alter diskgroup data add failgroup fg1 disk 'ORCL:D5GD1';
alter diskgroup data add failgroup fg1 disk 'ORCL:D5GD1'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15410: Disks in disk group DATA do not have equal size.

SQL> alter diskgroup data add failgroup fg1 disk 'ORCL:D5GD1','ORCL:D5GD2' failgroup fg2 disk 'ORCL:D5GD3','ORCL:D5GD4'
  2  drop disk 'D2GD1','D2GD2','D2GD3';

Diskgroup altered.


CAUSE


1) Starting on 12.1.0.2 ASM release, this ASM constraint/validation is available:
15410, 00000, "Disks in disk group %s do not have equal size."
// *Cause: The disks in the diskgroup were not of equal size.
// *Action: Ensure that all disks in the diskgroup are of equal size. If
//          adding new disks to the diskgroup, their size must be equal to
//          the size of the existing disks in the diskgroup. If resizing, all
//          disks in the diskgroup must be resized to the same size.

2) Disks with uneven capacity can create allocation problems (e.g. "ORA-15041: diskgroup space exhausted") that prevent full use of all of the available storage in the failgroup /diskgroup.  

3) This validation/constraint ensure that all disks in the same diskgroup have the same size, doing so provides more predictable overall performance and space utilization.

4) If the disks are the same size, then ASM spreads the files evenly across all of the disks in the diskgroup. This allocation pattern maintains every disk at the same capacity level and ensures that all of the disks in a diskgroup have the same I/O load. Because ASM load balances workload among all of the disks in a diskgroup, different ASM disks should not share the same physical drive.
5) This ASM new feature is enabled by default on '12.1.0.2' Grid Infrastructure/ASM release and onwards.

Thursday, March 19, 2015

Installing the Microsoft ODBC Driver for SQL Server on Linux

http://www.microsoft.com/en-us/download/details.aspx?id=36437
https://msdn.microsoft.com/library/hh568451(SQL.110).aspx

[root@localhost msodbcsql-11.0.2270.0]# uname -a
Linux localhost.localdomain 3.8.13-44.1.5.el7uek.x86_64 #2 SMP Wed Nov 12 12:55:08 PST 2014 x86_64 x86_64 x86_64 GNU/Linux
[root@localhost msodbcsql-11.0.2270.0]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.0 (Maipo)
[root@localhost msodbcsql-11.0.2270.0]# cat /etc/oracle-release
Oracle Linux Server release 7.0
[root@localhost msodbcsql-11.0.2270.0]# yum install glibc libgcc libstdc++ krb5-libs openssl libuuid

INSTALLING THE DRIVER MANAGER


[root@localhost ~]# tar zxvf msodbcsql-11.0.2270.0.tar.gz
msodbcsql-11.0.2270.0/
msodbcsql-11.0.2270.0/include/
msodbcsql-11.0.2270.0/include/msodbcsql.h
msodbcsql-11.0.2270.0/bin/
msodbcsql-11.0.2270.0/bin/SQLCMD.rll
msodbcsql-11.0.2270.0/bin/BatchParserGrammar.dfa
msodbcsql-11.0.2270.0/bin/BatchParserGrammar.llr
msodbcsql-11.0.2270.0/bin/bcp.rll
msodbcsql-11.0.2270.0/bin/bcp-11.0.2270.0
msodbcsql-11.0.2270.0/bin/sqlcmd-11.0.2270.0
msodbcsql-11.0.2270.0/WARNING
msodbcsql-11.0.2270.0/build_dm.sh
msodbcsql-11.0.2270.0/lib64/
msodbcsql-11.0.2270.0/lib64/msodbcsqlr11.rll
msodbcsql-11.0.2270.0/lib64/libmsodbcsql-11.0.so.2270.0
msodbcsql-11.0.2270.0/install.sh
msodbcsql-11.0.2270.0/LICENSE
msodbcsql-11.0.2270.0/README
msodbcsql-11.0.2270.0/docs/
msodbcsql-11.0.2270.0/docs/en_US.tar.gz
[root@localhost ~]#
[root@localhost ~]# cd msodbcsql-11.0.2270.0
[root@localhost msodbcsql-11.0.2270.0]# ls
bin  build_dm.sh  docs  include  install.sh  lib64  LICENSE  README  WARNING
[root@localhost msodbcsql-11.0.2270.0]# ls -l
total 72
drwxrwxr-x. 2 root root  4096 Jan 15  2013 bin
-rwxr-xr-x. 1 root root 10001 Jan 15  2013 build_dm.sh
drwxrwxr-x. 2 root root    25 Jan 15  2013 docs
drwxrwxr-x. 2 root root    24 Jan 15  2013 include
-rwxr-xr-x. 1 root root 23323 Jan 15  2013 install.sh
drwxrwxr-x. 2 root root    63 Jan 15  2013 lib64
-rw-r--r--. 1 root root 17327 Jan 15  2013 LICENSE
-rw-r--r--. 1 root root  7103 Jan 15  2013 README
-rw-r--r--. 1 root root  1105 Jan 15  2013 WARNING
[root@localhost msodbcsql-11.0.2270.0]# ./build_dm.sh --help

Build unixODBC 2.3.0 DriverManager script
Copyright Microsoft Corp.

Usage: build_dm.sh [options]

This script downloads, configures, and builds unixODBC 2.3.0 DriverManager so that it is
ready to install for use with the Microsoft SQL Server ODBC Driver V1.0 for Linux

Valid options are --help, --download-url, --prefix, --libdir, --sysconfdir
  --help - prints this message
  --download-url=url | file:// - Specify the location (and name) of unixODBC-2.3.0.tar.gz.
       For example, if unixODBC-2.3.0.tar.gz is in the current directory, specify
       --download-url=file://unixODBC-2.3.0.tar.gz.
  --prefix - directory to install unixODBC-2.3.0.tar.gz to.
  --libdir - directory where ODBC drivers will be placed
  --sysconfdir - directory where unixODBC 2.3.0 DriverManager configuration files are placed

[root@localhost msodbcsql-11.0.2270.0]# ./build_dm.sh

Build unixODBC 2.3.0 DriverManager script
Copyright Microsoft Corp.

In order to use the Microsoft ODBC Driver 11 for SQL Server on Linux,
the unixODBC DriverManager must be installed on your computer.  unixODBC
DriverManager is a third-party tool made available by the unixODBC Project.
To assist you in the installation process, this script will attempt to
download, properly configure, and build the unixODBC DriverManager from
http://www.unixodbc.org/ for use with the Microsoft ODBC Driver 11
for SQL Server ODBC Driver on Linux.

Alternatively, you can choose to download and configure unixODBC
DriverManager from
http://www.unixodbc.org/ yourself.

Note: unixODBC DriverManager is licensed to you under the terms of an
agreement between you and the unixODBC Project, not Microsoft.  Microsoft
does not guarantee the unixODBC DriverManager or grant any rights to
you.  Prior to downloading, you should review the license for unixODBC
DriverManager at
http://www.unixodbc.org/.

The script is provided as a convenience to you as-is, without any express
or implied warranties of any kind.  Microsoft is not liable for any issues
arising out of your use of the script.

Enter 'YES' to have this script continue: YES

Verifying processor and operating system ................................... OK
Verifying wget is installed ................................................ OK
Verifying tar is installed ................................................. OK
Verifying make is installed ................................................ OK
Downloading unixODBC 2.3.0 DriverManager ................................... OK
Unpacking unixODBC 2.3.0 DriverManager ..................................... OK
Configuring unixODBC 2.3.0 DriverManager ................................... OK
Building unixODBC 2.3.0 DriverManager ...................................... OK
Build of the unixODBC 2.3.0 DriverManager complete.

Run the command 'cd /tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0; make install' to install the driver manager.

PLEASE NOTE THAT THIS WILL POTENTIALLY INSTALL THE NEW DRIVER MANAGER OVER ANY
EXISTING UNIXODBC DRIVER MANAGER.  IF YOU HAVE ANOTHER COPY OF UNIXODBC INSTALLED,
THIS MAY POTENTIALLY OVERWRITE THAT COPY.


[root@localhost msodbcsql-11.0.2270.0]# cd /tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0; make install
Making install in extras
make[1]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/extras'
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/extras'
make[2]: Nothing to be done for `install-exec-am'.
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/extras'
make[1]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/extras'
Making install in log
make[1]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/log'
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/log'
make[2]: Nothing to be done for `install-exec-am'.
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/log'
make[1]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/log'
Making install in lst
make[1]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/lst'
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/lst'
make[2]: Nothing to be done for `install-exec-am'.
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/lst'
make[1]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/lst'
Making install in ini
make[1]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/ini'
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/ini'
make[2]: Nothing to be done for `install-exec-am'.
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/ini'
make[1]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/ini'
Making install in libltdl
make[1]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/libltdl'
make  install-am
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/libltdl'
make[3]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/libltdl'
test -z "/usr/lib64" || /usr/bin/mkdir -p "/usr/lib64"
test -z "/usr/include" || /usr/bin/mkdir -p "/usr/include"
test -z "" || /usr/bin/mkdir -p ""
make[3]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/libltdl'
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/libltdl'
make[1]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/libltdl'
Making install in odbcinst
make[1]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/odbcinst'
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/odbcinst'
test -z "/usr/lib64" || /usr/bin/mkdir -p "/usr/lib64"
/bin/sh ../libtool   --mode=install /usr/bin/install -c   libodbcinst.la '/usr/lib64'
libtool: install: /usr/bin/install -c .libs/libodbcinst.so.1.0.0 /usr/lib64/libodbcinst.so.1.0.0
libtool: install: (cd /usr/lib64 && { ln -s -f libodbcinst.so.1.0.0 libodbcinst.so.1 || { rm -f libodbcinst.so.1 && ln -s libodbcinst.so.1.0.0 libodbcinst.so.1; }; })
libtool: install: (cd /usr/lib64 && { ln -s -f libodbcinst.so.1.0.0 libodbcinst.so || { rm -f libodbcinst.so && ln -s libodbcinst.so.1.0.0 libodbcinst.so; }; })
libtool: install: /usr/bin/install -c .libs/libodbcinst.lai /usr/lib64/libodbcinst.la
libtool: finish: PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/sbin" ldconfig -n /usr/lib64
----------------------------------------------------------------------
Libraries have been installed in:
   /usr/lib64

If you ever happen to want to link against installed libraries
in a given directory, LIBDIR, you must either use libtool, and
specify the full pathname of the library, or use the `-LLIBDIR'
flag during linking and do at least one of the following:
   - add LIBDIR to the `LD_LIBRARY_PATH' environment variable
     during execution
   - add LIBDIR to the `LD_RUN_PATH' environment variable
     during linking
   - use the `-Wl,-rpath -Wl,LIBDIR' linker flag
   - have your system administrator add LIBDIR to `/etc/ld.so.conf'

See any operating system documentation about shared libraries for
more information, such as the ld(1) and ld.so(8) manual pages.
----------------------------------------------------------------------
test -z "/etc" || /usr/bin/mkdir -p "/etc"
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/odbcinst'
make[1]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/odbcinst'
Making install in DriverManager
make[1]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/DriverManager'
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/DriverManager'
test -z "/usr/lib64" || /usr/bin/mkdir -p "/usr/lib64"
/bin/sh ../libtool   --mode=install /usr/bin/install -c   libodbc.la '/usr/lib64'
libtool: install: /usr/bin/install -c .libs/libodbc.so.1.0.0 /usr/lib64/libodbc.so.1.0.0
libtool: install: (cd /usr/lib64 && { ln -s -f libodbc.so.1.0.0 libodbc.so.1 || { rm -f libodbc.so.1 && ln -s libodbc.so.1.0.0 libodbc.so.1; }; })
libtool: install: (cd /usr/lib64 && { ln -s -f libodbc.so.1.0.0 libodbc.so || { rm -f libodbc.so && ln -s libodbc.so.1.0.0 libodbc.so; }; })
libtool: install: /usr/bin/install -c .libs/libodbc.lai /usr/lib64/libodbc.la
libtool: finish: PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/sbin" ldconfig -n /usr/lib64
----------------------------------------------------------------------
Libraries have been installed in:
   /usr/lib64

If you ever happen to want to link against installed libraries
in a given directory, LIBDIR, you must either use libtool, and
specify the full pathname of the library, or use the `-LLIBDIR'
flag during linking and do at least one of the following:
   - add LIBDIR to the `LD_LIBRARY_PATH' environment variable
     during execution
   - add LIBDIR to the `LD_RUN_PATH' environment variable
     during linking
   - use the `-Wl,-rpath -Wl,LIBDIR' linker flag
   - have your system administrator add LIBDIR to `/etc/ld.so.conf'

See any operating system documentation about shared libraries for
more information, such as the ld(1) and ld.so(8) manual pages.
----------------------------------------------------------------------
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/DriverManager'
make[1]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/DriverManager'
Making install in exe
make[1]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/exe'
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/exe'
test -z "/usr/bin" || /usr/bin/mkdir -p "/usr/bin"
  /bin/sh ../libtool   --mode=install /usr/bin/install -c isql dltest odbcinst iusql odbc_config '/usr/bin'
libtool: install: /usr/bin/install -c .libs/isql /usr/bin/isql
libtool: install: /usr/bin/install -c dltest /usr/bin/dltest
libtool: install: /usr/bin/install -c .libs/odbcinst /usr/bin/odbcinst
libtool: install: /usr/bin/install -c .libs/iusql /usr/bin/iusql
libtool: install: /usr/bin/install -c odbc_config /usr/bin/odbc_config
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/exe'
make[1]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/exe'
Making install in cur
make[1]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/cur'
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/cur'
test -z "/usr/lib64" || /usr/bin/mkdir -p "/usr/lib64"
/bin/sh ../libtool   --mode=install /usr/bin/install -c   libodbccr.la '/usr/lib64'
libtool: install: /usr/bin/install -c .libs/libodbccr.so.1.0.0 /usr/lib64/libodbccr.so.1.0.0
libtool: install: (cd /usr/lib64 && { ln -s -f libodbccr.so.1.0.0 libodbccr.so.1 || { rm -f libodbccr.so.1 && ln -s libodbccr.so.1.0.0 libodbccr.so.1; }; })
libtool: install: (cd /usr/lib64 && { ln -s -f libodbccr.so.1.0.0 libodbccr.so || { rm -f libodbccr.so && ln -s libodbccr.so.1.0.0 libodbccr.so; }; })
libtool: install: /usr/bin/install -c .libs/libodbccr.lai /usr/lib64/libodbccr.la
libtool: finish: PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/sbin" ldconfig -n /usr/lib64
----------------------------------------------------------------------
Libraries have been installed in:
   /usr/lib64

If you ever happen to want to link against installed libraries
in a given directory, LIBDIR, you must either use libtool, and
specify the full pathname of the library, or use the `-LLIBDIR'
flag during linking and do at least one of the following:
   - add LIBDIR to the `LD_LIBRARY_PATH' environment variable
     during execution
   - add LIBDIR to the `LD_RUN_PATH' environment variable
     during linking
   - use the `-Wl,-rpath -Wl,LIBDIR' linker flag
   - have your system administrator add LIBDIR to `/etc/ld.so.conf'

See any operating system documentation about shared libraries for
more information, such as the ld(1) and ld.so(8) manual pages.
----------------------------------------------------------------------
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/cur'
make[1]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/cur'
Making install in DRVConfig
make[1]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/DRVConfig'
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/DRVConfig'
make[3]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/DRVConfig'
make[3]: Nothing to be done for `install-exec-am'.
make[3]: Nothing to be done for `install-data-am'.
make[3]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/DRVConfig'
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/DRVConfig'
make[1]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/DRVConfig'
Making install in Drivers
make[1]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/Drivers'
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/Drivers'
make[3]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/Drivers'
make[3]: Nothing to be done for `install-exec-am'.
make[3]: Nothing to be done for `install-data-am'.
make[3]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/Drivers'
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/Drivers'
make[1]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/Drivers'
Making install in include
make[1]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/include'
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/include'
make[2]: Nothing to be done for `install-exec-am'.
test -z "/usr/include" || /usr/bin/mkdir -p "/usr/include"
/usr/bin/install -c -m 644 odbcinst.h odbcinstext.h sql.h sqlext.h sqltypes.h sqlucode.h uodbc_stats.h uodbc_extras.h '/usr/include'
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/include'
make[1]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/include'
Making install in doc
make[1]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc'
Making install in AdministratorManual
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/AdministratorManual'
make[3]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/AdministratorManual'
make[3]: Nothing to be done for `install-exec-am'.
make[3]: Nothing to be done for `install-data-am'.
make[3]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/AdministratorManual'
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/AdministratorManual'
Making install in ProgrammerManual
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/ProgrammerManual'
Making install in Tutorial
make[3]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/ProgrammerManual/Tutorial'
make[4]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/ProgrammerManual/Tutorial'
make[4]: Nothing to be done for `install-exec-am'.
make[4]: Nothing to be done for `install-data-am'.
make[4]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/ProgrammerManual/Tutorial'
make[3]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/ProgrammerManual/Tutorial'
make[3]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/ProgrammerManual'
make[4]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/ProgrammerManual'
make[4]: Nothing to be done for `install-exec-am'.
make[4]: Nothing to be done for `install-data-am'.
make[4]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/ProgrammerManual'
make[3]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/ProgrammerManual'
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/ProgrammerManual'
Making install in UserManual
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/UserManual'
make[3]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/UserManual'
make[3]: Nothing to be done for `install-exec-am'.
make[3]: Nothing to be done for `install-data-am'.
make[3]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/UserManual'
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/UserManual'
Making install in lst
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/lst'
make[3]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/lst'
make[3]: Nothing to be done for `install-exec-am'.
make[3]: Nothing to be done for `install-data-am'.
make[3]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/lst'
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc/lst'
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc'
make[3]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc'
make[3]: Nothing to be done for `install-exec-am'.
make[3]: Nothing to be done for `install-data-am'.
make[3]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc'
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc'
make[1]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/doc'
Making install in samples
make[1]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/samples'
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/samples'
make[2]: Nothing to be done for `install-exec-am'.
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/samples'
make[1]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0/samples'
make[1]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0'
make[2]: Entering directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0'
make[2]: Nothing to be done for `install-exec-am'.
touch /etc/odbcinst.ini
touch /etc/odbc.ini
mkdir -p /etc/ODBCDataSources
/usr/bin/odbc_config --header > /usr/include/unixodbc_conf.h
make[2]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0'
make[1]: Leaving directory `/tmp/unixODBC.4656.11060.7585/unixODBC-2.3.0'


[root@localhost unixODBC-2.3.0]# /usr/bin/odbc_config --version
2.3.0
[root@localhost msodbcsql-11.0.2270.0]# odbc_config --odbcinstini
/etc/odbcinst.ini
[root@localhost msodbcsql-11.0.2270.0]# cat /etc/odbcinst.ini

INSTALLING THE MICROSOFT ODBC DRIVER 11 FOR SQL SERVER ON LINUX

[root@localhost msodbcsql-11.0.2270.0]# pwd
/root/msodbcsql-11.0.2270.0
[root@localhost msodbcsql-11.0.2270.0]# odbc_config --odbcinstini
/etc/odbcinst.ini


[root@localhost msodbcsql-11.0.2270.0]# cat /etc/odbcinst.ini
[root@localhost msodbcsql-11.0.2270.0]# ./install.sh

Microsoft ODBC Driver 11 for SQL Server Installation Script
Copyright Microsoft Corp.

Starting install for Microsoft ODBC Driver 11 for SQL Server

Unknown command given.
Usage: install.sh [global options] command [command options]

Global options:
   --help - prints this message
Valid commands are verify and install
  install) install the driver (also verifies before installing and registers
           with the driver manager)
  verify) check to make sure the unixODBC DriverManager configuration is
          correct before installing
install command take the following options:
  --bin-dir=<directory> - location to create symbolic links for bcp and sqlcmd utilities,
      defaults to the /usr/bin directory
  --lib-dir=<directory> - location to deposit the Microsoft SQL Server ODBC Driver for Linux,
      defaults to the /opt/microsoft/msodbcsql/lib directory
  --force - continues installation even if an error occurs
  --accept-license - forgoes showing the EULA and implies agreement with its contents

[root@localhost msodbcsql-11.0.2270.0]# ./install.sh verify

Microsoft ODBC Driver 11 for SQL Server Installation Script
Copyright Microsoft Corp.

Starting install for Microsoft ODBC Driver 11 for SQL Server

Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ............................. OK
unixODBC Driver Manager configuration correct .............................. OK*
Microsoft ODBC Driver 11 for SQL Server already installed ............ NOT FOUND

Install log created at /tmp/msodbcsql.19893.20704.15951/install.log.

One or more steps may have an *. See README for more information regarding
these steps.
[root@localhost msodbcsql-11.0.2270.0]#

[root@localhost msodbcsql-11.0.2270.0]# ./install.sh install

Microsoft ODBC Driver 11 for SQL Server Installation Script
Copyright Microsoft Corp.

Starting install for Microsoft ODBC Driver 11 for SQL Server

MICROSOFT SOFTWARE LICENSE TERMS

MICROSOFT ODBC DRIVER 11 FOR SQL SERVER
MICROSOFT COMMAND LINE UTILITIES 11 FOR SQL SERVER

These license terms are an agreement between Microsoft Corporation (or based on
where you live, one of its affiliates) and you. Please read them. They apply to
the software named above, which includes the media on which you received it, if
any. The terms also apply to any Microsoft
<....Omitted for clear reading...>

Enter YES to accept the license or anything else to terminate the installation: YES

Checking for 64 bit Linux compatible OS ..................................... OK
Checking required libs are installed ........................................ OK
unixODBC utilities (odbc_config and odbcinst) installed ..................... OK
unixODBC Driver Manager version 2.3.0 installed ............................. OK
unixODBC Driver Manager configuration correct .............................. OK*
Microsoft ODBC Driver 11 for SQL Server already installed ............ NOT FOUND
Microsoft ODBC Driver 11 for SQL Server files copied ........................ OK
Symbolic links for bcp and sqlcmd created ................................... OK
Microsoft ODBC Driver 11 for SQL Server registered ................... INSTALLED

Install log created at /tmp/msodbcsql.16000.1297.15412/install.log.

One or more steps may have an *. See README for more information regarding
these steps.
[root@localhost msodbcsql-11.0.2270.0]#


[root@localhost msodbcsql-11.0.2270.0]# odbc_config --odbcinstini
/etc/odbcinst.ini
[root@localhost msodbcsql-11.0.2270.0]# cat /etc/odbcinst.ini
[ODBC Driver 11 for SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading=1
UsageCount=1

[root@localhost msodbcsql-11.0.2270.0]# odbcinst -q -d -n "ODBC Driver 11 for SQL Server"
[ODBC Driver 11 for SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading=1
UsageCount=1

[root@localhost msodbcsql-11.0.2270.0]# /opt/microsoft/msodbcsql/bin/sqlcmd-11.0.2270.0
Microsoft (R) SQL Server Command Line Tool
Version 11.0.2270.0 Linux
Copyright (c) 2012 Microsoft. All rights reserved.

usage: sqlcmd            [-U login id]          [-P password]
  [-S server or Dsn if -D is provided]
  [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-K application intent]
  [-M multisubnet failover]
  [-b On error batch abort]
  [-D Dsn flag, indicate -S is Dsn]
  [-X[1] disable commands, startup script, environment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

[root@localhost msodbcsql-11.0.2270.0]# /opt/microsoft/msodbcsql/bin/bcp-11.0.2270.0
usage: /opt/microsoft/msodbcsql/bin/bcp-11.0.2270.0 {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-q quoted identifier]
  [-t field terminator]     [-r row terminator]
  [-a packetsize]           [-K application intent]
  [-S server name or DSN if -D provided]             [-D treat -S as DSN]
  [-U username]             [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-d database name]
 
[root@localhost msodbcsql-11.0.2270.0]# cat ~/.odbc.ini
[MSSQLPROD1]
Driver = ODBC Driver 11 for SQL Server
Server = tcp:192.168.6.132,49160
#Server = 192.168.6.132,49160

 
[root@localhost msodbcsql-11.0.2270.0]# isql MSSQLPROD1 sa p_ssw0rd
SQL>  select cast(@@version as char(100))
+-----------------------------------------------------------------------------------------------------+
|                                                                                                     |
+-----------------------------------------------------------------------------------------------------+
| Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
        Feb 20 2014 20:04:26
        Copyright (c) Microsoft Corpo|
+-----------------------------------------------------------------------------------------------------+
SQLRowCount returns 0
1 rows fetched
SQL> quit
[root@localhost msodbcsql-11.0.2270.0]#

[root@localhost msodbcsql-11.0.2270.0]# cat ~/.odbc.ini
[MSSQLPROD1]
Driver = ODBC Driver 11 for SQL Server
Server = tcp:192.168.6.132,49160
#Server = 192.168.6.132,49160
Database = Northwnd

[root@localhost msodbcsql-11.0.2270.0]# isql MSSQLPROD1 sa p_ssw0rd
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from dbo.region
+------------+---------------------------------------------------+
| RegionID   | RegionDescription                                 |
+------------+---------------------------------------------------+
| 1          | Eastern                                           |
| 2          | Western                                           |
| 3          | Northern                                          |
| 4          | Southern                                          |
| 5          | Central                                           |
| 6          | Non-USA                                           |
| 7          | Singapore                                         |
| 8          | China                                             |
| 9          | Duplicated                                        |
| 10         | Duplicated2                                       |
+------------+---------------------------------------------------+
SQLRowCount returns 0
10 rows fetched

[root@localhost msodbcsql-11.0.2270.0]# /opt/microsoft/msodbcsql/bin/sqlcmd-11.0.2270.0 -S 192.168.6.132,49160 -U sa
Password:
1> select @@version
2> go
                                                                                                                                                      
-----------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
        Feb 20 2014 20:04:26
        Copyright (c) Microsoft Corporation
        Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)


(1 rows affected)

1> select * from northwnd.dbo.region
2> go
RegionID    RegionDescription
----------- --------------------------------------------------
          1 Eastern
          2 Western
          3 Northern
          4 Southern
          5 Central
          6 Non-USA
          7 Singapore
          8 China
          9 Duplicated
         10 Duplicated2

(10 rows affected)
1> quit

[root@localhost msodbcsql-11.0.2270.0]# /opt/microsoft/msodbcsql/bin/sqlcmd-11.0.2270.0 -S MSSQLPROD1 -D -U sa -P p_ssw0rd
1> select @@version;
2> go
                                                                                                                                                      
------------------------------------------------------------------------
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
        Feb 20 2014 20:04:26
        Copyright (c) Microsoft Corporation
        Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)


(1 rows affected)
1> quit

Tuesday, March 17, 2015

VMware Transport Modes: Best practices and troubleshooting

 

Encountered issues restoring via “SAN” failed during the boot up, error symptom is “operating system not found”, tried again with “NBD”, it works.

http://www.symantec.com/business/support/index?page=content&id=tech183072

 

Issue

A VMware Backup Host can access Virtual Machine data from datastores using four different methods – SAN, LAN(NBD), HotAdd, NBDSSL. These methods are referred to as VMware Transport modes. This article talks about these transport modes, the best practices around them and troubleshooting tips for some commonly seen errors related to transport modes in NetBackup and Backup Exec.

Solution

For both Backup and Restore operations, NetBackup and Backup Exec allow choosing any of the four transport modes or a combination of these. If a combination of the transport modes is given, NetBackup and Backup Exec will try all of them one by one until gaining successful access to the data of the Virtual Machine.

Details on each of the transport modes

1. SAN:  The SAN transport mode requires the VMware Backup Host to reside on a physical machine with access to Fibre Channel or iSCSI SAN containing the virtual disks to be accessed. This is an efficient data path because no data needs to be transferred through the production ESX/ESXi host.

In this mode, vStorage APIs obtain information from the vCenter server or ESX/ESXi host about the layout of VMFS LUNs and, using this information, reads data directly from the SAN or iSCSI LUN where the VMDK resides.

Best practices around SAN:

  • For using SAN, make sure that datastore LUNs are accessible to the VMware Backup Host.
  • SAN transport is usually the best choice for backups when running on a physical VMware Backup Host. However, it is disabled inside virtual machines, so use HotAdd instead on a virtual VMware Backup Host.
  • SAN transport is not always the best choice for restores. It offers the best performance on thick disks, but the worst performance on thin disks, because of the way vStorage APIs work. For thin disk restore, LAN(NBD) is faster.
  • For SAN restores, disk size should be a multiple of the underlying VMFS block size, otherwise the write to the last fraction of a disk will fail. For example, if virtual disk has a 1MB block size and the datastore is 16.3MB large, the last 0.3MB will not get written. The workaround in this case would be to use NBD for restores of such Virtual Machines.
  • When using SAN transport or hot-add mode on a Windows Server 2008/2008 R2 VMware Backup Host, make sure to set:
    • SAN policy to onlineAll
    • SAN disk as read-only, except during restores

2. LAN (NBD): In this mode, the ESX/ESXi host reads data from storage and sends it across a network to the VMware Backup Host. As its name implies, this transport mode is not LAN‐free, unlike SAN transport.

LAN transport offers the following advantages:

  • The ESX/ESXi host can use any storage device, including local storage or NAS.
  • The VMware Backup server could be a virtual machine, so you can use a resource pool and scheduling capabilities of VMware vSphere to minimize the performance impact of backup. For example, you can put the VMware Backup Host in a different resource pool than the production ESX/ESXi hosts, with lower priority for backup.
  • If the ESX/ESXi host and VMware Backup Host are on a private network, you can use unencrypted data transfer,which is faster and consumes fewer resources than NBDSSL. If you need to protect sensitive information, you have the option of transferring virtual machine data in an encrypted form using NBDSSL.

Best Practices when using LAN:

  • Since the data in this case is read by ESX/ESXi server from storage and then sent to VMware Backup Host, It is must to have network connectivity between ESX/ESXi server and VMware Backup Host. If the VMware Backup Host has connectivity to vCenter server but not the ESX/ESXi server- snapshots will succeed but vmdk read/write operations will fail.
  • The VMware Backup Host will need the ability to connect to TCP port 902 on ESX/ESXi hosts while using NBD/NBDSSL for backup/restores.
  • VMware uses Network File Copy (NFC) protocol to read VMDK using NBD transport mode. You need one NFC connection for each VMDK file being backed up. There is a limit on the number of NFC connections that can be made per ESX/vCenter server. These limits differ in different versions of vSphere - please refer to the NetBackup for VMware Admin Guide(linked below) for these limits. Backup/Restore operations using NBD might hang if this limit is reached.

3. HotAdd: When running VMware Backup Host on a Virtual Machine, vStorage APIs can take advantage of the SCSI Hot-add capability of the ESX/ESXi server to attach the VMDKs of a Virtual Machine being backed up to the VMware Backup Host. This is referred to as HotAdd transport mode.

Running the VMware Backup server on a virtual machine has two advantages: it is easy to move a virtual machine around and it can also back up local storage without using the LAN, although this incurs more overhead on the physical ESX/ESXi host than when using SAN transport mode.

Best practices when using HotAdd:

  • HotAdd works only with virtual machines with SCSI disks and is not supported for backing up virtual machines with IDE disks.
  • A single SCSI controller can have a maximum of 15 disks attached. To run multiple concurrent jobs totally more than 15 disks it is necessary to add more SCSI controllers to the HotAdd host.  The maximum number of 4 SCSI controllers can be added to a HotAdd host, so a total of 60 devices are supported at the maximum.
  • HotAdd requires the VMware Backup Host to have access to datastores where the Virtual Machine being backed up resides. This essentially means:
    • ESX where the VMware backup host is running should have access to datastores where the Virtual Machine being backed up resides. 
    • Both the VMware backup host and Virtual Machine being backed up should be under the same datacenter.
  • HotAdd cannot be used if the VMFS block size of the datastore containing the virtual machine folder for the target virtual machine does not match the VMFS block size of the datastore containing the VMware Backup Host virtual machine. For example, if you back up virtual disk on a datastore with 1MB blocks, the VMware Backup Host must also be on a datastore with 1MB blocks.
  • Restores using HotAdd on a Windows Server 2008 proxy require setting the SAN policy toonlineAll
  • If you are converting a physical machine to a virtual machine with the intention of using hottadd to back up the virtual machine, do not use IDE controllers for any disks that are used during the conversion process.
  • The VMware Backup Host will need the ability to connect to TCP port 902 on ESX/ESXi hosts while using HotAdd for backup/restores.

4. NBDSSL:  NBDSSL is the same as NBD, except that NBDSSL uses SSL to encrypt all data passed over the TCP/IP connection.

Troubleshooting for some common transport mode related failures

Backups/Restores failing with status 6 or status 13 or status 11 with following indication in Activity monitor might indicate that there is some issue with transport modes:-

  • ERR - Error opening the snapshot disks using given transport mode: Status 23 indicates that there was some problem in accessing the vmdk using given transport mode.
    Here are some tips on handling this kind of error:
    • If you are using NBD, make sure the VMware Backup Host has connectivity to ESX server hosting the virtual machine.
    • If you are using SAN, please make sure that the datastore LUNs are accessible to VMware Backup Host.
    • If you are using HotAdd, please make sure that your backup host is Virtual Machine and following conditions are satisfied:
      • The VM should not contain IDE disks.
      • Ensure that there are sufficient SCSI controllers attached on the Backup Host VM.
      • The Backup Host VM has access to datastores where VM being backed up resides.
      • The Backup Host VM and VM being backed up should be under the same datacenter.
      • If the previous backup failed, it might have left some disks of the backup VM attached to Backup Host. These disks need to be manually removed before attempting the next backup.
    • If a non-default port for vCenter is in use, then that port needs to be defined while adding vCenter credentials to NetBackup or Backup Exec.
    • If using NBD/NBDSSL/HotAdd, please make sure the VMware Backup Host is able to communicate to port 902 of ESX server hosting the VM.
  • file read failed indicates that there might be problem in reading the VMDK using the given transport mode. 
  • file write failed indicates that there might be some problem in writing to the VMDK using the given transport mode.
    • If using SAN for restores, please make sure datastore LUNs are accessible to the VMware Backup Host and in an online state.
    • If using HotAdd for restore, please make sure that SAN policy on the Backup Host is set to OnlineAll.
    • If using SAN for restore, make sure that the size of VMDK is multiple of datastore block size.  Otherwise, the write of the last block will fail.  In this case, a workaround would be to use NBD for restore.
    • Please make sure that the you assign necessary privileges to the user configured in NetBackup or Backup Exec to log on to vSphere.

Saturday, March 14, 2015

SQL Server T-SQL examples for Backup

USE [master]
GO
EXEC master.dbo.sp_addumpdevice 
@devtype = N'disk', @logicalname = N'BackupStore', @physicalname = N'C:\MSSQL12.PROD1\MSSQL\Backup\BackupStore.bak'
GO


BACKUP DATABASE [NORTHWND] TO  DISK = N'C:\Northwind.bak' WITH NOFORMAT, INIT, 
NAME = N'NORTHWND-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [NORTHWND] TO  DISK = N'C:\Northwind.bak' WITH  RETAINDAYS = 2, -- EXPIREDATE = N'03/14/2015 00:00:00'
    FORMAT, INIT, 
NAME = N'NORTHWND-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10, CONTINUE_AFTER_ERROR
GO


BACKUP DATABASE [NORTHWND] TO [BackupStore] WITH NOFORMAT, NOINIT, 
NAME = N'NORTHWND-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [NORTHWND] TO  [BackupStore] WITH  DIFFERENTIAL , NOFORMAT, NOINIT, 
NAME = N'NORTHWND-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


BACKUP LOG [NORTHWND] TO  [BackupStore] WITH NOFORMAT, NOINIT, 
NAME = N'NORTHWND-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO

BACKUP LOG [NORTHWND] TO  [BackupStore] WITH NOFORMAT, NOINIT,  NAME = N'NORTHWND-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'NORTHWND' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'NORTHWND' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''NORTHWND'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  [BackupStore] WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

-- Backup Tail of Translog
BACKUP LOG [NORTHWND] TO  [BackupStore] WITH  NO_TRUNCATE , NOFORMAT, NOINIT, 
NAME = N'NORTHWND-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  NORECOVERY , COMPRESSION,  STATS = 10
GO

/*
Msg 33101, Level 16, State 1, Line 51
Cannot use certificate 'DatabaseSecureBackup', because its private key is not present or it is not protected by the database master key. SQL Server requires the ability to automatically access the private key of the certificate used for this operation.
Msg 3013, Level 16, State 1, Line 51
BACKUP DATABASE is terminating abnormally.
USE [Master];
CREATE CERTIFICATE DatabaseSecureBackup
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
   WITH SUBJECT = 'Database Secure Backup',
   EXPIRY_DATE = '20181031';
GO
*/


USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
go
CREATE CERTIFICATE DatabaseSecureBackup2 WITH SUBJECT = 'Database Secure Backup2';
go

BACKUP DATABASE [NORTHWND] TO  DISK = N'C:\Northwind.bak'
WITH FORMAT, INIT,  MEDIANAME = N'New Secure Media',  NAME = N'NORTHWND-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = [DatabaseSecureBackup2]),  STATS = 10
GO

/*
Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.
11 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
51 percent processed.
61 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 576 pages for database 'NORTHWND', file 'Northwind' on file 1.
100 percent processed.
Processed 1 pages for database 'NORTHWND', file 'Northwind_log' on file 1.
BACKUP DATABASE successfully processed 577 pages in 0.845 seconds (5.327 MB/sec).
*/