SQL 2011 Denali Basic Setup and Configurations

Posted by Deepak Kumar | Posted in SQL BI/DW/SSRS, SQL DBA, SQL Dev, Uncategorized | Posted on 22-12-2010

0

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

  • Supported Operating Systems:Windows 7;Windows Server 2008 R2;Windows Server 2008 Service Pack 2;Windows Vista Service Pack 2
  • 32-bit systems
  • Computer with Intel or compatible 1GHz or faster processor (2 GHz or faster is recommended.)
  • 64-bit systems
  • 1.4 GHz or faster processor
  • Minimum of 1 GB of RAM (2 GB or more is recommended.)
  • 2.2 GB of available hard disk space

The very first screen you will see is typical SQL Server installation, For this installation I used new SQL Server stand alone installation.

clip_image002

clip_image004

clip_image006

clip_image008

clip_image010

clip_image012

clip_image014

clip_image016

clip_image018

clip_image020

clip_image022

clip_image024

clip_image026

clip_image028

clip_image030

clip_image032

clip_image034

clip_image036 

 

clip_image038

clip_image040

Scripts to prepare SQL Server Inventory

Posted by Deepak Kumar | Posted in Scripts, SQL DBA, SQL Dev, Uncategorized | Posted on 21-12-2010

3

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
© 2010 SQLKnowledge.com Increase your website traffic with Attracta.com