Где хранятся базы данных sql server
SELECT
mdf.database_id,
mdf.name,
mdf.physical_name as data_file,
ldf.physical_name as log_file,
db_size = CAST((mdf.size * 8.0)/1024 AS DECIMAL(8,2)),
log_size = CAST((ldf.size * 8.0 / 1024) AS DECIMAL(8,2))
FROM (SELECT * FROM sys.master_files WHERE type_desc = ‘ROWS’ ) mdf
JOIN (SELECT * FROM sys.master_files WHERE type_desc = ‘LOG’ ) ldf
ON mdf.database_id = ldf.database_id
- Click Execute (or hit the F5 key).
- Review the list of server database files in the query execution results:
Click here to edit
- Open Netwrix Auditor and navigate to Reports -> Predefined -> SQL Server — State-in-Time -> SQL Server Databases.
- Click View.
Get Detailed SQL Server Database File Location Information in a Few Clicks
Microsoft SQL Server has become one of the most popular relational database management systems for small and large businesses alike. IT pros need to be able to say exactly where important SQL database files are stored, without delays or errors, for multiple reasons, such as to facilitate backups and recovery processes, to transfer files to another location, or to find the folder if a server instance was installed to a non-standard location.
SQL Server stores data using two file extensions:
- MDF files are the data files that hold the data and objects such as tables, indexes, stored procedures and views.
- LDF files are the transaction log files that record all transactions and the database modifications made by each transaction.
The default database file location for server instances depends on the version of the Microsoft SQL Server software:
- SQL Server 2014 — C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\
- SQL Server 2016 — C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\
- SQL Server 2017 —C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\
You have two native options for finding out where the SQL server stores its database files: either right-click on the instance name in SQL Server Management Studio (SSMS) and navigate to the ‘Database Settings’ tab, or use a T-SQL query. SSMS allows you to check one database at a time, and queries require a fair amount of expertise and time to compose them.
With Netwrix Auditor for SQL Server, you can get a comprehensive report on SQL Server database file locations that includes a summary of all key settings in just a few clicks. You can also report and alert on configuration changes, such as modifications to important functions, schemas, functions, database options and object owners. Even better, Netwrix Auditor also provides deep visibility into permissions to your SQL instances, databases and other SQL objects, as well as reporting and alerting on changes to those permissions. With this powerful tool, you can spot and revert improper or malicious changes to configurations and permissions before they lead to downtime or a data breach.
Где хранятся базы данных sql server
Где мои файлы локальной базы данных SQL Server Express?
Я прочитал, что мои локальные файлы базы данных SQL Server Express должны находиться здесь (поскольку пользователь на моем компьютере — «bclay»):
C: \ Users \ bclay \ AppData \ Local \ Microsoft \ Локальная база данных Microsoft SQL Server \ Instances \ MSSQLLocalDB
. но этого не может быть, поскольку самый новый файл существует более двух месяцев назад, и я обновил данные SQL Server Express сегодня (через мое приложение C # Winforms, созданное с помощью Visual Studio 2019). Как я могу узнать, где находятся эти файлы, чтобы сделать их резервную копию?
ОБНОВИТЬ
Используя ответ от marc_s, я смог получить его из обозревателя сервера Visual Studio:
ОБНОВЛЕНИЕ 2
Хорошо, это кажется странным: я перехожу к этому месту в проводнике Windows, и он не показывает мне, что находится в подпапке \ Data. Хотя я выбрал подпапку Data, она показывает мне, что находится в подпапке Installs под ней, и когда я щелкаю правой кнопкой мыши подпапку Data, чтобы скрыть ее свойства, она сообщает мне, что у меня нет учетных данных для ее просмотра:
Как я могу сделать резервную копию файла .mdf, если я его даже не вижу?
ОБНОВЛЕНИЕ 3
Я произвел поиск по всему жесткому диску всех файлов * .mdf, и он не «нашел» (не показал мне) мои таблицы. Единственные, которые могут быть связаны, здесь являются верхними, но у них есть общие имена:
ОБНОВЛЕНИЕ 4
Я думаю, что это был marc_s, который где-то упоминал об использовании MS SQL Server Management Studio и выборе Task> Backup. Я загрузил / установил его, и, похоже, он работал: он создал папку Backup в ожидаемом месте. Я вижу, что он был создан сегодня, 01.10.2020, но я по-прежнему ничего не вижу в папке, поэтому ничего не могу скопировать из нее. Вот что я вижу (содержимое последней выбранной папки), когда выбираю папку «Резервное копирование»:
Transact-SQL, Создание и удаление базы данных
Большинство авторов, начинают рассматривать SQL, начиная с операторов получения данных из базы данных. Именно с этого начинается рассмотрение SQL-92, но у нас еще нет баз данных и не откуда брать данные. Мы же начнем с самого начала, т.е. с создания базы данных, создания таблиц и изменения их структуры. Когда у нас будет готова тестовая база данных, мы заполним ее данными и тогда уже научимся работать с этими данными.
Итак, в этой главе нам предстоит узнать:
- Как создавать базу данных с помощью SQL запросов;
- Как изменять параметры базы данных;
- Как создавать таблицы;
- Как изменять параметры таблицы.
Я рекомендую выполнять все задания, которые будут описаны в книге в этой главе, потому что созданная нами структура будет использоваться в последующих главах. Если вы хотите перейти к рассмотрению следующих глав, то рекомендую последовательно выполнить все сценарии из директории Chapter1 на компакт диске. Это позволит вам иметь готовую структуру базы данных, на которой можно будет тестировать запросы из следующих глав.
Большинство из описываемых в данной главе операторов SQL в равной степени будут работать на большинстве баз данных. Но в некоторых случаях могут быть отличие. Например, в MS Access нельзя создавать базу данных, потому что здесь база данных это файл, который создается с помощью одноименной программы. В других базах данных операторы создания баз данных и таблиц имеют точно такой же синтаксис, но может быть отличие в поддерживаемых параметрах из-за большего или меньшего количества возможностей.
Операторы по описанию объектов базы данных выделают в отдельный язык (подязык SQL) — DDL (Data Definition Language, Язык Объявления Данных). Именно этот язык будет рассматриваться в этой главе, ведь нам предстоит научиться описывать данные таблицы.
Создание и удаление базы данных
Информация о каждой базе данных в SQL Server хранится в таблице sysdatabases базы данных master. Поэтому желательно (но не обязательно) использовать базу данных master, во время создания базы. К тому же, после изменения любой пользовательской базы данных создавать резервную копию базы данных master. О резервном копировании и восстановлении мы поговорим в разделе 4.10. Объявление базы данных – это процесс указания имени и указания размера и расположения файлов.
В Transact-SQL для создания базы данных есть команда CREATE DATABASE. Эта команда может выполняться только с сервером SQL Server. При использовании базы данных MS Access команда не доступна, потому что базой данных является файл с расширением .mdb, который создается в программе Access и к которому мы подключены.
Сервер MS SQL Server может содержать несколько баз данных. Вы можете подключиться к любой из них (системной или тестовой, которые присутствуют в стандартной поставке) и создать новую базу данных, но желательно подключаться к базе данных master.
Синтаксис команды создания базы данных показан в листинге 1.1.
Листинг 1.1. Создание базы данных
Давайте разберем этот синтаксис подробно, чтобы в будущем вы могли читать подобные описания. Первая строка содержит имя команды и параметр:
В качестве параметра выступает имя создаваемой базы данных.
Если вы посмотрите на остальные параметры синтаксиса, то увидите, что все они находятся в квадратных скобках. Все, что в квадратных скобках – описывает не обязательные параметры. Получается, что минимальная команда создания базы выглядит как
Имя в данном случае передается без каких-либо кавычек, если имя состоит из одного слова. Если имя должно быть из двух слов, то его необходимо оформить в квадратные кавычки:
Очень интересной является следующая строка:
Здесь в квадратных скобках указано два значения, разделенных вертикальной чертой. Это значит, что эти значения являются не обязательными, а вертикальная черта соответствует слову «или», т.е. в запросе можно будет указывать или FOR LOAD, или FOR ATTACH, или вообще ничего. Оба параметра указывать нельзя.
В угловых скобках указываются имена секций. Например, в описании оператора CREATE DATABASE есть два указания на < filespec >. Эта секция может идти после ключевого слова ON и после LOG ON. Описание самой секции идет после:
Не понятно? Попробуем еще раз. Описание оператора CREATE DATABASE выглядит так:
Далее идут описания секций < filespec > и < filegroup >:
Теперь, заменяем в описании CREATE DATABASE название секции < filespec > на саму секцию. Если вы имеете опыт программирования на одном из высокоуровневых языков, то в секциях вы уже наверно увидели аналогию с процедурами. Название секции < filespec > аналогично имени процедуры, а после < filespec > ::= идет сам код процедуры.
Следующая интересная запись:
Запись < filespec > — описание файла, а [ . n ] указывает на то, что возможно несколько описаний.
С помощью круглых скобок параметры объединяются в группу, например:
В данном случае в группу объединены параметры NAME, FILENAME, SIZE, MAXSIZE и FILEGROWTH. Все эти параметры описывают файл, поэтому и объединены в группу. Из всей группы только параметр FILENAME является обязательным. После круглых скобок идет снова можно увидеть [ . n ], значит может быть несколько описаний файлов (для каждого файла базы данных свое описание).
Параметр FILENAME интересен еще и тем, что его значение задается с помощью знака равенства, после которого идет текст в одинарных кавычках:
Кавычки в данном случае указывают на их обязательное присутствие в SQL запросе. По наличию кавычек достаточно просто определить тип параметра. Если они присутствуют, то параметр строковый, иначе числовой. Например, параметр SIZE не содержит кавычек, а значит, он числовой:
На первый взгляд общий вид достаточно сложен, но здесь не так уж и много параметров. Давайте рассмотрим основные, а потом увидим их действия на практике:
- PRIMARY. Этот параметр указывает файл в основной файловой группе. Эта файловая группа содержит все системные базы данных. Она также содержит все объекты, не назначенные другим файловым группам. Каждая база данных содержит один основной файл данных. Основной файл – это стартовая точка базы данных и указывает на место ее нахождения. Рекомендуемое файловое расширение для основного файла .mdf. Если вы не укажите этого параметра, первый файл списка описания будет использован как основной.
- FILENAME. Этот параметр указывает имя и путь к файлу в операционной системе. Путь должен указывать на папку на сервере, где установлен SQL Server. Нельзя использовать сетевые диски с других компьютеров.
- SIZE. Этот параметр указывает размер файла данных или журнала. Вы можете указать размер в мегабайтах MB (значение по умолчанию) или в килобайтах KB. Минимальный размер – 512KB для обоих файлов – журнала транзакций и файла данных. Размер, указанный для основного файла базы данных должен быть больше или равен размеру основного файла базы данных model. Мы уже говорили, что база model копируется во все новые базы данных, поэтому размер новой, не может быть меньше размера model, иначе копирование станет невозможным. Когда вы добавляете новый файл базы данных или журнала без указания размера – то сервер использует значение размера по умолчанию = 1МБ.
- MAXSIZE. Этот параметр указывает максимальный размер, до которого файл может увеличиваться. Вы можете указать размер в мегабайтах MB (значение по умолчанию) или в килобайтах KB. Если вы не укажите максимальный размер, файл будет увеличиваться, пока диск не будет заполнен полностью.
- FILEGROW. Этот параметр указывает размер приращения файла. Значение этого параметра для файла не может превышать значение MAXSIZE. Значение 0 указывает на запрет увеличения. Значение может быть указано в мегабайтах (по умолчанию), килобайтах или процентах. Значение по умолчанию, если этот параметр не указан — 10%, а минимальный размер – 64кб. Указанный размер округляется до ближайшего числа, кратному 64кб.
- COLLATION. Этот параметр указывает значение по умолчанию для сопоставления в базе данных. Сопоставления (кодировка или раскладка) включают роли контролирующие использование символов для языка и алфавита.
Во время создания базы данных, очень важно понимать, как SQL Server хранит данные, чтобы вы могли посчитать и указать количество дискового пространства для размещения базы данных. Во время создания баз учитывайте следующее:
- Все базы данных имеют основной файл данных, определяемый именем файла с расширением .mdf, и один или более файлов журнала определяемый именем файла с расширением .ldf. База данных может также иметь вторичные файлы данных, которые определяются по имени файла с расширением .ndf. Файлы могут объединяться в группы, о чем мы поговорим в разделе 1.1.1.
- Физические файлы имеют двойное именование – имя ОС и имя, которые вы можете использовать в операторах Transact-SQL (логическое имя, которое указывается в параметре NAME).
- Когда вы создаете базу данных, в нее копируется содержимое базы данных model, которая включает системные таблицы и может содержать пользовательские таблицы, созданные вами. Минимальный размер создаваемой базы данных должен быть равен или больше размера базы данных model.
- Сервер SQL хранит, читает и записывает данные блоками по 8кб, эти блоки называются страницами. База данных может хранить 128 страниц на мегабайт (1 мегабайт или 1048576 байт разделить 8 килобайт или 8192 байт). Все страницы хранятся в пространстве. Пространство – это 8 последовательных страниц, или 64кб. Поэтому база данных имеет 16 пространств в мегабайте.
Страницы и пространства это основа структуры данных физической базы данных SQL Server. Сервер MS SQL использует различные типы страниц, некоторые следят за выделенным пространством, а некоторые содержат пользовательские данные и индексы. Страницы, которые отслеживают выделенное пространство, содержат плотно сжатую информацию. Это позволяет MS SQL Server эффективно помещать их в память для легкого просмотра.
Сервер SQL использует два типа пространств:
- Пространства, которые хранят страницы от двух и более объектов, называемые смешанными. Каждая таблицы начинается как смешанное пространство. Вы используете смешанное пространство главным образом для страниц, которые хранят пространство и содержат маленькие объекты.
- Пространства, которые имеют все 8 страниц выделенных одному объекту, называемый однородным пространством. Они используются, когда таблице или индексу надо более 64 кб пространства.
Первое пространство для каждого файла является смешанным и содержит страницы заголовка файла, следующие по три выделенные страницы. Сервер выделяет эти смешанные пространства, когда вы создаете основной файл данных и использует эти страницы для своих внутренних задач. Страница заголовка файла – содержит атрибуты файла, такие как имя базы данных, которая хранится в файле, файловая группа, минимальный размер, размер приращения. Это первая страница в каждом файле (Страница 0).
Страница свободного пространства (PFS) – это выделенная страница, содержащая информацию о свободном пространстве доступном в файле. Эта информация хранится в странице 1. Каждая такая страница может простираться на 8000 смежных страниц, что приблизительно 64мб данных.
Журнал транзакций захватывает всю необходимую информацию о происходящих на сервере изменениях для восстановления базы данных в момент системной ошибки и для обеспечения целостности данных. О журнале мы будем говорить чуть позже, а сейчас необходимо понимать, что это отдельный файл, который требует дискового пространства.
Теперь рассмотрим, как можно удалять созданную базу данных, ведь нужно иметь возможность удалить тестовую базу данных, которую мы будем создавать для тестирования:
Нельзя удалять базу данных если она:
- Базу данных, которая открыта для чтения или записи любым пользователем, поэтому при удалении вы также не должны быть к ней подключены. Лучше всего подключиться к базе данных master;
- Базу данных, которая опубликовала любую свою таблицу как часть репликации SQL Server
- Системную базу данных
Давайте рассмотрим простейший пример создания и удаления базы данных. Для создания базы данных достаточно написать:
Все остальные параметры являются не обязательными. Попробуем создать базу данных с именем TestDatabase и удалить. Сначала создадим базу:
И тут же ее удалим:
Эти команды нужно выполнять по отдельности. Одновременно выполнять нельзя, иначе сервер вернет ошибку. Рекомендую тестировать примеры. После этого тестовые базы данных можно удалять.
Теперь посмотрим, какие еще возможности дает нам команда создания базы данных. Но сначала вы должны знать, что имя базы данных может иметь не более 128 символов, если вы явно указываете логическое имя файла журнала. Я считаю, это вполне достаточно. Если логическое имя журнала не задано, то размер сокращается до 123 символов. Это связано с тем, что логическое имя журнала также имеет ограничение в 128 символов и если оно не указано, то в качестве имени используется имя базы плюс суффикс _log. Самое интересное, что суффикс занимает четыре символа, а 128-4=124. Почему Microsoft ограничивает имя до 123, для меня остается загадкой. Быть может, составители документации разучились считать?
Рассмотрим основные правила, которым должны подчиняться имена баз данных и объектов в ней:
- Первый символ должен быть буква a-z, A-Z.
- После первого символа может быть буква, цифра или символ _, @, или #.
- Идентификаторы, начинающиеся с символов, имеют специальное назначение:
- Идентификаторы, начинающиеся с символа @, являются локальными переменными или параметрами.
- Идентификаторы, начинающиеся с символа #, являются временные таблицы или процедуры.
- Идентификаторы, начинающиеся с символа ##, являются глобальными временными объектами.
Эти правила относятся ко всем именам – базы данных, таблиц, полей и так далее. С некоторыми из них мы еще будем не раз встречаться на практике, как например, создание временных объектов.
Если необходимо указать дополнительные параметры, то после оператора CREATE DATABASE нужно указать ON и далее в круглых скобках идут параметры:
Если какие-то параметры не заданы, то их значения берутся по умолчанию. Пока мы не будем рассматривать возможные параметры, потому что они одинаковы для базы данных и журнала транзакций, и нужно рассмотреть, как описывается журнал.
Журнал транзакций это файл, в котором сохраняется вся активность в отношении базы данных. Например, вы выполняете запрос на обновление данных. В общем виде сервер выполняет следующие действия:
- Если явно не указано, то сервер автоматически создает новую транзакцию;
- Запрос сохраняется в журнале;
- Сервер начинает выполнять обновление;
- Если все изменения прошли успешно, то транзакция помечается как завершенная, и данные считаются обновленными;
- Если произошел сбой сервера, или ошибка обработки данных, то транзакция не завершается. В этом случае, все изменения сделанные незавершенной транзакцией отменяются, как будто ничего не произошло. Если во время транзакции отключилось питание, и данные не успели обновиться, то при следующей загрузке сервер увидит в журнале не завершенную транзакцию и попытается завершить.
Получается, что журнал обеспечивает целостность данных. Посмотрим на файловую систему NTSF. Это в своем роде тоже база данных. Если какой-либо файл нарушен и не читаемый, то он только нарушает целостность файловой системы и его необходимо удалить. Программа сканирования scandisk проверяет все транзакции с файловой системой и быстро находит незавершенные операции, отменяя их или завершая.
Вы должны уделять внимание не только корректности и оптимальности создания базы данных, но и журналу транзакций.
Теперь давайте, рассмотрим, какие параметры можно указывать при создании файлов базы данных и журнала транзакций:
- NAME – логическое имя, которое будет отображаться в SQL сервере;
- FILE NAME – физическое расположение и имя файла;
- SIZE – начальный размер файла. Это начальное значение, которое может увеличиваться по мере надобности.
- MAXSIZE – максимальный размер файла. Чтобы файл не имел ограничений, необходимо указать в этом параметре UNLIMITED вместо реального значения. Но я рекомендую указывать такое значение, которое не сможет переполнить весь жесткий диск.
- FILEGROWTH – на сколько должен увеличится размер файла, если текущего размера не достаточно. Это значение можно указывать как в реальных значениях (мегабайты) так и в процентном отношении к текущему размеру. Увеличение должно быть достаточным, чтобы эта операция выполнялась как можно реже.
Давайте теперь рассмотрим несколько примеров, которые позволят закрепить все вышесказанное. В листинге 1.2 показан пример, в котором описывается параметры файла данных и параметры журнала. Если какой-то параметр не указан явно, то его значение берется по умолчанию.
Листинг 1.2. Создание базы данных с описанием параметров файлов
Посмотрим, что делает вышеуказанный сценарий. В первой строке мы указываем, что необходимо создать базу данных с именем TestDatabase. Затем идет ключевое слово ON, и в круглых скобках указываются параметры файла базы данных. Мы указываем пять параметров: логическое имя (NAME), физическое расположения файла данных (FILENAME), начальный размер устанавливаем в 10 мегабайт (SIZE), максимальный размер ограничивается размером в 1000 гигабайт (MAXSIZE), а в качестве приращения указываем всего лишь 5 мегабайт. Этого достаточно только для тех баз данных, где добавление новых записей происходит не часто.
Для часто изменяемых баз я рекомендую указывать большое приращение для предотвращения частого авто увеличения. Это облегчит административные задачи и уменьшит фрагментацию файла и избавляет сервер от лишнего расширения файлов базы. Приращение относиться как к файлу базы данных, так и к файлу журнала.
Если база данных не изменяется в размере (не добавляются новые данные), но очень часто происходит изменение уже существующих данных, то приращение для файла данных можно сделать небольшим (5-10 мегабайт, зависит от размера одной записи таблицы). При этом файл журнала должен иметь большее приращение и лучше всего в процентах от существующего размера. Исходя из практики, я бы порекомендовал значение 10%, но в зависимости от задачи, значение можно скорректировать.
Если данные базы используются только как справочник и изменяются очень редко, то размер приращения базы и журнала можно сделать минимальным. Запросы на выборку данных не сохраняются в журнале транзакций, поэтому можно ограничиться увеличением в 1 мегабайт. Но при этом, значение приращения желательно сделать больше размера 100 строк данных. Размер строки можно примерно рассчитать по размеру всех полей самой большой таблицы.
Если вы используете автоматическое увеличение, то лучше всего указать максимальный размер. Это позволит вам предотвратить заполнение базой данных всего жесткого диска.
Приращение для файла данных можно указывать и в процентах, например:
Вернемся к нашему примеру из листинга 1.2. После задания параметров файла данных, стоит ключевое слово LOG ON, после которого идет описание параметров файла журнала транзакций. Тут все примерно то же самое, только приращение идет в процентах (может быть и в мегабайтах). Это очень удобно, потому что журнал пополняется не только при добавлении данных, но и при изменении. Приращение в процентах позволяет уменьшить количество операций по увеличению размера файла.
С помощью команды создания базы данных, можно подключать уже существующие файлы. Не путайте с восстановлением данных, когда база восстанавливается из резервной копии. Подключение – это создание базы данных из существующего mdf файла, а не резервной копии. Файл журнала в этом случае желателен, но не обязателен. Более подробно о подключении и отключении базы можно узнать в разделах 1.1.2 и 4.12.
Когда создается новая база данных, она дублирует базу данных model. Любые опции и настройки в базе данных model копируются в новую базу данных. Если вам необходимо создать несколько схожих баз или все баши базы данных должны обладать схожими свойствами, то можно внести необходимые изменения в model.
В одной из баз данных, которую мне пришлось однажды оптимизировать, я нашел очень интересное решение. В базе данных model была создана процедуры создания резервной копии и восстановления. Таким образом, все новые базы получали в свое распоряжение готовый код, который достаточно просто подкорректировать и он будет готов использованию.
Не смотря на удобство использования model, я не рекомендую перегружать его возможностями, особенно таблицами с данными. Если эти таблицы в новой базе не найдут своего применения, то они лягут на нее лишним грузом, да и время создания базы неоправданно увеличиться.
Напоследок отметим одно замечание. При создании базы данных, нельзя указывать только параметры журнала, без описания файла данных. Это значит, что следующий запрос завершиться ошибкой:
В данном случае, указание файла данных является необходимым. Как минимум, вы должны указать для файла его логическое и физическое имя. Остальные параметры описания файла не обязательны и для них будут взяты значения по умолчанию:
Файловые группы
Если ваш сервер содержит несколько дисков, вы можете расположить определенные объекты и файлы на индивидуальный диск, группируя ваши файлы базы данных в файловые группы. Файловая группа – это несколько файлов, сгруппированные под определенным именем. Сервер SQL включает одну файловую группу, которая по умолчанию называется как PRIMARY. Вы можете создавать дополнительные файловые группы во время, и после создания базы данных.
С помощью файловой группы вы можете располагать определенные объекты в определенных файлах, которые могут находиться на разных дисках, но сервером SQL будут восприниматься как одно целое пространство для хранения данных. Системный администратор может резервировать и восстанавливать индивидуально файлы или файловые группы, что очень удобно при больших размерах базы данных для эффективной стратегии резервирования и копирования.
Распределение файлов на несколько физических дисков позволяет повысить производительность базы данных. У каждого диска свой контроллер и каждый из них может параллельно производить операции чтения/записи.
Использование файловых групп – это новая и очень мощная технология дизайна баз данных, которую реализовала Microsoft, за что им низкий поклон и уважение. Вы должны четко понимать структуру вашей базы данных, данные, транзакции и запросы уже на этапе проектирования для определения лучшего пути для расположения файлов и индексов в специфичные файловые группы.
Файлы журнала не являются частью файловых групп, и управление ими происходит отдельно от файлов данных. В группы можно объединять только файлы данных.
Сервер SQL поддерживает следующие два типа файловых групп:
- Основанная файловая группа, которая содержит системные таблицы и основные файлы данных.
- Файловые группы определенные пользователем, которые содержат любые файловые группы, которые указаны с использованием ключевого слова FILEGROUP.
Когда вы создаете базу данных, основная файловая группа автоматически становиться группой по умолчанию. Файловая группа по умолчанию получает все новые таблицы, индексы и файлы, для которых файловая группа не указана. Если ваша база данных содержит более чем одну файловую группу, рекомендуется изменить группу по умолчанию, чтобы ею была группа определенная вами. Это позволит изолировать основную файловую группу, которая содержит системные таблицы и обезопасить от разрушения из-за пользовательских таблиц, за счет того, что пользовательские данные будут попадать в пользовательскую файловую группу.
Если файловая группа по умолчанию становится основной, установите корректный размер. Если в файловой группе закончится свободное пространство, вы не сможете добавлять какую-либо информацию в системные таблицы. Если пользовательская файловая группа не сможет увеличиваться в размере, то добавлять информацию можно будет только в пользовательские таблицы, расположенные в этой группе.
Итак, одна база данных в MS SQL Server может состоять из нескольких файлов данных, файловых групп или журналов. Это очень удобно, когда необходимо распределить нагрузку между несколькими жесткими дисками. Давайте рассмотрим эту возможность и увидим, какие преимущества она дает и как их использовать.
По умолчанию на сервере уже существует файловая группа PRIMARY. Все создаваемые базы данных попадают в нее, в том числе и файлы баз данных, которые мы уже создали в этой главе. Рекомендуется, чтобы в этой файловой группе были все системные таблицы SQL сервера.
Вы можете создать новые файловые группы и в них размещать пользовательские таблицы. Основная файловая группа может быть расположена на одном физическом диске, а группа с пользовательскими таблицами может быть на другом диске. Таким образом, два диска могут читать данные параллельно. Только это уже позволит повысить производительность, ведь при обращении к пользовательским данных очень часто необходимо обратиться и к системным таблицам. Это происходит незаметно для пользователя, но если чтение и запись будет происходить параллельно, то сервер сможет обрабатывать больше запросов со стороны пользователя. В наше время жесткий диск является наиболее слабым местом (это единственное устройство, не считая съемных носителей, которое остается механическим, а не электронным).
Помимо этого, сервер позволяет производить резервное копирование определенных файловых групп. Допустим, что у вас в базе данных две таблицы, которые имеют очень большой размер, но одна изменяется часто, а вторая нет. Резервировать обе таблицы с одинаковой частотой нет смысла. Если поместить их в разные файловые группы, то группу с часто изменяемой таблицей можно резервировать хоть каждый час, а таблицу с редко изменяемыми данными можно резервировать по мере изменения данных.
Чтобы еще больше оптимизировать процесс резервирования, можно в одну группу файлов поместить часто изменяемые таблицы и резервировать эту группу с больше частотой, чем ту группу, в которой находятся менее изменяемые таблицы. Более подробно о резервировании мы поговорим в разделе 4.10.
Итак, давайте создадим базу данных, в которой будет две файловые группы: обязательная PRIMARY (в нее поместим два файла) и пользовательская FILEGR1 (в ней разместим 3 файла). Код создания можно увидеть в листинге 1.3.
Листинг 1.3. Создание базы данных с файловыми группами
Перед описанием первого файла, мы явно указываем, что он будет создан в основной файловой группе (ON PRIMARY). После этого в круглых скобках через запятую идет описание двух файлов. Потом создается новая файловая группа DBGroup1 с помощью оператора FILEGROUP:
И теперь идет описание файлов для этой файловой группы. Обратите внимание, что каждый файл находиться на своем жестком диске. Это позволит добиться параллельного чтения записи в каждый файл. На рисунке 1.1 вы можете видеть окно свойств созданной базы данных с несколькими файлами (окно программы Enterprise Manager). В последней колонке списка показана файловая группа файла данных. Единственное, на рисунке все файлы находятся на одном диске, потому что у меня в компьютере нет необходимого количества разделов, потому что у меня не сервер, а простой ноутбук. Но на рабочих серверах лучше устанавливать по несколько дисков и распределять между ними нагрузку.
Напоминаю, что желательно изменять файловую группу по умолчанию. Если ваша база данных имеет много файловых групп для разных типов данных, назначьте одну пользовательскую в качестве группы по умолчанию.
Подключение базы данных
Когда подключение удобнее и даже выгоднее резервирования? Недавно я столкнулся с такой проблемой, что настройки репликации не попадают в резервную копию. Это оказалось серьезной проблемой. У меня на работе есть два офиса, который находятся в разных концах города и не соединены через Интернет. Чтобы производить репликацию, базу данных удаленного офиса нужно перевозить в главный офис, но через резервную копию этого не получилось. База данных восстанавливается, как будто никаких настроек репликации не было.
Первое, что приходит в голову после такого разочарования – таскать жесткий диск или весь системный блок для репликации в основной офис. Это, конечно же, не очень удачный выход. И вот однажды мы попытались отключить базу данных на удаленном сервере, привести в центральный офис и подключить. Настройки репликации сохранились!
Для отключения базы данных используется системная процедура sp_detach_db. С процедурами мы пока еще не знакомы и это тема 3-й главы, поэтому пока просто выполните следующую команду для отключения:
Про отключение баз данных мы поговорим в разделе 4.12. Там же процедура sp_detach_db будет рассмотрена более подробно.
Посмотрим, как можно подключить файл базы данных к серверу с помощью оператора CREATE DATABASE:
В первой строке указаны ключевые слова CREATE DATABASE, после которых указывается имя подключаемой базы данных. Ключевое слово ON PRIMARY означает создание в основной файловой группе. После этого в круглых скобках указывается путь к существующему файлу данных. И в последней строке указываем FOR ATTACH, то есть для подключения.
Обратите внимание, что имя подключаемой базы отличается от имени базы, которую мы отключали. Раньше имя было TestDatabase, а после подключения оно превратилось в Archive. Таким образом, мы смогли переименовать уже существующую базу данных. С помощью оператора ALTER DATABASE, который используется для редактирования параметров (см. разд. 1.3) базы переименование невозможно.
Сопоставление
У нас остался еще один параметр, который мы не рассмотрели – это Collation (сопоставление). Что это такое? У начинающих администраторов он вызывает страх, а у опытных – уважение. С помощью Collation можно указать раскладку (кодировку), которая будет использоваться по умолчанию для заданной базы данных. В MS SQL Server существует три способа задать раскладку (кодировку):
- Для каждого поля в отдельности. Вы можете указать кодировку конкретного поля таблицы. Если у поля не указана кодировка, то будет взято значение, указанное для базы данных;
- Для базы данных в целом. Указанная кодировка для базы данных будет использоваться по умолчанию для всех полей таблиц, если не указано иного. Если при создании базы данных параметр Collation не задан, то будет использоваться значение по умолчанию, указанное для сервера баз данных в целом.
- Глобальная кодировка. Это значение задается во время установки MS SQL Server и по умолчанию устанавливается в соответствии с региональными настройками ОС сервера.
Самый простой вызов команды:
Описание кодировки ставиться после описания файлов базы данных и журнала. То есть, если вы описывали файлы, то параметр COLLATE должен быть в самом конце, как показано в листинге 1.4.
Листинг 1.4. Создание базы данных с описанием параметров файлов и кодировки
Чтобы определить возможные значения раскладки, можно выполнить следующий запрос:
На данном этапе не будем останавливаться на том, что здесь происходит, потому что запросы SELECT это тема второй главы. Замечу только, что в результате на экране вы увидите таблицу из двух колонок name (имя) и description (описание).
Определенная раскладка может быть назначена только текстовым полям. Это вполне логично, ведь числовые поля и даты содержать только числа, которые для любого национального языка одинаковы.
SQL-server и база данных на внешнем носителе
(1) Подключи внешнее хранилище как сетевой диск.
Назначь ему локальное имя, к примеру диск D:
и скорми его SQL серверу.
Вот только в таком случае скорость работы СУБД будет очень низкая и возможно очень неустойчивая.Когда-нибудь пробовал скинуть файл по сети?
Замечал, что тот же самый файл между папками одного диска пролетает гораздо быстрее.
Вот и тут тот же самый случай.- Скопировать ссылку
- Перейти
- Скопировать ссылку
- Перейти
(3) А точно, точно.
SQL сервер не дает использовать сетевые диски. Скорее всего такое поведение программы объяснимо.Вы пишете «мне как раз и необходимо провести сравнительный анализ возможности такого подключения»
Наверно ни чего не выйдет.Если очень нужны подобные тестовые результаты. Попробуйте найти старинный IDE диск или подключите по USB флэшку.
Тормоза будут обеспечены и соответственно, примерные результаты.- Скопировать ссылку
- Перейти
- Скопировать ссылку
- Перейти
- Скопировать ссылку
- Перейти
- Скопировать ссылку
- Перейти
Я там подчеркнул в конце ключевые слова. Правда, речь в статье идет о SQL Server 2014. А у меня — 2008.
Вопрос к уважаемым пользователям SQL Server 2014 — можно ли при создании БД указать путь на сетевой диск? Или его (если верить данной статье) можно только изменить на диск сетевой?
Попробуйте в Management Studio создать новую БД (только начальный этап пройти, саму базу можно и не создавать) и указать место хранения базы на сетевой диск. Получится или нет?
- Скопировать ссылку
- Перейти
- Скопировать ссылку
- Перейти
- Скопировать ссылку
- Перейти
- Скопировать ссылку
- Перейти
(9) Frogger1971, Так-то оно проще, только вот есть одно но.
У меня есть два физических сервера, они разнесены территориально и соединены оптикой. Имеют свои имена и ip-адреса в одной подсети.
Задача:
1. Иметь рабочую 1С-станцию (назовем её Рабочий сервер), с которой работают все через терминальный доступ.
2. Должна быть резервная 1С-станция (назовем её Чистый сервер), с которой возможна работа через терминалку в экстренном случае.
3. При работе 1С на Рабочем сервере время от времени выполняет выгрузки «правильных» данных в 1С на Чистом сервере.
4. В случае наступления экстренного случая, Рабочий сервер обесточивается, терминальные соединения разрываются. Скрипт подключения терминалки, не видя Рабочего сервера, подключает всех на терминалку Чистого сервера, тот уже работает со своей 1С, своим SQL и своими «правильными» БД.Состав программ на обоих серверах одинаков. SQL server, 1C-сервер с толсто/тонкими клиентами, терминальный сервер.
Вероятность наступления экстренного случая минимальна, но она не равна нулю. Посему хотелось бы несколько разгрузить Рабочий сервер вынесением SQL на другой сервак.
- Скопировать ссылку
- Перейти
- Скопировать ссылку
- Перейти
- Скопировать ссылку
- Перейти
- Скопировать ссылку
- Перейти
ситуация 1. упал рабочий сервер, работает чистый сервер с базами что лежат на чистом сервере, базы на сетевом диске не доступны.
ситуация 2. упал чистый сервер, рабочий сервер не может работать так как нет sql. то есть какой смысл в сетевом пути, можно просто отправлять резервные копии по сети. все равно для того чтоб все заработало нужно будет ручками поработать.- Скопировать ссылку
- Перейти
(16) AlexInqMetal, Александр, Вы немного не поняли назначение серверов. В данном случае не строится отказоустойчивая система серверов. Тут несколько иное. Гхм.
В общем, недавний случай из жизни дружественной компании, работающей неподалеку от нас — пришли ребята в черных масках, нашли кабинет бухгалтерии, помахали автоматами, узнали где стоит сервер, изъяли до выяснения обстоятельств. В результате, сервер так и не вернули, сисадмин заказал новый сервер, еще неделю восстанавливали файлы и БД из бэкапов. Что пытались найти «нехорошие» люди в масках тоже непонятно, но происки конкурентов порой заходят далеко..Так вот, подобную ситуацию и необходимо исключить.
Рабочий сервер расположен в недосягаемом месте и он работает, пока не поступит сигнал тревожный. Как только, так сразу Рабочий отключается (на нем хранятся файлы БД, которые не надо светить, помните?) и управление передается Чистому, у которого в SQL лежат свои «красивые» БД и ссылки на несуществующий к тому времени путь к файлам с Рабочего сервера. Эти ссылки — есть скользкое место в данной схеме, но пусть для всех это будут ссылки на старую БД, которой нет уже давно.
Работа продолжается на Чистом сервере и в крайнем случае изъятию подвергнется именно он.- Скопировать ссылку
- Перейти
- Скопировать ссылку
- Перейти
- Скопировать ссылку
- Перейти
(19) foxey, скорее всего такая разгрузка приведет только к падению производительности, а не наоборот. При небольшом количестве активных пользователей (встречал цифру до 100) shared memory шустрее.
How can I find out the location of my (localdb) SQL Server 2012 database and back it up?
How can I find out the physical location of this database. How can I back this up? Can I just make a copy of the files, move these to another location and start the database again.
Here is my connection string:
8 Answers 8
It is quite confusing for people who touch with Entity Framework the first time.
If you use Code First, an mdf file is generated at %USERPROFILE% (e.g. C:\Users\<username> ).
If you use Database First, you create a database under SQL Server Object Explorer (not Server Explorer!), an mdf file will be generated at %LOCALAPPDATA%\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB .
By default, LocalDB database creates “*.mdf” files in the C:/Users/»username» directory.
Are you saying you can see it listed in SQL Server Management Studio? Right click on DataBase -> Properties -> Files will tell you where on your hard disk it lives. If you backup the mdf, be sure to back up the ldf too.
Alternatively, you can right click on the DB, and choose Tasks -> Backup . This will make a a single .bak file for you, and you don’t need to worry about the mdf/ldf.
5) Место нахождения файлов базы данных ms sql server. Системные базы данных. Подключение к базе данных, указывая путь файла базы данных.
Место нахождения файлов базы данных MS SQL server задается при ее создании.
Системная база данных
master
В этой базе данных хранятся все данные системного уровня для экземпляра SQL Server.
tempdb
Рабочее пространство для временных объектов или взаимодействия результирующих наборов.
msdb
Используется агентом SQL Server для планирования предупреждений и задач.
model
Используется в качестве шаблона для всех баз данных, создаваемых в экземпляре SQL Server. Изменение размера, параметров сортировки, модели восстановления и других параметров базы данных model приводит к изменению соответствующих параметров всех баз данных, создаваемых после изменения.
resource
Системные объекты физически хранятся в базе данных resource, но логически отображаются в схеме sys любой базы данных.
Строка подключения strConn определяет место нахождения базы данных и параметры подключения к этой базе данных. Для определения строки подключения в окне Обозреватель серверов выделите базу данных и откройте окно свойств. В окне свойств скопируйте значение параметра Строка подключения и вставляйте эту строку в код обработчика Form1_Load.
private void Form1_Load(object sender, EventArgs e)
string strConn = «Data Source=(local)\\SQLEXPRESS; » +
«Integrated Security=True; Pooling=False»;
string strCmd = «SELECT Name, Post, Oklad, BirthDay FROM Person»;
SqlDataAdapter adapPerson = new SqlDataAdapter(strCmd, strConn);
DataTable Person = new DataTable();
adapPerson.Fill(Person);
6) Команда select языка sql. Определение вычисляемых полей. Операнд where команды select. Формирование условий отбора записей с помощью операций сравнения.
Команда SELECT языка SQL.
Команда SELECT предназначена для отбора нужных записей.
Команда SELECT имеет следующий упрощенный формат:
FROM <Список таблиц>
WHERE <Условия отбора записей>
В этой команде обязательно должен быть указан только список полей и операнд FROM, а остальные операнды могут отсутствовать. В списке операнда FROM перечисляются имена таблиц, из которых отбираются записи. Этот список должен содержать как минимум одну таблицу. Например, для отбора всех записей таблицы следует выполнить SQL-запрос:
SELECT Name, Oklad, Post FROM Person
Определение вычисляемых полей.
Кроме физических полей таблиц, в SELECT-запрос можно включать вычисляемые поля. Для получения вычисляемого поля в списке полей указывается не имя этого поля, а выражение, по которому рассчитывается его значение. Например,
SELECT Name, Post, Oklad, Oklad*1.5 AS Зарплата
Здесь через ключевое слово AS вводится имя вычисляемого поля. Ввод имени поля не обязателен, но использование имени поля позволяет представлять информацию более наглядно.
Операнд WHERE команды SELECT. Формирование условий отбора записей с помощью операций сравнения.
На практике требуется отобрать записей, которые удовлетворяют каким-либо условиям отбора записей. Это условие отбора записей задается с помощью операнда WHERE.
Критерий отбора записей представляет собой логическое выражение, в котором можно использовать следующих операторов сравнения.
= — равно; <= — меньше или равно;
> — больше; <> — или != — не равно;
< — меньше; !> — не больше;
>= — больше или равно; !< — не меньше.
В качестве примера можно привести следующий SQL-запрос.
SELECT Name, Oklad FROM Person
WHERE Oklad > 20000;
Эта команда определяет получение списка сотрудников, имеющих оклад более 20000 рублей.
private void sql_load2_Click(object sender, EventArgs e)
/*string str = «SELECT Id, Name, Age, Position, Experience, Salary FROM Person Where Salary between 37000 and 50000»;