70-465 Designing Database Solutions for Microsoft SQL Server

I am in the process of putting my notes into web pages, please keep checking in later for more contents
mssql certification 70-465     70-465     new spec   20465     mssqltips  notes  aiotest     passleader   ensurepass 70-461     70-462     70-463     70-464    

microsoft sql server 2014 unleashed pdf   

sql profiler and sql replay 

microsoft sql server 2012 unleashed pdf  

70-468 70-986 Data Platform Knowledge exam
70-469 70-987 Data Platform Lab exam


70-457 Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 --- Part 1
70-458 Transition your MCTS on SQL Server 2008 to MCSA: SQL Server 2012 --- Part 2
70-459 Transition your MCITP on SQL Server 2008 to MCSE: Data Platform

Design a database structure
Design databases and database objects
Design database security
Design a troubleshooting and optimization solution

Managing a SQL Server Environment
Designing Database Security
Designing a Backup & Recovery Solution
Designing a High-Availability Solution
Troubleshooting & Maintaining a database

Quick Search


search term:      
   




top   prev   next  

Design a database structure (25-30%)


Design for business requirements
   Translate business needs to data structures; 
   de-normalize a database by using SQL Server features, 
     including materialization using indexed views, 
               distributed partitioned views, 
               filtered and non-key column indexes, 
               and snapshots

Design physical database and object placement
   Design a physical database, including file placement, FILESTREAM, FILETABLE, file groups, and RAID; 
   configure system database settings

Design a table and index partitioning strategy
    Develop the optimal strategy 
    for indexing, 
        archive using partitions and tables, 
        design columnstore indexes, 
        design XML indexes

Design a migration, consolidation, and upgrade strategy
    Upgrade with minimal downtime; 
    design a cross-cluster migration; 
    plan a database deployment, including Windows PowerShell, Server Core, and contained databases; 
    migrate query plans; 
    design a migration strategy using Distributed Replay Controller; 
    design a SQL Server virtualization strategy 
    migrate to SQL Database

Design SQL Server instances
    Identify hardware for new instances; 
    design CPU affinity; 
    design clustered instances using Microsoft Distributed Transaction Control (MSDTC); 
    define instance memory allocation; 
    design installation strategies, including sysprep, slipstream, and SMB file server; 
    define cross db ownership chaining

Design backup and recovery
    Design a backup strategy based on business needs, including differential, file, log, and striped; 
    design a database snapshot strategy; 
    design appropriate recovery models; 
    design a system database backup strategy; 
    recover Tail-Log backups
    Microsoft Azure Blob Storage Service

Create indexed views
Move user databases
Partitioned tables and indexes


top   prev   next  

Design databases and database objects (30-35%)


Design a database model
  Design a logical schema; 
  design a data access and data layer architecture; 
  design a database schema; 
  design a security architecture; 
  design a cross-server instance database model, 
    including linked servers, 
              security, 
              providers, 
              distributed transactions, 
              distributed partitioned views, 
              and Service Broker

Design tables
  Design tables appropriately, 
    including physical tables, 
              temp tables, 
              temp table variables, 
              common table expressions, 
              columnstore indexes, 
              user defined table types, 
              FILESTREAM, 
              FILETABLE, 
              and In-Memory OLTP; 
  design views and table valued functions; 
  design a compression strategy, including row and page; 
  select an appropriate data type; 
  design computed columns

Design for concurrency
  Develop a strategy to maximize concurrency; 
  define a locking and concurrency strategy; 
  design a transaction isolation strategy, including server database and session; 
  design triggers for concurrency

Design T-SQL stored procedures
  Create stored procedures; 
  design a data access strategy using stored procedures; 
  design appropriate stored procedure parameters, including input, output, and Table Valued; 
  design error handling; 
  design an In-Memory OLTP strategy for stored procedures

Design a management automation strategy
  Create a data archiving solution; 
  design automation and auditing, 
    including jobs, 
              alerts, 
              operators, 
              SSIS, 
              CDC, 
              auditing, 
              DDL triggers, 
          and Windows PowerShell; 
   automate across multiple databases and instances; 
   design data batch processing: design a database load test; 
   deploy to different environments, including development, staging, and production

Design for transactions
  Manage transactions, including time, savepoint, and mark; 
  design for implicit and explicit transactions; 
  ensure data integrity by using transactions; 
  design error handling for transactions, including TRY, CATCH, and THROW

ALTER DATABASE SET options (Transact-SQL)
In-Memory OLTP (In-Memory Optimization)


