tech-blog.IT

Archive for Listopad, 2009

Zabraliśmy się za tworzenie lustrzanych baz danych i nagle błąd:

Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments. To enable database mirroring for evaluation purposes, use trace flag 1400 during startup. For more information about trace flags and startup options, see SQL Server Books Online. (Microsoft SQL Server, Error: 1498)

Jak sobie z tym poradzić? Wchodzimy do właściwości serwera SQL i w zakładce Advanced przechodzimy do pozycji Startup Parameters. Na końcu parametrów startowych dopisujemy: -T1400 po czym restartujemy serwer SQL.

Aby sprawdzić aktualną nazwę instancji wykonujemy:

sp_helpserver
select @@servername

Teraz zmieniamy nazwę:

sp_dropserver  ‘stara_nazwa’
go
sp_addserver ‘nowa_nazwa’ , ‘local’
go

Aby sprawdzić, czy nazwa została zmieniona, wykonujemy pierwsze zapytanie.

1. Jak stworzyć nową bazę danych przy pomocy SQL Management Studio

Włączamy SQL Server Management Studio i łączymy się z instancją, w której chcemy dodać nową bazę danych. Klikamy prawym przyciskiem myszy na węźle Databases i wybieramy New Database…
W pozycję Database name wpisujemy nazwę bazy danych, poniżej w pozycji Owner możemy przypisać bazie danych jej właściciela. Aby to zrobić wciskamy przycisk […] i wybieramy jego nazwę. Jeśli chcemy włączyć pełnotekstowe wyszukiwanie w bazie danych, należy zaznaczyć opcje Use full-text indexing.
W sekcji Database files możemy zobaczyć dokładne informacje na temat tworzonej bazy danych:
Logical Name – logiczna nazwa bazy danych i logu transakcyjnego.
File type – typ plików (dane oraz log transakcyjny).
Filegroup – grupa plików w jakiej znajduje się dany plik. O grupach plików będzie można przeczytać dalej.
Initial Size (MB) – rozmiar każdego z utworzonych plików.
Autogrowth – służy to konfiguracji automatycznego przyrostu bazy danych.
Path – ścieżka, gdzie znajdują się pliki danych i logu transakcyjnego. Obydwa pliki domyślnie znajdują się w katalogu Data (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data). W każdej chwili jednak można zmienić ich lokalizację poprzez wciśnięcie przycisku […] . Pliki te mogą znajdować się w innych folderach, bądź na oddzielnych dyskach.
File name – nazwa pliku (nazwabazy_data.mdf oraz nazwabazy_log.ldf)

Przechodzimy do zakładki Options. W pozycji Collation możemy zmienić rodzaj kodowania językowego. Domyślnie jest on taki sam, jak ustawiony dla całego serwera w czasie jego instalacji.
W pozycji Recovery model możemy wybrać model odzyskiwania danych. Więcej o modelu odzyskiwania danych, w dalszej  części artykułu.
Poniżej w sekcji Compatibility level możemy wybrać poziom kompatybilności bazy. Domyślnie jest to SQL Server 2005. Do wyboru mamy jeszcze SQL Server 2000 lub SQL Server 7.0.

Wciskamy przycisk OK i baza danych zostaje stworzona.

2. Jak stworzyć nową bazę danych przy pomocy zapytania T-SQL.

Poniżej znajduje się listing z powyższej operacji tworzenia bazy danych:

CREATE DATABASE [moja_baza] ON  PRIMARY
( NAME = N’moja_baza’, FILENAME = N’C:\dane\moja_baza.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’moja_baza_log’, FILENAME = N’C:\logi\moja_baza_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N’moja_baza’, @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
begin
EXEC [moja_baza].[dbo].[sp_fulltext_database] @action = ‘disable’
end
GO
ALTER DATABASE [moja_baza] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [moja_baza] SET ANSI_NULLS OFF
GO
ALTER DATABASE [moja_baza] SET ANSI_PADDING OFF
GO
ALTER DATABASE [moja_baza] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [moja_baza] SET ARITHABORT OFF
GO
ALTER DATABASE [moja_baza] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [moja_baza] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [moja_baza] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [moja_baza] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [moja_baza] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [moja_baza] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [moja_baza] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [moja_baza] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [moja_baza] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [moja_baza] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [moja_baza] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [moja_baza] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [moja_baza] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [moja_baza] SET  READ_WRITE
GO
ALTER DATABASE [moja_baza] SET RECOVERY FULL
GO
ALTER DATABASE [moja_baza] SET  MULTI_USER
GO
ALTER DATABASE [moja_baza] SET PAGE_VERIFY CHECKSUM
GO
USE [moja_baza]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N’PRIMARY’) ALTER DATABASE [moja_baza] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

3. Omówienie przydatnych funkcji:

