Отправка запроса на все базы данных всех указанных серверов на примере MS SQL Server и C#.NET

  • Tutorial

Предисловие


Нередко необходимо послать запрос всем БД (базам данных) всем указанным серверам. Многие DML-запросы можно сделать встроенными средствами. Например, так. Но как быть с DDL-запросами?

В данной статье будет разобран пример реализации приложения, которое отправляет запрос всем базам данных всех указанных серверов, используя MS SQL Server и C#.NET.

Решение


Например, необходимо создать или изменить представление (для примера возьмем создание представления из задачи):

Запрос
USE [НАЗВАНИЕ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE view [inf].[vTableSize] as
with pagesizeKB as (
	SELECT low / 1024 as PageSizeKB
	FROM master.dbo.spt_values
	WHERE number = 1 AND type = 'E'
)
,f_size as (
	select p.[object_id], 
		   sum([total_pages]) as TotalPageSize,
		   sum([used_pages])  as UsedPageSize,
		   sum([data_pages])  as DataPageSize
	from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
	left join sys.internal_tables it on p.object_id = it.object_id
	WHERE OBJECTPROPERTY(p.[object_id], N'IsUserTable') = 1
	group by p.[object_id]
)
,tbl as (
	SELECT
	  t.[schema_id],
	  t.[object_id],
	  i1.rowcnt as CountRows,
	  (COALESCE(SUM(i1.reserved), 0) + COALESCE(SUM(i2.reserved), 0)) * (select top(1) PageSizeKB from pagesizeKB) as ReservedKB,
	  (COALESCE(SUM(i1.dpages), 0) + COALESCE(SUM(i2.used), 0)) * (select top(1) PageSizeKB from pagesizeKB) as DataKB,
	  ((COALESCE(SUM(i1.used), 0) + COALESCE(SUM(i2.used), 0))
	    - (COALESCE(SUM(i1.dpages), 0) + COALESCE(SUM(i2.used), 0))) * (select top(1) PageSizeKB from pagesizeKB) as IndexSizeKB,
	  ((COALESCE(SUM(i1.reserved), 0) + COALESCE(SUM(i2.reserved), 0))
	    - (COALESCE(SUM(i1.used), 0) + COALESCE(SUM(i2.used), 0))) * (select top(1) PageSizeKB from pagesizeKB) as UnusedKB
	FROM sys.tables as t
	LEFT OUTER JOIN sysindexes as i1 ON i1.id = t.[object_id] AND i1.indid < 2
	LEFT OUTER JOIN sysindexes as i2 ON i2.id = t.[object_id] AND i2.indid = 255
	WHERE OBJECTPROPERTY(t.[object_id], N'IsUserTable') = 1
	OR (OBJECTPROPERTY(t.[object_id], N'IsView') = 1 AND OBJECTPROPERTY(t.[object_id], N'IsIndexed') = 1)
	GROUP BY t.[schema_id], t.[object_id], i1.rowcnt
)
SELECT
  @@Servername AS Server,
  DB_NAME() AS DBName,
  SCHEMA_NAME(t.[schema_id]) as SchemaName,
  OBJECT_NAME(t.[object_id]) as TableName,
  t.CountRows,
  t.ReservedKB,
  t.DataKB,
  t.IndexSizeKB,
  t.UnusedKB,
  f.TotalPageSize*(select top(1) PageSizeKB from pagesizeKB) as TotalPageSizeKB,
  f.UsedPageSize*(select top(1) PageSizeKB from pagesizeKB) as UsedPageSizeKB,
  f.DataPageSize*(select top(1) PageSizeKB from pagesizeKB) as DataPageSizeKB
FROM f_size as f
inner join tbl as t on t.[object_id]=f.[object_id]

GO


Это представление показывает размеры всех таблиц для каждой БД (базы данных).

Создадим файл FileQuery.sql и запишем туда указанный выше запрос. Теперь определим функцию, которая отправляет запрос из файла всем БД всем указанным серверам:

Функция
/// <summary>
        /// Отправка запроса ко всем БД указанного сервера
        /// </summary>
        /// <param name="server">имя указанного сервера (экземпляра MS SQL Server)</param>
        /// <param name="sql">T-SQL-запрос</param>
        /// <param name="tw_log">Поток для логирования</param>
        void RunQueryInAllDBServer(string server, string sql, TextWriter tw_log)
        {
            SqlConnectionStringBuilder scsb = null;

            //список имен всех баз данных на сервере
            List<string> ldbs = new List<string>();

            //настройка строки подключения
            scsb = new SqlConnectionStringBuilder();
            scsb.ApplicationName = "НАЗВАНИЕ_ПРИЛОЖЕНИЯ";
            scsb.InitialCatalog = "master";
            scsb.IntegratedSecurity = true;
            scsb.DataSource = server;

            //вывод в лог текущего времени и названия экземпляра сервера MS SQL Server
            tw_log.WriteLine($"{DateTime.Now} ServerName: {server}:");

            //создание подключения с запросом для получения имен всех БД на сервере
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = scsb.ConnectionString;

                SqlCommand comm = new SqlCommand("select [name] from sys.databases");
                comm.CommandType = System.Data.CommandType.Text;
                comm.Connection = conn;

                conn.Open();
                var result = comm.ExecuteReader();

                while (result.Read())
                {
                    ldbs.Add(result.GetString(0).ToString());
                }
            }

            //выполнение запроса sql на каждой БД сервера
            for (int i = 0; i < ldbs.Count; i++)
            {
                using (SqlConnection conn = new SqlConnection())
                {
                    scsb.InitialCatalog = ldbs[i];
                    conn.ConnectionString = scsb.ConnectionString;

                    SqlCommand comm = new SqlCommand(sql);
                    comm.CommandType = System.Data.CommandType.Text;
                    comm.Connection = conn;

                    conn.Open();
                    try
                    {
                        comm.ExecuteNonQuery();
                        tw_log.WriteLine($"{DateTime.Now} DBName: {ldbs[i]} успешно выполнен запрос");
                    }
                    catch(Exception exp)
                    {
                        tw_log.WriteLine($"{DateTime.Now} DBName: {ldbs[i]} Exception: {exp.Message}");
                    }
                }
            }
        }


