Где хранятся базы данных sql server

How do I find the data directory for a SQL Server instance?

We have a few huge databases (20GB+) which mostly contain static lookup data. Because our application executes joins against tables in these databases, they have to be part of each developers local SQL Server (i.e. they can’t be hosted on a central, shared database server).

We plan on copying a canonical set of the actual SQL Server database files (*.mdf and *.ldf) and attach them to each developer’s local database.

What’s the best way to find out the local SQL Server instance’s data directory so we can copy the files to the right place? This will be done via an automated process, so I have to be able to find and use it from a build script.

15 Answers 15

It depends on whether default path is set for data and log files or not.

If the path is set explicitly at Properties => Database Settings => Database default locations then SQL server stores it at Software\Microsoft\MSSQLServer\MSSQLServer in DefaultData and DefaultLog values.

However, if these parameters aren’t set explicitly, SQL server uses Data and Log paths of master database.

Bellow is the script that covers both cases. This is simplified version of the query that SQL Management Studio runs.

Also, note that I use xp_instance_regread instead of xp_regread , so this script will work for any instance, default or named.

You can achieve the same result by using SMO. Bellow is C# sample, but you can use any other .NET language or PowerShell.

It is so much simpler in SQL Server 2012 and above, assuming you have default paths set (which is probably always a right thing to do):

Где хранятся базы данных 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

1. Click Execute (or hit the F5 key).
2. Review the list of server database files in the query execution results:

Click here to edit

1. Open Netwrix Auditor and navigate to Reports -> Predefined -> SQL Server — State-in-Time -> SQL Server Databases.
2. 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.

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»;

Организация хранения данных в SQL сервере

Несомненно, хранилище данных — один из основ­ных компонентов, определяющих производи­тельность и доступность больших и малых экзем­пляров SQL Server. В условиях возросших вычис­лительных возможностей физических и вирту­альных серверов и поддержки объемной памяти хранилища данных и подсистема ввода-вывода могут оказаться узки­ми местами, снижающими общую пропускную способ­ность. Неприятностей можно избежать, если иметь общее представление о том, как SQL Server использует хранилища данных, и знать основные приемы оптимальной организа­ции хранилищ SQL Server.

Данные и файлы журналов

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

• Файлы данных.В этих файлах хранится информация базы данных. Файлы данных SQL Server представляют собой файлы NTFS с расширением .mdf. Простейшая база данных обычно состоит из одного файла данных, но может состоять и из многих файлов данных, находя­щихся на одном или нескольких дисках.
• Файлы журналов. В этих файлах хранятся транзакции базы данных, что позволяет восстановить базу данных на определенный момент времени. Файлы журналов транзакций SQL Server представляют собой файлы NTFS с расширени ем .ldf. В базе данных может быть много файлов журна­лов, расположенных на одном или нескольких дисках.

Если для создания базы дан­ных используется среда SQL Server Management Studio (SSMS), то файлы данных и журналов хра­нятся на том же диске по умол­чанию. Если не указано иное, то файлы данных и журналов создаются в том же каталоге, что и системные базы данных SQL Server, то есть <диск>:\Рrogram Files\Microsoft SQL Server\MSSQL. MSSQLSERVER\MSSQL\DATA. Например, для экземпляра SQL Server 2014, установленного на диске С, файлы данных и журналов по умолчанию будут находить­ся в каталоге C:\Program Files\ Microsoft SQL Server\MSSQL12. MSSQLSERVER\MSSQL\DATA. Рекомендуется поместить файлы данных и журналов на различные диски. SQL Server записывает все транзакции базы данных в журнал транзакций, поэтому файлы жур­налов удобно располагать на дис­ках с высокой скоростью записи. Файлы данных используются для обслуживания запросов и часто должны выполнять множество операций чтения. При создании базы данных можно указать место­положение файлов данных и жур­налов с помощью команды T-SQL CREATE DATABASE. Чтобы изме­нить местонахождение существую­щих файлов данных и журналов, можно запустить команду ALTER DATABASE с параметром MODIFY FILE. В листинге показан пример переноса файла данных базы дан­ных в другое место.

Не все согласятся с рекомендаци­ей включить режим AutoGrow для баз данных SQL Server. При вклю­чении этой функции для базы данных файлы данных и журна­лов автоматически увеличиваются, если требуется больше места. Этот параметр не допускает оста­новки системы, если места не хва­тает.

И все же AutoGrow следует рассма­тривать как механизм последне­го рубежа защиты. Его не следует использовать в качестве основного метода управления ростом базы данных. Ростом всех файлов данных и журналов следует управлять вручную. Активность базы данных прекращается, когда происходят операции AutoGrow. Частые собы­тия AutoGrow — хороший индика­тор непредвиденного роста дан­ных. Следующая команда пока­зывает, как установить настрой­ку AutoGrow для файлов данных и журналов:

