Русские Блоги
Практика работы с локальной файловой базой данных SQL Server LocalDB
Официальный здесьMicrosoft SQL Server 2012 Express LocalDBДля иллюстрации В.С. с 2012 годаЭта функция должна быть интегрирована в начале.
Microsoft SQL Server 2012 Express LocalDB Это режим выполнения SQL Server Express для разработчиков программ.LocalDB При установке будет скопирован минимальный набор файлов, необходимый для запуска ядра базы данных SQL Server. устанавливатьLocalDB После этого разработчик будет использовать определенную строку подключения для инициации подключения. При подключении автоматически создается и запускается необходимая инфраструктура SQL Server, что позволяет приложениям использовать базу данных без выполнения сложных или трудоемких задач настройки. Инструменты разработчика могут предоставить разработчикам ядро базы данных SQL Server, чтобы они могли писать и тестировать код Transact-SQL без необходимости управлять полным экземпляром сервера SQL Server. используя SqlLocalDB.exe Утилита для управления SQL Server ExpressLocalDB Примеры. SQL Server ExpressLocalDB Его следует использовать для замены функций пользовательского интерфейса SQL Server Express, которые больше не рекомендуются.
Установить LocalDB
устанавливатьLocalDB Основным методом является использование программы SqlLocalDB.msi.LocalDB Это опция при установке любого SKU SQL Server 2012 Express. Во время установки SQL Server Express выберите на странице «Выбор компонентов»LocalDB, Для каждой основной версии ядра СУБД SQL Server существует толькоLocalDB Установка бинарных файлов. Вы можете запустить несколько процессов ядра базы данных, и эти процессы будут использовать один и тот же двоичный файл. Так какLocalDB Запущенный экземпляр ядра базы данных SQL Server имеет те же ограничения, что и SQL Server Express.
объяснение
LocalDB Установщик использует программу SqlLocalDB.msi для установки необходимых файлов на компьютер. После установкиLocalDB Это экземпляр SQL Server Express, который может создавать и открывать базу данных SQL Server. Системный файл базы данных хранится в локальном пути AppData пользователя, который обычно скрыт. напримерC:\Users\<user>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\LocalDBApp1\, Файл базы данных пользователя хранится в месте, указанном пользователем, обычноC:\Users\<user>\Documents\ Где-то в папке.
СвязанныйLocalDB Для получения подробной информации об API, пожалуйста, обратитесь кСправочник по API экземпляра SQL Server Express LocalDBс участием Функция LocalDBStartInstance。
Утилита SqlLocalDb может создаватьLocalDB Новый экземпляр, запуск и остановкаLocalDB Пример, и содержит помощь вам управлятьLocalDB Параметры. Для получения дополнительной информации об утилите SqlLocalDb см.Утилита SqlLocalDB。
БудетLocalDB Для параметра сортировки экземпляра установлено значение SQL_Latin1_General_CP1_CI_AS, его нельзя изменить. Обычно поддерживают сопоставление на уровне базы данных, на уровне столбца и на уровне выражения. Содержит базу данныхСодержит сопоставление базы данныхОпределенные метаданные и сопоставление tempdb.
предел
LocalDB Не может быть подписчиком репликации слиянием.
LocalDB FILESTREAM не поддерживается.
LocalDB Разрешены только локальные очереди компонента Service Broker.
Из-за перенаправления файловой системы Windows все встроенные учетные записи (такие как NT AUTHORITY \ SYSTEM)LocalDB Могут возникнуть проблемы с управляемостью, используйте обычную учетную запись Windows в качестве владельца.
Автоматические и именованные экземпляры
LocalDB Поддерживаются два типа экземпляров: автоматические экземпляры и именованные экземпляры.
LocalDB Автоматический экземпляр является публичным. Система автоматически создает и управляет такими экземплярами для пользователей и может использоваться любым приложением. Каждый установлен на компьютере пользователяLocalDB Есть автоматическая версияLocalDB Примеры. автоматическаяLocalDB Экземпляры обеспечивают плавное управление экземплярами. Не нужно создавать экземпляр, он может автоматизировать работу. Это упрощает установку и перенос приложения на другой компьютер. Если на целевом компьютере установлена указанная версия LocalDB, целевой компьютер также предоставляет автоматический экземпляр LocalDB этой версии. автоматическаяLocalDB Экземпляр имеет специальный шаблон имени экземпляра, который принадлежит зарезервированному пространству имен. Это предотвращает имена и именаLocalDB Экземпляр конфликтует. Имя автоматического экземпляра — это один символ v, за которым следуетxx.x созидательныйLocalDB Выпуск номер версии. Например,v11.0 Представляет SQL Server 2012.
LocalDB Именованный экземпляр посвящен. Эти именованные экземпляры принадлежат одному приложению, ответственному за создание и управление экземпляром. Именованные экземпляры обеспечивают изоляцию от других экземпляров и могут использоваться для повышения производительности за счет уменьшения конкуренции за ресурсы с другими пользователями базы данных. Именованный экземпляр должен быть передан пользователемLocalDB API управления создается явно или неявно через файл app.config управляемого приложения (хотя управляемое приложение также будет использовать API при необходимости).LocalDB Каждый именованный экземпляр имеет связанную версию LocalDB, указывающую на соответствующий набор двоичных файлов LocalDB.LocalDBИменованный экземпляр является типом данных sysname и может содержать до 128 символов. (Это отличается от обычных именованных экземпляров SQL Server, которые ограничивают имя обычным именем NetBIOS из 16 символов ASCII.)LocalDB Имя экземпляра может содержать любые допустимые символы Unicode в имени файла. Именованные экземпляры, использующие автоматические имена экземпляров, станут автоматическими экземплярами.
У разных пользователей компьютера могут быть экземпляры с одинаковыми именами. Каждый экземпляр — это отдельный процесс, выполняющийся от имени другого пользователя.
Общий экземпляр LocalDB
Для поддержки нескольких пользователей компьютера необходимо подключиться к одномуLocalDB Примеры решений,LocalDB Поддержка обмена экземплярами. Владелец экземпляра может разрешить другим пользователям компьютера подключаться к своим экземплярам.LocalDB И автоматические экземпляры, и именованные экземпляры могут быть общими. Для совместного использования экземпляра LocalDB пользователям необходимо выбрать для них общее имя (псевдоним). Поскольку имя общего ресурса видно всем пользователям компьютера, имя общего ресурса должно быть уникальным на компьютере. Общее имя экземпляра LocalDB имеетLocalDB Тот же формат для именованных экземпляров.
Только администраторы на компьютере могут создаватьLocalDB Общий экземпляр.LocalDB Из общих экземпляров может управляться администратором илиLocalDB Владелец общего экземпляра отменяет совместное использование. Чтобы поделиться и отменить определенныйLocalDB Примеры, пожалуйста, используйтеLocalDB API-методы LocalDBShareInstance и LocalDBUnShareInstance, либо используйте утилиты SqlLocalDb для совместного использования и отмены общего доступа.
Запустите LocalDB и подключитесь к LocalDB
Подключиться к автоматическому экземпляру
ИспользоватьLocalDB Самый простой способ — использовать строку подключения."Server=(localdb)\v11.0;Integrated Security=true" Подключитесь к автоматическому экземпляру, принадлежащему текущему пользователю. Чтобы использовать имя файла для подключения к конкретной базе данных, используйте что-то вроде"Server=(LocalDB)\v11.0; Integrated Security=true ;AttachDbFileName=D:\Data\MyDB1.mdf" Строка подключения.
Пользователь на компьютере сначала пытается подключиться кLocalDB , Вы должны создать и запустить автоматический экземпляр. Дополнительное время, необходимое для создания экземпляра, может привести к сбою попытки подключения с сообщением об истечении времени ожидания. Когда это произойдет, подождите несколько секунд до завершения процесса создания, а затем снова подключитесь.
Альтернативные методы организации и создания файловых информационных ресурсов
Ранее разработки в данном направлении уже велись, и в результате проделанной работы был создан продукт в виде десктопного приложения. К сожалению, он не получил широкого применения, так как использовался узким кругом сотрудников в довольно ограниченной сфере деятельности.
Приложение было разработано на языке программирования Visual Basic 6.0. Для описания содержимого файлов использовалось хранилище, реализованное на SQL server с типом данных varbinary(max).
Тип данных varbinary(max) позволяет записать в одну ячейку таблицы БД содержимое файла в двоичном виде объемом до 2 ГБ. Нет смысла излагать подробное описание работы продукта, так как на данный момент он устарел, хотя работает и активно используется до сих пор. Для инициализации файла используется проводник ОС. Содержимое файла преобразуется в бинарный тип данных с помощью функции VB и записывается в ячейку varbinary(max) таблицы. Так же в таблицу записываются внешние атрибуты файла, такие как название, расширение, размер и реквизиты пользователя приложения. При извлечении файла из базы данных, происходит преобразование данных в тот формат, который нам подскажет расширение файла, ранее записанное в базу данных. Все манипуляции с преобразованием форматов производятся с помощью Win32.
Есть замечательное свойство SQL сервера и его системы управления базами данных (СУБД), с помощью которого можно создать хранилище данных, отвечающее на данный момент большинству требований, предъявляемых заказчиками подобных продуктов. Этим свойством является один из возможных типов хранения информации, предоставленных SQL сервером, который называется FILESTREAM.
Хранилище FILESTREAM появилось, начиная с SQL Server 2008, и предлагает впечатляющие улучшения производительности для хранения данных BLOB в виде полноценных файлов, управляемых операционной системой. Поясним, как воспользоваться преимуществами хранилища FILESTREAM — установим, настроим и напишем код.
Настройка сервера для хранилища FILESTREAM
Для настройки хранилища FILESTREAM требуются одновременно права администратора на ОС и на SQL сервер. Так же нужно предоставить SQL Server разрешение на работу напрямую с сервером приложений.
Если необходима потоковая передача Win32, потребуется использование встроенной системы безопасности Windows, так как входы в систему SQL Server взаимодействуют с ОС. Эти небольшие ограничения помогают защитить вашу инфраструктуру и данные.
Для того, чтобы включить функцию FILESTREAM, необходимо в диспетчере конфигурации SQL Server, зайти на страницу свойств для отдельного экземпляра SQL Server, который вы хотите включить. В процессе настройки необходимо указать, какие виды прав доступа и потоковой передачи необходимы для SQL Server и удаленных клиентов.
Код для настройки SQL Server для доступа к FILESTREAM
У функции FILESTREAM есть три варианта включения:
0 — отключает поддержку FILESTREAM для этого экземпляра;
1 — включает функции FILESTREAM для доступа к T-SQL;
2 — включает поддержку FILESTREAM для доступа T-SQL и Win32
После внесения изменений из диспетчера конфигурации SQL Server в хост-системе создается новый общий ресурс с указанным именем. Однако это не типичная общая папка, и она предназначена только для потокового взаимодействия очень низкого уровня между SQL Server и авторизованными клиентами. Таким образом, Microsoft рекомендует, чтобы только учетная запись службы, используемая экземпляром SQL Server, имела доступ к этому общему ресурсу. Более того, поскольку это изменение происходит на уровне ОС или службы, невозможно включить его из SQL Server, необходимо перезапустить экземпляр SQL Server, чтобы изменение вступило в силу.
После перезапуска необходимо включить SQL Server, чтобы использовать этот вариант хранения. Запустите sp_configure с соответствующим уровнем доступа к файловому потоку (0, 1 или 2) в соответствии с вашими потребностями.
Настройка баз данных для хранилища FILESTREAM
После настройки хранилища FILESTREAM на уровне ОС и SQL Server вы можете приступить к созданию таблиц для хранения ваших документов. Ключом для использования преимуществ хранилища FILESTREAM является добавление к выбранному полю varbinary(max) атрибута FILESTREAM.
Лист 1. Код для создания базы данных и таблицы с поддержкой FILESTREAM
После того, как создали в своей базе данных специальную файловую группу FILESTREAM, данные хранящиеся в этом столбце, не будут сохраняться в страницах или экстентах, управляемых SQL Server. Вместо этого они будут передаваться в файловую систему, где можно буде воспользоваться повышенной производительностью, когда речь идет о больших размерах файлов. Обратите внимание, что на Лист 1 группа FILEGROUP, которая указана для использования с хранилищем FILESTREAM (с атрибутом CONTAINS FILESTREAM), на самом деле не указывает на такой файл, как files.ndf. Вместо этого она указывает на папку, в которой фактически будут храниться файлы и файловые данные. Таблицы с поддержкой FILESTREAM также требуют параметра UNIQUE ROWGUIDCOL для поддержки взаимодействия с хранилищем, на Лист 1 это сделано с помощью столбца FileId в примере таблицы. Аналогичным образом, в коде, показанном на Лист 1, также указывается значение по умолчанию (0x) в столбце FileData. Это значение по умолчанию заставляет SQL Server создавать новый пустой файл в папке хранилища FILESTREAM для клиентов потоковой передачи с помощью Win32, чтобы получить к ним доступ и использовать как путь при попытке загрузить содержимое файла. Без создания «пустого» файла по умолчанию новые файлы должны были бы создаваться путем потоковой передачи содержимого непосредственно через T-SQL, прежде чем к ним можно будет получить доступ через клиентские приложения Win32.
На Лист 2 показано типичное использование FILESTREAM, когда задействован только T-SQL. Оператор INSERT создает новую строку в таблице (где содержимое виртуального файла фактически сохраняется на диске в виде файла). Затем инструкция SELECT использует новую встроенную функцию GET_FILESTREAM_TRANSACTION_CONTEXT() и новую функцию .PathName() для получения контекста транзакции или идентификатора. Затем этот контекст транзакции или идентификатор передается клиентскому приложению Win32 для доступа к содержимому файла в указанном FileData.PathName().
Лист 2. Код для включения поддержки T-SQL для хранилища FILESTREAM
Клиентские приложения и хранилище FILESTREAM
В качестве реального примера использования данных FILESTREAM можно создать очень простое WinForms-приложение для отображения содержимого таблицы FileSamples, созданной ранее. Это простое приложение также позволяет пользователям загружать файлы со персонального компьютера (или из другого места) в хранилище SQL Server FILESTREAM.
В программном коде статьи, логика пользовательского интерфейса переведена в Form1.cs, а все взаимодействия FILESTREAM помещены во вспомогательный класс в файле FileStreamSample.cs. Этот класс, содержит три метода, метод извлечения списка текущих файлов в таблице FileSamples, и два метода позволяющих загружать и выгружать файлы по мере необходимости. Кроме того, поскольку файлы идентифицируются по столбцу UNIQUEIDENTIFIER FileId, логика всех трех методов тесно связана с идентификаторами GUID в качестве дескрипторов, помогающих выполнять прямые операции.
Код на Лист 3 создает новый файл, и с помощью оператора INSERT загружает метаданные файла. Затем код создает новую транзакцию, прикрепляет командный объект к транзакции (чтобы получить контекст транзакции) и выполняет соединение SqlDataReader, чтобы вернуть полный путь (в SQL Server) к файлу, который будет загружен, вместе с идентификатором контекста транзакции, который можно использовать для запуска процесса потоковой передачи файлов Win32.
Лист 3. Код для получения контекста транзакции
Используя идентификатор контекста и фактический путь к хранилищу FILESTREAM для нового файла, код может затем инициировать прямую операцию потоковой передачи файлов с помощью класса System.Data.SqlTypes.SqlFileStream, впервые появившегося в .NET 3.5 SP1. Затем, см. Лист 4, создается новый объект System.IO.FileStream для локального чтения файла и буферизируются байты порциями по 512 КБ (произвольный выбор) в объект SqlFileStream, пока не останется байтов для передачи.
Лист 4. Код для потоковой передачи содержимого файла с помощью объекта SqlFileStream
На этом этапе можно закрыть транзакцию и файл успешно загружен на SQL Server, как данные файла, вместо обычных данных varbinary(max). Обратите внимание, поскольку содержимое файла передается в процесс Win32, необходимо использовать встроенную безопасность, так как входы в систему SQL не могут генерировать необходимые токены для доступа к базовой файловой системе.
Операции загрузки следуют той же парадигме создания новой транзакции, и получения идентификатора контекста транзакции и пути к файлу, который необходимо передать вашему клиенту. Когда дело доходит до операции потоковой передачи с загрузками, всё делается в обратном порядке — извлекаются данные из объекта SqlFileStream в буфер и проталкиваются в локальный объект FileStream до тех пор, пока не останется байтов для передачи.
Заключение.
Хранилище FILESTREAM стоит использовать во многих проектах. Помимо преимуществ в производительности, оно также позволяет превысить ограничение в 2 ГБ типа данных varbinary(max).
В хранилище FILESTREAM можно хранить большие двоичные объекты настолько больших размеров, насколько позволяет ОС.
Хранилище FILESTREAM также обеспечивает безопасность транзакций при хранении файлов и предлагает улучшенные возможности управления файлами, включая очевидное преимущество функций резервного копирования и восстановления файлов средствами SQL server. Другие важные преимущества включают в себя использование репликации и возможности полнотекстового индексирования непосредственно для файлов, хранящихся и управляемых SQL Server.
Не призываю повсеместно переводить все ФИР на новый метод хранения данных, описанный в статье. Использование данного метода хранения данных, по моему мнению, может подойти для хранения информации в высокой степенью конфиденциальности.
Доступ к файловой системе через DBMS LOB-процедуры
Доступ к файловой системе через UTL_FILE процедуры, несомненно, очень удобный и, как следствие, популярный способ. Л поскольку он популярный, то логично, что администраторы, которые уделяют внимание безопасности СУБД, пытаются тем или иным способом ограничить доступ на выполнение небезопасных процедур из пакета UTL_FILE. О том, как это делать, будет подробно рассказано в главе 11, а сейчас мы рассмотрим еще один способ получения доступа к файловой системе, который может помочь в случае, если по тем или иным причинам получить доступ к ОС через пакет UTL_FILE не получилось.
В Oracle существует пакет процедур Dbmslob. Это стандартный пакет для работы с типом данных LOB (large objects). С помощью такой процедуры, как dbmslob.fileopen, возможно прочитать содержимое файлов на сервере. Способ чтения файлов через dbms lob.fileopen не такой очевидный, как при использовании процедур из пакета UTL_FILE, поэтому рассмотрим его подробно.
Чтение файлов
Для начала необходимо создать обьект-директорию my_files, указывающую на корневой раздел диска С: так же, как при использовании UTL_FILE. Для этого пользователь должен иметь привилегии CREATE ANY DIRECTORY:
create or replace directory my_files as ‘C:’;
Далее создаются вспомогательные объекты, такие как таблица demo, в поля которой будет сохранен файл, и последовательность blob_seq для чтения файла:
create table demo(id int primary key, theBlob blob );
create sequence blob_seq;
После чего создается сама процедура, которая, собственно, открывает файл, читает его содержимое и записывает в таблицу:
create or replace procedure dbst_load_a_file( p_dir_name in varchar2,p_file_name in varchar2 ) as
l_bfile bfile; begin
insert into demo values ( blob_seq.nextval, empty_blob() )returning theBlob into l_Blob;
l_bfile := bfilename( p_dir_name, p_file_name ); dbms_lob.fileopen( l_bfile );
dbms_lob. loadf romf ile ( l_blob, l_bfile, dbms_lob. getlength ( l_bfile ) ); dbms_lob.fileclose( l_bfile ); end;
Теперь нам остается только вызвать процедуру с нужными параметрами:
exec dbst_load_a_file( , MY_FILES’, ‘boot.ini’ );
Последнее, что нам необходимо сделать, чтобы получить данные в удобочитаемом виде, это переконвертировать их при помощи функции utl_raw.cast_to_varchar2, при извлечении их из таблицы.
select utl_raw.cast_to_varchar2(dbms_lob.substr(theblob,200,1)) from demo;
В результате мы получим содержимое нашего файла (рис. 8.2.2-1).
Доступ к файловой системе ОС через СУБД
Рис. 8.2.2-1. Содержимое файла boot.ini, прочитанное при помощи процедур DBMS_LOB
Вот мы и научились читать файлы еще одним способом. Чем это может быть полезно? К примеру, в ОС UNIX есть такие замечательные файлы, как bash_history, в которых можно найти пароли в открытом виде и другую интересную информацию. Также не лишним будет взглянуть на файл /etc/passwd (рис. 8.2.2-2), из которого мы сможем получить список учетных записей пользователей, к которым можно в дальнейшем попробовать подобрать пароли. Еще, как уже говорилось выше, в некоторых системах, например в старых версиях ОС HP-UX, в файле /etc/passwd хранятся хэши паролей, а сам файл доступен на чтение непривилегированному пользователю. В общем, возможностей, как видите, много.
Как получить доступ к удаленной базе MS SQL?
У напарника на ПК стоит база данных MS SQL , IP статичесский. Можно ли мне как-то получить доступ и работать с ней через свою SSMS ?
Вам необходимо сделать следующее. Сначала настроить сервер
Необходимо Настроить сервер для прослушивания указанного TCP-порта(по умолчанию 1433). Также настроить файреволл для разрешения входящих подключений на заданный порт.
Иметь или создать нового пользователя на SQL сервере, под именем которого будем подключаться
Теперь что касается клиента. При запуске SSMS появляется окно для подключения к базе данных. Заполняем ее