•  
  • Archives for January 2011 (3)

SQLskills Master Immersion Events 2011 competition

by Muthukkumaran kaliyamoorthy Published on: January 24, 2011
Comments: 3 Comments
Tags: ,
Categories:DBA

SQLskills Master Immersion Events 2011 competition




      I have completed engineering and my native is M.Uthamasolagan (village near chidambaram ). I have started my career as SQL server DBA with my brother’s Rajakrishnan Rajadurai support. To be frank I am not good in English and so I feared to work during the initial time. My senior DBA helped and encouraged me a lot. Now I love to work with SQL server because of SQL community helpers.

I have started a blog three months back and now my interest in writing  blogs  has increased tremendously because,

  1. I got a good local SQL server team.
  2. When I write about a particular topic, my knowledge of SQL is also growing rigorously.
  3. I have started this blog in order to help the fresh DBA’s.

SQL skills are offering special 5-day class in Dallas on February 21 to 25 http://www.sqlskills.com/Master1-Dallas-20110221.asp

Detailed Agenda Master Immersion Event on Internals and Performance

Why you want to come to a class taught by Paul Randal and Kimberly Tripp?

Why you’d make the best use of the knowledge you’ll get from being in the class?

Paul & Kim asked a question simply but, its huge list why the world likes you pair.


  • As everyone knows as a pair you are number one in the world’s “SQL server community”.
  • We have learned lots of things from your websites, books & forums.
  • No one is going to miss this chance to see you guys (class) face to face, because that’s the lifetime achievement for all the DBA’s.
  • Whenever I get junior DBAs and fresher, I always suggest they “SQLskills” to learn everything in order to improve their skills. I also did the same. (Not just said to win the competition)
  • You both have worked more than 15 years in SQL server and have real world experience.
  • Few of the experts are teaching the MCM and SQL server internals in the world that’s, Why we are coming to you (SQLSkills).
  •  I have watched some of your online PASS presentations. It’s really awesome and the way of presentation is excellent.
  • When I first started my carrier with SQL server the first thing came to my mind was the Google search engine and find the best site to learn SQL server.

 

Once again nobody is going to miss your class even if they are masters in SQL server.

 

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

How to find the SQL server port number

by Muthukkumaran kaliyamoorthy Published on: January 19, 2011
Comments: 9 Comments
Tags: ,
Categories:DBA

 

One day I got a call from my junior DBA. She asked me, how can I find out the port number for a particular server? Then our conversation started,

Me:

I asked a question to her what version of SQL server is that.

Junior DBA:

Its SQL server 2005

Me:

I told her to check the configuration manager.

Start –> all programs –> Microsoft SQL server 2005 –> Configuration Tools –> SQL server

Configuration Manager.

 

Junior DBA:

I don’t have direct remote access. (MSTSC)

Me:

After that, I told to check the SQL error log by using T-SQL

 

 

SP_readerrorlog 0,1,'listening','server'

Junior DBA:

I didn’t see ‘’listening’ such a word.

(0 row(s) affected)

 

Me:

Again I told to change the first parameter 0 to 1.

sp_readerrorlog 1,1,'listening','server'

Junior DBA:

Yep, I got it.

Results:

2011-01-19 11:36:23.340        Server Server is listening on [ 'any' <ipv6> 1433].

2011-01-19 11:36:23.340        Server Server is listening on [ 'any' <ipv4> 1433].

Here is another one method,
(For default instance)

 

CREATE PROCEDURE usp_getport_number
AS
BEGIN
DECLARE @findport_number nvarchar(5)
EXEC xp_regread
@rootkey    =    'HKEY_LOCAL_MACHINE',
@key        =    'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name    =    'TcpPort',
@value        =    @findport_number OUTPUT
print 'The server port number = '+@findport_number
END

EXEC usp_getport_number

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

Step by step backup/restore using T-SQL

 

This article mainly for SQL server learners, who is going to become a DBA, in this article I am going to explain the backup and restore using T-SQL.

Why I choose the T-SQL instead of GUI?

Below are reasons,

I have seen a number of articles and blogs shows the GUI.

Anyone can use the GUI to restore the database, if they don’t well in SQL.

Again most of the DBAs hasn’t used the command and they don’t know the T-SQL syntax to restore the database. I recommend using the T-SQL to restore the database because it’s the greatest tool when you are migrating a huge number of databases (Not to right click each database to restore) and time saver.

Backup:

I enjoyed when I read BrentOzar’s backup best practice articles. I have recommended my readers too.

Let’s see the funny story

You have some problem with your mobile phone. You lost all your contact numbers even your girlfriend (wife) number too. Suddenly, she called to you then you asked who are you? …

So, keep doing the backup regularly otherwise ready to update your resume – :)

Before going to restore section keep verify your backups regularly (sometimes your backup might be damaged)

Important: Backup is the free D/R solution, when you lost your database.

 

Step by step restore: Backup has been taken in D drive.

USE MASTER
GO
BACKUP DATABASE DBA_TEST TO DISK='D:\BACKUP\DBA_TEST.BAK' with copy_only

 


Filelistonly returns the logical file names, physical files of data, log locations & file sizes.


USE MASTER
GO
RESTORE FILELISTONLY FROM DISK = 'BACKUP LOCATION'
RESTORE FILELISTONLY FROM DISK ='D:\BACKUP\DBA_TEST.BAK'

 




Create the same copy\content of the database DBA_new from DBA_test. Use XP_fixeddrives to find the drive free space.


EXEC XP_FIXEDDRIVES
SELECT * FROM SYS.MASTER_FILES


“sys.master_files” will show the current database files location. All the companies have certain rules like, data file will go one separate physical LUN (RAID level) and the log file will go another physical LUN (RAID level). It will help to improve the performance.

So, before going to restore checks and ask the same.

Restore database <database name> from disk =’backup location’
With
Move ‘logical name of data file’ to ’data file location’,
Move ‘logical name of log file’ to ’log file location’

RESTORE DATABASE DBA_NEW FROM DISK ='D:\BACKUP\DBA_TEST.BAK'
WITH
MOVE 'DBA' TO 'D:\DATA\DBA.MDF',
MOVE 'DBA_LOG' TO 'E:\LOG\DBA_LOG.LDF'


You can also overwrite the database by using REPLACE option if you don’t need the existing database.
For more : Look the restoration options  WITH options (like overwrite(REPLACE),move etc).
RESTORE DATABASE DBA_TEST FROM DISK ='D:\BACKUP\DBA_TEST.BAK' WITH REPLACE
After complete a restoration its good to change the logical name. But It will not cause anything if you aren’t changing it.
SELECT * FROM SYS.MASTER_FILES
ALTER DATABASE DBA_NEW MODIFY FILE
(NAME = DBA,NEWNAME = DBA_NEW )
ALTER DATABASE DBA_NEW MODIFY FILE
(NAME = DBA_LOG,NEWNAME = DBA_NEW_LOG )


The database restoration has done in different machine then,  transfers the logins and the passwords  by using this Microsoft script.

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

page 1 of 1

Welcome , today is Thursday, April 17, 2014