Почти никогда не рекомендуется активировать функцию AutoShrink для базы данных. Как и операции AutoGrow, операции AutoShrink приводят к остановке всех дей­ствий базы данных. Кроме того, администратор не может контро­лировать время запуска AutoShrink. Использование AutoShrink может привести к спирали операций AutoGrow, а затем AutoShrink, а результатом будет снижение про­изводительности базы данных и чрезмерная фрагментация фай­лов. Запустить AutoShrink можно с помощью команды:

Еще один полезный прием при работе с хранилищами дан­ных — немедленная инициализа­ция файлов Instant File Initiation.

В отличие от большинства рас­смотренных в статье параметров, Instant File Initialization управля­ется политикой Windows Server. Instant File Initialization не обну­ляет выделенное пространство для файла, а просто выделя­ет нужное количество места. SQL Server использует Instant File Initialization во время создания базы данных, AutoGrow и опера­ции восстановления базы данных. Можно включить режим Instant File Initialization на сервере через меню Administrative, чтобы открыть Local Security Policy («Локальная политика безопас­ности»). Затем разверните Local Policies («Локальные политики») и дважды щелкните на пункте Performance volume maintenance tasks, как показано на экране. В результате открывается диало­говое окно свойств Properties для Performance volume maintenance tasks («Выполнение задач по обслу­живанию томов»), в котором можно ввести имя учетной записи SQL Server Service.

Хранение данных и уровни RAID

После того как освоены хранили­ща SQL Server, можно приступать к изучению следующей важней­шей концепции — уровней RAID, которые можно использовать для дисков в подсистеме хранения данных. Уровни RAID сильно влияют как на производительность, так и на доступность. Как и следова­ло ожидать, более дорогостоящие варианты, как правило, обеспе­чивают лучшую производитель­ность и доступность. Наиболее распространенные уровни RAID следующие:

• RAID0 (иногда именуется чере­дованием дисков).На этом уровне RAID данные распреде­ляются между всеми доступны­ми дисками. Он часто исполь­зуется в различных тестах про­изводительности баз данных. RAID 0 обеспечивает хорошую производительность, но его никогда не следует применять на производственном сервере, так как отказ одного диска при­водит к потере данных.
• RAID1 (иногда именуется зер­кальным отображением дисков).В конфигурации RAID 1 данные отображаются на дисках зер­кально. Скорость операций чте­ния и записи хорошая, но общая емкость дисков уменьшается вдвое. RAID 1 часто использу­ется для файлов журналов SQL Server. В случае отказа одного диска данные не теряются.
• RAID5 (иногда именуется чере­дованием дисков с контролем четности).В конфигурации RAID 5 данные распределяются по нескольким дискам с целью обеспечить избыточность дан­ных. Часто используется для фай­лов данных. Этот уровень RAID обеспечивает хорошую произво­дительность чтения и устойчив к отказу одного диска. Однако скорость записи невелика.
• RAID10 (иногда именуется зер­кальным отображением дисков с чередованием).RAID 10 сочета­ет в себе быстродействие вари­антов с чередованием и защиту через зеркальное отображение. RAID 10 обеспечивает самые высокие уровни производи­тельности и доступности среди всех уровней RAID. Для RAID 10 требуется вдвое больше дис­ков, чем для RAID 5, но кон­фигурация устойчива к отка­зу нескольких дисков. Массив
• RAID 10 продолжает успешно функционировать при отка­зе половины дисков в наборе. RAID 10 подходит как для фай­лов данных, так и для журна­лов.

Tempdb

Еще один важный компонент системы хранения данных SQL Server — tempdb. Это системная база данных SQL Server, которая представляет собой глобальный ресурс, доступный всем пользо­вателям. Tempdb используется для временных объектов пользовате­ля и внутренних операций ядра системы управления базами дан­ных, в том числе объединений, статистической обработки, кур­соров, сортировки, хэширования и управления версиями строк. В отличие от данных в типичной пользовательской базе данных, данные в tempdb не сохраняют­ся после отключения экземпляра SQL Server.