top   prev   next  

Design database security (10-15%)


Design an application strategy to support security
  Design security, 
    including security roles, 
              signed stored procedures, 
              encryption, 
              contained logins, 
              EXECUTE AS, 
          and credentials; 

implement schemas and schema security; 

design security maintenance, 
    including SQL logins, 
              integrated authentication, 
              permissions, 
          and mirroring

Design database, schema, and object security permissions
  Design a database schema 
   that meets security requirements, 
              schema ownership, 
              ownership chaining, 
              cross database chaining

Design instance-level security configurations
  Implement separation of duties using different login roles; 
  choose an authentication type, 
       including logon triggers, 
                 regulatory requirements, 
             and certificates; 

  implement data encryption, 
       including database master key 
             and configuration; 

  implement Data Description Language (DDL) triggers; 

  define a secure service account 

Tutorial: Signing stored procedures with a certificate 
cross db ownership chaining server configuration option 
DDL triggers 


top   prev   next  

Design a troubleshooting and optimization solution(20-30%)


Design a maintenance strategy for database servers
  Design maintenance plans; 
  design index maintenance, including rebuild, defragmentation, statistics, online rebuilds, offline rebuilds, and thresholds; 
  maintain physical and logical consistency (DBCC); 
  manage database files, including LDF, MDF, In-Memory OLTP, and garbage collection; 
  define a retention policy

Troubleshoot and resolve concurrency issues
  Examine deadlocking issues using SQL Server logs and trace flags; 
  design a reporting database infrastructure, including replicated databases; 
  monitor concurrency, including Dynamic Management Views (DMV); 
  diagnose blocking, including live locking and deadlocking; 
  diagnose waits; 
  use Extended Events; 
  implement query hints to increase concurrency

Design and implement a high availability solution
  Configure failover clustering, including multi-subnet; 
  design readable mirrors; 
  create a highly available configuration with low recovery time; 
  design and ensure uptime requirements, including monitoring and patching; 
  design and implement a replication architecture; 
  implement AlwaysOn Availability Groups and AlwaysOn failover clusters
  design and implement a database mirroring architecture
  design geographical fault-tolerance using Microsoft Azure SQL Database

Design a solution to monitor performance and concurrency
  Identify performance monitor counters; 
  monitor for performance and bottlenecks, including Wait Stats; 
  design a query monitoring and review strategy; 
  monitor for missing statistics

Design a monitoring solution at the instance level
  Design auditing strategies, 
    including Extended Events, 
              Event traces, 
              SQL Audit, 
              Profiler-scheduled or event-based maintenance, 
              Performance Monitor, 
          and DMV usage; 
  set up file and table growth monitoring; 
  collect performance indicators and counters; 
  create jobs to monitor server health; 
  audit using Windows Logs

sys.dm_tran_locks(Transact-SQL)
Overview of AlwaysOn Availability Groups (SQL Server)
Monitoring and troubleshooting merge for data and delta file pairs
175Q: http://www.braindump2go.com/70-465.html



top   prev   next  

Azure Blob storage

backup and restore to azure 

For SQL Server the Azure Blob Storage service
offers a better alternative to the often used tape option to archive backups. Tape storage might require physical transportation to an
off-site facility and measures to protect the media. Storing your backups in Azure Blob Storage provides an instant, highly available,
and a durable archiving option




top   prev   next  

VIEW SERVER STATE & VIEW DATABASE STATE

monitor the health of server instance:
VIEW SERVER STATE 

To query a dynamic management view or
function requires SELECT permission on object 
and VIEW SERVER STATE or VIEW DATABASE STATE permission.




top   prev   next  

clustered columnstore indexes

clustered columnstore indexes 
SQL Server 2014 has the features of SQL Server 2012 
plus updateable clustered columnstore indexes.




top   prev   next  

transaction isolation levels

snapshot
repeatable read
read committed
serializable




top   prev   next  

data type

data type 




top   prev   next  

Tutorial: Signing Stored Procedures with a Certificate

signing a stored procedure 

ownership chains and context switching 

revert 




top   prev   next  

cross database ownership chaining

cross database ownership chaining 

-- server level:
SELECT [name], value 
FROM [sys].configurations
WHERE [name] = 'cross db ownership chaining';

EXECUTE sp_configure 'show advanced', 1;  
RECONFIGURE;  
EXECUTE sp_configure 'cross db ownership chaining', 1;  
RECONFIGURE;  

