How to login MS SQL server 2008 database engine

Jephe Wu - http://linuxtechres.blogspot.com


Environment: Windows 2008 server R2 64bit with MS SQL Server 2008. Initially, user1 who belongs to Windows local Administrators group installed this SQL server,
after he resigned, another user who also belongs to local administrators users  group cannot login database engine.

Objective
: to make user2 to login to database engine.

Errors: When user2 tries to login sql server 2008 with Windows authentication mode, it gives the following error messages in event log:
Login failed for user MYDOMAIN\MYLOGIN'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

Steps:

1. login sql server management studio as the initial user1 who set up the sql server.
use [local] to login, you may want to try computer name in server name part

2.  under 'security - login' tab, create a new user login name as \\computername\jwu for Windows authentication, grant public and sysadmin server role , and enable 'grant permission to login database engine' under status tab.

3. logout user1, login as local administrator user 'jwu', use computername as servername to login sql server management studio.

4. you should be able to login now.

References:


1.  Choose an authenticatin mode - http://msdn.microsoft.com/en-us/library/ms144284.aspx
2.  How to change server authentication mode - http://msdn.microsoft.com/en-us/library/ms188670.aspx

How to make SQL server 2005 logshipping secondary database to re-sync with primary database again

Jephe Wu - http://linuxtechres.blogspot.com

Objective: to make secondary db server to sync with primary again, logshipping is not working due to some required log files were deleted by some job.
Environment: Windows 2003 32bit, MS SQL server 2005 (management studio)

Problem: logshipping is not working according to Windows event log and sql job history, due to some required files were deleted, so the restoring is not working.



Steps:
1.  disable restoration job in job agent from SQL management studio
SQL Server Agent -> Jobs - LSRestore_DBxxxx\DBRSQLP4_xxxxx

 also, disable any other sql server agent job which might affect restoration process.
2.   restore the full database backup from primary

a. right clicking database DR_DB1 on secondary server.
b. choose 'task', 'restore database', General, source for restore, from device, add a file which is copied from production daily full backup, tick it to choose it.
c. go to options, choose 'overwrite the existing database', do not care about 'Restore the database file as' part even the 'Restore As' name is not same as what the DR server is having now for that database.
Because we have already choosen 'Overwrite the existing database', so don't have to care, it will overwrite the existing database name in DR server.

For 'Recovery state', choose the second options which is 'Leave the database non-operational, and do not roll back uncommitted transactions, Additional transaction logs can be restored (RESTORE WITH NORECOVERY), then click 'OK'.

After finishing it, the restore job will start it automatically and database will automatically try to find the transaction log file to recovery . 

5. right click on the restoratin job to check history, It will check all transaction log files in the specified directory, skip those which already contained in the restored database.  click refresh and get more information.
6. check reports for transaction log.

References:
1. Index rebuild generate huge log file makes logshipping and mirroring out of sync - http://connect.microsoft.com/SQLServer/feedback/details/352338/index-rebuild-generate-huge-log-file-makes-logshipping-and-mirroring-out-of-sync

2. How to: Remove Log Shipping (SQL Server Management Studio)
This topic explains how to remove log shipping using Microsoft SQL Server Management Studio.
To remove log shipping

    Right-click the database you want to use as your primary database in the log shipping configuration, and then click Properties.

    Under Select a page, click Transaction Log Shipping.

    Clear the Enable this as a primary database in a log shipping configuration check box.

    Click OK to remove log shipping from this primary database.


=============
Part II - after DR test, reconfiguring logshipping 

You don't have to delete database on secondary, just re-configure everything from primary; if you don't have enough space, you can delete it.

Steps:

1. right click database on primary, transaction log shipping , click 'enable this as a primary atabase in a log shipping configuration
2. click backup settings.type in network path of the folder on primary where we will generate transaction log for sharing with secondary for read
\\pridb\logshipping
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\backup\logshipping

