tech-blog.IT

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

Artykuł ukazał się na łamach portalu WindowsHosting.pl.

Podobnie jak w przypadku log shipping, transakcje w lustrzanych kopiach baz danych są przenoszone z serwera głównego (Principial Server) na serwer pomocniczy, nazywany tutaj serwerem lustrzanym (Mirror Server) z tą różnicą, że transakcje są zatwierdzanie w momencie, kiedy następują.
Na serwerze głównym znajduje się główna baza danych, gdzie przeprowadzane są transakcje. Serwer główny zapisuje zmiany w głównym dzienniku transakcji i automatycznie przenosi informacje na temat przeprowadzonych transakcji na serwer lustrzany, który dokonuje wpisu w swoim dzienniku transakcji. Następnie serwer lustrzany wysyła potwierdzenie do serwera głównego.
W ten sposób serwer lustrzany zawiera pełną kopię bazy danych ze wszystkimi wykonanymi transakcjami. Należy jednak pamiętać, że nie można tworzyć lustrzanych baz danych z baz systemowych.
W celu zapewnienia wysokiej dostępności możemy skonfigurować dodatkowy serwer zwany serwerem pomocniczym (Witness Server), który monitoruje serwery z główną i lustrzaną bazą danych.  W momencie, gdy następuje awaria i przestanie działać serwer główny, serwer nadzorujący wykonuje automatyczne przekierowanie i serwer lustrzany pełni funkcję serwera głównego. Aplikacje, które używają SNAC (SQL Native Client) lub .NET Data Provider są przełączane z serwera głównego na lustrzany transparentnie.

Jak skonfigurować lustrzaną bazę danych w SQL Management Studio?

W SQL Management Studio klikamy prawym przyciskiem myszy na bazie danych i w jej właściwościach przechodzimy do strony Mirroring. Najpierw musimy skonfigurować zabezpieczenia serwera głównego i lustrzanego, a przy wyborze opcji z serwerem nadzorującym – także i jego. W tym celu przyciskamy Configure Security.
Na pierwszej stronie kreatora określamy, czy chcemy używać serwera nadzorującego (Witness Server instance), który służy do automatycznego przywracania po awarii w trybie synchronicznym .

Na kolejnym ekranie Choose Servers to Configure możemy zaznaczyć, które instancje chcemy skonfigurować.

Na stronie Principal Server Instance określamy węzeł końcowy (Endpoint), poprzez który główny serwer będzie akceptował połączenia z serwera lustrzanego i serwera nadzorującego. W pozycji Listener port wpisujemy numer portu, którego chcemy używać. Należy pamiętać, że jeśli którykolwiek z instancji serwera: główny, lustrzany czy nadzorujący znajduje się na tym samym serwerze, numer portu musi się różnić.  Wpisujemy port i nadajemy węzłowi końcowemu nazwę oraz zaznaczamy czy chcemy szyfrować dane wysyłane przez ten endpoint (Enrypt data sent through this endpoint).

Na kolejnej stronie konfigurujemy instancję serwera lustrzanego. Z rozwijanej listy Mirror server instance wybieramy żądaną instancję i serwer oraz wciskamy przycisk Connect. Poniżej, podobnie jak na ekranie powyżej konfigurujemy port oraz nazwę węzła końcowego.

Jeśli wybraliśmy opcję z serwerem nadzorującym, wybieramy nazwę jego instancji w pozycji Witness server instance, wciskamy Connect i ponownie konfigurujemy numer portu i nazwę węzła.

Jeśli instancje używają różnych kont w zaufanej domenie jako kont usług SQL, należy je wpisać w pozycje Principal, Witness i Mirror. Jeśli wszystkie instancje używają tego samego konta, klikamy przycisk Next bez wpisywania czegokolwiek.

Na ostatniej stronie czytamy podsumowanie i wciskamy przycisk Finish.

Po zakończeniu zostanie stworzony mechanizm tworzenia lustrzanych baz danych.
Przed włączeniem mechanizmu, należy wykonać pełną kopię zapasową głównej bazy danych i odtworzyć ją na serwerze lustrzanym z opcją RESTORE WITH NORECOVERY. Lustrzana baza danych będzie znajdowała się teraz w stanie In Recovery.

Po odtworzeniu bazy danych powracamy do kreatora lustrzanych baz danych i przed wciśnięciem przycisku Start Mirroring wybieramy tryb w sekcji Operating Mode. Do wyboru mamy trzy tryby:

• Synchroniczny z automatycznym przywracaniem po awarii (Synchronous with automatic failover (high availability)), który gwarantuje wysoką dostępność. Do działania w tym trybie niezbędny jest uprzednio skonfigurowany serwer nadzorujący. Transakcja zostaje uważana za zakończoną na serwerze główny, gdy serwer lustrzany zapisze transakcję w swoim dzienniku i wyśle potwierdzenie do serwera głównego. Serwer nadzorujący kontroluje automatyczne przywrócenie po awarii na serwerze lustrzanym, gdy serwer główny jest niedostępny.
• Asynchroniczny (Asynchronous (high performance)) – Zmiany zostają zatwierdzone na serwerze głównym a następnie transferowane są na serwer lustrzany. W tym trybie, serwer główny zatwierdza transakcje bez oczekiwania na potwierdzenie od serwera lustrzanego. Nie działa tu automatyczne przywracanie po awarii. Jeśli główny serwer przestaje działać, należy ręcznie  przekazać jego działania serwerowi lustrzanemu. Tryb ten jest szybszy w działaniu niż tryb synchroniczny, niestety istnieje możliwość, że część transakcji nie zostanie zapisanych na serwerze lustrzanym.
• Synchroniczny (Synchronous (high protection)) zapewniający wysoką ochronę – Zatwierdzanie zmian odbywa się na obydwu serwerach. Nie zawiera serwera nadzorującego. Jeśli główny serwer przestaje działać, pełna kopia danych znajdująca się na serwerze lustrzanym zostaje ręcznie wypromowana do działania jako serwer główny. Jeśli natomiast serwer lustrzany przestaje działać, serwer główny przechodzi w tryb offline, a dane nie zostają utracone.

Ten sam process można wykonać poprzez zapytanie T-SQL:

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = REQUIRED ALGORITHM RC4)
ALTER ENDPOINT [Mirroring] STATE = STARTED

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = REQUIRED ALGORITHM RC4)
ALTER ENDPOINT [Mirroring] STATE = STARTED

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = WITNESS, ENCRYPTION = REQUIRED ALGORITHM RC4)
ALTER ENDPOINT [Mirroring] STATE = STARTED

ALTER DATABASE [db] SET PARTNER = ‘TCP://serwer1.local:5022′
ALTER DATABASE [db] SET PARTNER = ‘TCP://serwer2.local :5022′
ALTER DATABASE [db] SET WITNESS = ‘TCP://witness.local:5023′