Model Odzyskiwania
Przechodząc do właściwości bazy danych i zakładki Options,  z rozwijanej listy możemy wybrać jeden z trzech modeli odzyskiwania (Recovery Model):
•    Full – dzięki rozległemu logowaniu oferuje możliwość odtworzenia stanu z dowolnego punktu w czasie i nie ma ryzyka utraty wyników pracy.
•    Bulk logged – uzupełnienie modelu Full, dodatkowo w pliku logu transakcyjnego rejestrowane są operacje bulk (import dużej ilości danych).
•    Simple – jako iż nie oferuje kopii zapasowych dzienników transakcyjnych, bazę danych można odzyskać jedynie z ostatniego pliku kopii zapasowych.

To samo możemy wykonać poprzez zapytanie T-SQL:

USE [master]
GO
ALTER DATABASE [baza2] SET RECOVERY FULL | SIMPLE | BULK_LOGGED WITH NO_WAIT
GO

Autogrowth
Aby zezwolić na powiększanie plików bazy danych i logu transakcyjnego, przechodzimy do właściwości bazy danych i na stronie Files wciskamy przycisk […] w pozycji Autogrowth,  a następnie zaznaczamy opcje Enable Autogrowth.
Poniżej mamy możliwość określenia czy plik będzie powiększał się procentowo (In Percent) czy będzie powiększał się w określoną ilość megabajtów (In Megabytes).
Możemy również określić maksymalny rozmiar pliku, zaznaczając opcję Restricted File Growth i określając ilość megabajtów. Jeśli nie chcemy określać maksymalnego rozmiaru, zaznaczamy Unrestricted File Growth.

File Groups
Oprócz głównych plików .mdf i .ldf można utworzyć dodatkowe pomocnicze pliki danych (pliki .ndf). Pliki te służą do odseparowania różnych danych (np. indeksy, procedury składowe, tabele użytkownika mogą znajdować się w innych plikach i na innych dyskach niż tabele systemowe).
Aby stworzyć nową grupę plików przechodzimy do właściwości bazy danych, a następnie do strony Files i wciskamy przycisk Add. W sekcji Database files zostanie dodany nowy plik. Nadajemy mu nazwę oraz z rozwijanej listy w pozycji Filegroup wybieramy New file group (ponieważ w grupie PRIMARY znajduje się główny plik danych).

W kolejnym oknie kreatora nadajemy nazwę nowo utworzonej grupie oraz określamy czy ma być grupą domyślną, gdzie będą zapisywane wszystkie nowo utworzone pliki (aktualną grupą domyślną jest grupa PRIMARY), jak i również, czy grupa ma być w trybie tylko do odczytu (Read-only).

Nową grupę plików możemy również utworzyć na stronie Filegroups. Wciskamy przycisk Add i w pozycji Name wpisujemy nazwę nowej grupy oraz zaznaczamy, czy grupa ma być domyślna i czy ma być w trybie tylko do odczytu.

W przypadku zapytania T-SQL, tworzenie nowej grupy wygląda w następujący sposób:

USE [master]
GO
ALTER DATABASE [moja_baza] ADD FILEGROUP [NOWA GRUPA]
GO

Natomiast dodawanie pliku do nowo utworzonej grupy:

USE [master]
GO
ALTER DATABASE [moja_baza] ADD FILE ( NAME = N’nowy_plik’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\nowy_plik.ndf’ , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [NOWA GRUPA]
GO

Pozostałe przydatne opcje na stronie Options w sekcji Other options:
AUTO_CLOSE – służy do automatycznego zamykania, gdy baza danych nie jest w użyciu. W momencie, gdy ktoś chce użyć bazy ponownie, baza się otwiera.
AUTO_CREATE_STATISTICS – służy do tworzenia automatycznych statystyk na kolumnach w tabelach.
AUTO_UPDATE_STATICTICS – służy do automatycznego uaktualniania statystyk.
AUTO_SHRINK – służy do automatycznego zmniejszania danych i logu transakcyjnego w przypadku, gdy 25% pliku zawiera niewykorzystane miejsce.

4. Jak usunąć bazę danych?

Aby usunąć bazę danych klikamy prawym przyciskiem myszy na jej nazwie i wybieramy Delete.
Na głównym ekranie widzimy nazwę bazy danych i jej właściciela. Poniżej możemy zaznaczyć dwie opcje: Delete backup and restore history for database, której zaznaczenie spowoduje usunięcie historii kopii zapasowych i odzyskiwania danej bazy danych oraz opcja Close existing connections, która pozwala na zamknięcie wszystkich aktualnie istniejących połączeń z bazą danych.
Aby usunąć bazę danych należy wcisnąć przycisk OK.

Poniżej znajduje się skrypt tej operacji:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N’moja_baza’
GO
USE [master]
GO
DROP DATABASE [moja_baza]
GO