Greetings!!!
Myself Hemantgiri S. Goswami, SQL Server MVP, I am extremely happy to join SQL Knowledge as an author again. In coming days I will be actively sharing an articles, scripts and tips here.
I would like to thank Deepak for inviting me to be a part of SQL Knowledge once again.
There are some condition & times when SQL Server CPU utilization exceeds threshold value (ex 80% or more) and stay constant even till 100% for a undefined duration. A DBA do not get notified automatically about performance degrade until its reported by application team or service desk . This usually happens when all in sudden unpredicted application workload goes to SQL Server for processing; but at good number of times, its because of some T-SQL Queries are taking longer than expected or because of bad execution plan or blocking. There may be more reason to add, but ultimately high CPU utilization on production systems is going to hurt performance.
Continue reading “How to Monitor SQL Server CPU Usage and get auto alerts” »
As a proactive DBA its one of responsibility to keep an eye on database’s growth and other file system changes for the databases hosted on SQL instance. However being busy on daily DBA tasks, its not always possible to prepare scripts or manually extract data from system for documentations & trend reporting purposes. I faced the similar challenge while working with large DB systems and came up with a T-SQL script that can do the needful without worries..
Continue reading “SQL Server Query for Capacity Planning – SQL 2005 & 2008” »
From SQLknowledge and Team, We wish you and your family a Merry Christmas! It was big fun on Christmas eve, we went to see a 90ft Christmas tree and gifts shopping.
I have joined sqlknowledge and in addition to Deepak and other members, I will be writing about SQL troubleshooting, tips and tricks keep checking for more..
Merry Christmas
In continuation of my earlier post for Denali CTP1 launch, here are SQL Server 2011 setup screen prints. To begin with you can download installation media from Microsoft Link. Before you start installation, you must check system requirement and are as follows:
System Requirements: Details
The very first screen you will see is typical SQL Server installation, For this installation I used new SQL Server stand alone installation.
Continue reading “SQL 2011 Denali Basic Setup and Configurations” »
Consolidation by definition is the process of combining multiple SQL Server Databases & Servers running on different machines (could be geographically separated) onto a smaller number of more powerful machines in a central location. However in my opinion regarding SQL, its a process of getting organized, spending for what you need or use, and off-course saving at the same time. Lets see how consolidation and virtualization works together?
Microsoft SQL Server since beginning is overall self maintaining, secure by default and self tuning DBMS that requires a very little or basic configuration during installation or later during usage, In a large Enterprise size organization its easier adaptability and usage can lead into uncoordinated installations, wasted hardware and licenses, lack of standards and security holes.
Think Again! You should consider adopting Virtualization and Consolidation technique if you have similar questions:
Looking by your SQL Server inventory list, you realized that number of SQL servers are going up every month?
Looking by server utilization reports, you realized that your team or vendor over estimated database server hardware requirements and workload; leading into buying excessive hardware that your application is never going to use?
Every time it take months to plan & implement SQL patching, upgrades, installation etc, Applying SQL Server best practices and security updates/settings are complex and far of reach.
Better together Approach: You can consider three best possible scenario to implement, but are having its own benefits and concerns.
One OS/One SQLinstance- multiple databases
Its the best that you can get, in simple words evaluate your application that uses a few database(s); move your databases to a shared database server. Concerns: shared cache, SA permission, similar name databases or objects and logins, maintenance window, remote desktop connection (rdp) etc
One OS/Multiple SQL Instance
Build a server having multiple SQL Server instances for applications that require its own unique collation, SQL version & build, own dedicated memory/cache, tempdb etc. Concerns: SA permissions, remote desktop etc.
One OS/One SQL Instance (virtualization)
For applications having extreme performance needs and unique set of configurations. build a server with multiple virtual Operating systems and each OS running SQL Instance.
Software dependency: Microsoft Hyper-V, VMware, hp polyserve etc
Consolidation & Virtualization benefits
Concern(s):
Milestones to Destination
Best practices:
You never know, when you may need to rebuild a SQL sever with exact properties and configurations. Although there are dozen of 3rd party tools and software available in market to prepare enterprise level SQL Server inventory hosted in your environment like Microsoft Assessment and Planning (MAP), but keeping your own DBA script handy is big pleasure. Here is my own version of script that can bring you many hidden properties of SQL Server that are worth storing along with SQL inventory.
SET NOCOUNT ON
-- FIND SQL SERVER COLLATION
declare @SerCollation varchar(70)
select @SerCollation=convert(sysname, serverproperty(N'collation'))
PRINT 'SQL Server Default Collation is: '+@SerCollation
-- FIND SQL SERVER AND AGENT SERVICE ACCOUNT INFORMATION
PRINT 'SQL & Agent Serice Account Information: '
DECLARE @SQLserviceaccount varchar(50), @AgentServiceaccount varchar(50)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER', N'ObjectName',
@SQLserviceaccount OUTPUT, N'no_output'
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT', N'ObjectName',
@AgentServiceaccount OUTPUT, N'no_output'
SELECT @SQLserviceaccount as [SQLServer_ServiceAccount], @AgentServiceaccount as SQLAgent_ServiceAccount
-- FIND SQL SERVER AUTHENTICATION, AUDIT MODE & INSTALLATION PATH
PRINT 'SQL Server Authentication, Audit Mode & Installation path: '
declare @SmoLoginMode int, @SmoRoot nvarchar(512), @SmoAuditLevel int
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup',
N'SQLPath', @SmoRoot OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode', @SmoLoginMode OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'AuditLevel', @SmoAuditLevel OUTPUT
SELECT(case when @SmoLoginMode =1 then 'Windows Authentication'
else 'Mixed Mode Authentication' END) AS [LoginMode],
(case When @SmoAuditLevel=0 then 'None.'
When @SmoAuditLevel=1 then 'Successful Logins Only'
When @SmoAuditLevel=2 then 'Failed Logins Only'
When @SmoAuditLevel=3 then 'Both Failed and Successful Logins Only'
else 'N/A' END) AS [AuditLevel],
@SmoRoot as [SQLInstallation Location]
-- FIND LICENSE AND PAGE FILE INFORMAITON
PRINT 'License and page file information: '
Declare @version varchar(47), @CDKey varchar(40), @PageFile varchar(50)
Select @version = @@version
create table #PageFileDetails (data varchar(500))
insert into #PageFileDetails exec master.dbo.xp_cmdshell 'wmic pagefile list /format:list'
select @PageFile=rtrim(ltrim(data)) from #PageFileDetails where data like 'AllocatedBaseSize%'
drop table #PageFileDetails
If charindex('2000',@version,1)>0
BEGIN
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Microsoft SQL Server\80\Registration',
@value_name='CD_KEY', @Value=@CDKey OUTPUT
SELECT 'SQL 2000' AS SQLVersion,
CONVERT(char(40), SERVERPROPERTY('ServerName')) AS SQL_Service_Name, @PageFile AS PageFile,
CONVERT(char(50), SERVERPROPERTY('Edition'))AS SQLEdition,
CONVERT(char(20), SERVERPROPERTY('productversion')) AS ProductVersion,
CONVERT(char(20), SERVERPROPERTY('LicenseType'))AS License_Type,
CONVERT(char(20), SERVERPROPERTY('NumLicenses')) AS Number_Of_Licenses,
@CDKey AS CDKey
END
Else If charindex('2008',@version,1)>0
BEGIN
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\Setup',
@value_name='ProductID', @value=@CDKey OUTPUT
SELECT 'SQL 2008' AS SQLVersion,
CONVERT(char(40), SERVERPROPERTY('ServerName')) AS SQL_Service_Name, @PageFile AS PageFile,
CONVERT(char(50), SERVERPROPERTY('Edition'))AS SQLEdition,
CONVERT(char(20), SERVERPROPERTY('productversion')) AS ProductVersion,
CONVERT(char(20), SERVERPROPERTY('LicenseType'))AS License_Type,
CONVERT(char(20), SERVERPROPERTY('NumLicenses')) AS Number_Of_Licenses,
@CDKey AS CDKey
END
Else If charindex('2008 R2',@version,1)>0
BEGIN
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Microsoft SQL Server\150\Tools\Setup',
@value_name='ProductID', @value=@CDKey OUTPUT
SELECT 'SQL 2008 R2' AS SQLVersion,
CONVERT(char(40), SERVERPROPERTY('ServerName')) AS SQL_Service_Name, @PageFile AS PageFile,
CONVERT(char(50), SERVERPROPERTY('Edition'))AS SQLEdition,
CONVERT(char(20), SERVERPROPERTY('productversion')) AS ProductVersion,
CONVERT(char(20), SERVERPROPERTY('LicenseType'))AS License_Type,
CONVERT(char(20), SERVERPROPERTY('NumLicenses')) AS Number_Of_Licenses,
@CDKey AS CDKey
END
Else
SELECT @version AS SQLVersion,
CONVERT(char(40), SERVERPROPERTY('ServerName')) AS SQL_Service_Name, @PageFile AS PageFile,
CONVERT(char(50), SERVERPROPERTY('Edition'))AS SQLEdition,
CONVERT(char(20), SERVERPROPERTY('productversion')) AS ProductVersion,
CONVERT(char(20), SERVERPROPERTY('LicenseType'))AS License_Type,
CONVERT(char(20), SERVERPROPERTY('NumLicenses')) AS Number_Of_Licenses
This is the pretty easy and clean script that you may use to check periodically your database server drive space. You can create a job to run on hourly basis to execute this code, along with a database mail code to send you alert if any of drive has less than 15% or 20% threshold value.
-- Create a global temp table
CREATE TABLE ##space( dletter varchar(3), tspace BIGINT, fspace int, percentfree numeric(5,2))
-- Insert drive details
INSERT INTO ##space (dletter, fspace) EXEC master.dbo.xp_fixeddrives
-- Declare variables
DECLARE @oFSO INT, @oDrive INT, @drsize VARCHAR(255), @ret INT
-- invoke OACreate
EXEC @ret = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO OUT
DECLARE @dletter VARCHAR(3), @fspace INT, @tspace BIGINT
while (select count(*) from ##space where tspace is null)>0
begin
select top 1 @dletter = dletter + ':\',@fspace = fspace from ##space where tspace is null
EXEC @ret = master.dbo.sp_OAMethod @oFSO, 'GetDrive', @oDrive OUT, @dletter
EXEC @ret = master.dbo.sp_OAMethod @oDrive, 'TotalSize', @drsize OUT
UPDATE ##space SET tspace = CAST(@drsize AS BIGINT) WHERE lower(dletter) + ':\' = lower(@dletter)
EXEC master.dbo.sp_OADestroy @oDrive
end
EXEC master.dbo.sp_OADestroy @oFSO
update ##space set percentfree = fspace/((tspace/1024.0)/1024.0)*100
-- Select your data
select [Drive] = dletter ,
[Total Space GB]= convert(numeric(10,3), (tspace/1024.0)/1024.0/1024) ,
[Free Space GB]=convert(numeric(10,3),fspace/1024.0) ,
[% Free]= percentfree
from ##space
-- Drop temporary table
drop table ##space
SQL Azure is cloud based relational database platform built on SQL Server technology; it allows storing structured, semi-structured and unstructured data over the cloud. Behind the scene databases are hosted on cluster SQL servers, and are load balanced plus replicated for scalability & high availability purposes. Each logical Server consists of one master and N number of user databases based on Wed or Business Edition.
SQL Azure Standard Consumption Pricing (check offers available with MSDN subscription and Microsoft Partner Network)
Web Edition Database
• $9.99 per database up to 1GB per month
• $49.99 per database up to 5GB per month
Business Edition Database
• $99.99 per database up to 10GB per month
• $199.98 per database up to 20GB per month
• $299.97 per database up to 30GB per month
• $399.96 per database up to 40GB per month
• $499.95 per database up to 50GB per month
How to connect to SQL Azure database: You may use SQL Server management studio or
Alternatively you can choose SQLCMD to connect to SQL Azure database:
sqlcmd -U <ProvideLogin@Server> -P <ProvidePassword> -S <ProvideServerName> -d master
How to get started?
Once connected to SQL Azure instance. If you pass a very basic database creation command “CREATE DATABASE SQLKnoweldge”, it will create default web edition database of maxsize 1GB. You can specify maxsize as like:
CREATE DATABASE SQLKnoweldge (EDITION=’WEB’, MAXSIZE=5GB)
Following command will create a database with default Maxsize of 10GB that you can increase up to 50GB.
CREATE DATABASE SQLKnoweldge (EDITION=’BUSINESS’)
What if you do not specify edition? It will check database size and bases value it will determine edition. Say, if MAXSIZE is set to 10, 20, 30, 40 or 50GB then its business edition. Here is database creation syntax for SQL Azure.
CREATE DATABASE database_name
{
(<edition_options> [, ...n])
}
<edition_options> ::=
{
(MAXSIZE = {1 | 5 | 10 | 20 | 30 | 40 | 50} GB)
|(EDITION = {'web' | 'business'})
}[;]
To copy a database:
CREATE DATABASE destination_database_name
AS COPY OF [source_server_name.] source_database_name [;]
What you ‘Currently’ don’t get with SQL Azure?
In addition to SQL Profiler, Perfmon, Trace Flags, Global Temporary Tables, Distributed Transactions, SQL Azure does not support heap tables, a table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table. That is the reason ‘select into’ statement fails. Read committed Snapshot Isolation (RCSI) is default instead Read Committed in SQL server; Connection timeout is 30 minutes plus long running transactions can be terminated without your knowledge. Here is the MS list for more details:
SQL Server 2008 R2 Features Not Supported by SQL Azure: The following features that were new to SQL Server 2008 R2 are not supported by SQL Azure:
• SQL Server Utility
• SQL Server PowerShell Provider. PowerShell scripts can be run on an on-premise computer, however, and connect to SQL Azure using supported objects (such as System Management Objects or Data-tier Applications Framework).
• Master Data Services
SQL Server 2008 Features Not Supported by SQL Azure: The following features that were new to SQL Server 2008 are not supported by SQL Azure:
• Change Data Capture
• Data Auditing
• Data Compression
• Extended Events
• Extension of spatial types and methods through Common Language Runtime (CLR)
• External Key Management / Extensible Key Management
• FILESTREAM Data
• Integrated Full-Text Search
• Large User-Defined Aggregates (UDAs)
• Large User-Defined Types (UDTs)
• Performance Data Collection (Data Collector)
• Policy-Based Management
• Resource Governor
• Sparse Columns
• SQL Server Replication
• Transparent Data Encryption
SQL Server 2005 Features Not Supported by SQL Azure: The following features that were new to SQL Server 2005 are not supported by SQL Azure:
• Common Language Runtime (CLR) and CLR User-Defined Types
• Database Mirroring
• Service Broker
• Table Partitioning
• Typed XML and XML indexing is not supported. The XML data type is supported by SQL Azure.
Other SQL Server Features Not Supported by SQL Azure: The following features from earlier versions of SQL Server are not supported by SQL Azure:
• Backup and Restore
• Replication
• Extended Stored Procedures
• SQL Server Agent/Jobs
