MS SQL резервное копирование с помощью .NET приложения

Автоматизация настройки резервного копирования MS SQL с помощью .NET приложения
http://habrahabr.ru/post/264927/

MS SQL Логирование и перехват операций

MS SQL Logging and Intercepting Database Operations
https://msdn.microsoft.com/en-us/data/dn469464.aspx

MS SQL Server тонкости. Подборка статей

интересные статьи по MS SQL
http://www.sqlservercurry.com/


7 Things Developers Should Know About SQL Server
http://www.brentozar.com/archive/2013/02/7-things-developers-should-know-about-sql-server/

--

guarantee order of table valued function results
http://stackoverflow.com/questions/7116004/guarantee-order-of-table-valued-function-results


------------

Stairway to Advanced T-SQL
http://www.sqlservercentral.com/stairway/119892/

MS SQL Server константы

Вот наиболее вразумительная ветка, которую нашел по поводу констант в MS SQL-сервере

Ms Sql - константы
http://www.sql.ru/forum/203599/konstanty-v-procedurah

SQL Server 2016 RC0

SQL Server 2016 RC0
https://habrahabr.ru/post/259729/

MS SQL Server индексы

14 вопросов об индексах в SQL Server, которые вы стеснялись задать
http://habrahabr.ru/post/247373/

1.2.7. Индексы в SQL Server
http://www.flenov.info/books.php?contentid=7

MS SQL Server переиндексация таблиц

переиндексация таблиц

https://msdn.microsoft.com/ru-ru/library/ms181671(v=sql.120).aspx

примеры

DBCC DBREINDEX ('DocCampSeasonPrice');
GO
DBCC DBREINDEX ('DocCampSeasonPricePos');
GO
DBCC DBREINDEX ('DocCampSeasonPosLap');
GO
DBCC DBREINDEX ('DocCampSeason');
GO

MS SQL Server Рекурсия в триггерах (Transcat SQL)

Окружение:

MS SQL Server 2014, Transact SQL

Ситуация

Триггер должен менять поля в других записях этой же таблицы

Вопросы

Работая над этим вопросом столкнулся с проблемой рекурсивных вызовов.
Если триггер апдейтит другие записи, то он может и не вызываться (у меня не вызывался).

За это поведение на уровне БД отвечает настройка рекурсивного вызова триггеров.
Вот скрипт изменения этой настройки.

ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF

Посмотреть текущее состояние - можно в свойствах БД.

Также в коде можно проверять уровень вложенности триггеров

IF ((SELECT TRIGGER_NESTLEVEL()) > 5 )RETURN

Обратите внимание, если Вы реите работать с рекурсивными триггерами, то курсоры вам нужно делать обязательно локальные

DECLARE cursor_name CURSOR LOCAL ...

Материалы по теме

How do I prevent a database trigger from recursing?
http://stackoverflow.com/questions/1529412/how-do-i-prevent-a-database-trigger-from-recursing

SQL SERVER – 2005 Understanding Trigger Recursion and Nesting with examples
http://blog.sqlauthority.com/2007/05/18/sql-server-2005-understanding-trigger-recursion-and-nesting-with-examples/

ALTER DATABASE SET Options (Transact-SQL)
https://msdn.microsoft.com/en-us/library/bb522682.aspx

Database Properties (Options Page)
https://msdn.microsoft.com/en-us/library/ms188124.aspx

Turning Recursive and Nested Triggers On and Off
http://www.devx.com/tips/Tip/30031

