这里可以下载这幅图的pdf格式文档
本来打算传原图供大家下载后利用大图片打印工具在普通打印机机上打印出来.
自己实践了一下,发现下载的图片已经被压缩了.
2009年5月13日星期三
2009年5月12日星期二
SQL Server 2008 R2 新特性
消息来源
Capitalize on Hardware Innovation
Increase in the number of logical processors supported from 64 up to 256. This will provide customers with more choices for obtaining single system scalability with high performance.
Optimize Hardware Resources
Dashboard viewpoints provide real-time insight into utilization and policy violations to help identify consolidation opportunities, maximize investments and maintain healthy systems.
Manage Efficiently at Scale
Through new extensions in SQL Server Management Studio, organizations will gain insights into their growing applications and databases and help ensure higher service levels through policies and dashboard viewpoints.
Enhance Collaboration Across Development and IT
Streamline Application Lifecycle Management through integration with Visual Studio. A new project type enables a single unit of management for packaging database schema with application requirements. This ensures higher quality application development while also accelerating deployments, moves, and changes over time.
Improve the Quality of Your Data
Centralized approach to defining, deploying, and managing master data can ensure reporting consistency across systems and deliver faster more accurate results across the enterprise.
Manage User-Generated Analytical Applications
Comprehensive management thru Microsoft SharePoint gives IT the ability to manage and secure all BI assets, thus freeing the original authors to focus on the priorities of the business.
Report with Ease
Decrease time and costs developing reports by giving users the ability to design their own queries, reports and charts through powerful and intuitive authoring and ad hoc reporting capabilities.
Get More Out of Your Data
New support for geospatial visualization can produce new insights and discoveries when geospatial data is combined with corporate data for reporting and analysis.
Build Robust Analytical Applications
With the in-memory analytics add-in for Microsoft Excel 2010, business users can quickly access, analyze and summarize vast amounts of data directly in Excel without the assistance of the IT department.
Consolidate Your Data
New “data mash up” capabilities will simplify time consuming data gathering and consolidation tasks. Integrate data from multiple sources, including corporate databases and external sources, using powerful tools within Microsoft Excel.
Share and Collaborate with Confidence
New collaboration tools make it easy to share analytic applications and reports through Microsoft Office SharePoint, where they are refreshed automatically, maintained, and made accessible to others.
上面的都是官方宣传词,下面这些是DBA们感兴趣的了
1. 对多核CPU的支持加强,从原来的64核增加到了256核,这样可以提高原有系统的性能和增加扩展性.
2. Management Studio的新特性:
2.1 新向导将帮助DBA快速发现环境中的数据库并加入到集中管理;
2.2 DBA可以通过管理的目标服务器或者集中管理服务器的程序执行统一的期望阀值等政策;
2.3 仪表板(DashBoard)将提供实时图像,反映资源利用率和政策符合度,这些将帮助管理员发现系统需要加固的地方,最大化资源利用率及强化系统健康;
2.4 Visual Studio的整合
加强了开发和运维的联系,新增的包类型可以使DBA参与到开发中,并方便部署
3. Master Data Services (MDS)
4. 对Excel2010 sharepoint 2010支持的增强;
5. Reporting Service 2008 R2增强,支持了更丰富的图形.
Capitalize on Hardware Innovation
Increase in the number of logical processors supported from 64 up to 256. This will provide customers with more choices for obtaining single system scalability with high performance.
Optimize Hardware Resources
Dashboard viewpoints provide real-time insight into utilization and policy violations to help identify consolidation opportunities, maximize investments and maintain healthy systems.
Manage Efficiently at Scale
Through new extensions in SQL Server Management Studio, organizations will gain insights into their growing applications and databases and help ensure higher service levels through policies and dashboard viewpoints.
Enhance Collaboration Across Development and IT
Streamline Application Lifecycle Management through integration with Visual Studio. A new project type enables a single unit of management for packaging database schema with application requirements. This ensures higher quality application development while also accelerating deployments, moves, and changes over time.
Improve the Quality of Your Data
Centralized approach to defining, deploying, and managing master data can ensure reporting consistency across systems and deliver faster more accurate results across the enterprise.
Manage User-Generated Analytical Applications
Comprehensive management thru Microsoft SharePoint gives IT the ability to manage and secure all BI assets, thus freeing the original authors to focus on the priorities of the business.
Report with Ease
Decrease time and costs developing reports by giving users the ability to design their own queries, reports and charts through powerful and intuitive authoring and ad hoc reporting capabilities.
Get More Out of Your Data
New support for geospatial visualization can produce new insights and discoveries when geospatial data is combined with corporate data for reporting and analysis.
Build Robust Analytical Applications
With the in-memory analytics add-in for Microsoft Excel 2010, business users can quickly access, analyze and summarize vast amounts of data directly in Excel without the assistance of the IT department.
Consolidate Your Data
New “data mash up” capabilities will simplify time consuming data gathering and consolidation tasks. Integrate data from multiple sources, including corporate databases and external sources, using powerful tools within Microsoft Excel.
Share and Collaborate with Confidence
New collaboration tools make it easy to share analytic applications and reports through Microsoft Office SharePoint, where they are refreshed automatically, maintained, and made accessible to others.
上面的都是官方宣传词,下面这些是DBA们感兴趣的了
1. 对多核CPU的支持加强,从原来的64核增加到了256核,这样可以提高原有系统的性能和增加扩展性.
2. Management Studio的新特性:
2.1 新向导将帮助DBA快速发现环境中的数据库并加入到集中管理;
2.2 DBA可以通过管理的目标服务器或者集中管理服务器的程序执行统一的期望阀值等政策;
2.3 仪表板(DashBoard)将提供实时图像,反映资源利用率和政策符合度,这些将帮助管理员发现系统需要加固的地方,最大化资源利用率及强化系统健康;
2.4 Visual Studio的整合
加强了开发和运维的联系,新增的包类型可以使DBA参与到开发中,并方便部署
3. Master Data Services (MDS)
4. 对Excel2010 sharepoint 2010支持的增强;
5. Reporting Service 2008 R2增强,支持了更丰富的图形.
2009年5月9日星期六
如何使用SQL SERVER 数据库管理员专用连接（DAC）
使用专用管理员连接
SQL Server 为管理员提供了一种特殊的诊断连接，以供在无法与服务器建立标准连接时使用。即使在 SQL Server 不响应标准连接请求时，管理员也可以使用此诊断连接访问 SQL Server，以便执行诊断查询并解决问题。
此专用管理员连接 (DAC) 支持 SQL Server 的加密功能和其他安全功能。DAC 只允许将用户上下文切换到其他管理用户。
SQL Server 尽力使 DAC 连接成功，但在非常特殊的情况下也可能会出现连接失败。
使用 DAC 连接
默认情况下，只能从服务器上运行的客户端建立连接。不允许进行网络连接，除非它们是使用带 remote admin connections 选项的 sp_configure 存储过程配置的。
只有 SQL Server sysadmin 角色的成员可以使用 DAC 连接。
数据库实例同一时间仅支持一个专用连接。
通过使用专用的管理员开关 (-A) 的 sqlcmd 命令提示实用工具，可以支持和使用 DAC。有关使用 sqlcmd 的详细信息，请参阅将 sqlcmd 与脚本变量结合使用。您还可以将前缀 admin: 连接到实例名上，格式为 sqlcmd -Sadmin:。还可以通过连接到 admin:<实例名>，从 SQL Server Management Studio 查询编辑器启动 DAC。
限制
由于 DAC 仅用于在极少数情况下诊断服务器问题，因此对连接有一些限制：
为了保证有可用的连接资源，每个 SQL Server 实例只允许使用一个 DAC。如果 DAC 连接已经激活，则通过 DAC 进行连接的任何新请求都将被拒绝，并出现错误 17810。
为了保留资源，SQL Server Express 不侦听 DAC 端口，除非使用跟踪标志 7806 进行启动。
DAC 最初尝试连接到与登录帐户关联的默认数据库。连接成功后，可以连接到 master 数据库。如果默认数据库脱机或不可用，则连接返回错误 4060。但是，如果使用以下命令覆盖默认数据库，改为连接到 master 数据库，则连接会成功：
sqlcmd –A –d master
由于只要启动数据库引擎实例，就能保证 master 数据库处于可用状态，因此建议使用 DAC 连接到 master 数据库。
SQL Server 禁止使用 DAC 运行并行查询或命令。例如，如果使用 DAC 执行下列任何语句，都会生成错误 3637。
RESTORE
BACKUP
DAC 只能使用有限的资源。请勿使用 DAC 运行需要消耗大量资源的查询（例如，对大型表执行复杂的联接）或可能造成阻塞的查询。这有助于防止将 DAC 与任何现有的服务器问题混淆。为了避免发生潜在的阻塞情况，如果必须执行可能会发生阻塞的查询，则尽可能在基于快照的隔离级别下运行查询；或者，将事务隔离级别设置为 READ UNCOMMITTED，将 LOCK_TIMEOUT 值设置为较短的值（如 2000 毫秒），或者同时执行这两种操作。这可以防止 DAC 会话被阻塞。但是，根据 SQL Server 所处的状态，DAC 会话可能会在闩锁上被阻塞。可以使用 CNTRL-C 终止 DAC 会话，但不能保证一定成功。如果失败，唯一的选择是重新启动 SQL Server。
为保证连接成功并排除 DAC 故障，SQL Server 保留了一定的资源用于处理 DAC 上运行的命令。通常这些资源只够执行简单的诊断和故障排除功能，如下所示。
虽然理论上可以运行任何不必在 DAC 上并行执行的 Transact-SQL 语句，但极力建议您限制使用下列诊断和故障排除命令：
查询动态管理视图以进行基本的诊断，例如查询 sys.dm_tran_locks 以了解锁定状态，查询 sys.dm_os_memory_cache_counters 以检查缓存质量，查询 sys.dm_exec_requests 和 sys.dm_exec_sessions 以了解活动的会话和请求。避免使用需要消耗大量资源的动态管理视图（例如，sys.dm_tran_version_store 扫描整个版本存储区，并且会导致大量的 I/O）或使用了复杂联接的动态管理视图。有关性能影响的信息，请参阅特定动态管理视图的文档。
查询目录视图。
基本 DBCC 命令，例如 DBCC FREEPROCCACHE、DBCC FREESYSTEMCACHE、DBCC DROPCLEANBUFFERS, 和 DBCC SQLPERF。请勿运行需要消耗大量资源的命令，如 DBCC CHECKDB、DBCC DBREINDEX 或 DBCC SHRINKDATABASE。
Transact-SQL KILL 命令。根据 SQL Server 的状态，KILL 命令并非一定会成功；如果失败，则唯一的选择是重新启动 SQL Server。下面是一般的指导原则：
请通过查询 SELECT * FROM sys.dm_exec_sessions WHERE session_id = 来验证 SPID 是否已被实际终止。如果没有返回任何行，则表明会话已被终止。
如果会话仍在运行，则通过运行查询 SELECT * FROM sys.dm_os_tasks WHERE session_id = 来验证是否为此会话分配了任务。如果发现还有任务，则很可能当前正在终止会话。注意，此操作可能会持续很长时间，也可能根本不会成功。
如果在与此会话关联的 sys.dm_os_tasks 中没有任何任务，但是在执行 KILL 命令后该会话仍然出现在 sys.dm_exec_sessions 中，则表明没有可用的工作线程。选择某个当前正在运行的任务（在 sys.dm_os_tasks 视图中列出的 sessions_id <> NULL 的任务），并终止与其关联的会话以释放工作线程。请注意，终止单个会话可能不够，可能需要终止多个会话。
DAC 端口
SQL Server 在启动数据库引擎时动态分配的专用 TCP/IP 端口上侦听 DAC。错误日志包含所侦听的 DAC 所在的端口号。默认情况下，DAC 侦听器只接受本地端口上的连接。有关激活远程管理员连接的代码示例，请参阅 remote admin connections 选项。
配置远程管理连接之后，会立即启用 DAC 侦听器而不必重新启动 SQL Server，并且客户端可以立即远程连接到 DAC。通过先在本地使用 DAC 连接到 SQL Server，然后再执行 sp_configure 存储过程接受远程连接，则即使 SQL Server 停止响应，DAC 侦听器仍然可以接受远程连接。
对于群集配置，DAC 在默认情况下是禁用的。用户可以执行 sp_configure 的 remote admin connection 选项，使 DAC 侦听器能够访问远程连接。如果 SQL Server 停止响应并且未启用 DAC 侦听器，则可能必须重新启动 SQL Server 来连接 DAC。因此，建议在群集系统上启用 remote admin connections 配置选项。
DAC 端口由 SQL Server 在启动时动态分配。当连接到默认实例时，DAC 会避免在连接时对 SQL Server Browser 服务使用 SQL Server 解决协议 (SSRP) 请求。它先通过 TCP 端口 1434 进行连接。如果失败，则通过 SSRP 调用来获取端口。如果 SQL Server 浏览器没有侦听 SSRP 请求，则连接请求将返回错误。若要了解 DAC 所侦听的端口号，请参阅错误日志。如果将 SQL Server 配置为接受远程管理连接，则必须使用显式端口号启动 DAC：
sqlcmd –Stcp:,
SQL Server 错误日志列出了 DAC 的端口号，默认情况下为 1434。如果将 SQL Server 配置为只接受本地 DAC 连接，请使用以下命令和环回适配器进行连接：
sqlcmd –S127.0.0.1,1434
示例
在此示例中，管理员发现服务器 URAN123 不响应，因此要诊断该问题。为此，用户激活 sqlcmd 命令提示实用工具，并使用 -A 指明 DAC 连接到服务器 URAN123。
sqlcmd -S URAN123 -U sa -P –A
现在，管理员可以执行查询来诊断问题，并且可以终止停止响应的会话。
SQL Server 为管理员提供了一种特殊的诊断连接，以供在无法与服务器建立标准连接时使用。即使在 SQL Server 不响应标准连接请求时，管理员也可以使用此诊断连接访问 SQL Server，以便执行诊断查询并解决问题。
此专用管理员连接 (DAC) 支持 SQL Server 的加密功能和其他安全功能。DAC 只允许将用户上下文切换到其他管理用户。
SQL Server 尽力使 DAC 连接成功，但在非常特殊的情况下也可能会出现连接失败。
使用 DAC 连接
默认情况下，只能从服务器上运行的客户端建立连接。不允许进行网络连接，除非它们是使用带 remote admin connections 选项的 sp_configure 存储过程配置的。
只有 SQL Server sysadmin 角色的成员可以使用 DAC 连接。
数据库实例同一时间仅支持一个专用连接。
通过使用专用的管理员开关 (-A) 的 sqlcmd 命令提示实用工具，可以支持和使用 DAC。有关使用 sqlcmd 的详细信息，请参阅将 sqlcmd 与脚本变量结合使用。您还可以将前缀 admin: 连接到实例名上，格式为 sqlcmd -Sadmin:
限制
由于 DAC 仅用于在极少数情况下诊断服务器问题，因此对连接有一些限制：
为了保证有可用的连接资源，每个 SQL Server 实例只允许使用一个 DAC。如果 DAC 连接已经激活，则通过 DAC 进行连接的任何新请求都将被拒绝，并出现错误 17810。
为了保留资源，SQL Server Express 不侦听 DAC 端口，除非使用跟踪标志 7806 进行启动。
DAC 最初尝试连接到与登录帐户关联的默认数据库。连接成功后，可以连接到 master 数据库。如果默认数据库脱机或不可用，则连接返回错误 4060。但是，如果使用以下命令覆盖默认数据库，改为连接到 master 数据库，则连接会成功：
sqlcmd –A –d master
由于只要启动数据库引擎实例，就能保证 master 数据库处于可用状态，因此建议使用 DAC 连接到 master 数据库。
SQL Server 禁止使用 DAC 运行并行查询或命令。例如，如果使用 DAC 执行下列任何语句，都会生成错误 3637。
RESTORE
BACKUP
DAC 只能使用有限的资源。请勿使用 DAC 运行需要消耗大量资源的查询（例如，对大型表执行复杂的联接）或可能造成阻塞的查询。这有助于防止将 DAC 与任何现有的服务器问题混淆。为了避免发生潜在的阻塞情况，如果必须执行可能会发生阻塞的查询，则尽可能在基于快照的隔离级别下运行查询；或者，将事务隔离级别设置为 READ UNCOMMITTED，将 LOCK_TIMEOUT 值设置为较短的值（如 2000 毫秒），或者同时执行这两种操作。这可以防止 DAC 会话被阻塞。但是，根据 SQL Server 所处的状态，DAC 会话可能会在闩锁上被阻塞。可以使用 CNTRL-C 终止 DAC 会话，但不能保证一定成功。如果失败，唯一的选择是重新启动 SQL Server。
为保证连接成功并排除 DAC 故障，SQL Server 保留了一定的资源用于处理 DAC 上运行的命令。通常这些资源只够执行简单的诊断和故障排除功能，如下所示。
虽然理论上可以运行任何不必在 DAC 上并行执行的 Transact-SQL 语句，但极力建议您限制使用下列诊断和故障排除命令：
查询动态管理视图以进行基本的诊断，例如查询 sys.dm_tran_locks 以了解锁定状态，查询 sys.dm_os_memory_cache_counters 以检查缓存质量，查询 sys.dm_exec_requests 和 sys.dm_exec_sessions 以了解活动的会话和请求。避免使用需要消耗大量资源的动态管理视图（例如，sys.dm_tran_version_store 扫描整个版本存储区，并且会导致大量的 I/O）或使用了复杂联接的动态管理视图。有关性能影响的信息，请参阅特定动态管理视图的文档。
查询目录视图。
基本 DBCC 命令，例如 DBCC FREEPROCCACHE、DBCC FREESYSTEMCACHE、DBCC DROPCLEANBUFFERS, 和 DBCC SQLPERF。请勿运行需要消耗大量资源的命令，如 DBCC CHECKDB、DBCC DBREINDEX 或 DBCC SHRINKDATABASE。
Transact-SQL KILL
请通过查询 SELECT * FROM sys.dm_exec_sessions WHERE session_id =
如果会话仍在运行，则通过运行查询 SELECT * FROM sys.dm_os_tasks WHERE session_id =
如果在与此会话关联的 sys.dm_os_tasks 中没有任何任务，但是在执行 KILL 命令后该会话仍然出现在 sys.dm_exec_sessions 中，则表明没有可用的工作线程。选择某个当前正在运行的任务（在 sys.dm_os_tasks 视图中列出的 sessions_id <> NULL 的任务），并终止与其关联的会话以释放工作线程。请注意，终止单个会话可能不够，可能需要终止多个会话。
DAC 端口
SQL Server 在启动数据库引擎时动态分配的专用 TCP/IP 端口上侦听 DAC。错误日志包含所侦听的 DAC 所在的端口号。默认情况下，DAC 侦听器只接受本地端口上的连接。有关激活远程管理员连接的代码示例，请参阅 remote admin connections 选项。
配置远程管理连接之后，会立即启用 DAC 侦听器而不必重新启动 SQL Server，并且客户端可以立即远程连接到 DAC。通过先在本地使用 DAC 连接到 SQL Server，然后再执行 sp_configure 存储过程接受远程连接，则即使 SQL Server 停止响应，DAC 侦听器仍然可以接受远程连接。
对于群集配置，DAC 在默认情况下是禁用的。用户可以执行 sp_configure 的 remote admin connection 选项，使 DAC 侦听器能够访问远程连接。如果 SQL Server 停止响应并且未启用 DAC 侦听器，则可能必须重新启动 SQL Server 来连接 DAC。因此，建议在群集系统上启用 remote admin connections 配置选项。
DAC 端口由 SQL Server 在启动时动态分配。当连接到默认实例时，DAC 会避免在连接时对 SQL Server Browser 服务使用 SQL Server 解决协议 (SSRP) 请求。它先通过 TCP 端口 1434 进行连接。如果失败，则通过 SSRP 调用来获取端口。如果 SQL Server 浏览器没有侦听 SSRP 请求，则连接请求将返回错误。若要了解 DAC 所侦听的端口号，请参阅错误日志。如果将 SQL Server 配置为接受远程管理连接，则必须使用显式端口号启动 DAC：
sqlcmd –Stcp:
SQL Server 错误日志列出了 DAC 的端口号，默认情况下为 1434。如果将 SQL Server 配置为只接受本地 DAC 连接，请使用以下命令和环回适配器进行连接：
sqlcmd –S127.0.0.1,1434
示例
在此示例中，管理员发现服务器 URAN123 不响应，因此要诊断该问题。为此，用户激活 sqlcmd 命令提示实用工具，并使用 -A 指明 DAC 连接到服务器 URAN123。
sqlcmd -S URAN123 -U sa -P
现在，管理员可以执行查询来诊断问题，并且可以终止停止响应的会话。
2009年4月21日星期二
SQL Server 2005数据库置疑状态的修复
重起服务器的时候遭遇了数据库置疑状态，在网上查找帖子，发现都是针对SQL2000的，不过借助这个原理，转换了一下SQL语句，成功解决，使用的脚本如下：
这个脚本适应于日志损坏的情况，在SQL Log里有如下信息：
反映的是日志状态不对。
Recovery of database 'SuspectDB' (5) is 96% complete (approximately 0 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
SQL Server detected a DTC/KTM in-doubt transaction with UOW {D5141B98-7BB7-4D42-BF0D-F294CB378AB7}.Please resolve it following the guideline for Troubleshooting DTC Transactions.
错误: 3437，严重性: 21，状态: 3。
An error occurred while recovering database 'SuspectDB'. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (0:32748311). Fix MS DTC, and run recovery again.
错误: 3414，严重性: 21，状态: 2。
An error occurred during recovery, preventing the database 'SuspectDB' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
alter database SuspectDB set emergency
go
alter database SuspectDB set single_user with rollback immediate
go
use master
go
alter database SuspectDB Rebuild Log on
(name=SuspectDB_log,filename='D:\Log\SuspectDB_log.LDF')
go
alter database SuspectDB set multi_user
go
DBCC CHECKDB('SuspectDB')
go
这个脚本适应于日志损坏的情况，在SQL Log里有如下信息：
反映的是日志状态不对。
Recovery of database 'SuspectDB' (5) is 96% complete (approximately 0 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
SQL Server detected a DTC/KTM in-doubt transaction with UOW {D5141B98-7BB7-4D42-BF0D-F294CB378AB7}.Please resolve it following the guideline for Troubleshooting DTC Transactions.
错误: 3437，严重性: 21，状态: 3。
An error occurred while recovering database 'SuspectDB'. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (0:32748311). Fix MS DTC, and run recovery again.
错误: 3414，严重性: 21，状态: 2。
An error occurred during recovery, preventing the database 'SuspectDB' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
alter database SuspectDB set emergency
go
alter database SuspectDB set single_user with rollback immediate
go
use master
go
alter database SuspectDB Rebuild Log on
(name=SuspectDB_log,filename='D:\Log\SuspectDB_log.LDF')
go
alter database SuspectDB set multi_user
go
DBCC CHECKDB('SuspectDB')
go
2009年4月16日星期四
如何重命名 SQL Server 故障转移群集实例
如果 SQL Server 实例包括在故障转移群集中，则重命名虚拟服务器的过程不同于重命名独立实例的过程。有关详细信息，请参阅如何重命名承载 SQL Server 独立实例的计算机。
虚拟服务器的名称始终与 SQL 网络名称（SQL 虚拟服务器的网络名称）相同。尽管您可以更改虚拟服务器的名称，但不能更改实例名。例如，您可以将名为 VS1\instance1 的虚拟服务器更改为其他名称（例如 SQL35\instance1），但是名称的实例部分 (instance1) 将保持不变。
开始重命名进程之前，请阅读下列各项。
除了在复制时使用日志传送的情况之外，SQL Server 不支持对复制所涉及的服务器进行重命名。如果主服务器永久丢失连接，则可以重命名日志传送中的辅助服务器。有关详细信息，请参阅复制和日志传送。
当您重命名被配置为使用数据库镜像的虚拟服务器时，必须在进行重命名操作之前先关闭数据库镜像，然后用新的虚拟服务器名称重新建立数据库镜像。数据库镜像的元数据将不会自动更新来反映新的虚拟服务器名称。
重命名虚拟服务器
使用群集管理器将 SQL 网络名称更改为新名称。
使网络名称资源脱机。这将使 SQL Server 资源和其他相关资源也脱机。
使 SQL Server 资源重新联机。
验证重命名操作
虚拟服务器被重命名之后，任何使用旧名称的连接现在都必须使用新名称来连接。
若要验证重命名操作是否已完成，请从 @@servername 或 sys.servers 中选择信息。@@servername 函数将返回新的虚拟服务器名称，sys.servers 表将显示新的虚拟服务器名称。若要验证故障转移过程是否能够使用新名称正常工作，用户还应尝试将 SQL Server 资源故障转移到其他节点。
对于从群集中任何节点进行的连接，都可以立即使用新名称。但是，对于从客户端计算机使用新名称进行的连接，则必须在新名称对该客户端计算机可见之后，才能使用新名称连接到服务器。根据网络配置，通过网络传播新名称所需的时间长度可能为几秒钟，也可能长至 3 到 5 分钟；旧的虚拟服务器名称在网络上不再可见也可能会需要一些时间。
若要最小化虚拟服务器重命名操作的网络传播延迟，请使用下列步骤：
最小化网络传播延迟
在服务器节点上从命令提示符发出下列命令：
ipconfig /flushdns
ipconfig /registerdns
nbtstat –RR
虚拟服务器的名称始终与 SQL 网络名称（SQL 虚拟服务器的网络名称）相同。尽管您可以更改虚拟服务器的名称，但不能更改实例名。例如，您可以将名为 VS1\instance1 的虚拟服务器更改为其他名称（例如 SQL35\instance1），但是名称的实例部分 (instance1) 将保持不变。
开始重命名进程之前，请阅读下列各项。
除了在复制时使用日志传送的情况之外，SQL Server 不支持对复制所涉及的服务器进行重命名。如果主服务器永久丢失连接，则可以重命名日志传送中的辅助服务器。有关详细信息，请参阅复制和日志传送。
当您重命名被配置为使用数据库镜像的虚拟服务器时，必须在进行重命名操作之前先关闭数据库镜像，然后用新的虚拟服务器名称重新建立数据库镜像。数据库镜像的元数据将不会自动更新来反映新的虚拟服务器名称。
重命名虚拟服务器
使用群集管理器将 SQL 网络名称更改为新名称。
使网络名称资源脱机。这将使 SQL Server 资源和其他相关资源也脱机。
使 SQL Server 资源重新联机。
验证重命名操作
虚拟服务器被重命名之后，任何使用旧名称的连接现在都必须使用新名称来连接。
若要验证重命名操作是否已完成，请从 @@servername 或 sys.servers 中选择信息。@@servername 函数将返回新的虚拟服务器名称，sys.servers 表将显示新的虚拟服务器名称。若要验证故障转移过程是否能够使用新名称正常工作，用户还应尝试将 SQL Server 资源故障转移到其他节点。
对于从群集中任何节点进行的连接，都可以立即使用新名称。但是，对于从客户端计算机使用新名称进行的连接，则必须在新名称对该客户端计算机可见之后，才能使用新名称连接到服务器。根据网络配置，通过网络传播新名称所需的时间长度可能为几秒钟，也可能长至 3 到 5 分钟；旧的虚拟服务器名称在网络上不再可见也可能会需要一些时间。
若要最小化虚拟服务器重命名操作的网络传播延迟，请使用下列步骤：
最小化网络传播延迟
在服务器节点上从命令提示符发出下列命令：
ipconfig /flushdns
ipconfig /registerdns
nbtstat –RR
2009年4月13日星期一
镜像数据库故障切换后的发布订阅延续问题
问题：
主体服务器A，镜像服务器B，2台服务器上的数据库DB_Mirror已经配置成镜像。
主题服务器A上已经新建了发布DB_Mirror_Repl，节点服务器C已经成功订阅了这个发布。
请问如何在发生故障转移的时候，节点服务器C继续能从镜像服务器B上同步过来数据。
参考答案：
有两个方案可以参考：
1. 暂时摒弃SQL的发布订阅机制，使用TableDiff等追差异工具定时运行，以保障数据同步。
本方案的主要应用场景是，服务器A和服务器B的硬件配置不对等，当发生切换的时候，DBA需要争取时间修复服务器A，等修好后再切换回服务器A。
2. 配置服务器A到C的复制过程中，将各个环节的脚本妥善保管，并针对服务器B进行相应修改，当切换发生后，在服务器B上应用脚本，实现快速配置发布订阅关系，中间可以借助TableDiff弥合数据差异，并跳过发布订阅初始化的漫长过程，达到快速恢复的目的。
本方案的应用场景是，服务器AB硬件配置相当，切换回去以A为主不是必须的情况。
微软官方文档说可以支持镜像服务器配置复制，有空研究一下
http://technet.microsoft.com/zh-cn/library/ms151799(SQL.90).aspx
镜像数据库本身是基于数据库的，不是基于服务器的，所以，超出镜像数据库本身的对象都要在第二服务器上创建，比如login，链接服务器、jobs等等，这些细节的关注是在故障发生时快速修复的
前提条件。
主体服务器A，镜像服务器B，2台服务器上的数据库DB_Mirror已经配置成镜像。
主题服务器A上已经新建了发布DB_Mirror_Repl，节点服务器C已经成功订阅了这个发布。
请问如何在发生故障转移的时候，节点服务器C继续能从镜像服务器B上同步过来数据。
参考答案：
有两个方案可以参考：
1. 暂时摒弃SQL的发布订阅机制，使用TableDiff等追差异工具定时运行，以保障数据同步。
本方案的主要应用场景是，服务器A和服务器B的硬件配置不对等，当发生切换的时候，DBA需要争取时间修复服务器A，等修好后再切换回服务器A。
2. 配置服务器A到C的复制过程中，将各个环节的脚本妥善保管，并针对服务器B进行相应修改，当切换发生后，在服务器B上应用脚本，实现快速配置发布订阅关系，中间可以借助TableDiff弥合数据差异，并跳过发布订阅初始化的漫长过程，达到快速恢复的目的。
本方案的应用场景是，服务器AB硬件配置相当，切换回去以A为主不是必须的情况。
微软官方文档说可以支持镜像服务器配置复制，有空研究一下
http://technet.microsoft.com/zh-cn/library/ms151799(SQL.90).aspx
镜像数据库本身是基于数据库的，不是基于服务器的，所以，超出镜像数据库本身的对象都要在第二服务器上创建，比如login，链接服务器、jobs等等，这些细节的关注是在故障发生时快速修复的
前提条件。
2009年3月26日星期四
SQL Server 2008导出数据到Access Excel2007的简单方法
SQL2008提供了强大的SSIS功能，但是操作起来比较复杂，而且避免不了需要数据驱动的问题
本文给出一种简单方法，可以简化操作过程。
SQL2008支持Office2007的数据格式，但是需要安装驱动，要么就要安装Office2007，这个动作从成本角度来说，是费时费钱的，（安装和正版Office授权）。
微软提供了访问驱动包
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=7554f536-8c28-4598-9b72-ef94e038c891
或
http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe
安装了该包以后，从SQL2008导出到Office2007的操作就很容易了。
在SSMS的数据库节点上选中导出数据，指定office格式即可。
本文给出一种简单方法，可以简化操作过程。
SQL2008支持Office2007的数据格式，但是需要安装驱动，要么就要安装Office2007，这个动作从成本角度来说，是费时费钱的，（安装和正版Office授权）。
微软提供了访问驱动包
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=7554f536-8c28-4598-9b72-ef94e038c891
或
http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe
安装了该包以后，从SQL2008导出到Office2007的操作就很容易了。
在SSMS的数据库节点上选中导出数据，指定office格式即可。
2009年3月2日星期一
SQL2008新变化系列1:全文索引干扰词
SQL2008是微软的数据库新产品,相对于上一个版本SQL2005有些许变化,下面就实际遇到的问题分主题罗列一下解决方法
使用全文索引查询时,对包含"之"字出现"全文搜索条件中包含干扰词"现象：
Sql server 2008全文索引的干扰词表默认在Resource库系统表内，无法更改，但sql2008提供了自定义干扰词表的功能，可绑定到某个全文索引上。
相关操作如下：
--sql server 2008 全文索引建立及创建全文非索引字表(干扰词表)
--以admin2003的mem_reginfo表为例
--选择数据库
USE admin2003
GO
--创建全文目录，这个是逻辑名
CREATE FULLTEXT CATALOG mem_reginfo AS DEFAULT;
GO
--创建全文非索引字表(干扰词表)
CREATE FULLTEXT STOPLIST T_FULLTEXT_STOPLIST_mem_reginfo --全文非索引字表表名
FROM SYSTEM STOPLIST; --从系统全文非索引字表导入
--删除我们不需要的干扰词，如"之"字
ALTER FULLTEXT STOPLIST [T_FULLTEXT_STOPLIST_mem_reginfo]
DROP '之' LANGUAGE 'Simplified Chinese';
--增加我们需要的干扰词，如"之"字
ALTER FULLTEXT STOPLIST [T_FULLTEXT_STOPLIST_mem_reginfo]
ADD '之' LANGUAGE 'Simplified Chinese';
--创建表mem_reginfo的全文索引
CREATE FULLTEXT INDEX ON [dbo].[mem_reginfo] --表名
([mem_name] --列名
LANGUAGE [Simplified Chinese])
KEY INDEX [PK_mem_reginfo] --聚集索引名
ON (FILEGROUP [ftfg_FT_mem_reginfo]) --指定文件组名，如不指定，则存在当前表所在文件组
WITH (CHANGE_TRACKING = AUTO,
STOPLIST =T_FULLTEXT_STOPLIST_mem_reginfo --指定使用的全文非索引字表
)
--其它：
--对已存在全文索引指定全文非索引字表，命令执行后，
--如果CHANGE_TRACKING = AUTO，则会自动修改已填充索引，但不会全部重填
ALTER FULLTEXT INDEX on mem_reginfo --表名
SET STOPLIST =SYSTEM --指定使用的全文非索引字表为系统自带
ALTER FULLTEXT INDEX on mem_reginfo --表名
SET STOPLIST=T_FULLTEXT_STOPLIST_mem_reginfo ;--指定使用的全文非索引字表为用户自定义
--启动填充，如果CHANGE_TRACKING != AUTO，则需要启动一次填充才使新设定的全文非索引字表生效;
ALTER FULLTEXT INDEX on mem_reginfo --表名
START FULL POPULATION
使用全文索引查询时,对包含"之"字出现"全文搜索条件中包含干扰词"现象：
Sql server 2008全文索引的干扰词表默认在Resource库系统表内，无法更改，但sql2008提供了自定义干扰词表的功能，可绑定到某个全文索引上。
相关操作如下：
--sql server 2008 全文索引建立及创建全文非索引字表(干扰词表)
--以admin2003的mem_reginfo表为例
--选择数据库
USE admin2003
GO
--创建全文目录，这个是逻辑名
CREATE FULLTEXT CATALOG mem_reginfo AS DEFAULT;
GO
--创建全文非索引字表(干扰词表)
CREATE FULLTEXT STOPLIST T_FULLTEXT_STOPLIST_mem_reginfo --全文非索引字表表名
FROM SYSTEM STOPLIST; --从系统全文非索引字表导入
--删除我们不需要的干扰词，如"之"字
ALTER FULLTEXT STOPLIST [T_FULLTEXT_STOPLIST_mem_reginfo]
DROP '之' LANGUAGE 'Simplified Chinese';
--增加我们需要的干扰词，如"之"字
ALTER FULLTEXT STOPLIST [T_FULLTEXT_STOPLIST_mem_reginfo]
ADD '之' LANGUAGE 'Simplified Chinese';
--创建表mem_reginfo的全文索引
CREATE FULLTEXT INDEX ON [dbo].[mem_reginfo] --表名
([mem_name] --列名
LANGUAGE [Simplified Chinese])
KEY INDEX [PK_mem_reginfo] --聚集索引名
ON (FILEGROUP [ftfg_FT_mem_reginfo]) --指定文件组名，如不指定，则存在当前表所在文件组
WITH (CHANGE_TRACKING = AUTO,
STOPLIST =T_FULLTEXT_STOPLIST_mem_reginfo --指定使用的全文非索引字表
)
--其它：
--对已存在全文索引指定全文非索引字表，命令执行后，
--如果CHANGE_TRACKING = AUTO，则会自动修改已填充索引，但不会全部重填
ALTER FULLTEXT INDEX on mem_reginfo --表名
SET STOPLIST =SYSTEM --指定使用的全文非索引字表为系统自带
ALTER FULLTEXT INDEX on mem_reginfo --表名
SET STOPLIST=T_FULLTEXT_STOPLIST_mem_reginfo ;--指定使用的全文非索引字表为用户自定义
--启动填充，如果CHANGE_TRACKING != AUTO，则需要启动一次填充才使新设定的全文非索引字表生效;
ALTER FULLTEXT INDEX on mem_reginfo --表名
START FULL POPULATION
2009年2月25日星期三
2009年2月17日星期二
修正GFI EventsManager8 在设置Failed SQL server logons时的错误
GFI EventsManager8 的Events Browse里关于Application Events\SQL Server events\Failed SQL server logons
的过滤条件设置有错误，导致即使有符合条件的日志也无法显示。
使用SQL监控发现拼接查询的时候使用了错误的字段，本来应该是表的APPLICATION_EVENTS的EXT_TAGS 字段，而默认使用的是 REL_EXT_TAGS ，修改方法如下：
Field 1 匹配条件 “Equal to” 改成 “contains” 即可
通过这个修改，Failed SQL server logons即可正确显示失败的日志。
的过滤条件设置有错误，导致即使有符合条件的日志也无法显示。
使用SQL监控发现拼接查询的时候使用了错误的字段，本来应该是表的APPLICATION_EVENTS的EXT_TAGS 字段，而默认使用的是 REL_EXT_TAGS ，修改方法如下：
Field 1 匹配条件 “Equal to” 改成 “contains” 即可
通过这个修改，Failed SQL server logons即可正确显示失败的日志。
SQLServer 2005 Agent无法启动的问题
SQLServer 2005服务的登录身份默认是Local System（本地系统帐户）。
如果修改成自己的一个Windows帐户后，Agent就启动不起来了，在Management studio的Agent服务处显示
“禁用代理XP” 在事件里面出现错误：
SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).
开始以为是这个windows账户必须加到SQL的sa组里，但是添加进去还是不行。
msdn里给的解决方案是“代理 XP”选项
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
执行脚本时提示：
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 5845, Level 16, State 1, Line 1
Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'Agent XPs' does not exist, or it may be an advanced option.
Msg 5845, Level 16, State 1, Line 1
Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.
恰好最近刚刚更换过Cluster服务账户，知道这个'lock pages in memory' 是组策略里的一个设置，修改之后重起SQL服务，然后Agent服务即可启动，修改方法如下：
To enable the lock pages in memory option
On the Start menu, click Run; in the Open box, type gpedit.msc.
The Group Policy dialog box opens.
On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
如果修改成自己的一个Windows帐户后，Agent就启动不起来了，在Management studio的Agent服务处显示
“禁用代理XP” 在事件里面出现错误：
SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).
开始以为是这个windows账户必须加到SQL的sa组里，但是添加进去还是不行。
msdn里给的解决方案是“代理 XP”选项
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
执行脚本时提示：
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 5845, Level 16, State 1, Line 1
Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'Agent XPs' does not exist, or it may be an advanced option.
Msg 5845, Level 16, State 1, Line 1
Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.
恰好最近刚刚更换过Cluster服务账户，知道这个'lock pages in memory' 是组策略里的一个设置，修改之后重起SQL服务，然后Agent服务即可启动，修改方法如下：
To enable the lock pages in memory option
On the Start menu, click Run; in the Open box, type gpedit.msc.
The Group Policy dialog box opens.
On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
2009年2月11日星期三
SQL Server 企业版和标准版之间的差别
SQL Server的企业版和标准版的License价格差5倍之多，在企业应用中，DBA经常会被这个问题问住，本帖将日常工作实践中遇到到版本问题给出第一手资料，陆续补充……
SQL 2008 镜像数据库：
企业版和标准版都支持镜像数据库，不同的是，企业版可以设置成高性能（异步）模式，标准版只能是高安全（同步）模式。
SQL 2008 压缩备份：
企业版可以压缩备份，标准版不支持压缩备份，但是可以恢复压缩后的备份文件。
在线建索引
ONLINE = { ON | OFF }
指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。默认值为 OFF。
注意：
联机索引操作仅在 SQL Server 2005 Enterprise Edition 及更高版本中可用。
ON
在索引操作期间不持有长期表锁。在索引操作的主要阶段，源表上只使用意向共享 (IS) 锁。这使得能够继续对基础表和索引进行查询或更新。操作开始时，在很短的时间内对源对象持有共享 (S) 锁。操作结束时，如果创建非聚集索引，将在短期内获取对源的 S（共享）锁；当联机创建或删除聚集索引时，以及重新生成聚集或非聚集索引时，将在短期内获取 SCH-M（架构修改）锁。对本地临时表创建索引时，ONLINE 不能设置为 ON。
OFF
在索引操作期间应用表锁。创建、重新生成或删除聚集索引或者重新生成或删除非聚集索引的脱机索引操作将对表获取架构修改 (Sch-M) 锁。这样可以防止所有用户在操作期间访问基础表。创建非聚集索引的脱机索引操作将对表获取共享 (S) 锁。这样可以防止更新基础表，但允许读操作（如 SELECT 语句）。
SQL 2008 镜像数据库：
企业版和标准版都支持镜像数据库，不同的是，企业版可以设置成高性能（异步）模式，标准版只能是高安全（同步）模式。
SQL 2008 压缩备份：
企业版可以压缩备份，标准版不支持压缩备份，但是可以恢复压缩后的备份文件。
在线建索引
ONLINE = { ON | OFF }
指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。默认值为 OFF。
注意：
联机索引操作仅在 SQL Server 2005 Enterprise Edition 及更高版本中可用。
ON
在索引操作期间不持有长期表锁。在索引操作的主要阶段，源表上只使用意向共享 (IS) 锁。这使得能够继续对基础表和索引进行查询或更新。操作开始时，在很短的时间内对源对象持有共享 (S) 锁。操作结束时，如果创建非聚集索引，将在短期内获取对源的 S（共享）锁；当联机创建或删除聚集索引时，以及重新生成聚集或非聚集索引时，将在短期内获取 SCH-M（架构修改）锁。对本地临时表创建索引时，ONLINE 不能设置为 ON。
OFF
在索引操作期间应用表锁。创建、重新生成或删除聚集索引或者重新生成或删除非聚集索引的脱机索引操作将对表获取架构修改 (Sch-M) 锁。这样可以防止所有用户在操作期间访问基础表。创建非聚集索引的脱机索引操作将对表获取共享 (S) 锁。这样可以防止更新基础表，但允许读操作（如 SELECT 语句）。
2009年2月8日星期日
SQL Server拉取式发布订阅从2000到2008的配置方法
本例特别针对SQL2000实例和SQL2008实例不在同一网段的情况。
一般我们在使用发布订阅时，发布端和订阅端一般都在同一网段的，IP是双向互通的，如果有跨网段的情况发生时，比如发布服务器有固定IP，而订阅服务器在一个局域网内，没有对外固定IP时，解决办法就是配置拉取式(Pull)复制。
基本梗概如下：
1. 在发布端配置发布；
2. 在订阅端执行创建订阅的脚本；
（主要会执行存储过程：sp_addpullsubscription，sp_addpullsubscription_agent）
3. 在发布端再执行脚本。
（主要会执行存储过程：sp_addsubscription）
脚本的生成，可以借用同一网段的机器配置成功发布订阅，然后修改得到。
一般我们在使用发布订阅时，发布端和订阅端一般都在同一网段的，IP是双向互通的，如果有跨网段的情况发生时，比如发布服务器有固定IP，而订阅服务器在一个局域网内，没有对外固定IP时，解决办法就是配置拉取式(Pull)复制。
基本梗概如下：
1. 在发布端配置发布；
2. 在订阅端执行创建订阅的脚本；
（主要会执行存储过程：sp_addpullsubscription，sp_addpullsubscription_agent）
3. 在发布端再执行脚本。
（主要会执行存储过程：sp_addsubscription）
脚本的生成，可以借用同一网段的机器配置成功发布订阅，然后修改得到。
2009年2月4日星期三
SQL Server孤儿用户Orphan user问题解决方法
在“logins”下没有abc的帐号，但是建立abc后一直提示错误——“Error 21002: [ SQL-DMO] 用户"xxxxx"已经存在”，这是孤儿用户存在的典型错误，解决方法如下：
EXEC sp_change_users_login 'report' [<=这是保留字,不要乱改.]
这是要求DB给你妳一份login的报告.妳你可以一目了然,Orphan account的名单.
EXEC sp_charge_users_login 'auto_fix', 'theOrphanLoginName'
这是修补Orphan account.
EXEC sp_change_users_login 'report' [<=这是保留字,不要乱改.]
这是要求DB给你妳一份login的报告.妳你可以一目了然,Orphan account的名单.
EXEC sp_charge_users_login 'auto_fix', 'theOrphanLoginName'
这是修补Orphan account.
2009年1月20日星期二
修复SQL2000的Master数据库
今天SQL2000数据库遇到了一些奇怪的症状，新建登录的菜单是灰色的，数据库对象的属性等菜单根本不存在，链接服务器也不见了，甚至代理节点下的数据库日志也消失了……
似乎系统出了大问题，重起了一下仍然没有解决，使用DBCC CheckDB Master 尝试修复，但是没有异常。
于是，准备重新恢复master数据库
在恢复master的备份时必须在单用户(single user)模式下进行
进入单用户模式的方法：
首先，在命令行模式下输入sqlservr -c -f -m或者输入sqlservr -m
其中：-c 可以缩短启动时间，SQL Server不作为Windows NT的服务启动；
-f 用最小配置启动SQL Server；
-m 单用户模式启动SQL Server。
也可以在控制面板-服务-MSSQLServer的启动参数中输入-c -f -m或者输入-m,点击开始
其次，进行master数据库的恢复
直接进入查询分析器,有个提示不要理会它。输入恢复语句进行数据库恢复:
RESTORE DATABASE master from disk='master.bak'
完成后，重起了sql服务，居然一切都恢复了。
虽然不知道是什么导致了这些问题，但是恢复master最终解决了问题。
对于master数据库彻底垮掉的情况，可以使用下面的步骤修复
重建Master库，使用Rebuildm.exe，将用到SQL的安装文件，从安装目录X86\Data中拷取原文件。
重建成功后，不要启动SQL Server，以单用户模式进入SQL
\bin\sqlservr.exe -m
然后还原数据库备份即可：
restore database master from disk='e:\master.bak'
似乎系统出了大问题，重起了一下仍然没有解决，使用DBCC CheckDB Master 尝试修复，但是没有异常。
于是，准备重新恢复master数据库
在恢复master的备份时必须在单用户(single user)模式下进行
进入单用户模式的方法：
首先，在命令行模式下输入sqlservr -c -f -m或者输入sqlservr -m
其中：-c 可以缩短启动时间，SQL Server不作为Windows NT的服务启动；
-f 用最小配置启动SQL Server；
-m 单用户模式启动SQL Server。
也可以在控制面板-服务-MSSQLServer的启动参数中输入-c -f -m或者输入-m,点击开始
其次，进行master数据库的恢复
直接进入查询分析器,有个提示不要理会它。输入恢复语句进行数据库恢复:
RESTORE DATABASE master from disk='master.bak'
完成后，重起了sql服务，居然一切都恢复了。
虽然不知道是什么导致了这些问题，但是恢复master最终解决了问题。
对于master数据库彻底垮掉的情况，可以使用下面的步骤修复
重建Master库，使用Rebuildm.exe，将用到SQL的安装文件，从安装目录X86\Data中拷取原文件。
重建成功后，不要启动SQL Server，以单用户模式进入SQL
然后还原数据库备份即可：
restore database master from disk='e:\master.bak'
2008年12月21日星期日
SQL Server2005数据库事务隔离级别实验记录(2)--Read Committed
Read Committed:SQL 默认级别，读取时设置共享锁，避免其他进程脏读。
实验：初始状态：Ntusername ='1' where rownumber =8
Part I：------------------------------------------------------------------
在默认隔离级别下，执行下面语句（52)进程
begin transaction
update t08102101
set Ntusername ='2'
where rownumber =8
select @@trancount
sp_lock看到的锁信息如下：
52 5 1582628681 1 KEY (08000c080f1b) X GRANT
52 5 1582628681 0 TAB IX GRANT
52 5 1582628681 1 PAG 1:333532 IX GRANT
然后执行下面的语句，（65）进程
set transaction isolation level read committed
select * from t08102101
where rownumber =8
无法完成查询，取不到任何数据，锁信息如下：
52 5 1582628681 1 KEY (08000c080f1b) X GRANT
52 5 1582628681 0 TAB IX GRANT
52 5 1582628681 1 PAG 1:333532 IX GRANT
65 5 1582628681 1 KEY (08000c080f1b) S WAIT
65 5 1582628681 0 TAB IS GRANT
65 5 1582628681 1 PAG 1:333532 IS GRANT
此时执行
select * from t08102101 (nolock)
where rownumber =8
可以得到 Ntusername ='2' where rownumber =8的结果，锁信息没有变化
而在52进程执行
select * from t08102101
where rownumber =8
结果是Ntusername ='2' where rownumber =8
Part II:------------------------------------------------------------------
回滚之前的更新操作，打开read_committed_snapshot开关：
alter database dbcenter
set read_committed_snapshot on
再次执行
begin transaction
update t08102101
set Ntusername ='2'
where rownumber =8
select @@trancount
锁信息如下
52 5 1582628681 1 KEY (08000c080f1b) X GRANT
52 5 1582628681 0 TAB IX GRANT
52 5 1582628681 1 PAG 1:333532 IX GRANT
执行下面的语句，（65）进程
set transaction isolation level read committed
select * from t08102101
where rownumber =8
可以得到Ntusername ='1' where rownumber =8的更新前结果
在另外进程执行(nolock)查询，得到Ntusername ='2' where rownumber =8
此时，sp_lock看到的只有52进程的排他锁信息。
而在52进程中执行
set transaction isolation level read committed
select * from t08102101
where rownumber =8
得到的是Ntusername ='2' where rownumber =8
结论：数据库默认的事务隔离级别是read committed，且该级别不允许读取未提交中的数据
--13:23 2008-12-21
实验：初始状态：Ntusername ='1' where rownumber =8
Part I：------------------------------------------------------------------
在默认隔离级别下，执行下面语句（52)进程
begin transaction
update t08102101
set Ntusername ='2'
where rownumber =8
select @@trancount
sp_lock看到的锁信息如下：
52 5 1582628681 1 KEY (08000c080f1b) X GRANT
52 5 1582628681 0 TAB IX GRANT
52 5 1582628681 1 PAG 1:333532 IX GRANT
然后执行下面的语句，（65）进程
set transaction isolation level read committed
select * from t08102101
where rownumber =8
无法完成查询，取不到任何数据，锁信息如下：
52 5 1582628681 1 KEY (08000c080f1b) X GRANT
52 5 1582628681 0 TAB IX GRANT
52 5 1582628681 1 PAG 1:333532 IX GRANT
65 5 1582628681 1 KEY (08000c080f1b) S WAIT
65 5 1582628681 0 TAB IS GRANT
65 5 1582628681 1 PAG 1:333532 IS GRANT
此时执行
select * from t08102101 (nolock)
where rownumber =8
可以得到 Ntusername ='2' where rownumber =8的结果，锁信息没有变化
而在52进程执行
select * from t08102101
where rownumber =8
结果是Ntusername ='2' where rownumber =8
Part II:------------------------------------------------------------------
回滚之前的更新操作，打开read_committed_snapshot开关：
alter database dbcenter
set read_committed_snapshot on
再次执行
begin transaction
update t08102101
set Ntusername ='2'
where rownumber =8
select @@trancount
锁信息如下
52 5 1582628681 1 KEY (08000c080f1b) X GRANT
52 5 1582628681 0 TAB IX GRANT
52 5 1582628681 1 PAG 1:333532 IX GRANT
执行下面的语句，（65）进程
set transaction isolation level read committed
select * from t08102101
where rownumber =8
可以得到Ntusername ='1' where rownumber =8的更新前结果
在另外进程执行(nolock)查询，得到Ntusername ='2' where rownumber =8
此时，sp_lock看到的只有52进程的排他锁信息。
而在52进程中执行
set transaction isolation level read committed
select * from t08102101
where rownumber =8
得到的是Ntusername ='2' where rownumber =8
结论：数据库默认的事务隔离级别是read committed，且该级别不允许读取未提交中的数据
--13:23 2008-12-21
SQL Server2005数据库事务隔离级别实验记录(1)--Read Uncommitted
Read Uncommitted：运行读取其他事务已修改未提交的的数据行：不放置共享锁就读取，即使已经防止排他锁。与nolock效果一致
实验：初始状态：Ntusername ='1' where rownumber =8
在第1进程执行：
begin transaction
update t08102101
set Ntusername ='2'
where rownumber =8
select @@trancount
在第2个进程执行
select * from t08102101
where rownumber =8
进程无法返回数据（第1个进程未提交）
改为下面的第3进程
set transaction isolation level read uncommitted
select * from t08102101
where rownumber =8
或第4进程：
select * from t08102101 (nolock)
where rownumber =8
得到Ntusername ='2'，即更新后，但是未提交的数据
在第1个进程提交 commit tran 或 rollback tran后数据或提交或回滚，重新执行允许脏读的两个（3,4）进程，都会得到或提交或回滚后的结果
结论：脏读是从内存中读取，无论是否提交，它读到的都是最新状态，而不一定是结果状态。
注意：设置事务隔离级别是针对连接线程而言的，对第3线程而言，只要没有关闭查询窗口，
只要设置隔离级别的命令一提交，则对该查询窗口且只对该窗口内执行的语句有效。
--11:58 2008-12-21
实验：初始状态：Ntusername ='1' where rownumber =8
在第1进程执行：
begin transaction
update t08102101
set Ntusername ='2'
where rownumber =8
select @@trancount
在第2个进程执行
select * from t08102101
where rownumber =8
进程无法返回数据（第1个进程未提交）
改为下面的第3进程
set transaction isolation level read uncommitted
select * from t08102101
where rownumber =8
或第4进程：
select * from t08102101 (nolock)
where rownumber =8
得到Ntusername ='2'，即更新后，但是未提交的数据
在第1个进程提交 commit tran 或 rollback tran后数据或提交或回滚，重新执行允许脏读的两个（3,4）进程，都会得到或提交或回滚后的结果
结论：脏读是从内存中读取，无论是否提交，它读到的都是最新状态，而不一定是结果状态。
注意：设置事务隔离级别是针对连接线程而言的，对第3线程而言，只要没有关闭查询窗口，
只要设置隔离级别的命令一提交，则对该查询窗口且只对该窗口内执行的语句有效。
--11:58 2008-12-21
2008年12月11日星期四
SQL Server 目录操作的扩展存储过程
需要sa权限
SQL2005默认禁止了xp_cmdshell的使用
下面有几个默认可以使用的扩展存储过程：
master.sys.xp_fixeddrives --显示磁盘剩余空间
master.sys.xp_dirtree --查看目录结构
master.sys.xp_create_subdir --创建目录
SQL2005默认禁止了xp_cmdshell的使用
下面有几个默认可以使用的扩展存储过程：
master.sys.xp_fixeddrives --显示磁盘剩余空间
master.sys.xp_dirtree --查看目录结构
master.sys.xp_create_subdir --创建目录
2008年12月10日星期三
SQLServer 数据文件的妙用
数据文件的妙用
当数据库所在的磁盘分区不足的时候，可以使用添加数据文件的方式解决，sql系统在准备扩充文件空间的时候，会优先检查所有的数据文件的空间大小，如果还有可用空间，则首先考虑使用它；等所有的磁盘文件空间都用完时，系统会按顺序扩充各文件，扩一个用一个，用完了再用下一个
当数据库所在的磁盘分区不足的时候，可以使用添加数据文件的方式解决，sql系统在准备扩充文件空间的时候，会优先检查所有的数据文件的空间大小，如果还有可用空间，则首先考虑使用它；等所有的磁盘文件空间都用完时，系统会按顺序扩充各文件，扩一个用一个，用完了再用下一个
2008年12月2日星期二
FAQ: SQL2000 - Log shipping
http://support.microsoft.com/kb/314515/EN-US/
Frequently asked questions - SQL Server 2000 - Log shipping
View products that this article applies to.
This article was previously published under Q314515
On This PageSUMMARY
MORE INFORMATION
Log shipping set up
Log shipping security considerations
Log shipping monitoring
Log shipping role change
Log shipping removal
REFERENCES
Expand all | Collapse all
SUMMARYThis article discusses several aspects of log shipping and answers the most comm...This article discusses several aspects of log shipping and answers the most commonly asked questions regarding setup, security, monitoring, role-change and the removal of log shipping in SQL Server 2000 Enterprise Edition.
Back to the top
MORE INFORMATIONLog shipping in SQL Server 2000 provides a means of establishing a warm back up...Log shipping in SQL Server 2000 provides a means of establishing a warm back up solution by using the SQL Server Maintenance Plan Wizard. Transaction log backups from a database can be automatically shipped to a different server and applied to a standby database. You can use the standby database to perform read-only operations (depending on the load state).
Log shipping set up
Q1: What edition of SQL Server do I have to have to set up log shipping?
A1: The following matrix shows the edition of SQL Server that is required for the three components that participate in log shipping:
Collapse this tableExpand this table
Component Edition of SQL Server required
Primary Server Enterprise or Developer Edition
Secondary Server Enterprise or Developer Edition
Monitor Server Any Edition
Q2: What do I have to do before I start the log shipping set up through SQL Server Enterprise Manager?
A2: Here is the list of what you must do before you start log shipping in SQL Server 2000.
Either start SQL Server and SQL Server Agent services under a domain account or configure the relevant primary, secondary and monitor servers for pass-through security (see question three in this heading for more information).
You can set up log shipping from any computer that has SQL Server Enterprise Manager (SEM) installed. You must register all computers that are running SQL Server that function as servers, which are intended to be the secondary servers, through SEM, on the computer from which log shipping is going to be set up.
Create a folder on the primary server for the transaction log back ups. You can create this folder anywhere on the primary computer. There must be enough free disk space on the drive on which you place the folder to hold at least one days worth of transaction log back ups. The exact space required is not easy to predict because it depends on the size and frequency of the transaction log back ups for the database. Microsoft recommends that you create a different folder for each database that you log ship.
Share the folders you created in the previous step. Make sure that you grant READ and CHANGE permissions to the Microsoft Windows NT accounts under which SQL Server and SQL Server Agent services are started for the servers that participate in log shipping. If you use pass-through security, grant these permissions to the local Windows NT account, under which the SQL Server related services are started.
Remove or disable any transaction log back up jobs on the databases that will be log shipped. This includes any third party back up jobs.
Q3: Do I have to start SQL Server related services under a domain account as opposed to a local Windows NT account?
A3: It is possible to configure SQL Server services to start under a local Windows NT account, unless SQL Server is configured to run as a virtual server in conjunction with Microsoft Cluster Service. You can use Windows NT pass-through security for this purpose. Follow these steps to configure pass-through security:
Create a Windows NT account on the primary, secondary and monitor computers with the same name and passwords.
Configure SQL Server related services to start under these Windows NT accounts on all computers.
The SQL Server services must be started under a domain account if SQL Server is configured to run as a virtual server with Microsoft Cluster Service. Even if SQL Server is a virtual server, Microsoft recommends that you use a domain account to start the services when SQL Server computers are in a domain. You gain the following advantage by having SQL Server related services start under a domain account:
Change of password for the SQL Server start up account will not result in a failure of log shipping jobs. To successfully continue log shipping in a pass-through security situation, all the servers must have the password changed for the Windows NT start up account, at the same time.
Q4: Where can I set up log shipping from?
A4: In SQL Server Enterprise Manager, right-click the database for which log shipping has to be set up, and then click Maintenance Plan. In the Welcome dialog box, click Next. Click to select the Ship the transaction log to other SQL Servers (log shipping) check box. The check box indicates to the SQL Server Maintenance Plan Wizard that this database must have log shipping. You can perform this step from a client that has SQL Server Enterprise Manager installed.
Q5: Why is the log shipping check box sometimes dimmed in the Maintenance Plan dialog box?
A5: The check box can be dimmed for one of the following reasons:
Multiple databases might be selected for the Maintenance Plan.
The database that is selected is not in the Full or Bulk Logged Recovery model.
SQL Server 2000 Enterprise Edition is not installed on the server.
Q6: Why does the log shipping set up fail while performing the initial configuration?
A6: There are several reasons that may cause the log shipping set up to fail. At this time there is at least one known problem that causes this behavior. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
298743 (http://support.microsoft.com/kb/298743/ ) BUG: All changes may not be rolled back when Log Shipping Maintenance Wizard fails
Q7: Are the table schema and database file structure changes propagated to the secondary server?
A7: In SQL Server 2000, all table schema and database file structure changes are logged operations. However, if a new NDF or LDF file is added to the primary database, the transaction log restore job fails while loading the transaction log backup that was performed immediately after the database file was added to the primary database. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
286280 (http://support.microsoft.com/kb/286280/ ) Description of the effect to database recovery after you add or remove database files
Q8: Can I script log shipping?
A8: No. Currently, it is not possible to script log shipping. The only supported means of setting up log shipping is through the wizard as described in question 4 of this section.
Q9: Can I set up log shipping between servers in multiple domains?
A9: Yes. It is possible to set up log shipping between servers that are in separate domains. There are two ways to do this:
Use pass-through security. Configure Windows NT accounts with the same name and passwords on the primary, secondary and monitor servers. Configure SQL Server related services to start under these accounts on all servers and use SQL authentication while setting up log shipping to connect to the monitor server. -or-
Use conventional Windows NT security. You must configure the domains with two-way trusts. SQL Server related services can be started under domain accounts. Either SQL authentication or Windows authentication can be used by jobs on the primary and secondary servers to connect to the monitor server. All other requirements are the same as explained in question 2 of this section.
Q10: Can I configure primary and secondary servers to use SQL authentication to connect to the monitor server?
A10: Yes. It is possible to use either Windows or SQL authentication for primary and secondary servers to connect to the monitor server. Microsoft recommends that you use Windows authentication for this purpose. However, if it is not possible to use Windows authentication, you can use SQL authentication. SQL Server will create a "log_shipping_monitor_probe" account on the primary, secondary and monitor servers, if it does not already exist, with the password specified when you set up log shipping. If SQL authentication is used for log shipping, you must configure SQL Server on the primary, secondary and monitor servers to use Mixed Mode authentication.
Log shipping security considerations
Q1: If I make the "guest" account unavailable before setting up log shipping, and I want my secondary database to be in a standby state, how can I allow users to have access to the secondary database (enforcing the same security model as the primary server)?
A1: The "guest" account must not be removed from SQL Server for any reason. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
315523 (http://support.microsoft.com/kb/315523/ ) Removal of the guest account may cause a 916 error in SQL Server 2000 SP4 or a handled exception access violation in earlier versions of SQL Server 2000
However, you can can make the "guest" account unavailable for databases where there might be security concerns. Because the secondary database is in a standby state, it is not possible to use the sp_change_users_login (http://msdn2.microsoft.com/en-us/library/aa259633(SQL.80).aspx) stored procedure to re-map the logins appropriately. To enforce the same security model on a standby database, create the logins on the secondary server by using the same security identifier (SID) value as the primary server. Read the following Microsoft Knowledge Base article for more information about creating logins with the same SID values:
303722 (http://support.microsoft.com/kb/303722/ ) How to grant access to SQL logins on a standby database when the guest user is disabled in SQL Server
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
321247 (http://support.microsoft.com/kb/321247/ ) How to configure security for SQL Server log shipping
Q2: What does sp_resolve_logins do?
A2: At the time of the log shipping role change, the sp_resolve_logins (http://msdn2.microsoft.com/en-us/library/aa238877(SQL.80).aspx) stored procedure requires a BCP file of the syslogins system table from the primary server. This stored procedure loads the BCP file into the temporary table and loops through each login to verify if a login with the same name exists in the secondary server's syslogins system table. It then checks to see if the SID value for this login exists in the secondary database's sysusers system table. It finally checks to see if the SID value in secondary database's sysusers system table is not the same as the SID value in the secondary server's syslogins table. If these checks are satisfied, the sp_resolve_logins stored procedure runs the sp_Change_users_login stored procedure for that login, and fixes the SID in the secondary database's sysusers system table. Execution of this stored procedure is required only if there are new logins created on the primary server after log shipping has been initialized and those same logins are not created on the secondary servers with the same SID (as described in Microsoft Knowledge Base article Q303722).
Q3: The sp_resolve_logins stored procedure runs successfully; however, it does not perform the expected modifications to the security on the secondary server. Why?
A3: The sp_resolve_logins stored procedure requires an up-to-date BCP file of the primary server's syslogins system table. These logins must already by created on the secondary server. If these two conditions are met, the sp_resolve_logins stored procedure performs the modifications to the sysusers system table in the secondary database.
Q4: Do I have to run a Transfer Logins DTS task in conjunction with the sp_resolve_logins stored procedure before performing the role change?
A4: Yes. You must use the Transfer Logins task to make sure that the logins exist in the syslogins system table on the secondary server. This does not guarantee that the user can use the secondary database (if the secondary database is loaded in standby mode). If the user has to use the secondary database before performing the log shipping role change, see question 1 in this section.
Q5: Does the sp_resolve_logins stored procedure work for remote logins in SQL Server?
A5: No. The sp_resolve_logins stored procedure only works for typical logins. Any remote logins must be created manually on the secondary server.
Log shipping monitoring
Q1: Log Shipping Backup and Out of Sync alerts are firing, even when the secondary server is updated with the transaction log backups. Is this possible?
A1: Yes. It is possible that the alerts might fire even when the secondary database is being updated. If the alert threshold is set to a value less than double the time between back up and copy or restore jobs, the alerts might be raised. If the alerts are being raised and the threshold is close to or less than two times the time between subsequent backup and copy or restore jobs, go ahead and increase the threshold.
Q2: Why do the transaction log backups fail to restore on the secondary server?
A2: Transaction Log backups can only be restored if they are in a sequence. This sequence is determined by the LastLSN and FirstLSN fields that are returned by the RESTORE HEADERONLY (http://msdn2.microsoft.com/en-us/library/aa238455(SQL.80).aspx) command. If the LastLSN field and the FirstLSN field do not display the same number on consecutive transaction log backups, they are not restorable in that sequence. There may be several reasons for transaction log backups to be out of sequence. Some of the most common reasons are:
There are redundant transaction log backup jobs on the primary server that are causing the sequence to be broken.
There are non-logged operations performed in the database. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
272093 (http://support.microsoft.com/kb/272093/ ) Description of the effects of nonlogged and minimally logged operations on transaction log backup and the restore process in SQL Server
The recovery model of the database was probably toggled between transaction log backups.
The Data Transformation Services (DTS) task on the primary server might be causing this problem. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
308267 (http://support.microsoft.com/kb/308267/ ) FIX: DTS Copy Objects Task (DMO) breaks transaction log backup chain by switching recovery mode to Simple during transfer
Q3: Where can I find information about errors while performing back up, copy or restore operations?
A3: To get more information about a particular log shipping pair, follow these steps:
Open SQL Server Enterprise Manager, and then connect to the monitor server.
Under Management, click Log Shipping Monitor. In the right window pane, all the log shipping pairs are displayed (that have been configured with this server as the monitor server). If the log shipping pair is not visible, right-click the Log Shipping Monitor (under Management), and then click Refresh.
Right-click the log shipping pair that you want information about, and then click View Backup History to view the back up job history.
Right-click the log shipping pair, and then click View Copy/Restore History to view the history for copy and restore jobs.
Right-click the log shipping pair, and then click Properties to view the current log shipping status, Source, and Destination alert status.
Q4: Does the file name first_file_000000000000.trn indicate that the copy or restore job was unsuccessful?
A4: Each run of the copy and restore job is associated with at least one file. By default, if no files are copied or restored in a certain run of any of these two jobs, SQL Server places first_file_000000000000.trn in the file name field. This may or may not indicate a problem. For example, the very first time that copy or restore jobs are run on the secondary server, there might not be any files available to copy or restore. In this case, first_file_000000000000.trn does not necessarily represent an error. However, under certain circumstances, this might represent a problem. Read the following Microsoft Knowledge Base article for more information:
292586 (http://support.microsoft.com/kb/292586/ ) Backup, copy, and load job information is not updated on the log shipping monitor
Q5: Is it possible to modify the frequency and destination of the transaction log backups, on the primary server, after log shipping has been operational for a while?
A5: Yes. This information is in the Maintenance Plan on the primary server. To view the information, follow these steps:
Double-click the Maintenance Plan on the primary server for the database for which this information must be modified.
Click the Transaction Log Backup tab. Modify the destination and frequency in the dialog box.
Because the copy job on the secondary server is expecting to copy transaction log backups from the share specified at the time log shipping was set up, this job might fail after modifying the target folder for the transaction log back ups. For more information about how to work around this problem, read the following article in the Microsoft Knowledge Base:
314570 (http://support.microsoft.com/kb/314570/ ) Cannot modify backup network share after you change the transaction log backup folder
Log shipping role change
Q1: How do I perform a log shipping role change?
A1: Click the following link to read the SQL Server 2000 Books Online topic about performing a log shipping role change:
How to set up and perform a log shipping role change (Transact-SQL) (http://msdn2.microsoft.com/en-us/library/aa215392(SQL.80).aspx)
Q2: Can I perform a role change while the primary server is offline or unavailable?
A2: Yes. Running the sp_change_primary_role (http://msdn2.microsoft.com/en-us/library/aa259617(SQL.80).aspx) stored procedure on the primary server is optional.
Q3: Why does the sp_resolve_logins stored procedure fail with error message 208 when run from the secondary database at the time of a role change?
A3: The sp_resolve_logins stored procedure does not qualify the sysusers system table with the master database prefix. This is a known problem with the code for the sp_resolve_logins stored procedure. For more information about this problem, read the following article in the Microsoft Knowledge Base:
310882 (http://support.microsoft.com/kb/310882/ ) BUG: sp_resolve_logins stored procedure fails if executed during log shipping role change
Q4: Is there a problem when promoting a secondary server to be a primary server, when there are multiple secondary servers involved in a role change?
A4: Read the following Microsoft Knowledge Base article about a known problem that can cause errors while performing a role change that involves multiple secondary servers:
300497 (http://support.microsoft.com/kb/300497/ ) FIX: Log shipping: Cannot change role from secondary to primary when database names are different
Q5: How can I re-establish log shipping after promoting the secondary server to be the primary server?
A5: If the Allow database to assume Primary role check box is selected, while setting up log shipping, in the Add Destination Database dialog box, follow these steps to add a new secondary server after performing a role change. If the setting was not selected, use the Maintenance Plan Wizard to set up log shipping after a role change.
Open SQL Server Enterprise Manager, and then connect to the promoted primary server. Register the server that you intend to add as the secondary server.
Expand Management (in SQL Server Enterprise Manager), and then click Maintenance Plans. Right-click the appropriate Maintenance Plan from the list, and then click Properties.
Click the Log Shipping tab, and then click Add.
Provide the appropriate information regarding the secondary server about this dialog box, and then click OK. This will add the new secondary server to log shipping.
Q6: How can I continue to log ship to the former primary server without restoring a database backup?
A6: It is possible to log ship between two servers repeatedly without having to restore the complete database backup. The requirement is that both the primary and secondary servers are available when you perform the role change procedure. As part of performing the role change, you must run the sp_change_primary_role (http://msdn.microsoft.com/en-us/library/aa259617.aspx) stored procedure. You must run the sp_change_primary_role stored procedure with a @final_state parameter of either 2 or 3. This will leave the primary database in an unrecovered state after performing the transaction log back up. Because the database is left in an unrecovered state, this database can be selected when the log shipping destination is added (as explained in the previous question). This way you do not have to reload a database backup.
Log shipping removal
Q1: How can I stop log shipping for a particular log shipping pair?
A1: Follow these steps to remove a log shipping pair:
Open the SQL Server Enterprise Manager on the primary server. Expand Management, and then click Maintenance Plan. Right-click the Maintenance Plan, and then click Properties.
Click the Log Shipping tab, and then click to select the log shipping pair that you want to remove.
Click the Delete command button to remove this pair from log shipping. If this is the last pair in log shipping, clicking Delete removes log shipping. If you want to continue log shipping to a different server or to a database, click Add. Then, click to select the appropriate server or database to act as the secondary server before you remove the existing log shipping secondary.
Q2: Is there a problem with removing log shipping for a database that has special characters in it's name?
A2: Read the following Microsoft Knowledge Base article, which discusses this problem in more detail:
295936 (http://support.microsoft.com/kb/295936/ ) FIX: Error removing log shipping on secondary database when database name has a quote
Back to the top
REFERENCES
For more information about log shipping, visit the following Microsoft Web sites:
How to setup log shipping (white paper)
http://support.microsoft.com/support/sql/content/2000papers/LogShippingFinal.asp (http://support.microsoft.com/?scid=http%3a%2f%2fsupport.microsoft.com%2fsupport%2fsql%2fcontent%2f2000papers%2flogshippingfinal.asp)
Log shipping
http://msdn2.microsoft.com/en-us/library/aa213785(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa213785(SQL.80).aspx)
275146 (http://support.microsoft.com/kb/275146/ ) Frequently asked questions - SQL Server 7.0 - Log shipping
Didn't see an answer to your question? Visit the Microsoft SQL Server Newsgroups at:
Microsoft SQL Server Newsgroupshttp://www.microsoft.com/communities/newsgroups/en-us/ (http://www.microsoft.com/communities/newsgroups/en-us/)
Comments about this or other Microsoft Knowledge Base articles? Drop us a note at SQLKB@Microsoft.com (mailto:sqlkb@microsoft.com) .
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
917544 (http://support.microsoft.com/kb/917544/ ) BUG: You receive an error message when you run the "Log Shipping Alert Job - Restore" job in SQL Server 2000
Back to the top
--------------------------------------------------------------------------------
APPLIES TO
Microsoft SQL Server 2000 Enterprise Edition
Microsoft SQL Server 2000 Developer Edition
Back to the top
Keywords: kbinfo KB314515
Back to the top
Frequently asked questions - SQL Server 2000 - Log shipping
View products that this article applies to.
This article was previously published under Q314515
On This PageSUMMARY
MORE INFORMATION
Log shipping set up
Log shipping security considerations
Log shipping monitoring
Log shipping role change
Log shipping removal
REFERENCES
Expand all | Collapse all
SUMMARYThis article discusses several aspects of log shipping and answers the most comm...This article discusses several aspects of log shipping and answers the most commonly asked questions regarding setup, security, monitoring, role-change and the removal of log shipping in SQL Server 2000 Enterprise Edition.
Back to the top
MORE INFORMATIONLog shipping in SQL Server 2000 provides a means of establishing a warm back up...Log shipping in SQL Server 2000 provides a means of establishing a warm back up solution by using the SQL Server Maintenance Plan Wizard. Transaction log backups from a database can be automatically shipped to a different server and applied to a standby database. You can use the standby database to perform read-only operations (depending on the load state).
Log shipping set up
Q1: What edition of SQL Server do I have to have to set up log shipping?
A1: The following matrix shows the edition of SQL Server that is required for the three components that participate in log shipping:
Collapse this tableExpand this table
Component Edition of SQL Server required
Primary Server Enterprise or Developer Edition
Secondary Server Enterprise or Developer Edition
Monitor Server Any Edition
Q2: What do I have to do before I start the log shipping set up through SQL Server Enterprise Manager?
A2: Here is the list of what you must do before you start log shipping in SQL Server 2000.
Either start SQL Server and SQL Server Agent services under a domain account or configure the relevant primary, secondary and monitor servers for pass-through security (see question three in this heading for more information).
You can set up log shipping from any computer that has SQL Server Enterprise Manager (SEM) installed. You must register all computers that are running SQL Server that function as servers, which are intended to be the secondary servers, through SEM, on the computer from which log shipping is going to be set up.
Create a folder on the primary server for the transaction log back ups. You can create this folder anywhere on the primary computer. There must be enough free disk space on the drive on which you place the folder to hold at least one days worth of transaction log back ups. The exact space required is not easy to predict because it depends on the size and frequency of the transaction log back ups for the database. Microsoft recommends that you create a different folder for each database that you log ship.
Share the folders you created in the previous step. Make sure that you grant READ and CHANGE permissions to the Microsoft Windows NT accounts under which SQL Server and SQL Server Agent services are started for the servers that participate in log shipping. If you use pass-through security, grant these permissions to the local Windows NT account, under which the SQL Server related services are started.
Remove or disable any transaction log back up jobs on the databases that will be log shipped. This includes any third party back up jobs.
Q3: Do I have to start SQL Server related services under a domain account as opposed to a local Windows NT account?
A3: It is possible to configure SQL Server services to start under a local Windows NT account, unless SQL Server is configured to run as a virtual server in conjunction with Microsoft Cluster Service. You can use Windows NT pass-through security for this purpose. Follow these steps to configure pass-through security:
Create a Windows NT account on the primary, secondary and monitor computers with the same name and passwords.
Configure SQL Server related services to start under these Windows NT accounts on all computers.
The SQL Server services must be started under a domain account if SQL Server is configured to run as a virtual server with Microsoft Cluster Service. Even if SQL Server is a virtual server, Microsoft recommends that you use a domain account to start the services when SQL Server computers are in a domain. You gain the following advantage by having SQL Server related services start under a domain account:
Change of password for the SQL Server start up account will not result in a failure of log shipping jobs. To successfully continue log shipping in a pass-through security situation, all the servers must have the password changed for the Windows NT start up account, at the same time.
Q4: Where can I set up log shipping from?
A4: In SQL Server Enterprise Manager, right-click the database for which log shipping has to be set up, and then click Maintenance Plan. In the Welcome dialog box, click Next. Click to select the Ship the transaction log to other SQL Servers (log shipping) check box. The check box indicates to the SQL Server Maintenance Plan Wizard that this database must have log shipping. You can perform this step from a client that has SQL Server Enterprise Manager installed.
Q5: Why is the log shipping check box sometimes dimmed in the Maintenance Plan dialog box?
A5: The check box can be dimmed for one of the following reasons:
Multiple databases might be selected for the Maintenance Plan.
The database that is selected is not in the Full or Bulk Logged Recovery model.
SQL Server 2000 Enterprise Edition is not installed on the server.
Q6: Why does the log shipping set up fail while performing the initial configuration?
A6: There are several reasons that may cause the log shipping set up to fail. At this time there is at least one known problem that causes this behavior. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
298743 (http://support.microsoft.com/kb/298743/ ) BUG: All changes may not be rolled back when Log Shipping Maintenance Wizard fails
Q7: Are the table schema and database file structure changes propagated to the secondary server?
A7: In SQL Server 2000, all table schema and database file structure changes are logged operations. However, if a new NDF or LDF file is added to the primary database, the transaction log restore job fails while loading the transaction log backup that was performed immediately after the database file was added to the primary database. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
286280 (http://support.microsoft.com/kb/286280/ ) Description of the effect to database recovery after you add or remove database files
Q8: Can I script log shipping?
A8: No. Currently, it is not possible to script log shipping. The only supported means of setting up log shipping is through the wizard as described in question 4 of this section.
Q9: Can I set up log shipping between servers in multiple domains?
A9: Yes. It is possible to set up log shipping between servers that are in separate domains. There are two ways to do this:
Use pass-through security. Configure Windows NT accounts with the same name and passwords on the primary, secondary and monitor servers. Configure SQL Server related services to start under these accounts on all servers and use SQL authentication while setting up log shipping to connect to the monitor server. -or-
Use conventional Windows NT security. You must configure the domains with two-way trusts. SQL Server related services can be started under domain accounts. Either SQL authentication or Windows authentication can be used by jobs on the primary and secondary servers to connect to the monitor server. All other requirements are the same as explained in question 2 of this section.
Q10: Can I configure primary and secondary servers to use SQL authentication to connect to the monitor server?
A10: Yes. It is possible to use either Windows or SQL authentication for primary and secondary servers to connect to the monitor server. Microsoft recommends that you use Windows authentication for this purpose. However, if it is not possible to use Windows authentication, you can use SQL authentication. SQL Server will create a "log_shipping_monitor_probe" account on the primary, secondary and monitor servers, if it does not already exist, with the password specified when you set up log shipping. If SQL authentication is used for log shipping, you must configure SQL Server on the primary, secondary and monitor servers to use Mixed Mode authentication.
Log shipping security considerations
Q1: If I make the "guest" account unavailable before setting up log shipping, and I want my secondary database to be in a standby state, how can I allow users to have access to the secondary database (enforcing the same security model as the primary server)?
A1: The "guest" account must not be removed from SQL Server for any reason. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
315523 (http://support.microsoft.com/kb/315523/ ) Removal of the guest account may cause a 916 error in SQL Server 2000 SP4 or a handled exception access violation in earlier versions of SQL Server 2000
However, you can can make the "guest" account unavailable for databases where there might be security concerns. Because the secondary database is in a standby state, it is not possible to use the sp_change_users_login (http://msdn2.microsoft.com/en-us/library/aa259633(SQL.80).aspx) stored procedure to re-map the logins appropriately. To enforce the same security model on a standby database, create the logins on the secondary server by using the same security identifier (SID) value as the primary server. Read the following Microsoft Knowledge Base article for more information about creating logins with the same SID values:
303722 (http://support.microsoft.com/kb/303722/ ) How to grant access to SQL logins on a standby database when the guest user is disabled in SQL Server
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
321247 (http://support.microsoft.com/kb/321247/ ) How to configure security for SQL Server log shipping
Q2: What does sp_resolve_logins do?
A2: At the time of the log shipping role change, the sp_resolve_logins (http://msdn2.microsoft.com/en-us/library/aa238877(SQL.80).aspx) stored procedure requires a BCP file of the syslogins system table from the primary server. This stored procedure loads the BCP file into the temporary table and loops through each login to verify if a login with the same name exists in the secondary server's syslogins system table. It then checks to see if the SID value for this login exists in the secondary database's sysusers system table. It finally checks to see if the SID value in secondary database's sysusers system table is not the same as the SID value in the secondary server's syslogins table. If these checks are satisfied, the sp_resolve_logins stored procedure runs the sp_Change_users_login stored procedure for that login, and fixes the SID in the secondary database's sysusers system table. Execution of this stored procedure is required only if there are new logins created on the primary server after log shipping has been initialized and those same logins are not created on the secondary servers with the same SID (as described in Microsoft Knowledge Base article Q303722).
Q3: The sp_resolve_logins stored procedure runs successfully; however, it does not perform the expected modifications to the security on the secondary server. Why?
A3: The sp_resolve_logins stored procedure requires an up-to-date BCP file of the primary server's syslogins system table. These logins must already by created on the secondary server. If these two conditions are met, the sp_resolve_logins stored procedure performs the modifications to the sysusers system table in the secondary database.
Q4: Do I have to run a Transfer Logins DTS task in conjunction with the sp_resolve_logins stored procedure before performing the role change?
A4: Yes. You must use the Transfer Logins task to make sure that the logins exist in the syslogins system table on the secondary server. This does not guarantee that the user can use the secondary database (if the secondary database is loaded in standby mode). If the user has to use the secondary database before performing the log shipping role change, see question 1 in this section.
Q5: Does the sp_resolve_logins stored procedure work for remote logins in SQL Server?
A5: No. The sp_resolve_logins stored procedure only works for typical logins. Any remote logins must be created manually on the secondary server.
Log shipping monitoring
Q1: Log Shipping Backup and Out of Sync alerts are firing, even when the secondary server is updated with the transaction log backups. Is this possible?
A1: Yes. It is possible that the alerts might fire even when the secondary database is being updated. If the alert threshold is set to a value less than double the time between back up and copy or restore jobs, the alerts might be raised. If the alerts are being raised and the threshold is close to or less than two times the time between subsequent backup and copy or restore jobs, go ahead and increase the threshold.
Q2: Why do the transaction log backups fail to restore on the secondary server?
A2: Transaction Log backups can only be restored if they are in a sequence. This sequence is determined by the LastLSN and FirstLSN fields that are returned by the RESTORE HEADERONLY (http://msdn2.microsoft.com/en-us/library/aa238455(SQL.80).aspx) command. If the LastLSN field and the FirstLSN field do not display the same number on consecutive transaction log backups, they are not restorable in that sequence. There may be several reasons for transaction log backups to be out of sequence. Some of the most common reasons are:
There are redundant transaction log backup jobs on the primary server that are causing the sequence to be broken.
There are non-logged operations performed in the database. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
272093 (http://support.microsoft.com/kb/272093/ ) Description of the effects of nonlogged and minimally logged operations on transaction log backup and the restore process in SQL Server
The recovery model of the database was probably toggled between transaction log backups.
The Data Transformation Services (DTS) task on the primary server might be causing this problem. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
308267 (http://support.microsoft.com/kb/308267/ ) FIX: DTS Copy Objects Task (DMO) breaks transaction log backup chain by switching recovery mode to Simple during transfer
Q3: Where can I find information about errors while performing back up, copy or restore operations?
A3: To get more information about a particular log shipping pair, follow these steps:
Open SQL Server Enterprise Manager, and then connect to the monitor server.
Under Management, click Log Shipping Monitor. In the right window pane, all the log shipping pairs are displayed (that have been configured with this server as the monitor server). If the log shipping pair is not visible, right-click the Log Shipping Monitor (under Management), and then click Refresh.
Right-click the log shipping pair that you want information about, and then click View Backup History to view the back up job history.
Right-click the log shipping pair, and then click View Copy/Restore History to view the history for copy and restore jobs.
Right-click the log shipping pair, and then click Properties to view the current log shipping status, Source, and Destination alert status.
Q4: Does the file name first_file_000000000000.trn indicate that the copy or restore job was unsuccessful?
A4: Each run of the copy and restore job is associated with at least one file. By default, if no files are copied or restored in a certain run of any of these two jobs, SQL Server places first_file_000000000000.trn in the file name field. This may or may not indicate a problem. For example, the very first time that copy or restore jobs are run on the secondary server, there might not be any files available to copy or restore. In this case, first_file_000000000000.trn does not necessarily represent an error. However, under certain circumstances, this might represent a problem. Read the following Microsoft Knowledge Base article for more information:
292586 (http://support.microsoft.com/kb/292586/ ) Backup, copy, and load job information is not updated on the log shipping monitor
Q5: Is it possible to modify the frequency and destination of the transaction log backups, on the primary server, after log shipping has been operational for a while?
A5: Yes. This information is in the Maintenance Plan on the primary server. To view the information, follow these steps:
Double-click the Maintenance Plan on the primary server for the database for which this information must be modified.
Click the Transaction Log Backup tab. Modify the destination and frequency in the dialog box.
Because the copy job on the secondary server is expecting to copy transaction log backups from the share specified at the time log shipping was set up, this job might fail after modifying the target folder for the transaction log back ups. For more information about how to work around this problem, read the following article in the Microsoft Knowledge Base:
314570 (http://support.microsoft.com/kb/314570/ ) Cannot modify backup network share after you change the transaction log backup folder
Log shipping role change
Q1: How do I perform a log shipping role change?
A1: Click the following link to read the SQL Server 2000 Books Online topic about performing a log shipping role change:
How to set up and perform a log shipping role change (Transact-SQL) (http://msdn2.microsoft.com/en-us/library/aa215392(SQL.80).aspx)
Q2: Can I perform a role change while the primary server is offline or unavailable?
A2: Yes. Running the sp_change_primary_role (http://msdn2.microsoft.com/en-us/library/aa259617(SQL.80).aspx) stored procedure on the primary server is optional.
Q3: Why does the sp_resolve_logins stored procedure fail with error message 208 when run from the secondary database at the time of a role change?
A3: The sp_resolve_logins stored procedure does not qualify the sysusers system table with the master database prefix. This is a known problem with the code for the sp_resolve_logins stored procedure. For more information about this problem, read the following article in the Microsoft Knowledge Base:
310882 (http://support.microsoft.com/kb/310882/ ) BUG: sp_resolve_logins stored procedure fails if executed during log shipping role change
Q4: Is there a problem when promoting a secondary server to be a primary server, when there are multiple secondary servers involved in a role change?
A4: Read the following Microsoft Knowledge Base article about a known problem that can cause errors while performing a role change that involves multiple secondary servers:
300497 (http://support.microsoft.com/kb/300497/ ) FIX: Log shipping: Cannot change role from secondary to primary when database names are different
Q5: How can I re-establish log shipping after promoting the secondary server to be the primary server?
A5: If the Allow database to assume Primary role check box is selected, while setting up log shipping, in the Add Destination Database dialog box, follow these steps to add a new secondary server after performing a role change. If the setting was not selected, use the Maintenance Plan Wizard to set up log shipping after a role change.
Open SQL Server Enterprise Manager, and then connect to the promoted primary server. Register the server that you intend to add as the secondary server.
Expand Management (in SQL Server Enterprise Manager), and then click Maintenance Plans. Right-click the appropriate Maintenance Plan from the list, and then click Properties.
Click the Log Shipping tab, and then click Add.
Provide the appropriate information regarding the secondary server about this dialog box, and then click OK. This will add the new secondary server to log shipping.
Q6: How can I continue to log ship to the former primary server without restoring a database backup?
A6: It is possible to log ship between two servers repeatedly without having to restore the complete database backup. The requirement is that both the primary and secondary servers are available when you perform the role change procedure. As part of performing the role change, you must run the sp_change_primary_role (http://msdn.microsoft.com/en-us/library/aa259617.aspx) stored procedure. You must run the sp_change_primary_role stored procedure with a @final_state parameter of either 2 or 3. This will leave the primary database in an unrecovered state after performing the transaction log back up. Because the database is left in an unrecovered state, this database can be selected when the log shipping destination is added (as explained in the previous question). This way you do not have to reload a database backup.
Log shipping removal
Q1: How can I stop log shipping for a particular log shipping pair?
A1: Follow these steps to remove a log shipping pair:
Open the SQL Server Enterprise Manager on the primary server. Expand Management, and then click Maintenance Plan. Right-click the Maintenance Plan, and then click Properties.
Click the Log Shipping tab, and then click to select the log shipping pair that you want to remove.
Click the Delete command button to remove this pair from log shipping. If this is the last pair in log shipping, clicking Delete removes log shipping. If you want to continue log shipping to a different server or to a database, click Add. Then, click to select the appropriate server or database to act as the secondary server before you remove the existing log shipping secondary.
Q2: Is there a problem with removing log shipping for a database that has special characters in it's name?
A2: Read the following Microsoft Knowledge Base article, which discusses this problem in more detail:
295936 (http://support.microsoft.com/kb/295936/ ) FIX: Error removing log shipping on secondary database when database name has a quote
Back to the top
REFERENCES
For more information about log shipping, visit the following Microsoft Web sites:
How to setup log shipping (white paper)
http://support.microsoft.com/support/sql/content/2000papers/LogShippingFinal.asp (http://support.microsoft.com/?scid=http%3a%2f%2fsupport.microsoft.com%2fsupport%2fsql%2fcontent%2f2000papers%2flogshippingfinal.asp)
Log shipping
http://msdn2.microsoft.com/en-us/library/aa213785(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa213785(SQL.80).aspx)
275146 (http://support.microsoft.com/kb/275146/ ) Frequently asked questions - SQL Server 7.0 - Log shipping
Didn't see an answer to your question? Visit the Microsoft SQL Server Newsgroups at:
Microsoft SQL Server Newsgroupshttp://www.microsoft.com/communities/newsgroups/en-us/ (http://www.microsoft.com/communities/newsgroups/en-us/)
Comments about this or other Microsoft Knowledge Base articles? Drop us a note at SQLKB@Microsoft.com (mailto:sqlkb@microsoft.com) .
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
917544 (http://support.microsoft.com/kb/917544/ ) BUG: You receive an error message when you run the "Log Shipping Alert Job - Restore" job in SQL Server 2000
Back to the top
--------------------------------------------------------------------------------
APPLIES TO
Microsoft SQL Server 2000 Enterprise Edition
Microsoft SQL Server 2000 Developer Edition
Back to the top
Keywords: kbinfo KB314515
Back to the top
订阅：
博文 (Atom)
博文
