Комментарии (18)

1. 

   archimed7592

   20.02.2016 22:14

   #8780113

   Если хочется без нервотрепки, то должен быть на готове сервер с необходимыми ресурсами (место на диске, память, процессоры). Если есть такой сервер, то достаточно включить log shipping и не нужно будет никакие километры логов восстанавливать, да и время на восстановление уйдёт гораздо меньше.

   
   

   1. 

      minamoto

      21.02.2016 11:18

      #8780365

      +2

      log shipping уже не очень актуален, лучше уж AlwaysOn группу сделать тогда.
      

      
      

      1. 

         windcatcher

         22.02.2016 13:20

         #8781025

         AlwaysOn — это кончено замечательно. Но к сожалению не каждая организация может себе позволить иметь резервный сервер, и тем более DBA в штате. Также AlwaysOn не отменяет бэкапы.
         

1. 

   gep2005

   22.02.2016 05:27

   #8780839

   Поскольку предполагаю, что здесь в комментариях соберется большое количество знатоков SQL Server хочу спросить: А влияет ли на быстродействие базы модель восстановления (судя по названию нет :) )?
   

   
   

   1. 

      AlanDenton

      22.02.2016 09:39

      #8780877

      Влияет. Иногда очень существенно. Почитайте про BULK INSERT и команды с минимальным протоколированием.
      

      
      

      1. 

         windcatcher

         22.02.2016 13:13

         #8781015

         Не могу согласиться. Вопрос все-таки был немного другой. Основные модели восстановления это Полная и Простая. И с точки зрения производительности БД один одинаковы, т.к. в обоих случаях ведется журнал транзакций, только при Простой модели он усекается автоматически. Что касается модель восстановления с неполным протоколированием (Bulk Logged), то эта модель предназначена для временной замены Полной модели на период проведения массовых операций, например, массовой вставки данных или перестроением индексов.
         

         
         

         1. 

            AlanDenton

            22.02.2016 14:11

            #8781073

            Разница все таки есть. Я не говорил, что она проявляется везде и всегда. Но она есть! Гипотетический пример. Удаление большого числа данных из таблицы:
            
            

            SET NOCOUNT ON;
            
            DECLARE @r INT = 1
            
            WHILE @r > 0 BEGIN
            
                BEGIN TRANSACTION
            
                    DELETE TOP(10000) dbo.tbl
                    --WHERE ...
            
                COMMIT TRANSACTION
            
                SET @r = @@ROWCOUNT
            
                CHECKPOINT -- SIMPLE
                BACKUP LOG -- FULL
            
            END

            
            В случае SIMPLE модели восстановления, чтобы лог не рос нужно делать CHECKPOINT, для FULL спасает только бекап лога, который априори медленнее работает, чем CHECKPOINT. Хотя опять же с оговорками… Например, можно сделать бекап лога быстрее:
            
            

            BACKUP LOG db TO DISK 'nul'

            
            но ни к чему хорошему это не приведет, если мы говорим про продакшен.
            

            
            

            1. 

               gep2005

               22.02.2016 14:16

               #8781081

               Т.е. получается Full даже медленнее в отдельных случаях?
               

               
               

               1. 

                  AlanDenton

                  22.02.2016 14:22

                  #8781091

                  FULL модель восстановления в некоторых случаях более интенсивно использует лог файл. Полноценный пример приведу в комментарии ниже минут через 15.
                  

         
         

         1. 

            gep2005

            22.02.2016 14:14

            #8781075

            Спасибо. Всегда было интересно. Просто в моих задачах с точки зрения восстановления простая модель вполне устраивает, но переживал, за быстродействие.
            Но рекомендация разносить базу и журнал на разные диски все равно актуальна так? И автоматическое усечение не влияет на производительность?
            

            
            

            1. 

               AlanDenton

               22.02.2016 14:41

               #8781111

               SIMPLE:
               
               

               SET NOCOUNT ON;
               
               USE [master]
               GO
               
               IF DB_ID('shrink') IS NOT NULL BEGIN
                   ALTER DATABASE [shrink] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
                   DROP DATABASE [shrink]
               END
               GO
               
               CREATE DATABASE [shrink]
               ON
                   PRIMARY (NAME = shrink_data, FILENAME = N'D:\shrink_data.mdf', SIZE = 25MB, MAXSIZE = 200MB, FILEGROWTH = 10%) 
                   LOG ON (NAME = shrink_log, FILENAME = N'D:\shrink_log.ldf', SIZE = 3MB, MAXSIZE = 200MB, FILEGROWTH = 10%) 
               GO 
               
               ALTER DATABASE [shrink] SET RECOVERY SIMPLE
               
               USE [shrink]
               GO
               
               CREATE TABLE dbo.tbl (
                     c1 INT IDENTITY CONSTRAINT pk PRIMARY KEY NONCLUSTERED
                   , c2 CHAR(3000) DEFAULT 'дефолтный бред'
               )
               GO
               
               DECLARE @i INT = 1
               
               WHILE @i <= 40000 BEGIN
               
                   INSERT INTO dbo.tbl DEFAULT VALUES
               
                   IF @i % 500 = 0 CHECKPOINT 
               
                   SET @i += 1
               
               END
               GO 
               
               DECLARE @i INT = 1
               
               WHILE @i <= 20000 BEGIN
               
                   DELETE FROM dbo.tbl WHERE c1 = @i 
               
                   IF @i % 500 = 0 CHECKPOINT
               
                   SET @i += 1
               
               END
               GO
               
               CHECKPOINT
               GO
               
               SELECT name, size * 8192 / 1048576
               FROM sysfiles 
               GO 
               
               DBCC SHRINKFILE(shrink_data, 40) WITH NO_INFOMSGS
               GO
               
               SELECT name, size * 8192 / 1048576
               FROM sysfiles

               
               До шринка файла данных:
               
               

               ---------------- -----------
               shrink_data      169
               shrink_log       3

               
               После шринка:
               
               

               name             
               ---------------- -----------
               shrink_data      81
               shrink_log       10

               
               FULL:
               
               

               SET NOCOUNT ON;
               
               USE [master]
               GO
               
               IF DB_ID('shrink') IS NOT NULL BEGIN
                   ALTER DATABASE [shrink] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
                   DROP DATABASE [shrink]
               END
               GO
               
               CREATE DATABASE [shrink]
               ON
                   PRIMARY (NAME = shrink_data, FILENAME = N'D:\shrink_data.mdf', SIZE = 25MB, MAXSIZE = 200MB, FILEGROWTH = 10%) 
                   LOG ON (NAME = shrink_log, FILENAME = N'D:\shrink_log.ldf', SIZE = 3MB, MAXSIZE = 200MB, FILEGROWTH = 10%) 
               GO 
               
               ALTER DATABASE [shrink] SET RECOVERY FULL
               BACKUP DATABASE [shrink] TO DISK = 'NUL' 
               
               USE [shrink]
               GO
               
               CREATE TABLE dbo.tbl (
                     c1 INT IDENTITY CONSTRAINT pk PRIMARY KEY NONCLUSTERED
                   , c2 CHAR(3000) DEFAULT 'дефолтный бред'
               )
               GO
               
               DECLARE @i INT = 1
               
               WHILE @i <= 40000 BEGIN
               
                   INSERT INTO dbo.tbl DEFAULT VALUES
               
                   IF @i % 500 = 0
                       BACKUP LOG [shrink] TO DISK = 'NUL' 
               
                   SET @i += 1
               
               END
               GO 
               
               DECLARE @i INT = 1
               
               WHILE @i <= 20000 BEGIN
               
                   DELETE FROM dbo.tbl WHERE c1 = @i 
               
                   IF @i % 500 = 0
                       BACKUP LOG [shrink] TO DISK = 'NUL' 
               
                   SET @i += 1
               
               END
               GO
               
               BACKUP LOG [shrink] TO DISK = 'NUL'
               GO
               
               SELECT name, size * 8192 / 1048576
               FROM sysfiles 
               GO 
               
               DBCC SHRINKFILE(shrink_data, 40) WITH NO_INFOMSGS
               GO
               
               SELECT name, size * 8192 / 1048576
               FROM sysfiles

               
               До шринка:
               
               

               -------------- -----------
               shrink_data    169
               shrink_log     3

               
               После:
               
               

               -------------- -----------
               shrink_data    81
               shrink_log     146

               
               Итого: При FULL модели восстановления видим, что лог используется более интенсивно (10 метров против 146).
               
               

               Но рекомендация разносить базу и журнал на разные диски все равно актуальна так?

               
               Мало дисков не бывает. Если есть возможность, то разносите и радуйтесь жизни :)
               
               

               И автоматическое усечение не влияет на производительность?

               
               Очень сильно влияет. Увеличивает фрагментацию файлов базы, увеличивает фрагментацию индексов, много ресурсов тратиться на последующий AutoGrow. Почитайте при случае Auto Shrink Events.
               

            
            

            1. 

               windcatcher

               22.02.2016 14:44

               #8781113

               Да, актуальна.
               Усечение не влияет на производительность. По крайней мере так, чтобы это заметили пользователи.
               

               
               

               1. 

                  windcatcher

                  22.02.2016 14:49

                  #8781117

                  Увидел пост коллеги сверху. Поэтому пояснение — выше я писал про автоматическое усечении лога БД в Простой модели восстановления.
                  

                  
                  

                  1. 

                     gep2005

                     22.02.2016 14:56

                     #8781127

                     Да. Спасибо. Я тоже про Простую модель говорил. Про настройку Автоусечения в параметрах базы знаю и так не делаю. В целом у меня все же складывается мнение, что в моих случаях вернее использовать простую модель, т.к. ни разу не возникало потребности восстановить базу на конкретный момент времени. (только возврат к конкретному бэкапу). Огромное спасибо за разъяснение обоим.
                     

                     
                     

                     1. 

                        windcatcher

                        22.02.2016 15:21

                        #8781149

                        Восстановление на конкретный момент времени, это скорее доп. фишка, но тоже весомая. Тут главное восстановление на максимально актуальный момент времени. И тут что бы понять Простая или Полная модель вам нужна задайте главный вопрос: за какой период мы можем себе позволить потерять данные? За 15 минут, за 2 часа, за 1 день?
                        Если толкового ответа получить не у кого, то попытайтесь просчитать последствия сами. Сколько народу вводят данные одновременно? реально ли будет ввести данные повторно, например за день или только с обеда? есть ли другие системы с которыми обменивается ваша БД и будут ли работать обмен, если вы откатите БД? и т.п.
                        

               
               

               1. 

                  AlanDenton

                  22.02.2016 14:52

                  #8781123

                  И автоматическое усечение не влияет на производительность?

                  
                  

                  Усечение не влияет на производительность.

                  
                  Влияет… опять же не всегда и везде! Поэтому может не стоит быть таким категоричным. Вашу статью будут читать в дальнейшем и такие комментарии начинающими могут быть восприняты буквально.
                  

                  
                  

                  1. 

                     gep2005

                     22.02.2016 15:00

                     #8781129

                     Да. Вы правы. Есть некоторая двусмысленность. Насколько я понял — не влияет автоусечение которое происходит само собой при модели восстановления "Простая". Но устанавливать такой параметр в настройках файлов базы и транзакционного лога не стоит.
                     

                     
                     

                     1. 

                        AlanDenton

                        22.02.2016 15:15

                        #8781141

                        Еще раз повторюсь. Необоснованный SHRINK на производительность влияет всегда… При любой модели восстановления.
                        
                        «Если Вы включаете AUTO_SHRINK на продакшене, то где-то в страшных муках умирает котенок...» об этом тоже нужно помнить :)