it is fantastic sql server database administrator (dba ... · pdf fileit is fantastic...
TRANSCRIPT
IT Is Fantastic –SQL Server Database Administrator (DBA)
www.ITIsFantastic.com
To download the latest version http://www.itisfantastic.com/ms-sql-server-dba.html
10/19/16
Contents
1. Course Introduction and Overview .......................................................... 3
2. Installation, Upgrade, Patch & Support .................................................... 4
3. Database Creation .................................................................................... 5
4. Table Design and Data ............................................................................. 6
5. Database Objects ..................................................................................... 7
6. Troubleshooting & Performance Detection .............................................. 9
7. Database Maintenance Jobs.................................................................... 11
8. Security .................................................................................................. 12
9. Capacity Planning .................................................................................. 13
10. Scheduler ............................................................................................. 14
11. Performance Tuning ............................................................................ 15
12. SSIS ..................................................................................................... 15
13. SSRS .................................................................................................... 15
14. Advanced Subjects ............................................................................... 15
1. Course Introduction and Overview Course Introduction and Overview Who is and what does a Database Administrator do? DBA - Database Administrator 1 - Database Architecture * configuration - processes, memory, storage, configuration 2 - Capacity planning * Storage, disk allocation, writing and reading speed 3 - Security * Users, Roles, Profiles 4 - Database design * Data Model/Design/ERD, Relational DB, Objects & SQL 5 - Troubleshooting 6 - High Availability * Cluster / backup & restore 7 - Performance * Quick retrieval and manipulation of data 8 - Operating system commands 9 - Database license * Install, upgrade and patch 10- SSIS and SSRS DBA Shortcuts: Goal is work oriented / Internship (Practice) DBA knows/Doesn't memorize Recorded GoToMeetings DBA doesn't memorize - understands and has scripts More than one way to do everything when to use SQL Vs GUI -- multiple databases Resume & Interview skills ** Wrap up installation Jobs – www.Dice.com / LinkedIn / User Groups / Study AdventureWorks2012 http://www.sqlserver-dba.com/2012/10/sql-server-dba-scripts.html http://www.sqlservercentral.com/Scripts/ http://beyondrelational.com/modules/30/scripts/485/sql-server-dba-scripts.aspx?tab=default&bs=207 https://www.simple-talk.com/sql/t-sql-programming/top-10-most-common-database-scripts/ Structure Query Language Database is a repository of objects DBA's favorite language to interact with Database objects is SQL SQL – Structured Query Language DDL – Data Definition Language Create, Alter, Drop DML – Data Manipulation Language Insert, Update, Delete (Truncate/Merge), SELECT Database objects include: User, Tables, Views, Triggers, Stored procedures Tables: Tables are the most important objects Data types - date/varchar/number/clob and blob Microsoft SQL Server Management Studio from top to bottom
2. Installation, Upgrade, Patch & Support Installation, Upgrade, Patch & Support To Download SQL Server 2012 Developer Edition http://www.itisfantastic.com/ms-sql-server-dba.html Microsoft Developer Network (MSDN – Subscription) https://msdn.microsoft.com/en-us/ YouTube Channel https://www.youtube.com/watch?v=69AWNQ5K9g8&feature=youtu.be
3. Database Creation Database creation Create through Graphical User Interface (GUI)
Create through SQL script
Create through restore
Note: Modify AdventureWorks database properties, setting “Files” owner to “sa”
4. Table Design and Data Table Design Note: Data Diagram (grant file to owner)
Table Data
Duplicate Table data -- [AccountID] INSERT INTO [ITIsFantastic].[dbo].[AccountInformation] ([Name], [Address], [Email], [StartDate], [Comments]) SELECT [Name], [Address], [Email], [StartDate], [Comments] FROM [ITIsFantastic].[dbo].[AccountInformation] SQL – Structured Query Language DDL GUI to generate SQL GUI to generate (complex) reports
Operating System Vs T-SQL -- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE GO
EXEC xp_servicecontrol N'Stop', N'RealPlayerUpdateSvc' exec master..xp_cmdshell 'copy C:\DELETE\File1.txt C:\DELETE\File2.txt'
5. Database Objects Views View - using GUI
View - using SQL statement
CREATE VIEW [dbo].[View_Account] AS
SELECT AccountID, Name, Address, Email FROM dbo.AccountInformation
Trigger Trigger
CREATE TRIGGER trgAfterUpdate ON [ITIsFantastic].[dbo].[AccountInformation] FOR UPDATE AS UPDATE [ITIsFantastic].[dbo].[AccountInformation] SET [Comments] = 'New record2'; GO
Stored Procedure Stored Procedure
USE AdventureWorks2012; GO CREATE PROCEDURE HumanResources.uspGetEmployeesTest2 @LastName nvarchar(50), @FirstName nvarchar(50) AS SET NOCOUNT ON; SELECT FirstName, LastName, Department FROM HumanResources.vEmployeeDepartmentHistory WHERE FirstName = @FirstName AND LastName = @LastName AND EndDate IS NULL; GO
USE AdventureWorks2012; GO CREATE PROCEDURE HumanResources.aaaspGetEmployeesTest2 AS SET NOCOUNT ON; SELECT FirstName, LastName, Department FROM HumanResources.vEmployeeDepartmentHistory ; GO
EXEC [HumanResources].[aaaspGetEmployeesTest2]
Stored Procedure Stored Procedure
CREATE TABLE tbl_Students ( [Studentid] [int] IDENTITY(1,1) NOT NULL, [Firstname] [nvarchar](200) NOT NULL, [Lastname] [nvarchar](200) NULL, [Email] [nvarchar](100) NULL )
Insert into tbl_Students (Firstname, lastname, Email) Values('Vivek', 'Johari', '[email protected]') Insert into tbl_Students (Firstname, lastname, Email) Values('Pankaj', 'Kumar', '[email protected]') Insert into tbl_Students (Firstname, lastname, Email) Values('Amit', 'Singh', '[email protected]') Insert into tbl_Students (Firstname, lastname, Email) Values('Manish', 'Kumar', '[email protected]') Insert into tbl_Students (Firstname, lastname, Email) Values('Abhishek', 'Singh', '[email protected]')
* Getstudentname is the name of the stored procedure*/ Create PROCEDURE Getstudentname( @studentid INT --Input parameter , Studentid of the student ) AS BEGIN SELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid END
Create PROCEDURE GetstudentnameInOutputVariable ( @studentid INT, --Input parameter , Studentid of the student @studentname VARCHAR(200) OUT -- Out parameter declared with the help of OUT keyword ) AS BEGIN SELECT @studentname= Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid END
/* Stored Procedure GetstudentnameInOutputVariable is modified to collect the email address of the student with the help of the Alert Keyword*/ Alter PROCEDURE GetstudentnameInOutputVariable ( @studentid INT, --Input parameter , Studentid of the student @studentname VARCHAR (200) OUT, -- Output parameter to collect the student name @StudentEmail VARCHAR (200)OUT -- Output Parameter to collect the student email ) AS BEGIN SELECT @studentname= Firstname+' '+Lastname, @StudentEmail=email FROM tbl_Students WHERE studentid=@studentid
END
Execute Getstudentname 1 Exec Getstudentname 1
Declare @Studentname as nvarchar(200) -- Declaring the variable to collect the Studentname Declare @Studentemail as nvarchar(50) -- Declaring the variable to collect the Studentemail Execute GetstudentnameInOutputVariable 1 , @Studentname output, @Studentemail output select @Studentname,@Studentemail -- "Select" Statement is used to show the output from Procedure
6. Troubleshooting & Performance Detection Troubleshooting & Performance Detection
Meta-data Vs User data (databases/schemas) A. To report all current users. USE master; GO EXEC sp_who; GO B. Listing a specific user's process EXEC sp_who 'ITISFANTASTIC\ITIsFantastical'; C. Displaying all active processes EXEC sp_who 'active'; D. Displaying a specific process identified by a session ID EXEC sp_who '10' --specifies the process_id; EXEC sp_who2 SELECT TOP 5 query_stats.query_hash AS "Query Hash", SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time", MIN(query_stats.statement_text) AS "Statement Text" , CASE dbid WHEN 32767 THEN 'Resource' ELSE DB_NAME(dbid) END AS DBName , OBJECT_NAME(objectid, dbid) FROM (SELECT QS.*, st.dbid, st.objectid, SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats GROUP BY query_stats.query_hash, dbid, objectid ORDER BY 2 DESC;
USE AdventureWorks2012; GO SELECT TOP 5 query_stats.query_hash AS "Query Hash", SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time", MIN(query_stats.statement_text) AS "Statement Text" FROM (SELECT QS.*, SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats GROUP BY query_stats.query_hash ORDER BY 2 DESC; Get query STATS USE AdventureWorks2012; GO SET STATISTICS TIME ON; GO SELECT ProductID, StartDate, EndDate, StandardCost FROM Production.ProductCostHistory WHERE StandardCost < 500.00; GO SET STATISTICS TIME OFF; GO
7. Database Maintenance Jobs Database Maintenance Jobs
8. Security Security
Windows Authentication Vs Database Authentication – when to pick which GUI Vs Script to create user
9. Capacity Planning Capacity Planning You can check autogrow settings here:
SELECT name, type_desc, size, growth, is_percent_growth FROM [ITIsFantastic].sys.database_files;
USE ITIsFantastic GO EXECUTE sp_spaceused GO
To determine the free/used space within each file of a database:
Select *, fileproperty(name, 'SpaceUsed') as Used From dbo.sysfiles
USE [ITIsFantastic] GO SELECT [TYPE] = A.TYPE_DESC ,[FILE_Name] = A.name ,[FILEGROUP_NAME] = fg.name ,[File_Location] = A.PHYSICAL_NAME ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100) ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -' WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted' ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id order by A.TYPE desc, A.NAME;
10. Scheduler Scheduler
11. Performance Tuning Performance Tuning Optimizing SQL
12. SSIS SSIS
13. SSRS SSRS
14. Advanced Subjects Advanced Subjects Advanced Subjects