tech-blog.IT

Archive for the ‘SQL Server 2005’ Category

Przed konfiguracją Log shipping należy stworzyć udział plików na backup logów transakcyjnych, aby nie obciążać serwera głównego i aby zapewnić wysoką dostępność najlepiej jest to zrobić na innym serwerze.
Następnie dla każdego serwera pomocniczego należy stworzyć folder, do którego będą kopiowane backupy logów transakcyjnych.

W SQL Management Studio klikamy prawym przyciskiem na nazwę bazy danych, dla której chcemy skonfigurować przesyłanie dziennika i przechodzimy do jej właściwości. Klikamy na stronę Transaction Log Shipping i zaznaczamy Enable this as a primary database In a log shopping configuration.
Poniżej w sekcji Trasaction log backups wciskamy przycisk Backup Settings, aby skonfigurować opcję wykonywania kopi zapasowej logu transakcyjnego

Backup logów transakcyjnych jest wykonywany poprzez zadanie Agenta serwera SQL działającego na głównym serwerze SQL.
Na stronie Transaction Log Backup Settings określamy ścieżkę do udziału sieciowego, gdzie będą przechowywane backupy. Poniżej, jeśli folder backupów znajduje się na głównym serwerze, możemy wpisać lokalną ścieżkę do tego folderu.
Mamy tu również możliwość określenia, po ilu godzinach pliki backupu będą kasowane. Domyślnie są to 72 godziny.
Poniżej w Backup Job klikamy na przycisk Schedule i określamy harmonogram, kiedy zadanie ma się wykonywać.
Klikamy przycisk OK. i wracamy do głównego okna konfiguracyjnego.
Uwaga: Jeśli wykonujemy kopię zapasową logów transakcyjnych wraz z innym zadaniem lub planem konserwacyjnym, Management Studio nie będzie w stanie wykonać odzyskiwania backupu na instancjach na serwerze pomocniczym.

[ls2]

Następnie w głównym oknie, w sekcji Secondary Server instances and databases wciskamy przycisk Add, aby dodać serwer pomocniczy.
Na stronie Secondary Database Settings wciskamy przycisk Connect i łączymy się z serwerem, który będzie pełnił rolę standby i w pozycji Secondary database wybieramy nazwę bazy danych.

Na serwerze pomocniczym należy odzyskać pełną kopię zapasową głównej bazy danych przed tym, gdy serwer pomocniczy stanie się miejscem przeznaczenia przesyłania dzienników transakcji. Na zakładce Initialize Secondary Database możemy zaznaczyć w jaki sposób będzie przebiegał proces odzyskiwania z backupu na serwerze pomocniczym. Do wyboru mamy opcje:
- Tak, stwórz pełny backup głównej bazy danych i odzyskaj ją na serwerze pomocniczym (oraz utwórz bazę pomocniczą, jeśli taka nie istnieje),
- Tak, odzyskaj istniejący plik backupu serwera głównego na bazę danych na serwerze pomocniczym (oraz utwórz pomocniczą bazę danych, jeśli taka nie istnieje).
- Nie, pomocnicza baza danych jest zainicjowana.

Jeśli wybierzemy opcję pierwszą, należy kliknąć przycisk Restore Options. Jeśli baza pomocnicza będzie utworzona w momencie odtwarzania backupu, możemy określić folder, w którym zostaną stworzone pliki danych oraz logu transakcyjnego. Wpisujemy zatem ścieżkę do folderów na serwerze pomocniczym.
Jeśli wybierzemy opcję drugą, musimy określić ścieżkę do zasobu sieciowego, gdzie znajduje się plik kopii zapasowej, do którego ma dostęp serwer pomocniczy.
Trzecią opcję wybieramy w przypadku wypromowania pomocniczej bazy danych na główną.

[ls3]

Pliki są kopiowane z katalogu backup, na serwer docelowy poprzez zadanie Agenta serwera SQL działające na serwerze pomocniczym. Zakładka Copy Files służy do skonfigurowania folderu docelowego.
Poniżej możemy określić, po jakim czasie skopiowane pliki mają zostać usunięte. Domyślnie są to 72 godziny.
W sekcji Copy Job możemy określić, kiedy zadanie kopiowania ma się wykonywać. Wciskamy przycisk Schedule i określamy harmonogram wykonywania zadania.

[ls4]

Pliki są odzyskiwane z katalogu docelowego przy użyciu zadania Agenta serwera SQL działającego na serwerze pomocniczym. W zakładce Restore Transaction Log możemy określić stan bazy danych po wykonaniu zadania odzyskiwania z backupu. Do wyboru mamy No recovery mode lub Standby mode. Rzecz jasna jak i w poprzednich zakładkach, możemy określić, kiedy zadanie ma się wykonywać.