-- database level
SELECT [name] AS [Database], [is_db_chaining_on]
FROM [sys].databases
ORDER BY [name]; 

ALTER DATABASE Database1 SET DB_CHAINING ON;  
ALTER DATABASE Database2 SET DB_CHAINING ON;  

EXEC sp_dboption 'Original_Database', 'db_chaining', 'true';
GO

EXEC sp_dboption 'Chained_Database', 'db_chaining', 'true';
GO

SELECT  
    so.[name] AS [Object] 
  , sch.[name] AS [Schema] 
  , USER_NAME(COALESCE(so.[principal_id], sch.[principal_id])) AS [OwnerUserName]
  , sp.NAME AS [OwnerLoginName]
  , so.type_desc AS [ObjectType] 
FROM sys.objects so 
  JOIN sys.schemas sch 
    ON so.[schema_id] = sch.[schema_id] 
  JOIN [sys].database_principals dp
    ON dp.[principal_id] = COALESCE(so.[principal_id], sch.[principal_id])
  LEFT JOIN [master].[sys].[server_principals] sp
    ON dp.sid = sp.sid
WHERE so.[type] IN ('U', 'P');

Ownership chaining in SQL Server security feature or security risk
Ownership chaining 




top   prev   next  

Application Role

CREATE APPLICATION ROLE weekly_receipts   
    WITH PASSWORD = '987G^bv876sPY)Y5m23'   
    , DEFAULT_SCHEMA = Sales;  
GO  

EXEC sp_setapprole 'weekly_receipts', '987G^bv876sPY)Y5m23';  
GO 

-- ---------------------------------------
DECLARE @cookie varbinary(8000);  
EXEC sp_setapprole 'Sales11', 'fdsd896#gfdbfdkjgh700mM'  
    , @fCreateCookie = true, @cookie = @cookie OUTPUT;  
-- The application role is now active.  
SELECT USER_NAME();  
-- This will return the name of the application role, Sales11.  
EXEC sp_unsetapprole @cookie;  
-- The application role is no longer active.  
-- The original context has now been restored.  
GO  
SELECT USER_NAME();  
-- This will return the name of the original user.   
GO





top   prev   next  

reversible encryption for credit card numbers

reversible encryption for credit card numbers  




top   prev   next  

CREDENTIAL And Sql Server Agent Proxy


agent proxy 

CREATE CREDENTIAL 

-- at instance level
select * from sys.credentials;

-- at database level
select * from sys.database_scoped_credentials;

CREATE CREDENTIAL AlterEgo WITH IDENTITY = N'MyDomain1\MyDomaunUser1',   
    SECRET = '';  
GO  

CREATE CREDENTIAL AlterEgo WITH IDENTITY = 'Mary5',   
    SECRET = '';  
GO  

-- ------------------------------------------------

CREATE CREDENTIAL CredentialForEKM  
    WITH IDENTITY='User1OnEKM', SECRET=''  
    FOR CRYPTOGRAPHIC PROVIDER MyEKMProvider;  
GO  

/* Modify the login to assign the cryptographic provider credential */  
ALTER LOGIN Login1  ADD CREDENTIAL CredentialForEKM;  

/* Modify the login to assign a non cryptographic provider credential */   
ALTER LOGIN Login1  WITH CREDENTIAL = AlterEgo;  
GO  
-- -----------------------------------------------

-- create a proxy

-- creates credential CatalogApplicationCredential  
USE msdb ;  
GO  
CREATE CREDENTIAL CatalogApplicationCredential WITH IDENTITY = 'REDMOND/TestUser',   
    SECRET = 'G3$1o)lkJ8HNd!';  
GO  
-- creates proxy "Catalog application proxy" and assigns
-- the credential 'CatalogApplicationCredential' to it.  
EXEC dbo.sp_add_proxy  
    @proxy_name = 'Catalog application proxy',  
    @enabled = 1,  
    @description = 'Maintenance tasks on catalog application.',  
    @credential_name = 'CatalogApplicationCredential' ;  
GO  
-- grants the proxy "Catalog application proxy" access to 
-- the ActiveX Scripting subsystem.  
EXEC dbo.sp_grant_proxy_to_subsystem  
    @proxy_name = N'Catalog application proxy',  
    @subsystem_id = 2 ;  
GO  




top   prev   next  

Configuring Alerts

Severity 16-25, 825 Error 