TRIGGER_NESTLEVEL (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms182737%28v=sql.105%29.aspx

DECLARE CURSOR (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms180169.aspx

MS SQL: Создание нового последовательного Guid в базе данных

База данных: MS SQL 2008
Возникла потребность при создании записи в таблице генерировать новый последовательный Guid. Причем генерировать на уровне базы данных.

К сожалению, newsequentialid() имеет ограничения: её можно использовать только в качетсве default-значения колонки. Соответственно пришлось прибегнуь к обходному маневру: создать свою хранимую процедуру

CREATE PROCEDURE dbo.GenerateNewSequentialId(
  @NewId uniqueidentifier output
)
AS 
BEGIN
  declare @returnid table (id uniqueidentifier default newsequentialid(), fakefield int)

  INSERT INTO @returnid(fakefield)
  VALUES(1)
    
  set @NewId =  (select top 1 id from @returnid)
END


Статьи по теме
MS SQL использование GUID как первичных ключей (Primary key)

Плохие практики при работе с БД

1. Запрос "СЕЛЕКТ ЗВЁЗДОЧКА"

Запрос вида

"SELECT * ..."

Почему плохо: потому что это мина замедленного действия, которая может приветси к торможению при расширении модели БД.
Пример: была таблица из 2-х полей к которой мы применили запрос select * и реально нам для работы в конкретном сценарии нужно было только эти 2 поля.
Через 2 года модеь расширилась, таблица выросла до 20-ти полей, причем появились "длиннотекстовые" поля и блобы(картинки).
Этот же запрос, даже там, где дотстаточно было бы 2- полей теперь всегда выбирает 20.
как нужно: явно указывайте выбираемые поля через запятую. Используйте sql-менеджер для ускорения работы (Детали - у Луценко)

2. Обращение к полям в датасете по номеру.

Это тоже "бомба замедленного действия" , т.к. запрос и обращение к его полям могут быть визуально разделены в прграмме. И, если через год кто-то добавит в средину запроса новое поле (расширяя модель), то сразу нарушится порядок полей. И весь ваш код, в котором Вы обращаетесь к полю по номеру - рушится.
И хорошо, если ошибка видна сразу, а может же случиться, что одно поле переместится на место другого поля, такого-же типа, явной ошибки не будет, и система будет продолжать считать, только данные уже будут не те (например если из-за сдвига подменится поле "сумма заказа" на "сумма заказа со скидкой").
Кроме того очень ухудшается читаемость кода. Как например найти нужное поле в 20-ти строках присваивания вида

ДатаСет.Поле[1] = значение
ДатаСет.Поле[2] = значение
ДатаСет.Поле[3] = значение
...
ДатаСет.Поле[20] = значение


Как нужно: обращайтесь к полям по имени.

3. Формирование запроса в коде путем склеивания строк (подстановка параметров в виде текста)

почему плохо:
а) увеличивается риск получить простейшую ошибку, если вы подставляете в запрос строку, а в строке есть кавычка - она сломает ваш код.
б) увеличивается риск хакнуть систему путем sql injection (посмотрите в вики).
в) сложно отлаживать запрос, например если захотиете вытащить его из программы и проверить в скл-менеджере

как лучше: задавате запрос константной строкой. в общем случае используйте параметры. Если без текстовых вставок не обойтись - делайте в строке запроса шаблоны вида __TEMPLATE_NAME__ и заменяйте их функцией замены строки при формировании реального запроса. И, только в крайних случаях, склеивайте запрос как строку. 

MS SQL использование GUID как первичных ключей (Primary key)

Проблема

В двух словах: планирую новый проект на MS SQL
планируется распределенная структура (несколько баз)
соовтетсвенно в очередной раз возник вопрос: какими должны быть ключевые поля
Int или GUID
(предметно-зависимые отбросил сразу, т.к. на практике ни к чему хорошему не приводило)

распределенная структура располагает с GUID-ключам
но в общем случае при использовании GUID  в лоб появляеются проблемы с производительностью.

Обзор "лучших практик" привел к решению
Последовательные или комбинированные (COMB) гуиды
COMB от NHibernate
или на уровне БД - NEWSEQUENTIALID

все ссылки по тексту ниже.

Разделил их на 4 раздела

1. Базовые знания
2. Исследование вопроса GUID-ов
3. Варианты решений
4. (факультатив) связь с Entity Framework

1. Базовая подготовка по MS SQL / Guid

GUID - вики статья
(вдруг кто не знает)
http://ru.wikipedia.org/wiki/GUID


Описание типа данных uniqueidentifier в MS SQL
Использование данных uniqueidentifier
http://technet.microsoft.com/ru-ru/library/ms190215(v=sql.105).aspx

Функция NEWSEQUENTIALID (Transact-SQL)
описание функции в MS SQL
http://msdn.microsoft.com/ru-ru/library/ms189786.aspx

