Every SQL Server relies on FIVE primary system databases, each of which must be present for the server to
operate effectively.
1. Master : The Master database stores basic configuration information for the server. This includes
information about the file locations of the user databases, as well as logon accounts, server configuration
settings, and a number of other items such as linked servers and startup stored procedures.
2. Model : The Model database is a template database that is copied into a new database whenever
it is created on the instance. Database options set in model will be applied to new databases created on
the instance, and any objects created in model will be copied over as well.
3. MSDB : The MSDB database is used to support a number of technologies within SQL Server,
including the SQL Server Agent, SQL Server Management Studio, Database Mail, and Service Broker.
A great deal of history and metadata information is available in msdb, including the backup and
restore history for the databases on the server as well as the history for SQL agent jobs.
4. TempDB : The TempDB system databases is a shared temporary storage resource used by a
number of features of SQL Server, and made available to all users. Tempdb is used for temporary objects,
worktables, online index operations, cursors, table variables, and the snapshot isolation version store,
among other things. It is recreated every time that the server is restarted, which means that no objects
in tempdb are permanently stored.
SQL Server provides two types of temp tables based on the behavior and scope of the table. These are:
1. Local Temp Table
2. Global Temp Table
Local Temp Table
Local temp tables are only available to the current connection for the user; and they are
automatically deleted when the user disconnects from instances. Local temporary table name is
stared with hash ("#") sign.
Global Temp Table
Global Temporary tables name starts with a double hash ("##"). Once this table has been created by
a connection, like a permanent table it is then available to any user by any connection.
It can only be deleted once all connections have been closed.
Table created under tempdb considered as permanent temp table.
5. Resource : The Resource database is responsible for physically storing all of the
SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of
SQL Server system objects with the ability to overwrite only this database.
Apart from this 5, other databases also available.
ReportServer : Primary database for Reporting Services to store the meta data and object definitions.
ReportServerTempDB : Report Server Temp DB.
Saturday, May 17, 2014
SQL Server : System Databases
Sunday, May 4, 2014
SQL Server : Database Mirroring
Step 1: Take the Full Backup of Principal database using below backup command.
BACKUP DATABASE Temp TO DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Temp.bak';
Step 2: Take the Transaction Log Backup of Principal database using below backup command
BACKUP LOG Temp TO DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Temp.trn';
Step 3: Copy both backup to Mirror server and Restore the Full backup database with
RESTORE WITH NORECOVERY MODE in Mirror server using below command
RESTORE DATABASE Temp FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Temp.bak' with NORECOVERY
Step 4: Restore Transaction Log back up in Mirror server using below command.
RESTORE LOG Temp FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Temp.trn' with NORECOVERY
Step 5: Right Click on Principal database (Temp) and choose Properties and then choose Mirror
option in properties list
Step 6 : Click the "Configure Security" button and then click NEXT if the Configure Database
Mirroring Security Wizard screen appears.
After click on NEXT button,it will ask include Witness server or not, choose YES or NO
based on your requirements.
Step 7: Click on NEXT button, It will ask Principal Server instance details along with port number.
Step 8: Click on NEXT button, It will ask Mirror Server instance details along with port number.
Step 9: Click on NEXT button, It will ask Service account details.
Step 10: Click on NEXT button, It will configure Endpoints and Click on OK , Database Properties
window will appear , Click on START MIRRORING button.
Some of useful Queries in Morring:
To find Backup History of Database : SELECT S.DATABASE_NAME,M.PHYSICAL_DEVICE_NAME,S.BACKUP_START_DATE ,CASE S.[TYPE] WHEN 'D' THEN 'FULL' WHEN 'I' THEN 'DIFFERENTIAL' WHEN 'L' THEN 'TRANSACTION LOG'END AS BACKUPTYPE ,S.SERVER_NAME,S.RECOVERY_MODEL FROM MSDB.DBO.BACKUPSET S INNER JOIN MSDB.DBO.BACKUPMEDIAFAMILY M ON S.MEDIA_SET_ID = M.MEDIA_SET_ID WHERE S.DATABASE_NAME = DB_NAME() ORDER BY BACKUP_START_DATE DESC To Check the mirroring safety level : SELECT D.NAME, D.DATABASE_ID, M.MIRRORING_ROLE_DESC, M.MIRRORING_STATE_DESC, M.MIRRORING_SAFETY_LEVEL_DESC, M.MIRRORING_PARTNER_NAME, M.MIRRORING_PARTNER_INSTANCE, M.MIRRORING_WITNESS_NAME, M.MIRRORING_WITNESS_STATE_DESC FROM SYS.DATABASE_MIRRORING M JOIN SYS.DATABASES D ON M.DATABASE_ID = D.DATABASE_ID WHERE MIRRORING_STATE_DESC IS NOT NULL To find out about the Mirroring endpoints information SELECT E.NAME, E.PROTOCOL_DESC, E.TYPE_DESC, E.ROLE_DESC, E.STATE_DESC, T.PORT, E.IS_ENCRYPTION_ENABLED, E.ENCRYPTION_ALGORITHM_DESC, E.CONNECTION_AUTH_DESC FROM SYS.DATABASE_MIRRORING_ENDPOINTS E JOIN SYS.TCP_ENDPOINTS T ON E.ENDPOINT_ID = T.ENDPOINT_ID Setup Partner Manually, ALTER DATABASE Test SET PARTNER = 'TCP://(Mirror Server Name or IP) :5022' (on Principal side) ALTER DATABASE Test SET PARTNER = 'TCP://(Principal Server Name or IP) :5022' (on Mirror side)
Tuesday, November 19, 2013
Commands in HDFS :
Creating Directory in HDFS
->hadoop fs –mkdir /user/systemname/foldername
OR
->hadoop fs –mkdir foldername
Delete a Folder in HDFS
->hadoop fs –rmr /user/systemname/foldername
OR
->hadoop fs –rmr foldername
Delete a File in HDFS
->hadoop fs –rm /user/systemname/foldername/filename
OR
->hadoop fs –rm foldername/filename
Copying a file from one location to another location in HDFS
->hadoop fs –cp source destination
Moving a File from one location to another location in HDFS
->hadoop fs –mv source destination
Putting a file in HDFS
->hadoop fs –put /source /destination
OR
->hadoop fs –copyFromLocal /source /destination
Getting a file in HDFS
->hadoop fs –get /source /destination
OR
->hadoop fs –copyToLocal /source /destination
Getting list of file in HDFS
->hadoop fs –ls
Reading content of a file in HDFS
->hadoop fs –cat filelocation (head, tail)
Checking HDFS condition
->hadoop fsck
Getting Health of Hadoop
->sudo –u hdfs hadoop /
->sudo –u hdfs hadoop /sourcepath –files –blocks –racks
UnZip a file in Hadoop
-tar xzvf filename
Monday, November 18, 2013
Hadoop Installation steps on Centos
// Installation Steps in Centos//
// suppose MyClusterOne is masterMyClusterTwo and MyClusterThree are slaves and hdpuser is user in all //
Step 1: Install JAVA & Ecllipse using System->Administratio->add or remove progrm->package
collection select needed packages and download.
(OR)
Download latest java package from "http://www.oracle.com/technetwork/java/javase/downloads/....."
1)cd /opt/jdk1.7.0_40
2)tar -xzf /home/hdpuser/Downloads/jdk-7u40-linux-x64.tar.gz
3)alternatives --install /usr/bin/java java /opt/jdk1.7.0_40/bin/java 2
4)alternatives --config java
Step 2: Configure Environment variables for JAVA
# export JAVA_HOME=/opt/jdk1.7.0_40
# export JRE_HOME=/opt/jdk1.7.0_40/jre
# export PATH=$PATH:/opt/jdk1.7.0_40/bin:/opt/jdk1.7.0_40/jre/bin
Step 3: Steps to Give SUDO permission to User:
1) Go to Terminal and Type "su -" , it will connect to root and enter root password.
2) Type "visudo" , it will show "sudoer" file and enter "i" to edit the file.
3) Add user details after line
## Allow root to run any commands anywhere
Ex : hdpuser ALL=(ALL) ALL
4) Add Password permission details after line
## Same thing without a password
Ex : hdpuser ALL=(ALL) NOPASSWD: ALL
5) Press Esc, and Enter ":x" to save and exits.
Step 4: Create User ( We can also use existing user)
1) Sudo Useradd hdpuser
2) Sudo passwd hdpuser , Enter New password.
Step 5: Edit Host file
1) open /etc/hosts
2) Enter Master and slave node IP addresses in Node ( Master and Slves)
xxx.xx.xx.xxx MyClusterOne
xxx.xx.xx.xxx MyClusterTwo ....
Step 6: Configuring Key Based Login
1)Type "su - hdpuser"
2)Type "ssh-keygen -t rsa"
3)Type "sudo service sshd restart" to restart the service
4)Type "ssh-copy-id -i ~/.ssh/id_rsa.pub hdpuser@MyClusterOne"
5)Type "ssh-copy-id -i ~/.ssh/id_rsa.pub hdpuser@MyClusterTwo"
6)Type "ssh-copy-id -i ~/.ssh/id_rsa.pub hdpuser@MyClusterThree"
( Add details of all slavenodes one by one)
( if any connecting error,then need to start sshd service in all slaves)
7)Type "chmod 0600 ~/.ssh/authorized_keys"
8)Exit
9)ssh-add
Step 7: Download and Extract Hadoop Source
1) cd /opt/hadoop-1.2.1/
3) sudo wget http://apache.mesi.com.ar/hadoop/common/hadoop-1.2.1/hadoop-1.2.1.tar.gz
4) sudo tar -xzvf hadoop-1.2.1.tar.gz
5) sudo chown -R hdpuser /opt/hadoop-1.2.1
6) cd /opt/hadoop-1.2.1/conf
Step 8: Edit configuration files
1) gedit conf/core-site.xml ( Master Node Details)
<configuration>
<property>
<name>fs.default.name</name>
<value>hdfs://MyClusterOne:9000/</value>
</property>
<property>
<name>dfs.permissions</name>
<value>false</value>
</property>
</configuration>
2) gedit conf/hdfs-site.xml ( Master Node Details)
<configuration>
<property>
<name>dfs.data.dir</name>
<value>/opt/hadoop-1.2.1/dfs/name/data</value>
<final>true</final>
</property>
<property>
<name>dfs.name.dir</name>
<value>/opt/hadoop-1.2.1/dfs/name</value>
<final>true</final>
</property>
<property>
<name>dfs.replication</name>
<value>2</value>
</property>
</configuration>
3) gedit conf/mapred-site.xml
<configuration>
<property>
<name>mapred.job.tracker</name>
<value>MyClusterOne:9001</value>
</property>
</configuration>
4) gedit conf/hadoop-env.sh
export JAVA_HOME=/opt/jdk1.7.0_40
export HADOOP_OPTS=-Djava.net.preferIPv4Stack=true
export HADOOP_CONF_DIR=/opt/hadoop-1.2.1/conf
Step 9: Copy Hadoop Source to Slave Servers
1)su - hdpuser
2)cd /opt/hadoop-1.2.1
3)scp -r hdpuser MyClusterTwo:/opt/hadoop-1.2.1
4)scp -r hdpuser MyClusterThree:/opt/hadoop-1.2.1 ....
Step 10 : Configure Hadoop on Master Server Only
1) su - hdpuser
2) cd /opt/hadoop-1.2.1
3) gedit
conf/masters ( Add Master Node Name)
MyClusterOne
4) gedit conf/slaves ( Add Slave Node Names)
MyClusterTwo
MyClusterThree
Step 11 : To Communicate with Slave, Firewall need to be OFF
1)/etc/init.d/sudo service iptables save
2 )/etc/init.d/sudo service iptables stop
3) /etc/init.d/sudo chkconfig iptables off
Step 12: To use All system space or NameNode backup
1) edit Core-site.xml and add any Folder in /Home/
2) we should give permission to that created
sudo chmod 755 /home/folderrname
Step 13: Add hadoop Path details in hadoop.sh
1) gedit /etc/profile.d/hadoop.sh
Step 12: Format Name Node on Hadoop Master only
1) su - hdpuser
2) cd /opt/hadoop-1.2.1
3) bin/hadoop namenode -format
Step 13 : Start Hadoop Services
1) bin/start-all.sh
Comma Separated values
-- Sample Table
create table #Emp(Name varchar(10),Skills varchar(max))
insert into #Emp values('Ramesh','Hadoop,SQL,DBA')
insert into #Emp values('Arjun','SQL,MSBI')
insert into #Emp values('Mohan','Java')
select * from #Emp
Name Skills
---------------------------
Ramesh Hadoop,SQL,DBA
Arjun SQL,MSBI
Mohan Java
-- Code to Display below output
Name Skill
---------------------
Arjun MSBI
Arjun SQL
Mohan Java
Ramesh DBA
Ramesh Hadoop
Ramesh SQL
SELECT DISTINCT Name,LTRIM(RTRIM(i.value('.', 'VARCHAR(MAX)'))) AS SKILL into #sample
FROM
(
SELECT Name,Skills
,CAST('' + REPLACE(Skills, ',', '') + '' AS XML) AS Des
FROM #Emp
) List
CROSS APPLY Des.nodes('//i') x(i)
select * from #sample
-- for the above output, Code to Display below output
Name Skills
---------------------------
Ramesh Hadoop,SQL,DBA
Arjun SQL,MSBI
Mohan Java
SELECT Distinct Name
,STUFF((SELECT ','+SKILL FROM #sample f where f.Name=s.Name FOR XML PATH('')),1,1,'') AS Skills
from #sample s
Thursday, January 31, 2013
Differences....
Differences Between Funtion and Procedure
- Procedure can return zero or n values whereas function can return one value which is mandatory.
- Procedures can have input/output parameters for it whereas functions can have only input parameters.
- Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
- functions can be called from Procedure whereas Procedures cannot be called from function.
- Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a function.
- We can go for transaction management in Procedure whereas we can't go in function.
- Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
- UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored Procedures cannot be.
- UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
- Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
- Char datatype is used to store fixed length of characters. For example , if we declared char(10) it will allocates memory for 10 characters. Once we insert only 6 characters of word then only 6 characters of memory will be used and other 4 characters of memory will be wasted.
- Varchar means variable characters and it is used to store non-unicode characters. It will allocate the memory based on number characters inserted. For example, if we declared varchar(10) it will allocates memory of 0 characters at the time of declaration and Once we insert only 6 characters of word it will allocate memory for only 6 characters.
- Nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database. nvarchar datatype will take twice as much space to store extended set of characters as required by other languages.
- Primary Key creates clustered Index by default , where as Unique Key Create NoN-Clustered Index
- Primary Key doesn't allow NULLs , where as Unique Key allows only one NULL value
DELETE
- Delete is a DML Command
- We can use where clause to filter data, It deletes specified data if where condition exists
- DELETE statement is executed using a row lock, each row in the table is locked for deletion
- DELETE retain the identity
- Delete activates a trigger because the operation are logged individually
- Slower than truncate because, it keeps logs
- Rollback is possible
DROP
- DROP is a DDL Command
- Removes all rows and also the table definition, including indexes, triggers, grants, storage parameters
- No filter criteria allowed, removes all rows and No triggers fired
- DROP ANY privilege on a specific table cannot be granted to another user or role.
- Drop operation cannot be rolled back in any way as it is an auto committed , while statementDelete operation can be rolled back and it is not auto committed.
TRUNCATE
- TRUNCATE is a DDL command
- It Removes all the data
- TRUNCATE TABLE always locks the table and page but not each row
- If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column
- TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions
- Faster in performance wise, because it doesn't keep any logs
- Rollback is possible with TRANSACTION
Tuesday, October 16, 2012
Subscribe to:
Posts (Atom)