Данная функция получает название экземпляра MS SQL Server, запрос T-SQL и поток для логирования. Внутри заполняется список имен всех БД сервера. Далее к каждой БД производится запрос. В целях безопасности выставлена авторизация Windows: scsb.IntegratedSecurity = true;

Также стоит быть осторожным с данной функцией, т к она выполняет любой запрос на всех БД сервера.

Теперь создадим код вызова описанной выше функции:

Код
string sql = null;
using (Stream st_log = new FileStream("НАЗВАНИЕ_ФАЙЛА_ДЛЯ_ЛОГИРОВАНИЯ", FileMode.Create, FileAccess.Write))
            {
                using (TextWriter tw_log = new StreamWriter(st_log, Encoding.Default))
                {

                    using (Stream st = new FileStream("FileQuery.sql", FileMode.Open, FileAccess.Read))
                    {
                        using (TextReader tr = new StreamReader(st, Encoding.Default))
                        {
                            sql = tr.ReadToEnd();
                        }
                    }
//здесь servers-массив имен экземпляров MS SQL Server, на которые необходимо отправить запрос T-SQL
                    for (int i = 0; i < servers.Length; i++)
                    {
                        RunQueryInAllDBServer(servers[i], sql, tw_log);
                    }

                    tw_log.WriteLine($"Конец {DateTime.Now}");
                }
            }


Все. Приложение готово. Естественно, список серверов, а также путь к файлу запроса лучше вынести в настройки.

Результат


В данной статье был рассмотрен пример отправки запроса, который записан в файле, всем базам данных всех указанных серверов. Это позволяет посылать DDL-запросы ко всем БД, что нельзя сделать недокументированной хранимой процедурой sp_MSForEachDB.

А какими инструментами Вы пользуетесь для данной или подобной ей задачи?

Источники:


» MSDN
» Автоматизация по сбору данных о росте таблиц и файлов всех баз данных MS SQL Server
» Как применить операцию сразу ко всем таблицам или ко всем базам данных
Поделиться публикацией
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама
Комментарии 6
  • +1
    Этот способ не всегда корректен, если база, например, храниться у пользователя. Мы написали API, к которому пользователь подключается при запуске программы и сам забирает обновление! В вашем же случае, если изменения касаются и приложения и базы, этот способ категорически неверен! Например, вы убрали поля из представления, или из процедуры и клиент будет получать ошибки!
    • 0
      Да, согласен
      Будет ли у Вас возможность описать данный подход подробнее здесь или отдельной публикацией?
      • 0
        Доберусь до компьютера опишу здесь ответом!
        • +1
          В общем есть api, которая содержит список клиентов, и таблицу со списком и версией обновлений.
          Так же клиент хранит у себя хранит версию последнего обновления, которое он установил.
          Когда клиент запускает приложение, и подключается к базе, то он автоматически отправляет свою версию к api, и она проверяет, нужны ли обновления клиенту, и выдает их при необходимости.
          Далее клиент устанавливает и уведомляет о результатах обновления.
          Данный подход дал нам несколько плюсов:
          1) Мы видим статистику обновлений по клиентам и, в случае ошибок, мы имеем возможность быстро отреагировать.
          2) Обновления устанавливаются на базу, доступ к которой мы не имеем
          3) Так как у нас есть список клиентов, мы расширили функционал, настроили обмен с 1С, и автоматически формируем необходимые документы(например, акты), сделали возможность выполнять запросы, которые не просто обновлятют структуру, а так же выполняют запросы к базе и возвращают результат в виде таблицы, например статистика по базе
          4) Если база поднимается с бекапа (например, пользователь удалил какие-нибудь данные и т.д.), то при разворачивании бекапа, при запуске приложения автоматически установятся все необходимые для базы обновления (соответственно, есть функционал, который проверяет, восстановлена эта база для клиента или для тестов, или переехала на другой сервер, соответственно если база тестовая, то статистика (необходимая для отчетности) по ней не собирается, просто устанавливаются обвновления)
          Получилось, а-ля, обновление по требованию
          5) так же из этого приложения мы можем принудительно установить обновления на базы клиентов, к которым мы имеем доступ, и предупредить остальных клиентов о том, что им необходимо перезапуститься (но это индивидуальный случай, т.к. клиентов, дотуп к которым мы не имеем, очень мало, но тем не менее они есть)

          и соответственно пока 1 минус: для установки экстренного обновления клиенту необходимо перезапустить приложение, но мы так и не придумали, как это избежать, чтобы не нагружать сеть (есть клиенты, у которых интернет работает через USB-модем).

          Если будут дополнительные вопросы, буду рад ответить.
          Может у кого то есть свои предложения
          • 0
            По п.3 можно детальнее, что за статистика?
            Если не секрет для какой системы Вы используете СУБД-NAV, CRM, 1C или что-то другое?
            • +1
              У нас проект по расчёту стоимости коммунальных услуг, данные могут быть разными, к примеру общая площадь дома. Проект свой, акты через 1С

      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.