UuidCreateSequential function
http://msdn.microsoft.com/en-us/library/aa379322(VS.85).aspx

2. Статьи очерчивающие проблемы использвания GUID'ов для первичных ключей

Хорошая статья с замерами
GUID vs INT Debate
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/guid-vs-int-debate.aspx

Простенькая статья с замерами
Целочисленные, символьные и GUID-ключи в MS SQL
http://www.arbinada.com/main/node/38

Обсуждение отличий в скорости работы обычных гуидов и последовательных
What are the performance improvement of Sequential Guid over standard Guid?
http://stackoverflow.com/questions/170346/what-are-the-performance-improvement-of-sequential-guid-over-standard-guid/170363#170363

Статья отценивающая за и проив гуидов как первичных ключей
есть расчеты и самодельная генерация комбинированных гуидов на стооне сервера.
в двух словах: автор - за комбогуиды
The Cost of GUIDs as Primary Keys
http://www.informit.com/articles/article.aspx?p=25862

Еще статья оценивающая, хорошо ли брать гуиды в качестве первичных ключей
SQL Server: Is it OK to use a uniqueidentifier (GUID) as a Primary Key?
http://mitch-wheat.blogspot.com.au/2011/08/sql-server-is-it-ok-to-use.html

еще обсуждение на Stackoverflow
Про издержки в производительности при использовании гуидов
What are the performance improvement of Sequential Guid over standard Guid?
http://stackoverflow.com/questions/170346/what-are-the-performance-improvement-of-sequential-guid-over-standard-guid/170363#170363

3. Статьи с вариантами решения

Это решение взял за основу
CombGuid. Генерация “дружественных” к SQL серверам значений Guid в .net приложениях
пример самостоятельной генерации (тут логика взята из библиотеки Magnum)
http://habrahabr.ru/post/214667/

Собственно NHibernate'овская реализация Гуидов
(по нему сделано много комбовских реализаций)
https://github.com/nhibernate/nhibernate-core/blob/master/src/NHibernate/Id/GuidCombGenerator.cs

Хорошая статья - чуть не взял её за основу
но склонился к NHibernate'овской реализации.
она учитывает 2 аспекта
1. использование функции uuidcreatesequential
2. превращение функции с SQL-server-совместимый формат
http://www.shirmanov.com/2010/05/generating-newsequentialid-compatible.html
Generating NewSequentialID compatible Sequential Guids in C#

обсуждение на stackoverflow
Ищут .NET эквивалент для newsequentialid
Is there a .NET equalent to SQL Servers newsequentialid()
http://stackoverflow.com/questions/211498/is-there-a-net-equalent-to-sql-servers-newsequentialid
тут дали ссылку на PInvoke.net (см. ниже)
но еще есть пример реализации COMB от NHibernate

pinvoke.net
пример адаптации для Net Windows функции uuidcreatesequential (rpcrt4)
http://www.pinvoke.net/default.aspx/rpcrt4.uuidcreatesequential
это годный рабочий пример, но не уверен в дружественности сгенерированных гуидов к MS SQL

еще пример
по сути дублирует код с pinvoke.net
http://www.csharpque.com/2012/06/generating-sequential-guid.html

Немного про взаимодействие с EF

Using sequential guids as identifiers in Entity Framework
http://bartwullems.blogspot.com/2013/10/using-sequential-guids-as-identifiers.html
тут описаны 2 варианта
1. генерация гуида через скл-сервер
2. ссылка на нхибернейтовсую реализацию Comb

Статьи по теме

Пример создания последовательного Guid в базе
MS SQL: Создание нового последовательного Guid

Наследование в MS SQL Server с использованием Entity Framework (EF)

Ситуация 

Созреваю на применение наследования в базе данных. Работаю с MS SQL Server 2008 (планирую переход на 2012)

Моя мотивация: 

в системе есть несколько типов документов, а будет еще больше.
И у всех документов нужны типовые реквизиты:

• состояние (проведен/не проведен), 
• дата создания, 
• автор, 
• дата изменения, 
• последний изменивший,
• пометка удаления

Дублировать эти поля во всех типах документов очень не хочется.
Хотелось бы создать таблицу "Базовый документ" со всеми этими служебными полями и остальные документы наследовать от неё.