Как правило, tempdb — одна из самых активных баз данных в рабочем экземпляре SQL Server, поэтому следующие рекоменда­ции помогут обеспечить хорошую производительность базы данных SQL Server. Прежде всего, файлы данных и журналов tempdb следу­ет разместить на других физиче­ских дисках, нежели файлы жур­налов и данных рабочей базы дан­ных. По причине очень активного использования tempdb полезно защитить диски, организовав мас­сив RAID 1 или массив RAID 10 с чередованием. Специалисты группы Microsoft SQL Server Customer Advisory Team (SQLCAT) рекомендуют, чтобы в tempdb был один файл данных для каждо­го ядра процессора. Но эта реко­мендация эффективна для очень высоких рабочих нагрузок. Чаще рекомендуется, чтобы отноше­ние файлов данных к ядрам про­цессора составляло 1:2 или 1:4. Как и в большинстве случаев, это общие рекомендации; опти­мальные подходы для конкрет­ной системы могут различаться. Если вы не знаете точно, сколько файлов использовать для tempdb, можно начать с четырех фай­лов данных. Обычно для tempdb достаточно одного файла журна­ла. Более подробные рекоменда­ции tempdb вы найдете в мате­риалах, перечисленных во врезке «Учебная литература». Кроме того, размер tempdb должен быть достаточным, чтобы избежать операций AutoGrow. Как и поль­зовательские базы данных, tempdb будет испытывать задержки из-за операций AutoGrow. По умолча­нию tempdb содержит файл дан­ных в 8 Мбайт, файл журналов в 1 Мбайт и 10% пространства для AutoGrow, а это слишком мало для большинства производственных рабочих нагрузок. Также важно помнить, что при перезапуске SQL Server размер tempdb возвра­щается к последнему заданному значению.

Размер и перемещения файлов данных и журналов tempdb можно определять с помощью программ­ного кода, приведенного в разде­ле «Данные и файлы журналов». Запрос в листинге 2 (с сайта MSDN) показывает, как определить размер и процент роста файлов данных и журналов tempdb.

Твердотельные диски

Благодаря нескольким ядрам уве­личилась вычислительная мощь, и многие современные системы поддерживают очень большой объем оперативной памяти, из-за чего подсистема ввода-вывода стала узким местом для многих рабочих нагрузок. Традиционные жесткие диски стали более емкими, но быстродействие практически не увеличилось. Проблему можно решить с помощью твердотель­ных дисков (SSD). Твердотельные диски — сравнительно новая тех­нология хранения данных, кото­рая начала набирать вес на рынке SQL Server в течение последнего года. В прошлом цена устройств SSD была слишком велика, а информационная емкость слиш­ком мала для многих рабочих баз данных. Одна из причин расту­щей популярности твердотельных дисков — преимущество в произ­водительности перед традиционными жесткими дисками с враща­ющимся шпинделем. Например, диск Serial Attached SCSI (SAS) с частотой вращения шпинде­ля 15 000 об./мин может обеспе­чить пропускную способность 200 Мбайт/с. Для сравнения, SSD-диск Serial ATA (SATA) с 6-Гбайт соединением может обеспечить последовательную пропускную способность около 550 Мбайт/с. Основная причина превосходства SSD-дисков в быстродействии заключается в резком сокраще­нии времени поиска. Когда нужно получить данные с вращающего­ся жесткого диска, головка долж­на переместиться в новое место. У SSD-диска нет движущихся частей, поэтому перемещение к новому месту хранения данных определяется быстродействием ячеек памяти.

Твердотельные и быстродейству­ющие флэш-хранилища можно реализовать несколькими способами. Типичное применение — 2,5-дюймовые диски SSD. Эти устройства подключаются напрямую, как хранилища типа DAS, а электронный интерфейс — такой же, как у стандартного жесткого диска. Другая распро­страненная реализация SSD — в виде плат PCI Express (PCIe), подключаемых непосредственно к системной шине. В этом под­ходе используются преимущества быстродействующей шины PCIe, чтобы повысить число операций ввода-вывода в секунду (IOPS) и пропускную способность по сравнению со стандартным интерфейсом диска. Кроме того, многие хранилища SAN распола­гают разделами SSD и функцией автоматического распределения данных по разделам, что позво­ляет переместить важные рабо­чие нагрузки на высокопроизво­дительный раздел SSD, оставляя менее важные рабочие нагрузки на медленных и менее дорогосто­ящих жестких дисках. Существуют хранилища SSD раз­личных типов. Среди них — храни­лище SSD на основе DRAM и хра­нилище SSD на основе технологии флэш-памяти, такой как одноуров­невые ячейки (SLC) и многоуров­невые ячейки (MLC). У каждого типа есть свои достоинства и недо­статки.

