Thursday, June 17, 2010
Install the AdventureWorks Sample Database In SQL 2008
http://rajibsqldba.files.wordpress.com/2010/06/install-the-adventureworks-sample-database-in-sql-2008.doc
SQL Server 2008 Two node Cluster Installation and Setup step by step
http://rajibsqldba.files.wordpress.com/2010/06/sql-server-2008-two-node-cluster-installation-and-setup.doc
SQL server 2008 single node Installation step by step:
http://rajibsqldba.files.wordpress.com/2010/06/single-node-installation-for-sql-2008.doc
Sunday, June 13, 2010
Check your backup in SQL server
We all know that Database backups are the bread and butter task of the database administration job. We cannot have any database running without backups. Without proper and prompt backup it is not possible to recover the database in the event of data corruption and failure.
Therefore, it is critical to monitor the backups continuously.
In This scenario we can add one more step in our Backup User Databases job which monitoring to find any databases that have not been backed up in certain number of hour.
In our admin database we can create one store procedure uspMonitorBackups. Below I have mentioned the code of the stored procedure uspMonitorBackups, which queries the msdb.dbo.bacupset table for the last backup.
USE [admin]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[uspMonitorBackups]‘) AND type in (N’P', N’PC’))
DROP PROCEDURE [dbo].[uspMonitorBackups]
GO
CREATE PROCEDURE uspMonitorBackups @backuptype char(1), @numOfMinutes int
as
–D = Database
–L = Log
–Example: Exec admin.dbo.uspMonitorBackups ‘D’,24
declare @dbname varchar(2000)
select name as Databasename
from master.dbo.sysdatabases
where name not in (‘master’,'model’,'msdb’,'tempdb’,'LiteSpeedLocal’)
and name not in
(select distinct database_name
from msdb.dbo.backupset
group by database_name
having datediff(minute, max(backup_start_date),getdate()) < @numOfMinutes
)
Job step which need to add in Backup User Databases job
Exec admin.dbo.uspMonitorBackups ‘D’,24
The Stored procedure accepts two parameters. The first parameter is type of database backup and second parameter is number of hours
Therefore, it is critical to monitor the backups continuously.
In This scenario we can add one more step in our Backup User Databases job which monitoring to find any databases that have not been backed up in certain number of hour.
In our admin database we can create one store procedure uspMonitorBackups. Below I have mentioned the code of the stored procedure uspMonitorBackups, which queries the msdb.dbo.bacupset table for the last backup.
USE [admin]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[uspMonitorBackups]‘) AND type in (N’P', N’PC’))
DROP PROCEDURE [dbo].[uspMonitorBackups]
GO
CREATE PROCEDURE uspMonitorBackups @backuptype char(1), @numOfMinutes int
as
–D = Database
–L = Log
–Example: Exec admin.dbo.uspMonitorBackups ‘D’,24
declare @dbname varchar(2000)
select name as Databasename
from master.dbo.sysdatabases
where name not in (‘master’,'model’,'msdb’,'tempdb’,'LiteSpeedLocal’)
and name not in
(select distinct database_name
from msdb.dbo.backupset
group by database_name
having datediff(minute, max(backup_start_date),getdate()) < @numOfMinutes
)
Job step which need to add in Backup User Databases job
Exec admin.dbo.uspMonitorBackups ‘D’,24
The Stored procedure accepts two parameters. The first parameter is type of database backup and second parameter is number of hours
How to be a Good DBA – Strategy and Tactics
Introduction
Having been a DBA for about 9 years, I am frequently asked by some programmers, “I am tired of coding, now I want to switch my career to be a DBA, so where should I start?” I promised that I would write an article to answer these questions based on my experience, so this article is mainly based on my experience as a SQL Server and DB2 DBA, and I welcome other DBAs to share their experiences on this topic.
Strategies for Junior DBAs
Focusing on the basic DBA tasks: first thing first.
The quickest way to be a qualified junior DBA is to understand the scope of daily DBA tasks and grasp the necessary skills to do these tasks. I figure that a junior DBA’s work accounts for at least 40% of daily DBA tasks. The following is a list of tasks essential to every DBA.
•SQL Server installation. Knowledge needed: SQL Server components (EM, Profiler, Query Analyzer, other tools etc), edition of SQL Server and its environment requirements (hardware, OS, network protocols etc), remote / automatic / manual installation
•Backup/Restore. Knowledge needed: recovery models, backup / restore tools and SQL statements for backup / restore, and how to adapt your recovery plan to your business needs.
•Security: roles/login/user accounts. Knowledge needed: authentication modes, types of roles, how to manage security accounts, grant /revoke permission to access data and how to make a security plan matching your business needs.
•Database Maintenance. Knowledge needed: some DBCC commands (dbcc checkdb / dbreindex / checkcatalog / inputbuffer are among my favorites), sp_who, sp_kill and all the SQL task wizards.
Focusing on programming with SQL: foundation for future growth
T-SQL is a powerful tool that enables DBAs to personate their database management styles by scripting their daily tasks such as auditing, backups / restores, performance tuning etc. Most of application performance issues in real world are related to bad SQL statements. I cannot over emphasize the importance of writing good SQL scripts.
The following are the essential SQL statements to me:
a) CRUD (Create, Read, Update, Delete) statements about database, table, view, UDF, triggers, stored procedures.
b) T-SQL programming control statements, such as if…else…, begin…end, while…break…continue and case statement and use of cursor.
c) SQL Server internal functions, such as @@identity, @@spid, substring(), covert/cast(), etc.
Strategies for intermediate DBAs
•Focusing on data modeling: beyond data and into business. Knowledge needed: knowledge of business for which the database is going to be built; knowledge of logical / physical model (3rd norm); expertise in the modeling tools, like ERWin, Visio etc. A good data model will not only improve an application performances but also reduce the database maintenance. For example, I once reviewed a data model of a government agency, which stores all employees’ salary information in several tables, the original idea was to facilitate the quick summery reports needed by different departments but later is found too costly to synchronize all the information in terms of time and speed. (Some tables have triggers, which increase delays during synchronizations.)
•Focusing on performance tuning: fun and challenges. Knowledge/Skills: table/index architecture, Index choice/building; server / database /session options; execution plan; use of profiler to optimize and audit SQL statements, dbcc statements. Performance tuning is where a DBA can shine. Challenges always hide somewhere for a DBA to tackle so that a query can run within 2 seconds instead of two minutes. Believe me, it is fun to do performance tuning!
•Focusing on advanced management: key to being an expert. Knowledge / Skills: VBScript, DTS, SQL-DMO, replication. I always believe that a DBA who does not know VBScript cannot be creative in managing SQL Server system. Because Microsoft has made VBScript so powerful that it can be used to facilitate the administration of most Window-based applications, SQL Server is no exception. When combining VBScript with DTS and SQL-DMO, I bet almost all administration work can be scripted and automatically run under a schedule.
Strategies for senior DBAs
•Focusing on soft skills: key to career. As a senior DBA, you spend most time in doing requirements analysis with clients and providing guidance to other team members, here soft skills, such as time management, communication, negotiation and leadership etc, play more important roles than the technical skills. Without soft skills, you can hardly be a valuable DBA. I learnt the lesson the hard way. I still remembered my story happened a long time ago. I was assigned as a database architect for a project, and had to discuss with clients in different departments regarding their requirements. From time to time, I would release a new version of data model. However, during this process, there were a few columns that I made constant changes, by either adding them to different tables or simply getting removed or renamed. The problem was I forgot to document why I made the change based on my talk with whom. When I was challenged with the differences between version 1.0.04 (four weeks ago) and version 1.0.06 on a specific column, I could not tell why the column appeared in 10.04 but disappeared in 1.0.05 and then reappeared in 1.0.06. I was accused of being incapable of my role even though my project manager assured of my technical capability. Later, I was removed from my role because the project manager had to satisfy the client’s concern. How stupid I was! From then on, I formed the habit to record each reason behind every change I made to my base line project document.
•Focusing on innovations: the last step to glory. Innovation is to use the current technology and resources to create new processes / methods that can make better solutions in terms of time or economic efficiency. As a senior DBA, one needs to have a broader view and interests in contributing to your employer’s business bottom line. In the e-business era, more business are relying on data for decision and operation, and this gives all DBAs, esp. the senior DBAs more opportunities to creatively manipulate the data at each stage of the data value chain, from data collecting, processing, mining, to reporting, distributing and storing.
Conclusion
To be a qualified DBA, you have to master the right skills at the right time. You do not have to know everything to start your career as a DBA, but you have to know where to start. There is no distinct line between a junior DBA and an intermediate DBA or an intermediate DBA and a senior DBA. But my personal feeling is that when your rank goes higher, you need more than technical skills to demonstrate your values. My final word to those who are interested in DBA career:
“Do not think of database management as a routine job, but think of it as an art work that you can demonstrate your imagination and creativity.”
Having been a DBA for about 9 years, I am frequently asked by some programmers, “I am tired of coding, now I want to switch my career to be a DBA, so where should I start?” I promised that I would write an article to answer these questions based on my experience, so this article is mainly based on my experience as a SQL Server and DB2 DBA, and I welcome other DBAs to share their experiences on this topic.
Strategies for Junior DBAs
Focusing on the basic DBA tasks: first thing first.
The quickest way to be a qualified junior DBA is to understand the scope of daily DBA tasks and grasp the necessary skills to do these tasks. I figure that a junior DBA’s work accounts for at least 40% of daily DBA tasks. The following is a list of tasks essential to every DBA.
•SQL Server installation. Knowledge needed: SQL Server components (EM, Profiler, Query Analyzer, other tools etc), edition of SQL Server and its environment requirements (hardware, OS, network protocols etc), remote / automatic / manual installation
•Backup/Restore. Knowledge needed: recovery models, backup / restore tools and SQL statements for backup / restore, and how to adapt your recovery plan to your business needs.
•Security: roles/login/user accounts. Knowledge needed: authentication modes, types of roles, how to manage security accounts, grant /revoke permission to access data and how to make a security plan matching your business needs.
•Database Maintenance. Knowledge needed: some DBCC commands (dbcc checkdb / dbreindex / checkcatalog / inputbuffer are among my favorites), sp_who, sp_kill and all the SQL task wizards.
Focusing on programming with SQL: foundation for future growth
T-SQL is a powerful tool that enables DBAs to personate their database management styles by scripting their daily tasks such as auditing, backups / restores, performance tuning etc. Most of application performance issues in real world are related to bad SQL statements. I cannot over emphasize the importance of writing good SQL scripts.
The following are the essential SQL statements to me:
a) CRUD (Create, Read, Update, Delete) statements about database, table, view, UDF, triggers, stored procedures.
b) T-SQL programming control statements, such as if…else…, begin…end, while…break…continue and case statement and use of cursor.
c) SQL Server internal functions, such as @@identity, @@spid, substring(), covert/cast(), etc.
Strategies for intermediate DBAs
•Focusing on data modeling: beyond data and into business. Knowledge needed: knowledge of business for which the database is going to be built; knowledge of logical / physical model (3rd norm); expertise in the modeling tools, like ERWin, Visio etc. A good data model will not only improve an application performances but also reduce the database maintenance. For example, I once reviewed a data model of a government agency, which stores all employees’ salary information in several tables, the original idea was to facilitate the quick summery reports needed by different departments but later is found too costly to synchronize all the information in terms of time and speed. (Some tables have triggers, which increase delays during synchronizations.)
•Focusing on performance tuning: fun and challenges. Knowledge/Skills: table/index architecture, Index choice/building; server / database /session options; execution plan; use of profiler to optimize and audit SQL statements, dbcc statements. Performance tuning is where a DBA can shine. Challenges always hide somewhere for a DBA to tackle so that a query can run within 2 seconds instead of two minutes. Believe me, it is fun to do performance tuning!
•Focusing on advanced management: key to being an expert. Knowledge / Skills: VBScript, DTS, SQL-DMO, replication. I always believe that a DBA who does not know VBScript cannot be creative in managing SQL Server system. Because Microsoft has made VBScript so powerful that it can be used to facilitate the administration of most Window-based applications, SQL Server is no exception. When combining VBScript with DTS and SQL-DMO, I bet almost all administration work can be scripted and automatically run under a schedule.
Strategies for senior DBAs
•Focusing on soft skills: key to career. As a senior DBA, you spend most time in doing requirements analysis with clients and providing guidance to other team members, here soft skills, such as time management, communication, negotiation and leadership etc, play more important roles than the technical skills. Without soft skills, you can hardly be a valuable DBA. I learnt the lesson the hard way. I still remembered my story happened a long time ago. I was assigned as a database architect for a project, and had to discuss with clients in different departments regarding their requirements. From time to time, I would release a new version of data model. However, during this process, there were a few columns that I made constant changes, by either adding them to different tables or simply getting removed or renamed. The problem was I forgot to document why I made the change based on my talk with whom. When I was challenged with the differences between version 1.0.04 (four weeks ago) and version 1.0.06 on a specific column, I could not tell why the column appeared in 10.04 but disappeared in 1.0.05 and then reappeared in 1.0.06. I was accused of being incapable of my role even though my project manager assured of my technical capability. Later, I was removed from my role because the project manager had to satisfy the client’s concern. How stupid I was! From then on, I formed the habit to record each reason behind every change I made to my base line project document.
•Focusing on innovations: the last step to glory. Innovation is to use the current technology and resources to create new processes / methods that can make better solutions in terms of time or economic efficiency. As a senior DBA, one needs to have a broader view and interests in contributing to your employer’s business bottom line. In the e-business era, more business are relying on data for decision and operation, and this gives all DBAs, esp. the senior DBAs more opportunities to creatively manipulate the data at each stage of the data value chain, from data collecting, processing, mining, to reporting, distributing and storing.
Conclusion
To be a qualified DBA, you have to master the right skills at the right time. You do not have to know everything to start your career as a DBA, but you have to know where to start. There is no distinct line between a junior DBA and an intermediate DBA or an intermediate DBA and a senior DBA. But my personal feeling is that when your rank goes higher, you need more than technical skills to demonstrate your values. My final word to those who are interested in DBA career:
“Do not think of database management as a routine job, but think of it as an art work that you can demonstrate your imagination and creativity.”
Migrating Logins from One SQL Server to another server
As part of the normal work, a Database Administrator (DBA) will be required to migrate databases between servers. One of the reasons for a migration might be that we are moving an application from a quality assurance (QA) environment to a production environment.
There are a number of different ways to migrate SQL Server logins. We can manually re-enter the entire existing login on the new server. We can use DTS to transfer logins. There are probably a number of other ways to transfer logins. This article will discuss one of those other ways to streamline the migration of SQL Server logins, SQL Server 2005 Data Transformation Services (DTS) includes a Transfer Logins task, but this task doesn't cover all situations. The Transfer Logins task requires a network connection between SQL Server machines, which—for security reasons—isn't always possible. Also, the task can transfer only from SQL Server 7.0 or 2000 to SQL Server 2000; it can't transfer to pre-SQL Server 2000 releases.
Transfer logins between her SQL Server machines.
The following code shows how to script the sp_addlogin calls for the SQL Server logins:
SET NOCOUNT ON
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
,', @deflanguage = ''' + language + ''''
,', @encryptopt = ''skip_encryption'''
,', @passwd ='
, cast(password AS varbinary(256))
,', @sid ='
, sid
FROM syslogins
WHERE name NOT IN ('sa')
AND isntname = 0
Each row in the column list is a column in the result. The script uses the isntname column to ascertain whether a login is a SQL Server login or an NT login. Executing the above code in a Query Analyzer grid shows that the binary data (and other parameters) are in separate columns. Also, because the password column is in Unicode (and encrypted), the code converts the password column into VARBINARY (256), so that you don't lose characters.
The following code could use the following code to script the NT logins:
SELECT 'EXEC sp_grantlogin @loginame = ''' + loginname + ''''
,' EXEC sp_defaultdb @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
FROM syslogins
WHERE loginname NOT IN ('BUILTIN\Administrators')
AND isntname = 1
Save the output as a file and execute that file in the destination server.
You could get an error message if a login already exists. If you didn't want to get the error messages, you could script an IF NOT EXISTS and a check against the login name column in the destination server for each call to sp_addlogin and sp_grantlogin.
There are a number of different ways to migrate SQL Server logins. We can manually re-enter the entire existing login on the new server. We can use DTS to transfer logins. There are probably a number of other ways to transfer logins. This article will discuss one of those other ways to streamline the migration of SQL Server logins, SQL Server 2005 Data Transformation Services (DTS) includes a Transfer Logins task, but this task doesn't cover all situations. The Transfer Logins task requires a network connection between SQL Server machines, which—for security reasons—isn't always possible. Also, the task can transfer only from SQL Server 7.0 or 2000 to SQL Server 2000; it can't transfer to pre-SQL Server 2000 releases.
Transfer logins between her SQL Server machines.
The following code shows how to script the sp_addlogin calls for the SQL Server logins:
SET NOCOUNT ON
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
,', @deflanguage = ''' + language + ''''
,', @encryptopt = ''skip_encryption'''
,', @passwd ='
, cast(password AS varbinary(256))
,', @sid ='
, sid
FROM syslogins
WHERE name NOT IN ('sa')
AND isntname = 0
Each row in the column list is a column in the result. The script uses the isntname column to ascertain whether a login is a SQL Server login or an NT login. Executing the above code in a Query Analyzer grid shows that the binary data (and other parameters) are in separate columns. Also, because the password column is in Unicode (and encrypted), the code converts the password column into VARBINARY (256), so that you don't lose characters.
The following code could use the following code to script the NT logins:
SELECT 'EXEC sp_grantlogin @loginame = ''' + loginname + ''''
,' EXEC sp_defaultdb @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
FROM syslogins
WHERE loginname NOT IN ('BUILTIN\Administrators')
AND isntname = 1
Save the output as a file and execute that file in the destination server.
You could get an error message if a login already exists. If you didn't want to get the error messages, you could script an IF NOT EXISTS and a check against the login name column in the destination server for each call to sp_addlogin and sp_grantlogin.
Subscribe to:
Posts (Atom)