Изучение темы

К сожалению, на нашел в MS SQL каких-нибудь стандартных механизмов. Похоже, придется все делать "ручками".

Похоже выбирать буду метод TPT (Table Per Type)

Все материалы привожу ниже

Материалы по теме

Статья про реализацию наследования в БД
Implementing Table Inheritance in SQL Server
тут про бд описано нормально, но не освещено как с этим "взлетать": а именно, как работать в прикладном решении
http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server

Несколько веток на Stackoverflow
How can you represent inheritance in a database?
http://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database

Table “Inheritance” in SQL Server
http://stackoverflow.com/questions/529256/table-inheritance-in-sql-server


Статью по связке с Entity Framework
Implementing Inheritance with the Entity Framework 6 in an ASP.NET MVC 5 Application (11 of 12)
http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/implementing-inheritance-with-the-entity-framework-in-an-asp-net-mvc-application


Inheritance in the Entity Framework
http://blogs.msdn.com/b/adonet/archive/2007/03/15/inheritance-in-the-entity-framework.aspx

EF Designer TPT Inheritance
http://msdn.microsoft.com/en-us/data/jj618293.aspx

EF Designer TPH Inheritance
http://msdn.microsoft.com/en-us/data/jj618292.aspx

Инструкции по обновлению, вставке или удалению из хранилища затронули непредвиденное число строк ({0})

Окружение

Visual Studio 2012
EF 5
MS SQL Server 2008

При добавлении записи в таблицу возникает ошибка

Инструкции по обновлению, вставке или удалению из хранилища затронули непредвиденное число строк ({0}). Сущности могли быть изменены или удалены с момента их загрузки. Обновите записи диспетчера ObjectStateManager

Как искал

В русскоязычном сообществе почти ничего не нашел

нашел занятный сайт - переводчик ошибок
http://finderr.net/clr/ru/%D0%98%D0%BD%D1%81%D1%82%D1%80%D1%83%D0%BA%D1%86%D0%B8%D0%B8-%D0%BF%D0%BE-%D0%BE%D0%B1%D0%BD%D0%BE%D0%B2%D0%BB%D0%B5%D0%BD%D0%B8%D1%8E-%D0%B2%D1%81%D1%82%D0%B0%D0%B2%D0%BA%D0%B5-%D0%B8%D0%BB%D0%B8-%D1%83%D0%B4%D0%B0%D0%BB%D0%B5%D0%BD%D0%B8%D1%8E-%D0%B8%D0%B7-%D1%85%D1%80%D0%B0%D0%BD%D0%B8%D0%BB%D0%B8%D1%89%D0%B0-%D0%B7%D0%B0%D1%82%D1%80%D0%BE%D0%BD%D1%83%D0%BB%D0%B8-%D0%BD%D0%B5%D0%BF%D1%80%D0%B5%D0%B4%D0%B2%D0%B8%D0%B4%D0%B5-/17429932

Далее искал уже по ошибке

Store update, insert, or delete statement affected an unexpected number of rows ({0}). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

Свел возможные варианты решения

1. Вы не указываете значение первичного ключа при записи.
Проверьте. если дейстительно первичный ключ нулл - задайте его явно (часто ребята забывают возвращать его из формы, или при database сценарии указывают это поле как пропускаемое)

если у вас этот вариант - смотрите ссылки
http://rpelepei.blogspot.com/2010/05/0.html
или
http://stackoverflow.com/questions/1836173/entity-framework-store-update-insert-or-delete-statement-affected-an-unexpec

2. Ошибки многопользовательской работы
http://stackoverflow.com/questions/1836173/entity-framework-store-update-insert-or-delete-statement-affected-an-unexpec

3. Побочные эффекты триггеров

http://stackoverflow.com/questions/6819813/solution-for-store-update-insert-or-delete-statement-affected-an-unexpected-n

http://stackoverflow.com/questions/5820992/error-when-inserting-into-table-having-instead-of-trigger-from-entity-data-frame

http://www.britishdeveloper.co.uk/2010/06/entity-framework-optimisticconcurrencye.html

У меня был третий вариант. опишу поподробнее:
Сразу оговорюсь, что игра с SET NOCOUNT ON/OFF мне не помогла.
Также не помог фиктивный  селект в конце триггера.