note:
a. grant read/write permission to the sql server agent service account of primary server instance so that the backup job on primary can delete old transaction log files successfully.
b. read only permission to sql server agent service account for the secondary server instance in the domain
c. grant sysadmin privilege to primary db sql server agent service account on secondary db, so that the logshipping status 'last backup created' can be updated on primary db , also on secondary db server log shipping report page, Backup - Time Since Last will also be updated. Otherwise, it will only show the initial primary db backup date during logshipping configuration wizard.

in Schedule, to change default interval from 15 minutes to whatever you need. (e.g. 2 mins)

3. click 'add' to add secondary server instance, click connect to choose secondary server name and windows authentication.
click to choose a existing secondary database or enter a name to create new database. e.g. 'DR_PRODUCTION'

in 'Initialize Secondary Database' tab, choose one of three options, if you have recent full backup, choose the second option, then type in a

network path to the backup file that is accessible by secondary instance, e.g. \\primdb\backup\PRODUCTION_backup_201205150110.bak
or
after copying file to secondary, use \\secdb\d$\PRODUCTION_backup_201205150110.bak
Note: you cannot directly use d:\PRODUCTION_backup_201205150110.bak since it's asking network path, otherwise, you are not able to add it once clicking 'add'.

click 'restore options', type in data and log files path on secondary server: e.g.
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PRODUCTION
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\PRODUCTION

in 'copy files' tab, type in  destination folder on secondary where transaction log should be copied to
e.g. D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\logshipping

click on 'Schedule', change default 15 minutes interval to whatever you need (e.g. 2mins)

click on 'Restore Transaction Log' tab, choose 'no recovery mode' or 'standby mode' (readonly while applying transaction log).

choose 45 minutes for alert if no restore occurs within.

Note: you can also restore the full backup from secondary database first by right clicking 'database', choose 'restore', in 'options', choose the second option to leave database in non-operational state to be able to apply for transaction log and do not undo the uncommited transaction.

4. in Monitor server instance, choose secondary server.

5. user zabbix/dbforbix to monitor logshipping progress
refer to http://sqlmonitormetrics.red-gate.com/time-since-last-restore/

Measure the time since the last restore, in minutes.
Enter the T-SQL query that will collect data:

SELECT DATEDIFF(MINUTE, restore_date, GETDATE())
  FROM (SELECT TOP 1 restore_date
          FROM msdb.dbo.restorehistory
          WHERE destination_database_name = DB_NAME()
          ORDER BY restore_date DESC

       ) rd;

Vmware FAQ

Jephe Wu - http://linuxtechres.blogspot.com


  1. Performance issue when copying files between Windows 7 64bit host and Linux guest
 According to Vmware KB - Performance issues with bridged networking on Windows Server 2003 and Windows 2008 host operating systems - http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1006619
You must disable the TCP Offload Engine features in the driver on the host.
 
To disable the TCP Offload Engine features:
  1. Click Start > Run.
  2. Type regedit and press Enter.
  3. Browse to the following location:
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
  4. Create the key DisableTaskOffload (type: DWORD).
  5. Set the value of the key to 1 .
  6. Close the Registry Editor and restart the computer.

Oracle RAC Documentation

Jephe Wu - http://linuxtechres.blogspot.com

  1. How to check if it's running RAC
BEGIN
  IF dbms_utility.is_cluster_database THEN
      dbms_output.put_line('Running in SHARED/RAC mode.');
  ELSE
      dbms_output.put_line('Running in EXCLUSIVE mode.');
  END IF;
END;
/
 
or
SQL> show parameter CLUSTER_DATABASE
 
 
 2. How to stop/start dbconsole?
Linux: 
$ export ORACLE_HOME=xxx
$ export ORACLE_SID=xxx
$ ORACLE_HOME/bin/emctl stop dbconsole
Windows:
set ORACLE_HOME=xxx
set ORACLE_SID=xxx
ORACLE_HOME\bin\emctl stop dbconsole 
 
Linux:
$ export ORACLE_HOME=xxx
$ export ORACLE_SID=xxx
$ ORACLE_HOME/bin/emctl start dbconsole
Windows:
set ORACLE_HOME=xxx
set ORACLE_SID=xxx
ORACLE_HOME\bin\emctl start dbconsole
Reference: Enterprise Manager Database Console FAQ [ID 863631.1]
 