[ls5]

Klikamy OK aby zamkąć okno konfiguracji serwera pomocniczego oraz OK aby zamknąć główne okno konfiguracyjne i rozpocząć proces przesyłania dzienników.
Po zakończeniu możemy prześledzić raport z wykonanej operacji.

[ls6]

Natomiast na serwerze pomocniczym możemy zauważyć, że baza danych ma status Restoring.

[ls7]

Poniżej znajduje się listing z przeprowadzonej operacji:

– Execute the following statements at the Primary to configure Log Shipping
– for the database [ASGARD\FREY].[Data],
– The script needs to be run at the Primary in the context of the [msdb] database.
————————————————————————————-
– Adding the Log Shipping configuration

– ****** Begin: Script to be run at Primary: [ASGARD\FREY] ******

DECLARE @LS_BackupJobId    AS uniqueidentifier
DECLARE @LS_PrimaryId    AS uniqueidentifier
DECLARE @SP_Add_RetCode    As int

EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
@database = N’Data’
,@backup_directory = N’C:\zrodlo’
,@backup_share = N’\\asgard\zrodlo’
,@backup_job_name = N’LSBackup_Data’
,@backup_retention_period = 4320
,@backup_threshold = 60
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@backup_job_id = @LS_BackupJobId OUTPUT
,@primary_id = @LS_PrimaryId OUTPUT
,@overwrite = 1

IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
BEGIN

DECLARE @LS_BackUpScheduleUID    As uniqueidentifier
DECLARE @LS_BackUpScheduleID    AS int

EXEC msdb.dbo.sp_add_schedule
@schedule_name =N’LSBackupSchedule_ASGARD\FREY1′
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 5
,@freq_recurrence_factor = 0
,@active_start_date = 20090722
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_BackUpScheduleUID OUTPUT
,@schedule_id = @LS_BackUpScheduleID OUTPUT

EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_BackupJobId
,@schedule_id = @LS_BackUpScheduleID

EXEC msdb.dbo.sp_update_job
@job_id = @LS_BackupJobId
,@enabled = 1

END

EXEC master.dbo.sp_add_log_shipping_alert_job

– ****** End: Script to be run at Primary: [ASGARD\FREY]  ******

– Execute the following statements at the Secondary to configure Log Shipping
– for the database [THOR\MSSQL].[Data],
– the script needs to be run at the Secondary in the context of the [msdb] database.
————————————————————————————-
– Adding the Log Shipping configuration

– ****** Begin: Script to be run at Secondary: [THOR\MSSQL] ******

DECLARE @LS_Secondary__CopyJobId    AS uniqueidentifier
DECLARE @LS_Secondary__RestoreJobId    AS uniqueidentifier
DECLARE @LS_Secondary__SecondaryId    AS uniqueidentifier
DECLARE @LS_Add_RetCode    As int

EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary
@primary_server = N’ASGARD\FREY’
,@primary_database = N’Data’
,@backup_source_directory = N’\\asgard\zrodlo’
,@backup_destination_directory = N’\\thor\docelowy’
,@copy_job_name = N’LSCopy_ASGARD\FREY_Data’
,@restore_job_name = N’LSRestore_ASGARD\FREY_Data’
,@file_retention_period = 4320
,@overwrite = 1
,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT
,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT
,@secondary_id = @LS_Secondary__SecondaryId OUTPUT

IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
BEGIN

DECLARE @LS_SecondaryCopyJobScheduleUID    As uniqueidentifier
DECLARE @LS_SecondaryCopyJobScheduleID    AS int

EXEC msdb.dbo.sp_add_schedule
@schedule_name =N’DefaultCopyJobSchedule’
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 5
,@freq_recurrence_factor = 0
,@active_start_date = 20090722
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT

EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_Secondary__CopyJobId
,@schedule_id = @LS_SecondaryCopyJobScheduleID

DECLARE @LS_SecondaryRestoreJobScheduleUID    As uniqueidentifier
DECLARE @LS_SecondaryRestoreJobScheduleID    AS int

EXEC msdb.dbo.sp_add_schedule
@schedule_name =N’DefaultRestoreJobSchedule’
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 5
,@freq_recurrence_factor = 0
,@active_start_date = 20090722
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT

EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_Secondary__RestoreJobId
,@schedule_id = @LS_SecondaryRestoreJobScheduleID

END

DECLARE @LS_Add_RetCode2    As int

IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
BEGIN

EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database
@secondary_database = N’Data’
,@primary_server = N’ASGARD\FREY’
,@primary_database = N’Data’
,@restore_delay = 0
,@restore_mode = 0
,@disconnect_users    = 0
,@restore_threshold = 45
,@threshold_alert_enabled = 1
,@history_retention_period    = 5760
,@overwrite = 1

END

IF (@@error = 0 AND @LS_Add_RetCode = 0)
BEGIN

EXEC msdb.dbo.sp_update_job
@job_id = @LS_Secondary__CopyJobId
,@enabled = 1

EXEC msdb.dbo.sp_update_job
@job_id = @LS_Secondary__RestoreJobId
,@enabled = 1

END

– ****** Begin: Script to be run at Primary: [ASGARD\FREY] ******

EXEC master.dbo.sp_add_log_shipping_primary_secondary
@primary_database = N’Data’
,@secondary_server = N’THOR\MSSQL’
,@secondary_database = N’Data’
,@overwrite = 1

– ****** End: Script to be run at Primary: [ASGARD\FREY]  ******

– ****** End: Script to be run at Secondary: [THOR\MSSQL] ******

– ****** Begin: Script to be run at Monitor: [] ******

– ****** End: Script to be run at Monitor: [] ******

Jak wypromować serwer standby na serwer główny.

Przesyłanie dziennika transakcji odtwarza pełny backup bazy danych serwera głównego na serwer pomocniczy. Następnie cyklicznie przenosi transakcje ze zmianami z serwera głównego, na serwer standby.
Niestety w przypadku awarii serwera głównego, serwer standby działa jedynie w trybie tylko do odczytu (read-only) i nie może służyć jako serwer awaryjny. Jeśli po awarii chcemy użyć serwera standby jako serwera głównego, należy go wypromować ręcznie.
Aby to zrobić należy odtworzyć plik logu transakcyjnego serwera głównego na serwerze standby, a następnie wyłączyć zadania i kopiowania i odtwarzania. W dalszej kolejności należy uruchomić usługę przesyłania dziennika transakcji w taki sposób, jak zostało opisane powyżej z jedną zmianą: dodając nowy serwer pomocniczy na stronie Secondary Detabase Settings, w sekcji Secondary database wpisujemy nazwę bazy danych oryginalnego serwera głównego, a w zakładce Initialize Secondary Database zaznaczamy No, the secondary database is initialized.

Aby stworzyć użytkownika dla bazy danych i przypisać mu odpowiednie uprawnienia do wykonywania zadań w bazie danych należy rozwinąć węzeł z nazwą bazy danych i przejść do węzłów Security | Users. Klikamy prawym przyciskiem myszy i wybieramy New User…
W pozycji User name wpisujemy jego nazwę, natomiast w Login name wyszukujemy skonfigurowany uprzednio login. O tworzeniu loginu traktował poprzedni wpis.
W pozycji Default schema przypisujemy użytkownikowi domyślny schemat, natomiast poniżej w sekcji Schemas owned by this User zaznaczamy pozostałe schematy, które mają być własnością tego użytkownika.
W sekcji Database role membership, przypisujemy użytkownikowi rolę:

• db_accessadmin – zezwala na dodawanie i usuwanie kont,
• db_backupoperator – zezwala na wykonywanie kopii zapasowych,
• db_datareader – zezwala na odczyt baz danych,
• db_datawriter – zezwala na zapisywanie i modyfikację baz danych,
• db_ddladmin – zezwala na modyfikację i usuwanie obiektów baz danych,
• db_denydatareader – nie zezwala na odczyt baz danych,
• db_dentydatawriter – nie zezwala na zapisywanie i modyfikację baz danych,
• db_owner – zezwala na pełną kontrolę nad bazą danych,
• db_securityadmin – zezwala na zarządzanie uprawnieniami oraz rolami baz danych,
• public – rola domyślna, zapewniająca minimum uprawnień.

Poniżej znajduje się skrypt T-SQL z wykonanej operacji:

USE [baza]
GO
CREATE USER [monika] FOR LOGIN [monika]
GO
USE [baza]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [monika]
GO
USE [baza]
GO
EXEC sp_addrolemember N’db_owner’, N’monika’
GO

W SQL Management Studio klikamy na węzeł Databases | Security | Logins. Klikamy prawym przyciskiem myszy i wybieramy New Login.
Na głównej stronie określamy czy będzie to użytkownik Windows (użytkownik Active Directory lub lokalne konto), czy będzie to użytkownik SQL. W celu wybrania autoryzacji Windows – zaznaczamy Windows authentication, w przypadku autoryzacji SQL – SQL Server authentication.
W przypadku wybrania autoryzacji SQL, należy wprowadzić hasło dla loginu oraz poniżej wybrać jedną lub kilka z trzech opcji:

• Enforce password Policy – hasło nie może zawierać w sobie części nazwy loginu i nie może być krótsze niż 7 znków oraz powinno zawierać cyfry, duże i małe litery oraz znaki nie alfanumeryczne. Informacje pobierane są z polityki grupowej.
• Enforce password expiration – hasło wygasa po przekroczeniu wartości określonej w polityce grupowej.
• User must change password at next logon – wymaga zmiany hasła użytkownika przy kolejnym logowaniu.

Sekcje Default Database oraz Default language są widowczne zarówno dla loginu z autoryzacją SQL jak i Windows. W Default Database określamy domyślną bazę danych dla loginu.

Na stronie Server Roles wybieramy role serwerowe, przypisane dla tego loginu

• bulkadmin – zezwala na operację masowego wstawiania danych (BULK INSERT),
• dbcreator – zezwala na tworzenie, usuwanie,  modyfikację bazy danych oraz dodawanie do niej nowych członków (CREATE DATABASE),
• diskadmin – zezwala na zarządzanie plikami .mdf i .ldf (ALTER),
• processadmin – zezwala na kontrolę procesów (ALTER ANY CONNECTION oraz ALTER SERVER STATE),
• securityadmin – zezwala na zarządzanie loginami i uprawnieniami (ALTER ANY LOGIN),
• serveradmin – zezwala na konfigurację całego serwera (ALTER SERVER STATE, ALTER SETTINGS, SHUTDOWN),
• setupadmin –zezwala na zarządzanie serwerami połączonymi (ALTER ANY LINKED SERVER),
• sysadmin – zezwala na pełną kontrolę nad bazami danych (CONTROL SERVER with GRANT),

Na stronie User Mapping zaznaczamy do której bazy użytkownik będzie posiadał uprawnienia (Users mapped to this login), a następnie w sekcji Database role membership for : nazwa_bazy  zaznaczamy role, które chcemy nadać:

• db_accessadmin – zezwala na dodawanie i usuwanie kont,
• db_backupoperator – zezwala na wykonywanie kopii zapasowych,
• db_datareader – zezwala na odczyt baz danych,
• db_datawriter – zezwala na zapisywanie i modyfikację baz danych,
• db_ddladmin – zezwala na modyfikację i usuwanie obiektów baz danych,
• db_denydatareader – nie zezwala na odczyt baz danych,
• db_dentydatawriter – nie zezwala na zapisywanie i modyfikację baz danych,
• db_owner – zezwala na pełną kontrolę nad bazą danych,
• db_securityadmin – zezwala na zarządzanie uprawnieniami oraz rolami baz danych,
• public – rola domyślna, zapewniająca minimum uprawnień.

Strona Securables służy do przypisywania uprawnień do obiektów zabezpieczanych dla tego loginu.

Strona Status służy  do nadawania lub odejmowania uprawnień dla loginu do łączenia się z bazą danych (Permission to connect to Database engine) oraz blokowania konta.

Poniżej znajduje się skrypt wykonanej powyżej operacji:

USE [master]
GO
CREATE LOGIN [monika] WITH PASSWORD=N’Pa$$w0rd’ MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
EXEC master..sp_addsrvrolemember @loginame = N’monika’, @rolename = N’sysadmin’
GO
USE [moja_baza]
GO
CREATE USER [monika] FOR LOGIN [monika]
GO
USE [moja_baza]
GO
EXEC sp_addrolemember N’db_datareader’, N’monika’
GO
USE [moja_baza]
GO
EXEC sp_addrolemember N’db_datawriter’, N’monika’
GO
USE [moja_baza]
GO
EXEC sp_addrolemember N’db_owner’, N’monika’
GO

W przypadku tworzenia loginu jako użytkownika Windows, skrypt będzie wyglądał następująco:

USE [master]
GO
CREATE LOGIN [FREYA\pyszczek] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = N’FREYA\pyszczek’, @rolename = N’sysadmin’
GO
USE [baza]
GO
CREATE USER [FREYA\pyszczek] FOR LOGIN [FREYA\pyszczek]
GO
USE [baza]
GO
EXEC sp_addrolemember N’db_datareader’, N’FREYA\pyszczek’
GO
USE [baza]
GO
EXEC sp_addrolemember N’db_datawriter’, N’FREYA\pyszczek’
GO
USE [baza]
GO
EXEC sp_addrolemember N’db_owner’, N’FREYA\pyszczek’
GO