Место хранения базы данных
Область применения: SQL Server Analysis Services 
Azure Analysis Services Power BI Premium
Часто возникают ситуации, когда администратор базы данных SQL Server Analysis Services (dba) хочет, чтобы определенная база данных была размещена за пределами папки данных сервера. Обычно это связано с производственной необходимостью (например, чтобы повысить производительность или расширить хранилище). В таких ситуациях свойство базы данных DbStorageLocation позволяет SQL Server Analysis Services dba указать расположение базы данных на локальном диске или сетевом устройстве.
Свойство DbStorageLocation базы данных
Свойство базы данных DbStorageLocation указывает папку, в которой SQL Server Analysis Services создает и управляет всеми файлами данных и метаданных базы данных. Все файлы метаданных хранятся в папке DbStorageLocation , за исключением файла метаданных базы данных, который хранится в папке данных сервера. При изменении свойства DbStorageLocation базы данных следует руководствоваться следующими двумя важными соображениями.
- В свойстве базы данных DbStorageLocation должен быть задан путь к существующей папке в формате UNC или пустая строка. Пустая строка по умолчанию указывает на папку данных сервера. Если папка не существует, при выполнении команды Create, Attachили Alter возникнет ошибка.
- Свойство DbStorageLocation базы данных не может указывать на папку данных сервера или любую вложенную в нее папку. В противном случае при выполнении команды Create, Attachили Alter возникнет ошибка.
При использовании сети хранения данных (SAN), сети на основе iSCSI или локально подключенного диска рекомендуется указывать путь в формате UNC. Указание пути в формате UNC к сетевой папке или любым хранилищам с высокой задержкой сделает установку неподдерживаемой.
Сравнение свойств DbStorageLocation и StorageLocation
СвойствоDbStorageLocation указывает на папку, в которой находятся все файлы данных и метаданных, относящиеся к базе данных, тогда как свойство StorageLocation указывает на папку, в которой находятся одна или несколько секций куба. СвойствоStorageLocation можно задать независимо от свойства DbStorageLocation. Это решение SQL Server Analysis Services dba на основе ожидаемых результатов, и многократное использование одного свойства или другого будет перекрываться.
Использование свойства DbStorageLocation
Свойство DbStorageLocation базы данных должно включаться в команду базы данных Create в последовательности команд Detach/Attach , Backup/Restore или в команде Synchronize . Изменение свойства DbStorageLocation связано со структурными изменениями объекта базы данных. Это означает, что все метаданные будут созданы повторно, а данные повторно обработаны.
Место хранения базы данных не следует изменять командой Alter . Вместо этого рекомендуется использовать последовательность команд «Отсоединить/присоединение базы данных» (см. раздел «Перемещение базы данных служб Analysis Services», «Присоединение и отсоединение баз данных служб Analysis Services»).
Архитектура хранения данных в Microsoft SQL Server
Приветствую Вас на сайте Info-Comp.ru! Сегодня мы с Вами поговорим о том, как организовано хранение данных в Microsoft SQL Server на физическом уровне, в частности Вы узнаете, из каких файлов состоит база данных в SQL Server и как внутренне устроены эти файлы, т.е. как фактически хранятся данные.
Файлы базы данных SQL Server
Общая архитектура хранения
Данные в базе данных Microsoft SQL Server, как и в любой другой базе данных, физически хранятся в виде обычных файлов операционной системы, при этом в SQL Server внешне это выглядит, на самом деле, достаточно понятно.
Дело в том, что существует всего 3 типа файлов, которые могут существовать у базы данных в SQL Server. При этом, конечно же, каждый файл относится к какой-то конкретной базе данных, иными словами, у каждой базы данных есть свои индивидуальные файлы.
Стоит отметить, что в простейшем виде большинство баз данных, реализованных в SQL Server, будет состоять всего из двух файлов (mdf и ldf), именно это и создаёт понятную внешнюю картину физического хранения данных в Microsoft SQL Server.
По мере увеличения данных, увеличения нагрузки на базу данных, безусловно потребуется оптимизация хранения данных, за счёт их распределения по нескольким дискам, поэтому в крупных базах данных появляются дополнительные файлы данных (ndf), благодаря которым мы можем распределить данные одной базы данных на несколько дисков.
Типы файлов в SQL Server
- Файлы данных – это файлы, в которых хранятся сами данные. Такие файлы бывают двух типов:
- Первичный файл данных – имеет расширение .mdf (Master Data File). Данный файл присутствует в любой базе данных. Кроме данных, он еще содержит сведения, необходимые для запуска базы данных, и ссылки на другие файлы в базе данных;
- Вторичный файл данных – имеет расширение .ndf (Not Master Data File). Данные типы файлов база данных может и не содержать, они создаются дополнительно к первичному файлу. С помощью именно таких файлов мы можем распределять данные на несколько дисков.
По умолчанию файлы базы данных располагаются в каталоге, который Вы указали в момент установки SQL Server на этапе настройки ядра в поле «Каталог пользовательской базы данных» для файлов данных, и в поле «Каталог журналов пользовательской базы данных» для журнала транзакций.
Однако при создании базы данных, или добавлении файла к базе данных, Вы можете указать свой путь к каталогу, в котором хранить создаваемый файл.
Файловые группы
В Microsoft SQL Server есть возможность объединять файлы данных в файловые группы.
Файловая группа в SQL Server – это логический контейнер, который объединяет несколько файлов данных.
Файловые группы нужны нам в основном для более гибкого управления хранением данных в SQL Server. Дело в том, что с помощью файловых групп мы можем одни таблицы хранить в одних файлах, а другие в других, иными словами, благодаря файловым группам мы можем распределять таблицы по разных файлам и по разным дискам.
Например, мы знаем, что одна таблица у нас будет очень большой и на ее хранение потребуется несколько дисков, поэтому ее (т.е. только одну эту таблицу), мы можем поместить в отдельную файловую группу, в которую добавить несколько файлов, каждый из которых будет храниться на отдельном диске. Все остальные таблицы мы будем хранить в другой файловой группе, т.е. уже в других файлах и, соответственно, на других дисках.
Без файловых групп мы этого сделать не можем, т.е. мы можем, конечно же, создать дополнительные файлы данных, но распределять данные по этим файлам будет сам SQL Server, т.е. на это мы уже не можем повлиять.
Таким образом, при создании таблиц мы можем указать, в какой файловой группе создавать эту таблицу. Если в базе данных создавать объекты без указания файловой группы (в большинстве случаев так и делается), к которой они относятся, они создаются в файловой группе по умолчанию.
По умолчанию в SQL сервере создана файловая группа PRIMARY, и если Вы не создавали дополнительных файловых групп, то все объекты базы данных будут храниться именно в этой файловой группе.
Файловую группу по умолчанию можно переопределить инструкцией ALTER DATABASE, т.е. можно создать файловую группу и назначить ее файловой группой по умолчанию, при этом стоит отметить, что все системные объекты хранятся в файловой группе PRIMARY, а не в новой файловой группе по умолчанию. Иными словами, файловая группа PRIMARY – это особенная файловая группа, в которой хранятся системные объекты и которую нельзя удалить.
Также стоит отметить, что один файл данных может входить в состав только одной файловой группы.
Примечание! Файлы журнала транзакций не могут входить в файловые группы.
Рекомендации по работе с файлами и файловыми группами
- Для всех баз данных рекомендуется создать дополнительную файловую группу и сделать ее файловой группой по умолчанию, чтобы в файловой группе PRIMARY и в первичном файле хранились только системные таблицы и объекты;
- Чтобы увеличить производительность, разносите файлы и файловые группы по нескольким физическим дискам, при этом объекты, активно конкурирующие за свободное пространство, поместите в разные файловые группы;
- Используйте файловые группы для целенаправленного размещения объектов на конкретных физических дисках, например, размещайте большие и быстрорастущие таблицы на отдельных дисках;
- Если несколько таблиц очень часто используются в одних и тех же запросах с соединениями, можно поместить эти таблицы в разные файловые группы и тем самым увеличить производительность, так как для поиска соединяемых данных можно будет использовать параллельный ввод-вывод;
- Часто используемые таблицы и некластеризованные индексы, относящиеся к ним, можно помещать в разные файловые группы и на разные диски, что также увеличит производительность за счет параллельного ввода-вывода;
- Не помещайте файлы журнала транзакций на тот же физический диск, где находятся другие файлы и файловые группы. Иными словами, файл журнала транзакций по возможности помещайте на отдельный, достаточно быстрый диск.
Устройство файлов базы данных SQL Server
Мы с Вами поговорили о том, как на верхнем уровне хранятся данные в SQL Server, теперь давайте немного поговорим о том, как хранятся данные на более низком уровне, т.е. как организовано внутреннее хранение данных в тех самых файлах данных.
В файлах данных в SQL Server все данные хранятся на страницах, которые группируются в экстенты.
Поэтому давайте чуть более подробно поговорим о страницах и экстентах.
Страницы
Страница – основная единица хранения данных в SQL Server.
Дисковое пространство, выделенное для размещения файлов базы данных (MDF или NDF), логически разделяется на страницы. Иными словами, внутреннее пространство файлов данных разделено на страницы и именно в этих страницах хранятся наши данные.
Все дисковые операции ввода-вывода в SQL Server выполняются на уровне страницы и это означает, что SQL Server считывает или записывает целые страницы данных.
Например, в процессе оптимизации запросов мы очень часто говорим о количестве логических чтений, которые выполняются на уровне запроса, так вот – это количество как раз и представляет собой количество считанных страниц данных.
Если провести аналогию, то файл базы данных в SQL Server (MDF или NDF) представляет собой бумажную книгу, содержимое которой написано на страницах. Иными словами, в SQL Server все строки данных точно так же, как и в бумажной книге, записываются на страницы, которые имеют одинаковый физический размер 8 килобайт.
Основную часть файла данных занимают страницы с данными, как и у книги страницы с содержимым, а на некоторых страницах могут находиться метаданные об этом содержимом, например, как оглавление или алфавитный указатель в бумажной книге.
Как уже было отмечено размер страницы в SQL Server составляет 8 КБ. Это значит, что в одном мегабайте базы данных SQL Server содержится 128 страниц.
Каждая страница начинается с 96-байтового заголовка, который используется для хранения системных данных о странице. Эти данные включают номер страницы, тип страницы, объем свободного места на странице и идентификатор единицы распределения объекта, которому принадлежит страница.
Строки данных заносятся на страницу последовательно, сразу же после заголовка. В конце страницы располагается таблица смещения строк.
Таблица смещения строк содержит одну запись для каждой строки на странице. Каждая запись смещения строк регистрирует, насколько далеко от начала страницы находится первый байт строки. Таким образом, таблицы смещения строк помогает SQL Server быстро находить строки на странице. Записи в таблице смещения строк находятся в обратном порядке относительно последовательности строк на странице.
В следующей таблице представлены типы страниц, которые используются в файлах данных базы данных SQL Server.
Тип страницы Описание Data page Строки с данными, за исключением типов text, ntext, image, nvarchar(max), varchar(max), varbinary(max) и xml. Index page Содержимое индекса. Text/Image Текст/изображение. Типы данных больших объектов: text, ntext, image, nvarchar(max), varchar(max), varbinary(max) и данные xml.
Столбцы переменной длины, когда размер строки данных превышает 8 КБ: varchar, nvarchar, varbinary и sql_variant.Global Allocation Map (GAM) Глобальная карта распределения. На GAM-страницах записано, какие экстенты были размещены. В каждой карте GAM содержится 64 000 экстентов или почти 4 ГБ данных. В карте GAM приходится по одному биту на каждый экстент в покрываемом им интервале. Если бит равен 1, то экстент свободен, если бит равен 0, то экстент размещен. Shared Global Allocation Map (SGAM) Общая глобальная карта распределения. На SGAM-страницах записано, какие экстенты в текущий момент используются в качестве смешанных экстентов и имеют как минимум одну неиспользуемую страницу. В каждой карте SGAM содержится 64 000 экстентов или почти 4 ГБ данных. В карте SGAM приходится по одному биту на каждый экстент в покрываемом им интервале. Если бит равен 1, то экстент используется как смешанный экстент и имеет свободную страницу. Если бит равен 0, то экстент не используется как смешанный экстент, или он является смешанным экстентом, но все его страницы используются. Page Free Space (PFS) Сведения о размещении страниц и доступном на них свободном месте. Index Allocation Map (IAM) Карта распределения индекса. Сведения об экстентах, используемых таблицей или индексом для единицы распределения. Bulk Changed Map (BCM) Карта массовых изменений данных. Сведения об экстентах, измененных массовыми операциями со времени последнего выполнения инструкции BACKUP LOG для единицы распределения. Differential Changed Map (DCM) Карта изменений для разностной резервной копии. Сведения об экстентах, измененных с момента последнего выполнения инструкции BACKUP DATABASE для единицы распределения. Экстенты
Экстент — это набор из 8 физически непрерывных страниц.
Экстенты являются основными единицами организации пространства. Как было отмечено, экстент состоит из восьми непрерывных страниц или 64 КБ. Это означает, что в одном мегабайте базы данных SQL Server содержится 16 экстентов.
Экстенты используются для эффективного управления страницами.
В SQL Server есть два типа экстентов:
- Однородные экстенты (Uniform) – это экстенты, которые принадлежат одному объекту, и все восемь страниц экстента может использовать только этот владеющий объект;
- Смешанные экстенты (Mixed) – это экстенты, которые могут находиться в общем пользовании максимум у восьми объектов. Каждая из восьми страниц в экстенте может находиться во владении разных объектов.
SQL Server до 2016 версии не выделяет целые экстенты для таблиц с небольшими объемами данных. Под новую таблицу или индекс обычно выделяются страницы из смешанных экстентов. Когда таблица или индекс вырастают до восьми страниц, они переключаются на использование однородных экстентов для последующих распределений. Если Вы создаете индекс для существующей таблицы, в которой достаточно строк для создания восьми страниц в индексе, все выделения для индекса будут в однородных экстентах.
Начиная с SQL Server 2016 по умолчанию для большей части распределений в пользовательской базе данных и базе данных tempdb используются однородные экстенты. Это не касается распределений, принадлежащих первым восьми страницам цепочки IAM. Для распределений баз данных master и msdb, и model сохраняется предыдущее поведение.
SQL Server использует два типа карт распределения для выделения экстентов:
- Глобальная карта распределения (GAM) – на GAM-страницах записано, какие экстенты были размещены. В каждой карте GAM содержится 64 000 экстентов или почти 4 ГБ данных. В карте GAM приходится по одному биту на каждый экстент в покрываемом им интервале. Если бит равен 1, то экстент свободен; если бит равен 0, то экстент размещен.
- Общая глобальная карта распределения (SGAM) – на SGAM-страницах записано, какие экстенты в текущий момент используются в качестве смешанных экстентов и имеют как минимум одну неиспользуемую страницу. В каждой карте SGAM содержится 64 000 экстентов или почти 4 ГБ данных. В карте SGAM приходится по одному биту на каждый экстент в покрываемом им интервале. Если бит равен 1, то экстент используется как смешанный экстент и имеет свободную страницу. Если бит равен 0, то экстент не используется как смешанный экстент, или он является смешанным экстентом, но все его страницы используются.
Каждый экстент обладает следующими наборами битовых шаблонов в картах GAM и SGAM, основанными на его текущем использовании.
Текущее использование экстента Настройка битов карты GAM Настройка битов карты SGAM Свободно, в текущий момент не используется 1 0 Однородный экстент или заполненный смешанный экстент 0 0 Смешанный экстент со свободными страницами 0 1 Таким образом, упрощенный алгоритм управления экстентами страниц следующий:
- Для выделения однородного экстента SQL Server производит на карте GAM поиск бита 1 и заменяет его на бит 0;
- Для поиска смешанного экстента со свободными страницами SQL Server производит поиск на карте SGAM бита 1;
- Для выделения смешанного экстента SQL Server производит на карте GAM поиск бита 1, заменяет его на бит 0, а затем устанавливает значение соответствующего бита на карте SGAM равным 1;
- Для освобождения экстента SQL Server устанавливает бит GAM равным 1, а соответствующий бит SGAM равным 0.
Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.
На сегодня это все, надеюсь материал был Вам интересен и полезен, пока!
Где хранятся базы банных MS SQL на жёстком диске?
В общем работаю с SQL в MS Visual Studio. Там в Server Explorer у меня несколько баз данных.
s019. radikal. ru/i630/1203/b9/ebe578e44f9d.jpg
Как найти эти БД на жёстком диске ?Дополнен 11 лет назад
В свойствах нет соответствующего поля
http://s019.radikal.ru/i613/1203/d3/e2874aa98328.jpgДополнен 11 лет назад
Проблема решена, всем спасибо )
Лучший ответ
В sql server’е базы данных хранятся в виде обычных файлов на диске. Как минимум на одну БД приходится таких файлов 2: *.mdf и *.ldf. В первом хранятся сами данные, таблицы, индексы и пр. , а во втором находится т. н. transaction log, в котором находится информация необходимая для восстановления БД.
Остальные ответы
А в свойствах базы данных не пробовал смотреть?
ну поищи через F3 *.sql — у баз данных аналогичное расширение.
Базы данных, ау! Или где хранятся данные MySQL на вашем компьютере
От автора: а что это вы под ногами ищете? Базу данных? Поверьте, скорее все, она никуда не делась, и лежит «целехонькая» на вашем ПК или сервере! Там уже смотрели? Наверное, вы просто не знаете, где хранятся данные MySQL. Об этом мы вам сегодня и расскажем.
Импорт-экспорт данных в MySQL
Чаще всего найти утерянные базы данных нужно для импорта или копирования. К счастью, для этого не обязательно знать место их «дислокации» на дисковом пространстве. Программные интерфейсы для работы с СУБД MySQL имеют встроенные средства переноса и создания копий БД и таблиц.
В наших «широтах» самой востребованной остается оболочка phpMyAdmin, предназначенная для администрирования системы управления БД MySQL. На примере этого веб-приложения мы и рассмотрим встроенные средства, которые позволят нам остаться в неведении, где находится MySQL.
В phpMyAdmin создание копий баз данных происходит через вкладку «Экспорт». Для дублирования БД или таблицы слева в списке выберете нужный для копирования элемент. После этого перейдите в раздел «Экспорт». В окне параметров в подразделе «Способ экспорта» должно быть установлено значение «Обычный». Затем в списке выделите базу данных (таблицу). После чего нажимаете в самом низу окна «Ок».
В результате программа создаст копию и сохранит ее на клиентской стороне. Вся работа с веб-приложением происходит через браузер, поэтому созданный дубликат БД нужно искать в папке загрузок.
Место хранения оригиналов баз
Знание встроенного функционала phpMyAdmin поможет быстро создать копии нужных данных. Но если на компьютере не установлена ни одна из программных оболочек для администрирования СУБД. А вам нужно перенести БД на другой сервер. Или изменить хранящуюся в таблицах информацию. При этом выполнять запросы придется через консоль, где обязательно прописывается путь к БД. Как видите знать, где хранится база данных MySQL, важно и нужно.
Вот еще один пример «из жизни начинающего админа». Предположим, первоначально вы пользовались Denwer. Но в один «непогожий» день вы нечаянно удалили часть файлов локального сервера. В результате чего он больше не запускается, и вы начинаете «рыть» оставшиеся папки в надежде спасти хотя бы базы данных. Но в этом случает также «удобнее» знать, где копать. Вот пример пути, где в веб-сервере хранятся БД: F:\Webserver\usr\local\mysql-5.5\data, где F – это диск, на котором был установлен Denwer.
Для открытия файлов скопированных БД потребуется phpMyAdmin. Если вы установили сервер СУБД без оболочки, тогда по умолчанию место, где хранятся базы данных MySQL, находится здесь: C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data
Файл my.ini
Все описанные методы связаны с осуществлением поиска (в зависимости от варианта использования СУБД) той или иной папки, в которой программы (или сервер) сохраняют созданные базы. Но неужели пользователь никак не может повлиять на это, и самостоятельно указать место на дисковом пространстве MySQL, где хранятся все БД.
Такой вариант настройки был предусмотрен создателями этой системы управления. Все основные параметры задаются в файле my.ini. Он расположен в той же директории, куда установлена и система СУБД.
Данный тип файлов относится к конфигурационным, и открывается с помощью обычного «Блокнота». Чтобы задать «свое» место для всех созданным БД, откройте файл my.ini. Затем найдите в нем раздел [mysqld], и в параметре datadir пропишите путь к нужной папке. После сохранения внесенных изменений (Файл> Сохранить) все существующие базы будут перенесены в указанное вами место файловой системы компьютера или сервера.
Средства SQL
Все описанные выше варианты узнать в MySQL, где лежит БД, являются второстепенными. Потому что СУБД не понимает человеческой речи и письменности. Для общения с ней существует специальный язык SQL. Аббревиатура переводится как язык структурированных запросов (structured query language). То есть с помощью операторов и команд этого языка можно влиять на структуру содержащейся в источнике информации: делать выборки, обновлять данные, вставлять или удалять.
Но не будем слишком забираться в «дремучий лес» профессиональных знаний, нас интересует единственная команда SHOW. Точнее, один из ее вариантов SHOW VARIABLES. Эта команда SQL используется в администрировании для получения служебной информации о конкретном сервере или базе.
SHOW VARIABLES выводит список переменных и их значений из INFORMATION_SCHEMA, которая представляет собой «служебную» БД, в которой содержится информация обо всех базах, подключенных к серверу. Пример запроса: SHOW VARIABLES;
Но из всего этого списка нас интересует лишь одна переменная, с помощью которой можно «узнать» у сервера MySQL, где хранится база. Это переменная basedir, в значение которой записан путь к основной директории. Пример запроса: SHOW VARIABLES LIKE ‘basedir’;
А сейчас прекращайте понапрасну «рыться» в файловой системе своего ПК. Теперь вы точно знаете, как правильно «спросить» у СУБД MySQL, где хранятся базы. Надеемся, нам удалось научить вас этому!