Rebuild system database in MSSQL 2012

I faced the problem on MSSQL 2012 which I quite not familiar with, so bad luck of me. On the beginning, I ran a program which update the database records with checking the compatible of that program and I stuck in collation problem.

I install my MSSQL 2012 by using every default of installation steps, collation is "SQL_Latin1_General_CP1_CI_AS" and my new created database is "Thai_CI_AS" (which owner of application specified with this, cannot change it by the way).

The program check the collation and shown the error that is mismatch of both collation.

Wed Oct 16 18:00:10 ICT 2013 : Insert data to table MONTHLY_SECURITY_TRADING
       : com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve the collation conflict between "Thai_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Crosscheck in my MS SQL Server Management Studio each database or run following SQL statement for checking:

select name, collation_name from sys.databases;

The result is shown as in red rectangle in above picture. As you see, there is type mismatch between "SQL_Latin1_General_CP1_CI_AS" and "Thai_CI_AS", so I must change the system's collation.

Then, open up the command prompt (in Start --> Run.. --> type 'cmd' and enter), navigate the location to "C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012"

Note: should backup everything in your MSSQL before doing this!!! (backup data, users, etc.)

Run following command in the command prompt :

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="<computer's_name>\<administrator>" / SAPWD= <SA_password>  /SQLCOLLATION=<collation_to_change>

Then the output from entered command should be nothing errors. If check log file, it should say 'PASS' and that is all. The example of log file look likes as below:

Overall summary:
  Final result:                  Passed
  Exit code (Decimal):           0
  Start time:                    2556-10-21 09:24:26
  End time:                      2013-10-21 09:25:29
  Requested action:              RebuildDatabase

Machine Properties:
  Machine name:                  WIN-BPKOESH02D6
  Machine processor count:       16
  OS version:                    Windows Server 2008
  OS service pack:               Service Pack 2
  OS region:                     ไทย
  OS language:                   English (United States)
  OS architecture:               x86
  Process architecture:          32 Bit
  OS clustered:                  No

Product features discovered:
  Product              Instance             Instance ID                    Feature                                  Language             Edition              Version         Clustered
  SQL Server 2012      MSSQLSERVER          MSSQL11.MSSQLSERVER            Database Engine Services                 1033                 Enterprise Evaluation Edition 11.1.3000.0     No       
  SQL Server 2012      MSSQLSERVER          MSSQL11.MSSQLSERVER            SQL Server Replication                   1033                 Enterprise Evaluation Edition 11.1.3000.0     No       
  SQL Server 2012      MSSQLSERVER          MSSQL11.MSSQLSERVER            Full-Text and Semantic Extractions for Search 1033                 Enterprise Evaluation Edition 11.1.3000.0     No       
  SQL Server 2012      MSSQLSERVER          MSSQL11.MSSQLSERVER            Data Quality Services                    1033                 Enterprise Evaluation Edition 11.1.3000.0     No       
  SQL Server 2012      MSSQLSERVER          MSAS11.MSSQLSERVER             Analysis Services                        1033                 Enterprise Evaluation Edition 11.1.3000.0     No       
  SQL Server 2012      MSSQLSERVER          MSRS11.MSSQLSERVER             Reporting Services - Native              1033                 Enterprise Evaluation Edition 11.1.3000.0     No       
  SQL Server 2012                                                          Management Tools - Basic                 1033                 Enterprise Evaluation Edition 11.1.3000.0     No       
  SQL Server 2012                                                          Management Tools - Complete              1033                 Enterprise Evaluation Edition 11.1.3000.0     No       
  SQL Server 2012                                                          Client Tools Connectivity                1033                 Enterprise Evaluation Edition 11.1.3000.0     No       
  SQL Server 2012                                                          Client Tools Backwards Compatibility     1033                 Enterprise Evaluation Edition 11.1.3000.0     No       
  SQL Server 2012                                                          Client Tools SDK                         1033                 Enterprise Evaluation Edition 11.1.3000.0     No       
  SQL Server 2012                                                          Integration Services                     1033                 Enterprise Evaluation Edition 11.1.3000.0     No       

Package properties:
  Description:                   Microsoft SQL Server 2012 Service Pack 1
  ProductName:                   SQL Server 2012
  Type:                          RTM
  Version:                       11
  SPLevel:                       0
  Installation edition:         

User Input Settings:
  ACTION:                        RebuildDatabase
  CONFIGURATIONFILE:             C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20131021_092412\ConfigurationFile.ini
  ENU:                           false
  HELP:                          false
  IACCEPTSQLSERVERLICENSETERMS:  false
  INDICATEPROGRESS:              false
  INSTANCENAME:                  MSSQLSERVER
  QUIET:                         true
  QUIETSIMPLE:                   false
  SAPWD:                         *****
  SQLCOLLATION:                  THAI_CI_AS
  SQLSYSADMINACCOUNTS:           WIN-BPKOESH02D6\administrator
  UIMODE:                        Normal
  X86:                           false

  Configuration file:            C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20131021_092412\ConfigurationFile.ini

Detailed results:
  Feature:                       SQL Server Replication
  Status:                        Passed

  Feature:                       Database Engine Services
  Status:                        Passed

  Feature:                       Reporting Services - Native
  Status:                        Passed

  Feature:                       Analysis Services
  Status:                        Passed

  Feature:                       SQL Browser
  Status:                        Passed

  Feature:                       SQL Writer
  Status:                        Passed

Rules with failures:

Global rules:

There are no scenario-specific rules.

Rules report file:               C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20131021_092412\SystemConfigurationCheck_Report.htm

Go back to MS SQL Server Management Studio, check the collation once again. They are changed!

As you see, my databases which not system databases were gone. I have to bring them back by right click at Databases in left panel, select "Attach..." Click "Add..", then select the datafile which is lost and it will be back as before rebuilding system database.

