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

AlwaysOn Availability Groups learning resources

Overview of AlwaysOn Availability Groups (SQL Server) http://msdn.microsoft.com/en-us/library/ff877884.aspx Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server) http://msdn.microsoft.com/en-us/library/ff878487.aspx SQL AlwaysOn Team Blog http://blogs.msdn.com/b/sqlalwayson/ SQL Server Customer Advisory Team http://blogs.msdn.com/b/sqlcat/ MSDN Blogs  >  Brad Chen’s SQL Server Blog   >  All Tags  >  alwayson http://blogs.msdn.com/b/bradchen/archive/tags/alwayson/ AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution…

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

Burn an ISO image to physical DVD media with Windows 2008 R2 build-in tool "isoburn.exe"

  1.You must have the Desktop Experience pack installed    必須安裝桌面體驗 2.Windows Server 2008 R2 machines allow only the localsystem\administrator account permission to burn a CD/DVD on the local machine    必須使用預設的本機管理員帳戶localsystem/Administrator才能在本機燒錄光碟。 3.如果你的登入帳戶不是本機管理員帳戶localsystem/Administrator,則使用以下步驟:  (1)啟動命令提示字元,切換成localsystem/administrator身分再啟動一個命令提示字元。      C:\Users\CONTOSO.bradchen>runas /user:administrator cmd  (2)輸入本機管理員Administrator的密碼之後按確定,就會跳出新的命令提示字元。  (3)在新的命令提示字元輸入以下命令來燒錄。      C:\Windows\system32>isoburn /Q D: “C:\en_sql_server_2012_enterprise_edition_x86_x64_dvd_813294.iso”      PS.以上範例D:燒錄光碟機代號        [Reference] Windows Server 2008: How to…

0

Windows 2012 Failover Cluster Management Console Error "A weak Event was created and it lives on the wrong object"

最近因為客戶的需求在Windows Server 2012上安裝一組SQL Server 2012 Failover Cluster,遇到以下問題,以下是解決方法: [Symptoms] When you opened Failover-Manager and clicked on Roles or Nodes you get the following error. A weak Event was created and it lives on the wrong object, there is a very high Chance this will fail, please Review and make changes on your code to prevent the…

0

Install SQL Server 2012 Failover Cluster with Command Line and Configuration File

Install SQL Server 2012 Failover Cluster with Command Line and Configuration File   1.安裝設定好Windows 2008 R2 Failover Cluster。 可參考這一篇的前半部 Build a two-node SQL Server 2008 R2 Cluster with iSCSI 2.安裝SQL Server Failover Cluster之前,必須在兩個節點先安裝.Net Framework 3.5.1。 可以透過Server Manager或PowerShell的方式來安裝。 (1)使用Server Manager安裝。   (2)使用PowerShell安裝。 A.以系統管理員身分啟動PowerShell命令提示字元。 B.輸入以下命令。     Import-Module ServerManager     Add-WindowsFeature NET-Framework-Core How to install/enable .Net 3.5 SP1 on…

0

Install SQL Server 2012 with command line

Install SQL Server 2012 with command line 1.安裝SQL Server之前,必須先安裝.Net Framework 3.5.1。 可以透過Server Manager或PowerShell的方式來安裝。 (1)使用Server Manager安裝。         (2)使用PowerShell安裝。   A.以系統管理員身分啟動PowerShell命令提示字元。   B.輸入以下命令。     Import-Module ServerManager     Add-WindowsFeature NET-Framework-Core     How to install/enable .Net 3.5 SP1 on Windows Server 2008 R2 for SQL Server 2008 and SQL Server 2008 R2   http://blogs.msdn.com/b/sqlblog/archive/2010/01/08/how-to-install-net-framework-3-5-sp1-on-windows-server-2008-r2-environments.aspx   Overview of Server…

0

Setup SQL Server 2012 AlwaysOn Availability Groups using Certificate

Setup SQL 2012 AlwaysOn Availability Groups using Certificate 1.如果SQL Server的啟動帳戶使用本機帳戶或虛擬帳戶Virtual Account(本機受管理的服務帳戶Managed Local Accounts),若要在這種環境下設定AlwaysOn,則必須使用憑證Certificate來設定。 安裝SQL Server 2012時SQL Server服務的預設啟動帳戶就是Virtual Account,也就是NT Service\MSSQLSERVER 以下範例:2個Instance都是使用NT Service\MSSQLSERVER 2.使用憑證Certificate建立端點Endpoint與相關登入帳戶與權限設定 (1)Create encryption key, certificate and end-points on Principal Instance USE [master] GO SELECT * FROM sys.symmetric_keys; GO — Create Database Master Key CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘P@ssw0rd’; GO CREATE CERTIFICATE AG_HOST_SQL2012A1_PRIN_Cert…

0

Setup SQL Server 2012 AlwaysOn Availability Groups using Join Only

Setup SQL 2012 AlwaysOn Availability Groups using Join Only 1.事先將主要Instance上的MyDB資料庫做備份(a Full Backup and a Log Backup)並指定NO RECOVERY 還原到第二個Instance。 — 第1個也就是主要的Instance SQL2012A1 BACKUP DATABASE [MyDB] TO DISK = N’C:\Temp\MyDB.bak’ GO BACKUP LOG [MyDB] TO DISK = N’C:\Temp\MyDB.trn’ GO — 第2個Instance SQL2012A2 RESTORE DATABASE [MyDB] FROM DISK = N’C:\Temp\MyDB.bak’ WITH NORECOVERY GO RESTORE LOG [MyDB] FROM DISK…

0

Setup SQL Server 2012 AlwaysOn Availability Groups using Skip initial data synchronization

Setup SQL 2012 AlwaysOn Availability Groups using Skip initial data synchronization 1.在選取初始資料同步處理時,選擇[略過初始資料同步處理](Skip initial data synchronization) 2.驗證時會略過很多項目 3.完成時也會略過很多項目 4.此時AG新增成功,因為第二的Instance還沒有MyDB資料庫存在,所以在第二個Instance的可用性資料庫會出現驚嘆號 5.啟動[顯示儀表板],第二個Instance的MyDB會出現驚嘆號,同步處理狀態為”未進行同步處理” 6.接著可以進行新增可用性群組接聽程式。請參考這篇 Quick Setup a SQL Server 2012 AlwaysOn Availability Groups  的步驟7.新增可用性群組接聽程式 7.將主要Instance上的MyDB資料庫做備份(a Full Backup and a Log Backup)並指定NO RECOVERY 還原到第二個Instance。 — 第1個也就是主要的Instance SQL2012A1 BACKUP DATABASE [MyDB] TO DISK = N’C:\Temp\MyDB.bak’ GO BACKUP LOG [MyDB] TO DISK =…

0