20 Database Design Best Practices

CodeBalance: 20 Database Design Best Practices: Use well defined and consistent names for tables and columns (e.g. School, StudentCourse, CourseID ...). Use singular for table names ...

6.5 Million LinkedIn Passwords May Be In Hands of Hackers | Enterprise Security Today

6.5 Million LinkedIn Passwords May Be In Hands of Hackers | Enterprise Security Today

How to Set the /3GB Startup Switch in Windows

/3GB is a boot.ini switch that restricts memory usage of kernel mode to 1 GB.

Windows Server 2003 includes support for a startup switch that lets you tune the allocation of use of memory and memory address space.
Regardless of the amount of physical memory in your system, Windows uses a virtual address space of 4 GB, with 2 GB allocated to applications and 2 GB allocated to the operating system and kernel-mode drivers.
On systems that have 1 GB or more of physical memory, these two startup switches can be used to allocate more memory to applications (3 GB) and less memory to the operating system (1 GB).
This additional virtual address space helps reduce the amount of memory fragmentation in the virtual address space of the Exchange information store process.

The /3GB switch is used to effect this allocation change. The switch is entered in the system’s boot.ini file and takes effect after a restart.

The /3GB switch is supported only on the following operating systems:

Windows 2000 Advanced Server
Windows 2000 Datacenter Server
Windows Server 2003 Standard Edition
Windows Server 2003 Enterprise Edition
Windows Server 2003 Datacenter Edition

Note:- Windows 2000 Server does not support /GB and may cause OS or application crashes. 

To Set the /3GB Startup Switch in Windows Server 2003:

1.Right-click My Computer and select Properties. The System Properties dialog box will appear.
2.Click the Advanced tab.
3.In the Startup and Recovery area, click Settings. The Startup and Recovery dialog box will appear.
4.In the System startup area, click Edit. This will open the Windows boot.ini file in Notepad.
5.In the [Operating Systems] section, add the following switches to the end of the startup line that includes the /fastdetect switch: /3GB
6.Save the changes and close Notepad.
7.Click OK two times to close the open dialog boxes, and then restart the computer for the change to take effect.

Recomendations:

Less than or equal to 3 GB​ --> Do nothing. The OS and SQL will determine what's best.​
4 GB​ ​ --> Use /3GB in the boot.ini. AWE is not needed and will be ignored if enabled.​
5 to 16 GB​ ​--> Use /PAE in the boot.ini and AWE in SQL. The /3GB and /USERVA=2500 switches are optional.​
More than 16 GB -->​ ​ Use only /PAE in the boot.ini and AWE in SQL. The /3GB and /USERVA switches cannot be used.​

How to get last restore date and backup file name

This script is with a small modification to include the backup file name from the article published in

SqlServerCentral.com

 

This script will output the Orignal DB Name, Destination DB Name, Last Restore date and Backup file name for the databases in a SQL Server. 

SELECTbus.database_name Org_DBName,Restored_To_DBName,Last_Date_Restored,bus.name Backup_nameFROMmsdb..backupset busINNER
( JOINSELECTbackup_set_id,Restored_To_DBName,Last_Date_Restored
FROMmsdb
..restorehistoryINNER JOIN(
rhSELECT .destination_database_name Restored_To_DBName,

msdb
Max(rh.restore_date) Last_Date_RestoredFROM ..restorehistory rhGROUP BYrh

.destination_database_name) AS InnerRestONdestination_database_name = Restored_To_DBName ANDrestore_date = Last_Date_Restored) As RestDataONbus


You get a specific database just add a where clause against the appropriate field. It makes use of the restorehistory and backupset tables in the MSDB database..backup_set_id = RestData.backup_set_id

Performance Monitoring

Performance monitoring is an essential task for every DBA. Windows system monitor, Performance Logs and Alerts can be used for performance monitoring task. A 24-hour performance log is generated using the counters on a normal working day. This log can be used as a performance baseline document. SQL performance Baseline Template can be downloaded here.

If there exists sudden increase or decrease in threshold levels of any of the counters in everyday monitoring, an alert can be raised and to notify us.

The following is a partial list of counters that can be used for performance monitoring. Values of each counter may differ from server to server. So you should check with your baseline document.

