Rozwijając katalog System Databases z poziomu MS SQL Server Managent Studio widzimy cztery bazy
MASTER
Jest główną z punktu widzenia instalacji bazą danych.
Przechowywane w niej są informacje o instancji: loginy, linked server configuration , service broker endpoints, informacje o bazach zdefiniowanych w instancji serwera.
MSDB
Jest bazą służącą do obsługi agenta SQL, przechowuje jobs i ich Schedule, alerty, operators oraz całą historię. W bazie tej przechowywane są też informacje o tworzeniu i odtwarzaniu kopii zapasowych baz (backup i restore), konfiguracja i logio database mail, maintenence plan.
TEMPDB
Jest bazą tymczasową służącą do przechowywania obiektów tymczasowych, zmiennych, kursorów, wspomaga sortowanie i przebudowania indeksów.
Baza tymczasowa jest tworzona przy każdym restarcie instancji serwera, pozwala nam to w prosty sposób sprawdzić kiedy był ostatni restart
SELECT create_date AS [ostatni restart SQL]
FROM sys.databases
WHERE name = 'tempdb'
MODEL
Jest bazą wzorcową dla instancji serwera wszystkie nowo tworzone bazy są tworzone poprzez kopiowanie tej bazy, także tempdb (dla tempdb ustaiony jest tryb recovery na simple niezależnie od tego jaki jest w model).
Jeżeli masz jakieś ustawienia które musisz zastosować w każdej nowej bazie, userów, tabele możesz je dodać w bazie model i będą się pojawiały w każdej stworzonej bazie.
Dodatkowe bazy systemowe pojawia się podczas tworzenia replikacji
DISTRIBUTION
Jest bazą służącą do przechowywania informacji potrzebnych do realizacji usługi replikacji
DISTRIBUTION MODEL
Pełni podobną rolę jak baza model tylko dla bazy distribution, nie występuje w wersji 2008
Jest jeszcze niewidoczna z poziomu management studio baza
RESOURCE
w niektórych miejscach baza ta przedstawia się MSSQLSYSTEMRESOURCE
w niektórych miejscach baza ta przedstawia się MSSQLSYSTEMRESOURCE
USE [master]
Jest bazą przechowującą obiekty systemowe: procedury, widoki, funkcje.
W folderze z innymi bazami systemowymi pojawią się pliki mssqlsystemresource.mdf i mssqlsystemresource.ldf.
Twórcy bardzo postarali się żeby ją ukryć, nie widać jej z poziomu SSMS
select name ,database_id from sys.databases
name database_id
------------------------ -----------
master 1
tempdb 2
model 3
msdb 4
mydatabase 5
Nie widać również śladu po fizycznych plikach bazy.
SELECT * FROM sys.sysaltfiles
WHERE filename LIKE '%resource%'
--------------------
(0 row(s) affected)
Dopiero po uruchomieniu serwera w trybie single-user mode i połączeniu DAC możemy się do niej odwołać
USE mssqlsystemresource
go
SELECT db_name ()
--------------------------------
mssqlsystemresource
(1 row(s) affected)
Po podłączeniu się do serwera przy pomocy DAC możemy zapytać o bazy systemowe
SELECT id,name FROM master.sys.sysdbreg
order by id
id name
----------- ----------------
1 master
2 tempdb
3 model
4 msdb
....
32767 mssqlsystemresource
(16 row(s) affected)
32767 to 0x7fff a ta wartość pojawia się w definicji widoku
sp_helptext [sys.databases]
----------
CREATE VIEW sys.databases AS
SELECT d.name, d.id AS database_id,
r.indepid AS source_database_id,
d.sid AS owner_sid,
d.modified AS create_date,
convert(tinyint, (d.category/256) & 0xff) AS compatibility_level,
convert(sysname, CollationPropertyFromID(p.cid, 'name')) AS collation_name,
p.user_access, ua.name AS user_access_desc,
sysconv(bit, d.status & 0x400) AS is_read_only, -- DBR_RDONLY
sysconv(bit, d.status & 1) AS is_auto_close_on, -- DBR_CLOSE_ON_EXIT
sysconv(bit, d.status & 0x400000) AS is_auto_shrink_on, -- DBR_AUTOSHRINK
p.state, st.name AS state_desc,
sysconv(bit, d.status & 0x200000) AS is_in_standby, -- DBR_STANDBY
sysconv(bit, d.status & 0x40000000) AS is_cleanly_shutdown, -- DBR_CLEANLY_SHUTDOWN
sysconv(bit, d.status & 0x80000000) AS is_supplemental_logging_enabled, -- DBR_SUPPLEMENT_LOG
p.snapshot_isolation_state, si.name AS snapshot_isolation_state_desc,
sysconv(bit, d.status & 0x800000) AS is_read_committed_snapshot_on, -- DBR_READCOMMITTED_SNAPSHOT
p.recovery_model, ro.name AS recovery_model_desc,
p.page_verify_option, pv.name AS page_verify_option_desc,
sysconv(bit, d.restat & 0x1000000) AS is_auto_create_stats_on, -- DBR_AUTOCRTSTATS
sysconv(bit, d.restat & 0x40000000) AS is_auto_update_stats_on, -- DBR_AUTOUPDSTATS
sysconv(bit, d.restat & 0x80000000) AS is_auto_update_stats_async_on, -- DBR_AUTOUPDSTATSASYNC
sysconv(bit, d.restat & 0x4000) AS is_ansi_null_default_on, -- DBR_ANSINULLDFLT
sysconv(bit, d.restat & 0x4000000) AS is_ansi_nulls_on, -- DBR_ANSINULLS
sysconv(bit, d.restat & 0x2000) AS is_ansi_padding_on, -- DBR_ANSIPADDING
sysconv(bit, d.restat & 0x10000000) AS is_ansi_warnings_on, -- DBR_ANSIWARNINGS
sysconv(bit, d.restat & 0x1000) AS is_arithabort_on, -- DBR_ARITHABORT
sysconv(bit, d.restat & 0x10000) AS is_concat_null_yields_null_on, -- DBR_CATNULL
sysconv(bit, d.restat & 0x800) AS is_numeric_roundabort_on, -- DBR_NUMEABORT
sysconv(bit, d.restat & 0x800000) AS is_quoted_identifier_on, -- DBR_QUOTEDIDENT
sysconv(bit, d.restat & 0x20000) AS is_recursive_triggers_on, -- DBR_RECURTRIG
sysconv(bit, d.restat & 0x2000000) AS is_cursor_close_on_commit_on, -- DBR_CURSCLOSEONCOM
sysconv(bit, d.restat & 0x100000) AS is_local_cursor_default, -- DBR_DEFLOCALCURS
sysconv(bit, d.restat & 0x20000000) AS is_fulltext_enabled, -- DBR_FTENABLED
sysconv(bit, d.restat & 0x200) AS is_trustworthy_on, -- DBR_TRUSTWORTHY
sysconv(bit, d.restat & 0x400) AS is_db_chaining_on, -- DBR_DBCHAINING
sysconv(bit, d.restat & 0x08000000) AS is_parameterization_forced, -- DBR_UNIVERSALAUTOPARAM
sysconv(bit, d.restat & 64) AS is_master_key_encrypted_by_server, -- DBR_MASTKEY
sysconv(bit, d.category & 1) AS is_published,
sysconv(bit, d.category & 2) AS is_subscribed,
sysconv(bit, d.category & 4) AS is_merge_published,
sysconv(bit, d.category & 16) AS is_distributor,
sysconv(bit, d.category & 32) AS is_sync_with_backup,
d.svcbrkrguid AS service_broker_guid,
sysconv(bit, case when d.scope = 0 then 1 else 0 end) AS is_broker_enabled,
p.log_reuse_wait, lr.name AS log_reuse_wait_desc,
sysconv(bit, d.restat & 0x00000004) AS is_date_correlation_on -- DBR_DATECORRELATIONOPT
FROM master.sys.sysdbreg d OUTER APPLY OpenRowset(TABLE DBPROP, d.id) p
LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0 -- SRC_VIEWPOINTDB
LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state
LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access
LEFT JOIN sys.syspalvalues si ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state
LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model
LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option
LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait
WHERE d.id < 0x7fff
AND has_access('DB', d.id) = 1
Jest możliwość podejrzenia co w nich jest możemy zatrzymać instancję SQL Server skopiować te pliki, uruchomić instancję i zrobić attach.
GO
CREATE DATABASE [rk_source] ON
( FILENAME = N'C:\TEST\mssqlsystemresource.mdf' ),
( FILENAME = N'C:\TEST\mssqlsystemresource.ldf' )
FOR ATTACH
Okazuje się że procedury, widoki systemowe są już widoczne również jako zwykłe obiekty bazy danych, pod prawym przyciskiem pojawia się opcja modify
Możemy obejrzeć tabele
select name from rk_source.sys.all_objects
where type='U'
-----------
name
-------------------
syspalvalues
spt_server_info
spt_datatype_info
role_permissions
sysbinpals
syscolrdb
spt_provider_types
syspalnames
spt_permission_names
sysobjrdb
spt_datatype_info_ext
(11 row(s) affected)
To, że baza resourse jest używana przy zapytaniach możemy sprawdzić ustawiając
SET SHOWPLAN_TEXT ON
StmtText
-------------------------------
select * from sys.sql_logins
(1 row(s) affected)
StmtText
--------------------------------------------------------------------
|--Nested Loops(Left Outer Join)
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([p].[id]))
| |--Compute Scalar(DEFINE:([Expr1006]=CONVERT(bit,[master].[sys].[sysxlgns].[status] as [p].[status]&(128),0), [Expr1007]=CONVERT(bit,[master].[sys].[sysxlgns].[status] as [p].[status]&(131072),0), [Expr1008]=CONVERT(bit,[master].[sys].[sysxlgns
| | |--Filter(WHERE:(has_access('LG',[master].[sys].[sysxlgns].[id] as [p].[id])=(1)))
| | |--Clustered Index Scan(OBJECT:([master].[sys].[sysxlgns].[cl] AS [p]), WHERE:([master].[sys].[sysxlgns].[type] as [p].[type]='S'))
| |--Clustered Index Seek(OBJECT:([master].[sys].[syssingleobjrefs].[clst] AS [r]), SEEK:([r].[depid]=[master].[sys].[sysxlgns].[id] as [p].[id] AND [r].[class]=(63) AND [r].[depsubid]=(0)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([mssqlsystemresource].[sys].[syspalnames].[cl] AS [n]), SEEK:([n].[class]='LGTY' AND [n].[value]='S') ORDERED FORWARD)
Podczas wgrywania poprawki SQL Serwer dotyczącej obiektów systemowych podmieniana jest ta baza. Baza wprowadzona by usprawnić wgrywanie poprawek i ich wycofywanie. W niej również jest zapisana wersja serwera.
SELECT attribute_value
FROM rk_source.sys.spt_server_info
WHERE attribute_name ='DBMS_VER'
attribute_value
---------------------------------------
Microsoft SQL Server Yukon
Data ostatniej aktualizacji serwera pokrywa się z datą utworzenia plików bazy
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
-------------------------
2009-12-03 12:18:14.130
Baza ta instaluje się w miejscu wybranym jako lokalizacja baz systemowych serwera, domyślnie w C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\
Jest jedyną bazą której lokalizacji nie można zmieniać po zainstalowaniu instancji.
Brak komentarzy:
Prześlij komentarz