3.  How to stop/start RAC 
 Bring up the inst1 of database db1 
$ srvctl start instance -d db1 -i inst1

 Stop the db1 database: all its instances and all its services, on all nodes.
$ srvctl stop database -d db1
 
 
4. database uptime
SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" FROM sys.v_$instance;
 
5. alert log location
show parameter BACKGROUND_DUMP_DEST; 
 
6. stop/start RAC
Starting the Oracle 10g RAC Cluster 10g Environment:

- Run as oracle:  su - oracle

$ export ORACLE_SID=orcl1
$ srvctl start nodeapps -n linux1

$ srvctl start asm -n linux1

$ srvctl start instance -d orcl -i orcl1

$ emctl start dbconsole

Start/Stop All Instances with SRVCTL



Stopping the Oracle 10g RAC Cluster 10g Environment: 

- Run as oracle:  su - oracle

$ export ORACLE_SID=orcl1

$ emctl stop dbconsole

$ srvctl stop instance -d orcl -i orcl1
srvctl status instance -d dbname -i instancename

$ srvctl stop asm -n linux1 [-o immediate]

$ srvctl stop nodeapps -n linux1

Starting the Oracle RAC 10g Environment

To stop or start both database instances at once: 

$ srvctl start database -d orcl [-o open | -o mount | -o nomount]

$ srvctl stop database -d orcl [-o normal | -o transactional | -o immediate | -o abort] 
srvctl status database -d dbname

srvctl config database -d dbname (shows instances name, node and oracle home) ==========
 
Services:
srvctl status service -d dbname

 srvctl config service -d dbname

 srvctl start service -d dbname -s servicename

srvctl stop service -d dbname -s servicename 
 
7. lsnrctl PLSExtProc, XDB and XPT
PLSExtProc: This is used to Call OS Commands from PL/SQL using External Procedures. Default in the Listener file. 
the XDB service is used for the XML DB database option 
the XPT service is used for Dataguard. 

All three of these can be disabled if you are not using their associated features. 

Oracle 10g and 11g Installation Documentation for Linux and Windows

Jephe Wu - http://linuxtechres.blogspot.com



Part I: Documentation from Oracle website itself:


Part II: Documentation from third-party website:
10g 64bit installation requirement:
  • Install 10g on RHEL4 64bit - installation requirement , similar with 11g installation steps:
- http://download.oracle.com/docs/cd/B19306_01/install.102/b15667/pre_install.htm

  1. yum install xorg-x11-deprecated-libs
otherwise ,you will get error message jre/1.4.2/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or dir afer launching runInstlaler.

     2.  yum install libaio*

     3. run this:
yum install xorg-x11-deprecated-libs
yum install sysstat*
yum install libaio*
yum install unixODBC*
yum install binutils*
yum install compat-db*
yum install gcc-c++*
yum install libstdc++*
yum install make*
yum install sysstat*
yum install glibc*
 yum install control-center*

    Install Zabbix on CentOS 5.6

    Jephe Wu - http://linuxtechres.blogspot.com


    • PHP and Zabbix
    CentOS 5.6, after installing zabbix server, try to access http://localhost/zabbix/queue.php, it displays empty page, httpd error log shows
    [Wed Jun 15 11:08:14 2011] [error] [client 192.168.1.3] PHP Fatal error:  Call to undefined function bcscale() in /var/www/html/zabbix/include/defines.inc.php on line 794

    Solution: yum install bcmath
    • Check of pre-requisites
    When accessing http://localhost/zabbix/queue.php, we have to pass all the checking as follows:
    vi /etc/php.ini to change:
    timezone = Australia/Sydney
    post_max_size = 32M
    upload_max_filesize = 2M
    max_execution_time = 600  
    max_input_time = 600 

    yum install php-mbstring
    yum install php-gd
    • PHP and PHP53
    When you use yum to install php* , you can exclude php53* as follows:

    vi /etc/yum.conf to add
    exclude=php53*

    or yum install php* --exclude=php53*


    • References

    1. http://www.muck.net/?p=16  -   Installing Zabbix 1.4 on CentOS 5