Consolidating sql servers

Posted by / 08-Dec-2019 09:36

Consolidating sql servers

When using clustering, certain conventions will make administering and managing of the environment a little easier and sweat the assets better.We shall touch on a few of them briefly: -- Create Table to Store Log Shipping Data create table msdb dbo log_shipping_report (status bit, is_primary bit, server sysname, database_name sysname, time_since_last_backup int, last_backup_file nvarchar (500), backup_threshold int, is_backup_alert_enabled bit, time_since_last_copy int, last_copied_file nvarchar 500), time_since_last_restore int, last_restored_file nvarchar(500), last_restored_latency int, restore_threshold int, is_restore_alert_enabled bit); go -- Create an SQL Agent Job with the Following Script -- This will send an Email at Intervals determined by the job Schedule -- The Job Should be Created on the Log Shipping Secondary Clustered Instance -- This Job Requires that Database Mail is Enabled truncate table msdb dbo log_shipping_report go insert into msdb dbo log_shipping_report EXEC sp_help_log_shipping_monitor; go /* select [server] , database_name [database] , time_since_last_copy [Last Copy Time] , last_copied_file [Last Copied File] , time_since_last_restore [Last Restore Time] , last_restored_file [Last Restored File] , restore_threshold [Restore Threshold] , restore_threshold - time_since_last_restore [Restore Latency] from log_shipping_report; go */ DECLARE @table HTML NVARCHAR(MAX) ; DECLARE @Sec Server SYSNAME ; SET @Sec Server = @@SERVERNAME SET @table HTML = N'' N'' CAST ( ( SELECT td = lsr.server, '', td = lsr [database_name], td = lsr time_since_last_copy '', td = lsr last_copied_file td = lsr time_since_last_restore '', td = lsr last_restored_file, '', td = lsr restore_threshold '', td = case when lsr restore_threshold lsr time_since_last_restore ' ' '; EXEC msdb dbo.sp_send_dbmail @recipients='[email protected]', @copy_recipients='[email protected]', @subject = 'Transaction Log Shipping Report', @body = @table HTML, @body_format = 'HTML' ; One side effect of stacking SQL Server instance and making provision for several databases is the tendency to run out of drive letters.The other point in this direction is to ensure that the SQL Server service account for each instance must have the lock pages in memory privileges.Assigning Lock Pages in Memory ensures that when SQL Server acquires memory, the Operating System does not attempt to recover such memory when other processes on the server need memory.Consolidating SQL server databases is a practice that can lead to big savings by way of reduced licensing costs, and if properly designed, hardware costs as well.Watch this webinar for more benefits of SQL server consolidation, and strategies for the ideal implementation.Stacking SQL Server instances allows you to leverage on this licensing model by having instances share the same set of CPUs (sweating the asset).

Different versions of SQL Server can be stacked on the same environment to provide a robust environment that will support different applications.

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange We have a number of SQL Server 2008 R2 standard servers, each for a single application that we'd like to consolidate into one physical server.

It is not uncommon to have developers and project managers demanding a new instance of SQL Server for every new application or service.

While technologies like virtualization and Cloud have made spinning up new instances a breeze, some age-old techniques inbuilt with SQL Server make it possible to achieve low turn-around times when there is need to provide a new database for a new service or application.

consolidating sql servers-58consolidating sql servers-74consolidating sql servers-73

When using different Editions of SQL Server you may want to consider using Processor Affinity as described by Glen Berry in this article.