• DRAM.Как обычная оперативная память для компьютера, DRAM отличается очень высоким быстродействием, но ненадеж­на. Для DRAM требуется посто­янный элемент питания, чтобы сохранить данные на время отключения данных. Такие хра­нилища часто выпускаются в виде плат PCIe, устанавливае­мых на системной плате сервера. SLC. Быстродействие и жизненный цикл хранилищ на SLC выше, чем у MLC, поэтому SLC используется в хранилищах SSD корпоративного уровня. Однако цена устройств SLC существенно выше, чем у MLC.
• MLC.Обычно флэш-память типа MLC используется в потре­бительских устройствах и обхо­дится дешевле, чем SLC. Однако у MLC более низкая скорость операций записи и намно­го более высокий износ, чем у SLC.По быстродействию устройства SSD превосходят жесткие диски с вращающимся шпинделем, но срок их эксплуатации значи­тельно ниже. Приложения с интен­сивным вводом-выводом, такие как SQL Server, сокращают срок жизни накопителя SSD. Кроме того, чем больше используемая часть диска, тем меньше продол­жительность жизни. Рекомендуется убедиться, что по крайней мере 20% накопителя SSD не занято. Скорость чтения стабильна в тече­ние всего времени эксплуатации устройства. Однако быстродей­ствие при записи в процессе экс­плуатации ухудшается, то есть время, необходимое для записи, увеличивается. Важно также пом­нить, что нет необходимости деф-рагментировать диски SSD, потому что метод доступа к данным иной, чем у жестких дисков. В сущности, дефрагментация этого типа дисков приведет только к сокращению их жизненного цикла. Если нужно использовать диски SSD, не применяйте единственный накопитель SSD и приготовьтесь заменять диски SSD в течение срока эксплуатации сервера. Перечислим возможности применения SSD в SQL Server.
• Перемещение индексов на диски SSD.Как правило, индексы не очень велики и связаны с интенсивными беспорядочными операциями чтения, поэтому идеально под ходят для размещения на дисках SSD.
• Перемещение файлов данных на дискиSSD.С файлами данных обычно связано больше опера­ций чтения, чем записи, поэтому в большинстве случаев они под­ходят для дисков SSD.
• Перемещение файлов журналов на дискиSSD. Файлы журналов связаны с большим числом опе­раций записи. Поэтому, если для файлов журналов применяются диски SSD, используйте диски SSD корпоративного уровня и конфигурации RAID 1 или RAID 10 с зеркальным отобра­жением.
• ПеремещениеtempdbнаSSD-диск. Как правило, tempdb отличается высоким уровнем неупорядочен­ных операций записи, что может привести к порче SSD. Поэтому если диски SSD используют­ся для tempdb, то это должны быть SSD корпоративного уров­ня в конфигурации RAID 1 или RAID 10 с зеркальным отобра­жением, и нужен план замены дисков SSD. Кроме того, обрати­те внимание на вариант с PCIe DRAM для tempdb. Хранилище DRAM обеспечивает более высо­кое быстродействие при записи и имеет неограниченный срок эксплуатации. Однако цены хра­нилищ DRAM могут быть высо­кими.

Базовые уровни производительности

Другой основной подход — под­готовить базовые уровни произво­дительности и периодически сравнивать системную производитель­ность с этими базовыми уровнями. Это может быть очень полезным для диагностики неполадок, а также отслеживания роста базы данных и других тенденций. Сопоставление с базовым уровнем — один из луч­ших способов упреждающего управ­ления системами. Тема измерения производительности SQL Server выходит за рамки данной статьи, но ниже приводится обзор важней­ших измеряемых показателей хра­нилищ данных.

Первая группа счетчиков произ­водительности, которые необхо­димо отслеживать, представляет собой счетчики, относящиеся к памяти в системном мониторе Windows. Технически это не счет­чики хранилища данных, но если памяти недостаточно, то осталь­ные счетчики не имеют значе­ния. Обязательно отслеживайте счетчик Available MBytes объекта Memory. Этот счетчик показы­вает объем физической памяти, доступной для выделения процес­су или системе.

Если показатель меньше 100 Мбайт, то полезно увеличить размер памяти. Другой важный счетчик — % Usage объ­екта Paging File, который показы­вает используемый объем файла подкачки Windows. Это значение должно быть менее 70%. Если зна­чение выше, то, вероятно, системе требуется больше памяти.

Помимо счетчиков, связан­ных с памятью Windows, имеет­ся несколько счетчиков произво­дительности хранилища Windows Server. Однако показания этих счетчиков полезны лишь в том случае, если экземпляр SQL Server работает с системой хранения дан­ных с прямым подключением DAS. Если используется SAN, то нужно обращать внимание на метрики производительности SAN. Если экземпляр SQL Server исполь­зует DAS, то в первую очередь убедитесь, что на каждом диске NTFS свободно по крайней мере 20% пространства. Впоследствии можно проверить счетчики хра­нилища Windows Server с помощью системного монитора. В табли­це 1 приведен список нескольких наиболее важных счетчиков; все они связаны с объектом Logical Disk.

SQL Server располагает много­численными счетчиками произ­водительности, которые помога­ют управлять экземпляром SQL Server. Некоторые наиболее важные счетчики хранилища SQL Server, показания которых полезно отсле­живать, перечислены в таблице 2. Следить за ними можно с помощью системного монитора.

Сохраняем и движемся вперед

Хранилище — высококритич­ный компонент в производитель­ности базы данных SQL Server. Знание некоторых простых при­емов поможет оптимизировать доступность и производительность SQL Server.