Таблица заказов ORDER
на ней висит триггер на Insert.
Триггер в свою очередь вызывал хранимую процедуру (синхронизация с таблицей пользователей) в которой делалась выборка из заказа (ORDER) которая не возвращала записей.
Оказалось этот селект и мешал Entity Framework'у нормально работать.

Что сделал: я переделал логику так чтобы не было пустых селектов из исходной таблицы

Run Script On Multiple MS SQL DataBases

Problem

Run script on multiple MS SQL Server Databases (in My Case it is MS SQL Server 2008 databases)

Solution

I've write simple app to solve this problem.
Main idea: set the list of database connections and run script on each database from list

It's pretty simple, but very usefull for me.

Download

How to use

1. Fill database list
Fill the database list in file ConnectionStrings.txt
Format
host|dbname|user|password

One database info per line

2. Set the script
copy your script into file script.txt

3. Run Run.bat
That's all. All databases from ConnectionStrings.txt will be updated by script from script.txt

Feedback

Please, leave feedback in comments to this post

See also
Question on forums
http://www.dbforums.com/microsoft-sql-server/1621678-best-mssql-management-tools.html

discussions on stackoverflow
http://stackoverflow.com/questions/1461955/sql-server-execute-batch-t-sql-script-on-multiple-databases/27794906#27794906
http://stackoverflow.com/questions/3306218/run-script-on-multiple-dbssql-server

Накатить скрипт на несколько MS SQL баз

Задача

"Накатить" скрипт на несколько MS SQL баз (в моем случае MS SQL Server 2008)

Решение

Сделал простое приложение, которым и хочу здесь поделиться
Что делает приложение: накатывает скрипт на базы по списку

Скачать

Инструкция

1. Настроить список баз

задаете список баз в файле ConnectionStrings.txt

формат файла: список строк вида: 

host|dbname|user|password

по одной строке на базу

2. Указать скрипт

вносите SQL-скрипт в файл script.txt

3. Запустить Run.bat

Это все: скрипт из файла script.txt выполнится на всех базах из списка, который вы задали в файле ConnectionStrings.txt

Обратная связь

Пожелания/предложения оставляйте в комментариях к посту

См. также
Тема на форуме: обработка нескольких баз
http://www.dbforums.com/microsoft-sql-server/1621678-best-mssql-management-tools.html

обсуждение темы на stackoverflow
http://stackoverflow.com/questions/1461955/sql-server-execute-batch-t-sql-script-on-multiple-databases/27794906#27794906
http://stackoverflow.com/questions/3306218/run-script-on-multiple-dbssql-server

MS SQL - убирание всех соединений с базой

Задача:
обновить MS SQL базу, когда к базе есть подключения. нужно все подключения удалить

помог такой код с этой страницы http://www.julian-kuiters.id.au/article.php/sql-server-2005-snippet-drop-db-conn

USE master
GO

ALTER DATABASE database name
SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE database name
SET ONLINE

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

и имя базы нужно брать в квадратные скобки [ ]

MS SQL 2008: Client IP address on shared hosting

Task:

get IP-address of the client (IP-address of client connection)

(for example to write to the log address of the computer you are using to connect)

Surroundings and limitations:
MS SQL 2008 database is on a hosting without admin rights.
There are no rights to execute dm_exec_connections.

Solution
(key line highlighted)

select
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address 

Links

MSDN help

http://msdn.microsoft.com/en-us/library/bb895240.aspx

MS SQL 2008 IP адрес клиента

Задача: 
получить IP-адрес клиента (IP-дарес клиентского соединения)
(например для того чтобы записывать в лог адрес компьютера с которого производилось подключение)


Окружение и ограничения: 
база MS SQL 2008 на хостинге, прав администартора нет. Прав на процедуру dm_exec_connections нет.

Решение
(ключевую строчку выделил)


 select
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address 


Ссылки
Оригинал на английском
http://weblogs.sqlteam.com/peterl/archive/2010/03/20/Get-client-IP-address.aspx

Помощь в MSDN по свойству CONNECTIONPROPERTY
http://msdn.microsoft.com/en-us/library/bb895240.aspx