EXEC msdb.dbo.sp_add_alert @name=N'Severity 16 Error', 
		@message_id=0, 
		@severity=16, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 17 Error', 
		@message_id=0, 
		@severity=17, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 18 Error', 
		@message_id=0, 
		@severity=18, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 19 Error', 
		@message_id=0, 
		@severity=19, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 20 Error', 
		@message_id=0, 
		@severity=20, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 21 Error', 
		@message_id=0, 
		@severity=21, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
EXEC msdb.dbo.sp_add_alert @name=N'Severity 22 Error', 
		@message_id=0, 
		@severity=22, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
 
 
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 23 Error', 
		@message_id=0, 
		@severity=23, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 24 Error', 
		@message_id=0, 
		@severity=24, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 25 Error', 
		@message_id=0, 
		@severity=25, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
 
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 825', 
		@message_id=825, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

-- --------------------------------------------------------------------------------------

COnfigure Alerts 

severity: 023 - Fatal Error: Database Integrity Suspect

-- Add important SQL Agent Alerts to your instance
-- Change the @OperatorName as needed

USE [msdb];
GO

-- Make sure you have an Agent Operator defined
-- Change @OperatorName as needed
DECLARE @OperatorName sysname = N'SQLDBAGroup';

-- Change @CategoryName as needed
DECLARE @CategoryName sysname = N'SQL Server Agent Alerts';

-- Add Alert Category if it does not exist
IF NOT EXISTS (SELECT *
               FROM msdb.dbo.syscategories
               WHERE category_class = 2  -- ALERT
               AND category_type = 3
               AND name = @CategoryName)
    BEGIN
        EXEC dbo.sp_add_category @class = N'ALERT', @type = N'NONE', @name = @CategoryName;
    END

-- Get the server name
DECLARE @ServerName sysname = (SELECT @@SERVERNAME);

-- Alert Names start with the name of the server 
DECLARE @Sev19AlertName sysname = @ServerName + N' Alert - Sev 19 Error: Fatal Error in Resource';
DECLARE @Sev20AlertName sysname = @ServerName + N' Alert - Sev 20 Error: Fatal Error in Current Process';
DECLARE @Sev21AlertName sysname = @ServerName + N' Alert - Sev 21 Error: Fatal Error in Database Process';
DECLARE @Sev22AlertName sysname = @ServerName + N' Alert - Sev 22 Error Fatal Error: Table Integrity Suspect';
DECLARE @Sev23AlertName sysname = @ServerName + N' Alert - Sev 23 Error: Fatal Error Database Integrity Suspect';
DECLARE @Sev24AlertName sysname = @ServerName + N' Alert - Sev 24 Error: Fatal Hardware Error';
DECLARE @Sev25AlertName sysname = @ServerName + N' Alert - Sev 25 Error: Fatal Error';
DECLARE @Error825AlertName sysname = @ServerName + N' Alert - Error 825: Read-Retry Required';

-- Sev 19 Error: Fatal Error in Resource
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev19AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev19AlertName, 
                  @message_id=0, @severity=19, @enabled=1, 
                  @delay_between_responses=900, @include_event_description_in=1,
                  @category_name = @CategoryName, 
                  @job_id=N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
          FROM dbo.sysalerts AS sa
          INNER JOIN dbo.sysnotifications AS sn
          ON sa.id = sn.alert_id
          WHERE sa.name = @Sev19AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev19AlertName, @operator_name=@OperatorName, @notification_method = 1;
    END

-- Sev 20 Error: Fatal Error in Current Process
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev20AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev20AlertName, 
                  @message_id=0, @severity=20, @enabled=1, 
                  @delay_between_responses=900, @include_event_description_in=1,
                  @category_name = @CategoryName, 
                  @job_id=N'00000000-0000-0000-0000-000000000000'

-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
          FROM dbo.sysalerts AS sa
          INNER JOIN dbo.sysnotifications AS sn
          ON sa.id = sn.alert_id
          WHERE sa.name = @Sev20AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev20AlertName, @operator_name=@OperatorName, @notification_method = 1;
    END

-- Sev 21 Error: Fatal Error in Database Process
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev21AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev21AlertName, 
                  @message_id=0, @severity=21, @enabled=1, 
                  @delay_between_responses=900, @include_event_description_in=1,
                  @category_name = @CategoryName, 
                  @job_id=N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
          FROM dbo.sysalerts AS sa
          INNER JOIN dbo.sysnotifications AS sn
          ON sa.id = sn.alert_id
          WHERE sa.name = @Sev21AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev21AlertName, @operator_name=@OperatorName, @notification_method = 1;
    END

