SQL Server and SQL Server Agent Service Account(Startup Account) and Permissions

SQL Server and SQL Server Agent service account(Startup Account) and Permissions SQL Server與SQL Server Agent 服務帳戶(啟動帳戶)與權限 可設定的種類 1.Domain User Account (使用一個網域使用者帳戶) 2.Local User Account (使用一個本機使用者帳戶) 3.Local Service Account (NT AUTHORITY\LOCAL SERVICE) 本機服務帳戶 4.Network Service Account (NT AUTHORITY\NETWORK SERVICE) 本機網路帳戶 5.Local System Account (NT AUTHORITY\SYSTEM) 本機系統帳戶 6.Virtual Accounts (start from Windows 7 and Windows Server 2008…

0

Query SQL Server backup history and restore history records

SQL Server備份還原紀錄 1.使用以下TSQL語法查詢備份檔紀錄 SELECT bs.backup_set_id, bs.database_name, bs.backup_start_date, bs.backup_finish_date, CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ‘ ‘ + ‘MB’ AS [Size], CAST(DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date) AS VARCHAR(4)) + ‘ ‘ + ‘Seconds’ [TimeTaken], CASE bs.[type] WHEN ‘D’ THEN ‘Full Backup’ WHEN ‘I’ THEN ‘Differential Backup’ WHEN ‘L’ THEN ‘TLog Backup’ WHEN ‘F’ THEN ‘File or filegroup’ WHEN…

4

Apply snapshot from Alternate folder to improve performance when initialize a Subscription in Transactional Replication

Apply snapshot from Alternate folder to improve performance when initialize a Subscription in Transactional Replication 訂閱的初始化有兩個過程: 1.產生snapshot file,這個是snapshot agent所負責的工作。  Type of Replication Common Snapshot Files Snapshot  Replication or Transactional Replication schema (.sch);  data (.bcp); constraints and indexes (.dri); constraints (.idx); triggers  (.trg):for updating Subscribers only; compressed snapshot files (.cab). Merge  Replication schema (.sch);  data (.bcp);…

0

Quick to build a testing web site to access SQL Server

Classic ASPHow to access SQL Server in Active Server Pageshttp://support.microsoft.com/kb/169377/en-us ASP.NET 1.1逐步解說:在 Web Form 網頁中顯示資料Visual Studio .NET 2003 http://msdn.microsoft.com/zh-tw/library/cc438239(v=vs.71).aspx Walkthrough: Basic Data Access in Web Pages  Visual Studio 2005http://msdn.microsoft.com/en-us/library/tw738475(v=vs.80).aspx Walkthrough: Basic Data Access in Web PagesVisual Studio 2008http://msdn.microsoft.com/en-us/library/tw738475(v=vs.90).aspx Walkthrough: Basic Data Access in Web Pages.NET Framework 4http://msdn.microsoft.com/en-us/library/tw738475(v=vs.100).aspx

0

Remove a crash SQL Cluster Node and add new SQL Cluster Node back to exist SQL Cluster

如何移除一個硬體損毀的節點,並且重新加入原來的叢集 To recover from a failover cluster hardware failure 1.After Node 1 fails, the SQL Server failover cluster fails over to Node 2.   當節點1發生問題時,SQL Server會failover到節點2 2.Evict Node 1 from Microsoft Cluster Service (MSCS). To evict a node from MSCS, from Node 2, open Cluster Administrator, right-click the node you want to remove, and then click…

0

Manage a SQL Server failover cluster from command line or powershell

1.1 目前的Cluster Name c:\Windows\system32\cluster /prop PS C:\Users\Administrator.domain>Get-Cluster | fl * 1.2 顯示目前的網域裡的所有Cluster c:\Winodws\system32>cluster /list    PS C:\Users\Administrator.domain>Get-Cluster PS.Windows 2008 R2必須先Import failover cluster module PS C:\Windows\system32>Import-Module FailoverClusters   2.顯示目前的cluster的所有節點狀態 c:\Winodws\system32>cluster node   PS C:\Users\Administrator.domain>Get-ClusterNode 3.顯示Quorum狀態 c:\Winodws\system32>cluster /quorum PS C:\Users\Administrator.domain>Get-ClusterQuorum 4.顯示所有叢集群組 c:\Winodws\system32>cluster group   PS C:\Users\Administrator.domain>Get-ClusterGroup PS.可以查看目前Quorum在哪一個節點   顯示叢集群組(叢集應用程式)屬性 PS C:\Users\Administrator.domain>Get-ClusterGroup “叢集群組” | fl *    …

0

AD account permission required for SQL Server failover Cluster Installation

如果沒有Domain管理員的權限要安裝SQL Server Cluster需要在Active Directory額外設定一些電腦帳戶與權限,當參考以下這篇MSDN文件時常常會被一些名詞混淆。 Failover Cluster Step-by-Step Guide: Configuring Accounts in Active Directory http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx 文章裡面的cluster name account就是CNO也就是cluster computer account也就是一個在AD上的一個電腦帳戶(名稱是建立Cluster時所給予的一個network name),以這篇文章為例就是cluster1,如果在設定權限時要搜尋到這個電腦帳戶就必須輸入cluster1$ 所以 cluster name account (cluster name object) = CNO = cluster computer account = computer account of the cluster itself = cluster1$   以下列出3種可能的情境 Scenario 1.If you do not have domain administrative permissions (如果安裝者沒有網域管理員權限,必須事先給予安裝者與CNO需要的權限) (1)The…

0

Build a two-node SQL Server 2008 R2 Cluster with iSCSI

Build a two-node SQL Server 2008 R2 Cluster with iSCSI Lab VMs: 1.iSCSITarget (Windows Storage Server 2008 R2 SP1) (DC and iSCSI Target Server) 2.SQL1 (SQL Server 2008 R2 on Windows 2008 R2 SP1) 3.SQL2 (SQL Server 2008 R2 on Windows 2008 R2 SP1) [Action Plan] 1.on SQL1 and SQL2 configure ip address for public…

0

Add a node to an existing two-node SQL Server 2008 R2 Cluster

Add a node to an existing two-node SQL Server 2008 R2 Cluster 如何新增一個節點到已經存在的SQL Server 2008 R2 Cluster Lab VMs: 1.DC (Windows 2003) 2.iSCSITarget (Windows Storage Server 2008 R2 SP1) 3.SQL1 (SQL Server 2008 R2 on Windows 2008 R2 SP1, existing SQL Cluster Node1) 4.SQL2 (SQL Server 2008 R2 on Windows 2008 R2 SP1, existing SQL…

0

How to Rename SQL Server Cluster Virtual Server Name(Network name)

How to Rename SQL Server Cluster Virtual Server Name(Network name) Rename a SQL Server Failover Cluster Instance http://msdn.microsoft.com/en-us/library/ms178083.aspx The name of the virtual server is always the same as the name of the SQL Network Name (the SQL Virtual Server Network Name). Although you can change the name of the virtual server, you cannot change…

0