Counters	Purpose
SQL Server: SQL Statistics: Batch Requests/sec	Analyzing Statement Recompiles
SQL Server: SQL Statistics: SQL Compilations/sec
SQLServer: SQL Statistics: SQL Recompilations/sec
SQL Server: Access Methods: Mixed Page Allocations /Sec	Analyzing tempdb contention issues
SQL Server: Access Methods: Workfiles Created /Sec
SQL Server: Access Methods: Worktables Created /Sec
SQL Server: General Statistics: Temp Tables Created /Sec
SQL Server: General Statistics: Temp Tables For Destruction
LogicalDisk: % Free Space	Evaluating Disk Usage
Physical Disk: % Free Space
Physical Disk: Avg. Disk Queue Length
PhysicalDisk: % Disk Time
Memory: Available Bytes	Evaluating Memory Usage
Memory: Pages/sec
SQLServer: Memory Manager: Total Server Memory (KB)
Physical Disk: Disk Reads/sec
Physical Disk: Disk Writes/sec
SQLServer: Buffer Manager: Buffer Cache Hit Ratio
Network Interface: Bytes Received/sec	Evaluating Network Usage
Network Interface: Bytes Sent/sec
Network Interface: Bytes total/sec
Network Interface: Bytes/sec
Network Interface: Output Queue Length
Processor: % Privileged Time	Evaluating Processor Usage
Processor: % Processor Time
System: Processor Queue Length
SQLServer: General Statistics: User Connections	Evaluating User Connections
SQLServer: Databases: Log Growths	Monitoring Database Availability and Transaction Log Usage
SQLServer: Databases: Percent Log Used
SQLServer: Databases:Data File(s) Size (KB)
SQLServer: SQL Errors: Errors/sec
SQLServer: Latches: Average Latch Wait Time (ms)	Monitoring Latch Wait Times
SQLServer: Latches:Latch Waits/sec
SQLServer: Latches:Total Latch Wait Time (ms)
SQLServer: Access Methods: Full Scans/sec	Monitoring Server Availability
SQLServer: Databases: Transactions/sec
Paging File: % Usage	Monitoring Server Health
Process: % Processor Time
SQLServer: User Settable: Query
SQLServer: Locks: Lock Timeouts/sec	Monitoring Waits and Queues
SQLServer: Locks: Lock Waits/sec
SQLServer: Locks:Average Wait Time (ms)
SQLServer: Locks:Lock Requests/sec
SQLServer: Locks:Lock Timeouts (Timeout > 0)/sec
SQLServer: Locks:Lock Wait Time (ms)
SQLServer: Locks:Number of Deadlocks/sec
SQLServer:Wait Statistics:Lock Waits
SQLServer:Wait Statistics:Log Buffer Waits
SQLServer:Wait Statistics:Log Write Waits
SQLServer:Wait Statistics:Memory Grant Queue Waits
SQLServer:Wait Statistics:Network IO Waits
SQLServer:Wait Statistics:Non-Page Latch Waits
SQLServer:Wait Statistics:Page IO Latch Waits
SQLServer:Wait Statistics:Page Latch Waits
SQLServer:Wait Statistics:Thread-Safe Memory Objects Waits
SQLServer:Wait Statistics:Transaction Ownership Waits
SQLServer:Wait Statistics:Wait For The Worker
SQLServer:Wait Statistics:Workspace Synchronization Waits
.NET CLR Memory: # GC Handles	SQL CLR Memory Usage
.NET CLR Memory: % Time In GC
.NET CLR Memory: Allocated Bytes/Sec

Reference: Monitoring Resource Usage (System Monitor)

SQL Server 2005 Mainstream support Ended

The Microsoft SQL Server 2005 Mainstream support has ended by 12-April-2011. Therfore the certification exams associated with the product will also be expired. So please note that all the Microsoft SQL Server 2005 exams are scheduled to retire on June 30, 2011.

Choose from the following three MCITP certifications on SQL Server 2005.

MCITP certification	Prerequisite MCTS certification	Exam(s)
MCITP: Database Administrator	MCTS: SQL Server 2005	Exam 70-443:* PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005 and Exam 70-444:* PRO: Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005
MCITP: Database Developer	MCTS: SQL Server 2005	Exam 70-441:* PRO: Designing Database Solutions by Using Microsoft SQL Server 2005 and Exam 70-442:* PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
MCITP: Business Intelligence Developer	MCTS: SQL Server 2005, Business Intelligence Development and Maintenance	Exam 70-446:* PRO: Designing a Business Intelligence Solution by Using Microsoft SQL Server 2005

*This exam is scheduled to retire on June 30, 2011.

Buy a Microsoft Certification Pack with Second Shots and save up to 20%

Microsoft Certification Exam Packs provide large discounts plus a Second Shot for every exam in the pack. The Second Shot offer allows you to retake an exam if you do not pass it the first time.

New: If you obtained your Exam Pack voucher code before January 1, 2011, you have until June 30, 2011, to complete your exams and any retakes. If you obtain your Exam Pack voucher code on or after January 1, 2011, you have until June 30, 2011, to schedule and take your first exam. 

You can find the benefits of doing certification from Microsoft’s web site. You can find the salary ranges in INR by job for employees with MCTS – SQL Server 2005 certification.

Those who have are willing to have SQL Server 2005 certification should consider to take the exam before 30-June-2011 followed by a upgrade exam for SQL Server 2008.

Error Handling in SQL Server 2005

TRY…CATCH construct is used for error handling in side T-SQL code. A TRY…CATCH construct consists of 2 blocks. One is TRY block that is immediately followed by a CATCH block. TRY…CATCH construct can be nested. When an error condition is identified in TRY block, the control is immediately transferred to CATCH block where the error can be handled.

The following code represents a basic TRY…CATCH construct.

BEGIN TRY

SELECT 1/0;

END TRY

BEGIN CATCH

      SELECT ERROR_MESSAGE();

END CATCH

Error Functions:

The following error functions can be used inside a TRY…CATCH construct to get the information about the error that is occurred.

ERROR_NUMBER() returns the error number.

ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times.

ERROR_SEVERITY() returns the error severity.

ERROR_STATE() returns the error state number.

ERROR_LINE() returns the line number inside the routine that caused the error.

ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

TRY…CATCH with RAISERROR

RAISERROR can be used inside a TRY…CATCH construct for error handling. RAISERROR generates error message using user defined messages that is stored inside sys.messages catalog view or build a message dynamically.

The following code returns an error message and the WITH LOG statement causes the error to be logged into windows application log. Using event viewer, you can view the log.

BEGIN TRY

    RAISERROR ('Error raised in TRY block.', 16,10) WITH LOG;

END TRY

BEGIN CATCH

    SELECT Error_Message() as Message_text,

               Error_Severity() as Severity,

               Error_State() as State

END CATCH

Questions:

1. What types of error cannot be handled by TRY…CATCH construct. Choose all correct answers from the following:

a. All DDL statement errors

b. All DML statement errors

c. Divide by Zero error

d. Syntax errors that prevent a batch from executing.

Answers:

1. d.