Anyway, the database user is still not correctly, I still cannot login to database engine by using SQL server authentication method. I have to remove that user out and re-create it.

Note: I found a website which has preparation steps which I think it is very useful, as below
http://www.mssqltips.com/sqlservertip/1531/rebuilding-the-sql-server-master-database-part-1-of-3/

Many thanks to http://sqlbuzz.wordpress.com/2011/08/20/how-to-rebuild-master-database-aka-rebuilding-sql-server-2008r2/ as well, it help me a lot to find out which path I must navigate to in command prompt.


I hope this may be helpful to anyone also. Good luck!

NFS share between HP-UX servers

There is some different file and step to implement between HP-UX 11.23 (and lower version) and HP-UX 11.31. But here is example on HP-UX 11.31.

At source server

#vi /etc/dfs/dfstab
Insert below line into that file;
        share -F nfs -o rw <mountpoint>
#shareall -F nfs
If you want to check the status of NFS server, use below command;
#showmount -e <ip> 

At destination server

#mount -F nfs <ip host>:<mountpoint>  <mountpoint>
#vi /etc/fstab
Insert below line into that file;
      <ip>:<mountpoint> <mountpoint> nfs defaults 0 0
#mount -a

Use 'bdf' to check your mount path.

For restarting the service
# /sbin/init.d/nfs.server start
    Starting NFS SERVER subsystem 
    
    Reading in /etc/dfs/dfstab
    Starting up the mount daemon
        /usr/sbin/rpc.mountd 
    Starting up the NFS server daemon
        /usr/sbin/nfsd
      Starting up nfsmapid daemon


All is done!

Default username, password and IP address of iDRAC

During these few days, I just got a new Dell PowerEdge R620 server and I am new with it. After I searched for more information about remote control GUI, below is the information I got.

Management LAN is iDRAC (LAN port on rear view)
Default iDRAC IP address is 192.168.0.120

Default username to login on the iDRAC web page is root

Default password to login is calvin

If you login on iDRAC's web page, it will let you to change the default password, but there is option for keeping this default password. If you want to keep this default password, select that option.

Hope this blog will help you some when you face same problem as mine.


Credits: 
- http://rodolfovaraujo.wordpress.com/2012/05/25/dell-idrac-default-user-and-password/ (for default username and password)
- one of my friend, who gave me the default IP address, thank you so much.

SSH with no prompt for password

This is step for use in shell script which there is transfer across the machine within the script. Hope it may helpful.


Note: do on source host and put key on destination host and I did it on HP-UX machine, can be apply to every unix OS.

On source host
$ mkdir .ssh
$ chmod 700 .ssh
$ ssh-keygen -t rsa    ##do not use passphrase
$ touch .ssh/authorized_keys
$ chmod 644 .ssh/authorized_keys
$ ls -al
total 176
drwxr-x---   3 oper1      users         8192 Sep  2 16:26 .
drwxr-xr-x  17 root       root          8192 Sep  2 15:45 ..
-r--r--r--   1 oper1      users          842 Mar 14  2005 .cshrc
-r--r--r--   1 oper1      users          347 Nov 14  2000 .exrc
-r--r--r--   1 oper1      users          344 Mar 14  2005 .login
-r--r--r--   1 oper1      users          449 Mar 14  2005 .profile
-rw-------   1 oper1      users          118 Sep  2 16:26 .sh_history
drwx------   2 oper1      users           96 Sep  2 16:25 .ssh
-rw-r--r--   1 oper1      users          396 Sep  2 16:26 id_rsa.pub
$ sftp targethost
Connecting to targethost...
Password:
sftp> pwd
Remote working directory: /home/oper1
sftp> put id_rsa.pub
Uploading id_rsa.pub to /home/oper1/id_rsa.pub
id_rsa.pub                                                                                        100%  224     0.2KB/s   00:00  
sftp> quit

On target host
$ pwd
/home/oper1/.ssh
$ cat id_rsa.pub >> .ssh/authorized_keys



I think this method is simple, easy and no need more software to install.

How to set the console output

This problem I found when the server has several output terminal; KVM console (VGA output) and serial console.

How to check what primary output in-use:- 

1. No any output stream on the terminal

2. Check via command line in shell mode

    shell> conconfig

This means primary output terminal

To change the primary output:- 

    shell> conconfig 2 primary

Means change terminal 2 as primary instead.

Ps. I do not have any pictures in my hands now. I will add some pictures later (when I found one :P)

Some basic thing which should to know

Many of my colleagues are familiar with linux, once they use the HP-UX they usually confused.

Here below is the guide for learning HP-UX as basically.

1. Most of HP-UX use korn shell (ksh). I said "most of" because in few years later the new installation is bourne shell (sh).

2. The user's profile is located at ~/.profile

3. You can use history command by <esc>+k . Yes, correct. It use vi mode, means that you can search the typed command by /<keyword> and 'n' for next search. 

4. Root's home is located at '/' 

5. To check the disk partition use command 'bdf'. It is easier than another command. 

6. HP-UX is no option '-h' in df, do not try to use 'df -h'. 

These are some example I figure out this time. Hope these may useful for some newbies. 

About me, myself

I have worked as system administrator after I graduated from university about 1 year. From the beginning until now, 7 years approximately.

My purpose of creating this blog is, keep my knowledge in here, once I forget something so I can search in here and still share my knowledge to another as well.

Every blogs in here, come from my past experience and found from another website which I do not remember which website. If you found some of my experience is useful and want to use or share, you can use it without of any permission from me. Hope it works :)

My most strength is HP-UX. Moreover, I still have some experience about Oracle database, Oracle weblogic and more, I will point out in each entry.