SQL Server System Views

they expose database metadata.

Catelog views:

Catalog views return information that is used by the SQL Server Database Engine. All user-available catalog metadata is exposed through catalog views.

some examples:

sys.databases, sys.database_files,sys.master_files

sys.linked_logins, sys.remote_logins,sys.servers

sys.objects, sys.tables, sys.indexes,sys.views, sys.procedures, sys.partitions,sys.schemas

Dynamic Management views and functions:

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. Dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in this release.

All dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. you must prefix the name of the view or function by using the sys schema.

some examples:

sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_sql_text

sys.dm_tran_database_transactions

Compatibility views:

Many of the system tables from earlier releases of SQL Server are now implemented as a set of views. These views are known as compatibility views, and they are meant for backward compatibility only. The compatibility views expose the same metadata that was available in SQL Server 2000. However, the compatibility views do not expose any of the metadata related to features that are introduced in SQL Server 2005 and later. Therefore, when you use new features, such as Service Broker or partitioning, you must switch to using the catalog views.

some examples:

you can use sys. prefix in front of the sysxxx.

sysusers, sysprocesses, sysobjects,syscolumns, sysdatabases, sysservers

Replication Views:

views are available in different related databases: msdb, distribution, publication, subscription databases.

Information Schema Views:

One of several methods SQL Server provides for obtaining metadata. it comply with ISO standard definition of INFORMATION_SCHEMA. Good for generic purpose DB tools to use.

Information schema views are defined in a special schema named INFORMATION_SCHEMA. This schema is contained in each database. Each information schema view contains metadata for all data objects stored in that particular database

End a database restoration process

following all those with norecovery, issue an statement like this to put database back to operational.

restore database xxx with recovery

SQL Mirroring

comprehensive explanation:

http://msdn.microsoft.com/en-us/library/cc917680.aspx

Because database mirroring is so fast, it is much more suitable for keeping a hot standby of a publisher database.

http://msdn.microsoft.com/en-us/library/ms189852.aspx

Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions

There are two mirroring operating modes:

high-safety mode, high-performance mode

One of them, high-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.

The second operating mode, high-performance mode, runs asynchronously. The mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. However, typically, the gap between the databases is small. However, the gap can become significant if the principal server is under a heavy work load or the system of the mirror server is overloaded.

1. make full backup, as well as a log backup

2. restore mirror database with norecovery on both database and logs

3. if sql server runs under network service, it need to create login for machine

create login [domain\machine$] from windows with default_database=[a database, maybe master]

later, after an endpoint was created, connect privilege should be granted

grant connect on endpoint::endpointname to [domain\machine$]

If windows are in domain, it'd be easier to just run under same domain user, create

login in sql server for the domain user, and grant connect on endpoint to it.

4. on principle db, you can set up mirroring with wizard. if it's successful, the status should be

fully synchronized. Extra information will also be added in database name in the management studio, such as (Principle, Synchronized)

5. it can also be configured with scripts. here are some statements for reference.

restore database xxx from disk='xxx' with norecovery

restore log xxx form disk='xxx' with norecovery

create endpoint xxx state=started as tcp(listener_port=xxxx, listener_ip=all or xxx)

for database_mirroring (role=parnter|all)

create login [xxx] xxx

grant connect on endpoint:xxx to [xxx]

alter database xxx set partner='tcp://xxx.xxx.xxx:port'

--turn off mirroring

alter database xxx set partner off

drop endpoint mirroring

--check status

select * from sys.database_mirroring_endpoints

select * from sys.database_mirroring

OR USE DATABASE MIRRORING MONITOR which requires host name instead of ip

manual fail over

On the former principal, clients are disconnected from the database and in-flight transactions are rolled back.

1. using SSMS

2. using t-sql

use master

go

ALTER DATABASE database_name SET PARTNER FAILOVER

where database_name is the mirrored database.

Client side redirect

example:

"Data Source=A;Failover Partner=B;Initial Catalog=AdventureWorks;Integrated Security=True;"

Force service

ALTER DATABASE  SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Knowledge point to prepare

1. backup recovery (syntax and scenario based) 2. High availability (log shipping, replication, mirroring, clustering) 3. Security(authentication modes, impersonating) 4. Architecture bases theoretical questions 5. transaction log based questions (u need to know how transaction log works) 6. tempdb (its purpose) 7. Installation (troubleshooting) 8. various editions 9. upgrade(troubleshooting/precautions) 10. Migration (login/job scripting/precautions)

Blocking and detection

What is blocking and how would you troubleshoot it?

Blocking occurs when a process has acquired lock on a set of rows, and another process is trying to acquire a lock on the same set of rows. In such a case, the other process has to wait until the first process finishes its job and releases the lock on the above said rows.

• Use sp_lock procedure to see type of locks acquired by various sessions on the server to find the cause of blocking.
• Problem is hinted by the WAIT status is a lot of rows that are returned as an output of sp_lock stored procedure execution.
• Use sp_who and sp_who2 to return more columns to get more information around the blocking.
• Use DBCC INPUTBUFFER (spid).This will show the last 128 characters of the last T-SQL statement executed from connection referred through spid. This way one can identify the stored procedure or application module that caused blocking.
• To resolve blocking, you can disconnect the connection causing the blocking using KILL command. If this does not solve the problem permanently, then rewrite the stored procedure/module causing the block more efficiently.

sp_lock

sp_who2

select * from master..sysprocesses

(

select spid,cmd,program_name,blocked,waittime,waitresource,lastwaittype from master..sysprocesses WHERE spid in (66,64) and dbid=7

)

to view the last issued statement, select * from ::fn_get_sql(sql handle returned from sysprocesses)

SQL Server Services

