SQL Server Consolidation & Virtualization Practice..

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

0

 

    image 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

    image

      Consolidation & Virtualization benefits

      • Reduced Software & Operating System Licensing cost
      • Reduced Server Hardwar cost, fewer number of servers required
      • Datacenter space, Power consumption, Cooling Cost cut down (GreenIT)
      • Monitoring & Support cost; fewer resources needed to monitor/control/patch servers etc
      • Easier Server move-ability with scale up & scale out solutions
      • High availability option (depends on setup)
      •  

        Concern(s):

        • Single point of failure (But, you can implementing a good high availability solution to deal with this issue)
        • Takes time & efforts to consolidate (But, once setup year by year return in terms of savings)
        • Complex Service Charge Model. If your organization do the billing of services provided to various business units, then you may need to do complex calculation based on usage before billing to individual units.
        •  

          Milestones to Destination

          • Inventory: Prepare inventory of SQL Server hosted in your environment. You may consider using Microsoft Assessment and Planning tool (MAP)
          • Hardware Sizing: Document database server resource available on the server like CPU, Memory, Storage, DISKIO, etc
          • Hardware Usage: Identify database server usage/utilization over a period of time, prepare histogram of Peak, Low & average usage. You may use perfmon or 3rd party tools like VMware capacity planner.
          • Savings: Calculate server operational cost in current setup and compare with new consolidated & virtualized model.
          • Going ahead: You may want to ask some specific database related questions to application owners
            • Is it a vendor supported/provided SQL Instance with limitations or internal home grown application? and what is workload or capacity planning guidelines for future.
            • Any significant reason a physical server is required? or why physical to virtual (P2V) should not be done?
            • Can Databases’ from SQL Instance be clubbed/consolidated with other SQL Instance? what is frequency of database changes/deployment or downtime requirements.
            • Can SQL instance be upgraded to latest SQL Server version & build? as per virtualization standard in your environment?
            • What are high availability options implemented for the databases & SQL Service?
          • Best practices:

            • Its better to divide entire SQL Server inventory into multiple smaller sections. Example- Should look at creating 5 subset of 100 servers rather than going in for virtualization of all 500 servers in a single attempt. Apply learning, experiences and best practices in later subsets.
            • Use single machine with individual SQL Named Instance or VM for Production/Test/Development/Staging db requirements. (depends on application/environment)
            • Never ever, oversubscribe resources for your server on virtual platform
            • Calculate total server workload in virtualization model with real-time application and database workload in different scenario or timings.
            • Carefully choose virtualization technique and server hardware to implement

            Resources: Microsoft, VMware, MS PDF

            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