-- Sev 22 Error: Fatal Error Table Integrity Suspect
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev22AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev22AlertName, 
                  @message_id=0, @severity=22, @enabled=1, 
                  @delay_between_responses=900, @include_event_description_in=1,
                  @category_name = @CategoryName, 
                  @job_id=N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
          FROM dbo.sysalerts AS sa
          INNER JOIN dbo.sysnotifications AS sn
          ON sa.id = sn.alert_id
          WHERE sa.name = @Sev22AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev22AlertName, @operator_name=@OperatorName, @notification_method = 1;
    END

-- Sev 23 Error: Fatal Error Database Integrity Suspect
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev23AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev23AlertName, 
                  @message_id=0, @severity=23, @enabled=1, 
                  @delay_between_responses=900, @include_event_description_in=1,
                  @category_name = @CategoryName, 
                  @job_id=N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
          FROM dbo.sysalerts AS sa
          INNER JOIN dbo.sysnotifications AS sn
          ON sa.id = sn.alert_id
          WHERE sa.name = @Sev23AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev23AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END

-- Sev 24 Error: Fatal Hardware Error
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev24AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev24AlertName, 
                  @message_id=0, @severity=24, @enabled=1, 
                  @delay_between_responses=900, @include_event_description_in=1,
                  @category_name = @CategoryName, 
                  @job_id=N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
          FROM dbo.sysalerts AS sa
          INNER JOIN dbo.sysnotifications AS sn
          ON sa.id = sn.alert_id
          WHERE sa.name = @Sev24AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev24AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END

-- Sev 25 Error: Fatal Error
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Sev25AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Sev25AlertName, 
                  @message_id=0, @severity=25, @enabled=1, 
                  @delay_between_responses=900, @include_event_description_in=1, 
                  @category_name = @CategoryName,
                  @job_id=N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
          FROM dbo.sysalerts AS sa
          INNER JOIN dbo.sysnotifications AS sn
          ON sa.id = sn.alert_id
          WHERE sa.name = @Sev25AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Sev25AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END

-- Error 825: Read-Retry Required
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @Error825AlertName)
    EXEC msdb.dbo.sp_add_alert @name = @Error825AlertName, 
                  @message_id=825, @severity=0, @enabled=1, 
                  @delay_between_responses=900, @include_event_description_in=1, 
                  @category_name = @CategoryName, 
                  @job_id=N'00000000-0000-0000-0000-000000000000';

-- Add a notification if it does not exist
IF NOT EXISTS(SELECT *
          FROM dbo.sysalerts AS sa
          INNER JOIN dbo.sysnotifications AS sn
          ON sa.id = sn.alert_id
          WHERE sa.name = @Error825AlertName)
    BEGIN
        EXEC msdb.dbo.sp_add_notification @alert_name = @Error825AlertName, @operator_name = @OperatorName, @notification_method = 1;
    END
GO




top   prev   next  

Error 823, 824 and 832

Error 823, 824 and 832, since these errors indicate corruption.
Memory Corruptions, or Why You Need DBCC CHECKDB

error 823 vs. 832 

Don't confuse error 823 and error 832
memory or db corruption 

823 is a nasty error to get -- it says that an I/O operation failed at the OS level 
and the I/O subsystem is causing corruption 
SQL Server didn't even get a chance to check the page checksum (and potentially raise an 824 error).

Error 832 is way worse. When you get one, you'll see an error like the following:

A page that should have been constant has changed 
(expected checksum: 1dcb28a7, actual checksum: 68c626bb, 
database 13, file 'E:\Program Files\microsoft sql server\MSSQL\data\BlahBlah.mdf', page (1:112644)). 
This usually indicates a memory failure or other hardware or OS corruption. 




top   prev   next  

SQL Server Profiler

 
sql server profiler 

How to be proactively alerted of SQL Server performance problems 




top   prev   next  

Move TempDB to its own drive

tempdb 

use master
go
alter database tempdb modify file (name='tempdev', filename='T:\MSSQL\DATA\tempDB.MDF', size = 1mb)
go
alter database tempdb modify file (name='templog', filename='L:\MSSQL\LOGS\templog.LDF', size = 1mb)
go

-- add additional datafile to tempdb
USE [master] GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'T:\MSSQL\DATA\tempdev2.ndf' , SIZE = 10GB , FILEGROWTH = 0)
GO




top   prev   next  

xxxxx