From online book.
http://msdn.microsoft.com/en-us/library/ms143504%28v=sql.90%29.aspx

• SQL Server Database Services - The service for the SQL Server relational Database Engine.
  
• SQL Server Agent - Executes jobs, monitors SQL Server, fires alerts, and allows automation of some administrative tasks.
  

  Note:

  For SQL Server and SQL Server Agent to run as services in Windows, SQL Server and SQL Server Agent must be assigned a Windows user account. Typically, both SQL Server and SQL Server Agent are assigned the same user account—either the local system or a domain user account. However, you can customize the settings for each service during the installation process. For more information about how to customize account information for each service, see Service Account.

• Analysis Services - Provides online analytical processing (OLAP) and data mining functionality for business intelligence applications.
  
• Reporting Services - Manages, executes, renders, schedules, and delivers reports.
  
• Notification Services - A platform for developing and deploying applications that generate and send notifications.
  
• Integration Services - Provides management support for Integration Services package storage and execution.
  
• Full-Text Search - Quickly creates full-text indexes on content and properties of structured and semi-structured data to allow fast linguistic searches on this data.
  
• SQL Server Browser - The name resolution service that provides SQL Server connection information for client computers.
  
• SQL Server Active Directory Helper - Publishes and manages SQL Server services in Active Directory.
  
• SQL Writer - Allows backup and restore applications to operate in the Volume Shadow Copy Service (VSS) framework.

SQL server XML operation sample

with lan as
(SELECT progmsgid,msgTime,
MsgText.value('(/s2sMessage/s2sHeader/@messageId)[1]', 'varchar(20)') AS messageId
FROM progressivemsglan),
wan as
(
SELECT progmsgid,msgTime,
MsgText.value('(/s2sMessage/s2sHeader/@messageId)[1]', 'varchar(20)') AS messageId
FROM progressivemsgwan
)
SELECT lan.progmsgid,lan.messageid,lan.msgtime,wan.messageid,wan.msgtime
FROM lan left join wan on lan.messageid=wan.messageid
--where wan.messageid is null
order by lan.progmsgid desc

SQL Server, Database mirroring

--principle and mirror role(hot standby server).
--All database mirroring sessions support only one principal server and one mirror server
--High-safety mode with automatic failover requires a third server instance, known as a witness

role switching
roling upgrade

Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions

operation mode:
high-safety==synchronous ,
high-performance== asynchronous

openrowset,

openrowset
Includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.

opendatasource

The OPENDATASOURCE function can be used in the same Transact-SQL syntax locations as a linked server name. Thus, OPENDATASOURCE can be used as the first part of a four-part name that refers to a table or view name in a SELECT, INSERT, UPDATE, or DELETE statement, or to a remote stored procedure in an EXECUTE statement. When executing remote stored procedures, OPENDATASOURCE should refer to another SQL Server. OPENDATASOURCE does not accept variables for its arguments.

Like the OPENROWSET function, OPENDATASOURCE should only reference OLE DB data sources accessed infrequently. Define a linked server for any data sources accessed more than a few times. Neither OPENDATASOURCE, nor OPENROWSET provide all the functionality of linked server definitions, such as security management and the ability to query catalog information. All connection information, including passwords, must be provided each time OPENDATASOURCE is called.

SELECT   * FROM      OPENDATASOURCE(          'SQLOLEDB',          'Data Source=ServerName;User ID=MyUID;Password=MyPass'          ).Northwind.dbo.Categories

openquery
Executes the specified pass-through query on the given linked server, which is an OLE DB data source. The OPENQUERY function can be referenced in the FROM clause of a query as though it is a table name. The OPENQUERY function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

EXEC sp_addlinkedserver 'OracleSvr',     'Oracle 7.3',     'MSDAORA',     'ORCLDB' GO SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')  GO

setup SQL server replication using backup files

from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127368
/* Steps to set up replication using backup files instead of snapshots  1. Run the Create Publication wizard on the publisher. Make sure NOT to create a snapshot.  2. Once the wizard has finished, right-click the new publication, choose Properties    and make sure that the "Allow initialization from backup files" is set to True  3. Disable the "Distribution clean up: distribution" job to make sure that no     commands are deleted from MSrepl_commands before the entire backup/restore operation     is complete  4. Create a full database backup to disk and make sure that the file is available for the     publisher until the entire replication setup is finished.   5. Transfer the backup file to the subscriber server and restore it there. If you do     regular backups to disk make sure to use one that was taken *after* the publication     was created. Also restore trans log backups taken after the full backup. The last backup    should be restored using WITH RECOVERY, and keep in mind that a more recent backup saves     you time in the syncing process when the replication is being initialized.  6. Disable all triggers on the subscriber database:    EXEC sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'  7. Disable all constraints on subscriber database:    EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'   8. Run the following script ON THE PUBLISHING SERVER IN THE PUBLISHING DATABASE to enable     the replication:    EXEC sp_addsubscription      @publication ='myPublication',              --> Name of the publication     @subscriber = 'myserver.myDomain.com',      --> Fully qualified name or IP of subscriber server     @destination_db ='MySubscriberDB',          --> Name of the database you just restored (doesn't have to be the same as the publisher)     @sync_type = 'initialize with backup',      --> no need to change this     @backupdevicetype = 'disk',                 --> no need to change this     @backupdevicename = 'F:\backupfile.bak'     --> Pointer to the last backupfile that was restored, but from the folder on the on the                                                       publishing server. If you restored trans logs also the last translog file is what you                                                      need to put here  9. Enable the "Distribution clean up: distribution" job again  */

openrowset

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'ip';'sa';'password',
'SELECT *
FROM tbl'
) AS a;