Pertimbangan Desain SQL Server

System Center Operations Manager memerlukan akses ke instans server yang menjalankan Microsoft SQL Server untuk mendukung database audit operasional, gudang data, dan ACS. Database operasional dan gudang data diperlukan dan dibuat saat Anda menyebarkan server manajemen pertama di grup manajemen Anda, sementara database ACS dibuat saat Anda menyebarkan kolektor ACS di grup manajemen Anda.

Di lingkungan lab atau penyebaran Manajer Operasi skala kecil, SQL Server dapat ditempatkan bersama di server manajemen pertama dalam grup manajemen.

Dalam penyebaran terdistribusi skala menengah hingga perusahaan, instans SQL Server harus terletak di server mandiri khusus atau dalam konfigurasi ketersediaan tinggi SQL Server. Dalam kedua kasus, SQL Server harus sudah ada dan dapat diakses sebelum Anda memulai penginstalan server manajemen pertama atau kolektor ACS.

Kami tidak merekomendasikan pemanfaatan database Operations Manager dari Instans SQL yang memiliki database aplikasi lain. Ini untuk menghindari potensi masalah dengan I/O dan pembatasan sumber daya perangkat keras lainnya.

Penting

Operations Manager tidak mendukung instans SQL Platform as a Service (PaaS), termasuk produk seperti Azure SQL Managed Instance atau Amazon Relational Database Service (AWS RDS). Silakan gunakan instans SQL Server yang terinstal pada komputer Windows. Satu-satunya pengecualian untuk ini adalah dalam Azure Monitor SCOM Managed Instance, yang menggunakan Azure SQL MI, dan tidak dapat dikonfigurasi ulang.

Persyaratan SQL Server

Versi SQL Server Enterprise & Standard Edition berikut ini didukung untuk penginstalan versi Manajer Operasi Pusat Sistem yang ada untuk menghosting server pelaporan, operasional, gudang data, dan database ACS:

• SQL Server 2022

• SQL Server 2019 dengan Pembaruan Kumulatif 8 (CU8) atau yang lebih baru seperti yang dijelaskan di sini

  Catatan

  • Operations Manager 2022 mendukung SQL 2019 dengan CU8 atau yang lebih baru; namun, ini tidak mendukung SQL 2019 RTM.
  • Gunakan ODBC 17.3 atau yang lebih baru, dan MSOLEDBSQL 18.2 atau yang lebih baru.

• SQL Server 2017 dan Updates Kumulatif seperti yang dijelaskan di sini

Sebelum meningkatkan SQL Server, lihat informasi peningkatan untuk 2017 dan informasi peningkatan untuk SQL 2019.

Catatan

• Masing-masing komponen SQL Server berikut yang mendukung infrastruktur SCOM harus berada pada versi SQL Server utama yang sama:
  • SQL Server instans mesin database yang menghosting salah satu database SCOM (yaitu, OperationManager, OperationManagerDW, dan database SSRS ReportServer & ReportServerTempDB).
  • instans SQL Server Reporting Services (SSRS).
• Pengaturan kolase SQL Server harus menjadi salah satu jenis yang didukung seperti yang dijelaskan di bagian pengaturan kolase SQL Server di bawah ini.
• SQL Server Pencarian Teks Lengkap diperlukan untuk semua instans mesin database SQL Server yang menghosting salah satu database SCOM.
• Opsi penginstalan Windows Server 2016 (Server Core, Server dengan Pengalaman Desktop, dan Nano Server) yang didukung oleh komponen database Operations Manager didasarkan pada opsi penginstalan Windows Server apa yang didukung oleh SQL Server.

Catatan

Pelaporan Manajer Operasi Pusat Sistem tidak dapat diinstal secara berdampingan dengan versi sebelumnya dari peran Pelaporan dan harus diinstal dalam mode asli saja (mode terintegrasi SharePoint tidak didukung).

Pertimbangan perangkat keras dan perangkat lunak tambahan berlaku dalam perencanaan desain Anda:

• Kami menyarankan agar Anda menjalankan SQL Server di komputer dengan format file NTFS.
• Harus ada setidaknya 1024 MB ruang disk kosong untuk database operasional dan gudang data. Ini diberlakukan pada saat pembuatan database, dan kemungkinan akan tumbuh secara signifikan setelah penyiapan.
• .NET Framework 4 diperlukan.
• .NET Framework 4.8 didukung dari Operations Manager 2022.
• Server Pelaporan tidak didukung di Windows Server Core.

Untuk informasi selengkapnya, lihat Persyaratan Perangkat Keras dan Perangkat Lunak untuk Menginstal SQL Server.

Catatan

Meskipun Operations Manager hanya menggunakan autentikasi Windows selama penginstalan, pengaturan Autentikasi Mode Campuran SQL akan tetap berfungsi jika tidak ada akun lokal yang memiliki peran db_owner. Akun lokal dengan peran db_owner diketahui menyebabkan masalah dengan Manajer Operasi Pusat Sistem. Hapus peran db_owner dari semua akun lokal sebelum menginstal produk dan jangan tambahkan peran db_owner ke salah satu akun lokal setelah penginstalan.

pengaturan kolaset SQL Server

Kolae SQL Server dan Windows berikut didukung oleh System Center Operations Manager.

Catatan

Untuk menghindari masalah kompatibilitas dalam membandingkan atau menyalin operasi, kami sarankan Anda menggunakan kolase yang sama untuk SQL dan Operations Manager DB.

kolae SQL Server

• SQL_Latin1_General_CP1_CI_AS

Kolabasi Windows

• Latin1_General_100_CI_AS
• French_CI_AS
• French_100_CI_AS
• Cyrillic_General_CI_AS
• Chinese_PRC_CI_AS
• Chinese_Simplified_Pinyin_100_CI_AS
• Chinese_Traditional_Stroke_Count_100_CI_AS
• Japanese_CI_AS
• Japanese_XJIS_100_CI_AS
• Traditional_Spanish_CI_AS
• Modern_Spanish_100_CI_AS
• Latin1_General_CI_AS
• Cyrillic_General_100_CI_AS
• Korean_100_CI_AS
• Czech_100_CI_AS
• Hungarian_100_CI_AS
• Polish_100_CI_AS
• Finnish_Swedish_100_CI_AS

Jika instans SQL Server Anda tidak dikonfigurasi dengan salah satu kolase yang didukung yang tercantum sebelumnya, melakukan penyiapan baru penyiapan Operations Manager akan gagal. Namun, peningkatan di tempat akan berhasil diselesaikan.

Konfigurasi Firewall

Manajer Operasi bergantung pada SQL Server untuk menghosting database dan platform pelaporan untuk menganalisis dan menyajikan data operasional historis. Peran server manajemen, Operasi, dan konsol Web harus berhasil berkomunikasi dengan SQL Server, dan penting untuk memahami jalur komunikasi dan port untuk mengonfigurasi lingkungan Anda dengan benar.

Jika Anda merancang penyebaran terdistribusi yang akan mengharuskan Grup Ketersediaan AlwaysOn SQL untuk menyediakan fungsionalitas failover untuk database Operations Manager, ada pengaturan konfigurasi firewall tambahan yang perlu disertakan dalam strategi keamanan firewall Anda.

Tabel berikut membantu Anda mengidentifikasi port firewall yang diperlukan oleh SQL Server yang perlu diizinkan minimal agar peran server di grup manajemen Manajer Operasi Anda berhasil berkomunikasi.

Skenario	Port	Arah	Peran Manajer Operasi
SQL Server menghosting database Operations Manager	TCP 1433 *	Masuk	server manajemen dan konsol Web (untuk Application Advisor dan Diagnostik Aplikasi)
layanan browser SQL Server	UDP 1434	Masuk	server manajemen
Koneksi Admin Khusus SQL Server	TCP 1434	Masuk	server manajemen
Port tambahan yang digunakan oleh SQL Server - Panggilan prosedur jarak jauh Microsoft (MS RPC) - Instrumentasi Manajemen Windows (WMI) - Koordinator Transaksi Terdistribusi Microsoft (MS DTC)	Protokol Kendali Transmisi 135	Masuk	server manajemen
SQL Server Pendengar Grup Ketersediaan AlwaysOn	Port yang dikonfigurasi administrator	Masuk	server manajemen
SQL Server Reporting Services menghosting Server Pelaporan Manajer Operasi	TCP 80 (default)/443 (SSL)	Masuk	server manajemen dan konsol Operasi

* Meskipun TCP 1433 adalah port standar untuk instans default Mesin Database, ketika Anda membuat instans bernama pada SQL Server mandiri atau telah menyebarkan Grup Ketersediaan AlwaysOn SQL, port kustom akan ditentukan dan harus didokumentasikan untuk referensi sehingga Anda mengonfigurasi firewall Anda dengan benar dan memasukkan informasi ini selama penyiapan.

Untuk gambaran umum yang lebih rinci tentang persyaratan firewall untuk SQL Server, lihat Mengonfigurasi Windows Firewall untuk Mengizinkan akses SQL Server.

Pertimbangan kapasitas dan penyimpanan

Database Manajer Operasi

Database Operations Manager adalah database SQL Server yang berisi semua data yang diperlukan oleh Operations Manager untuk pemantauan sehari-hari. Ukuran dan konfigurasi server database sangat penting untuk performa keseluruhan grup manajemen. Sumber daya paling penting yang digunakan oleh database Operations Manager adalah subsistem penyimpanan, tetapi CPU dan RAM juga signifikan.

Faktor-faktor yang memengaruhi beban pada database Operations Manager meliputi:

• Tingkat pengumpulan data operasional. Data operasional terdiri dari semua peristiwa, pemberitahuan, perubahan status, dan data performa yang dikumpulkan oleh agen. Sebagian besar sumber daya yang digunakan oleh database Operations Manager digunakan untuk menulis data ini ke disk saat masuk ke sistem. Tingkat data operasional yang dikumpulkan cenderung meningkat karena paket manajemen tambahan diimpor dan agen tambahan ditambahkan. Jenis komputer yang dipantau agen juga merupakan faktor penting yang digunakan saat menentukan tingkat keseluruhan pengumpulan data operasional. Misalnya, agen yang memantau komputer desktop penting bisnis dapat diharapkan untuk mengumpulkan lebih sedikit data daripada agen yang memantau server yang menjalankan instans SQL Server dengan sejumlah besar database.
• Laju perubahan ruang instans. Memperbarui data ini dalam database Operations Manager sangat relatif terhadap penulisan data operasional baru. Selain itu, ketika data ruang instans berubah, server manajemen membuat kueri tambahan ke database Operations Manager untuk menghitung konfigurasi dan perubahan grup. Laju perubahan ruang instans meningkat saat Anda mengimpor paket manajemen tambahan ke dalam grup manajemen. Menambahkan agen baru ke grup manajemen juga untuk sementara meningkatkan laju perubahan ruang instans.
• Jumlah Konsol Operasi dan koneksi SDK lainnya yang berjalan secara bersamaan. Setiap konsol Operasi membaca data dari database Operations Manager. Mengkueri data ini mengonsumsi sumber daya I/O penyimpanan dalam jumlah besar, waktu CPU, dan RAM. Konsol operasi yang menampilkan sejumlah besar data operasional dalam Tampilan Peristiwa, Tampilan Status, Tampilan Pemberitahuan, dan Tampilan Data Performa cenderung menyebabkan beban terbesar pada database.

Database Operations Manager adalah sumber kegagalan tunggal untuk grup manajemen, sehingga dapat dibuat sangat tersedia menggunakan konfigurasi failover yang didukung seperti SQL Server Grup Ketersediaan AlwaysOn atau Instans Kluster Failover.

Anda dapat menyiapkan dan meningkatkan database Operations Manager dengan penyiapan Always-On SQL yang ada tanpa perlu perubahan konfigurasi pasca.

Mengaktifkan Broker SQL pada database Operations Manager

Manajer Operasi Pusat Sistem bergantung pada SQL Server Service Broker untuk menerapkan semua operasi tugas. Jika SQL Server Service Broker dinonaktifkan, semua operasi tugas akan terpengaruh. Perilaku yang dihasilkan dapat bervariasi sesuai dengan tugas yang dimulai. Oleh karena itu, penting untuk memeriksa status SQL Server Service Broker setiap kali perilaku tak terduga diamati di sekitar tugas di Manajer Operasi Pusat Sistem.

Untuk mengaktifkan SQL Server Service Broker, ikuti langkah-langkah berikut:

1. Jalankan kueri SQL berikut ini:

   SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'
   

2. Lewati langkah ini jika nilai yang ditampilkan di is_broker_enabled bidang adalah 1 (satu). Jika tidak, jalankan kueri SQL berikut:

   ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
   ALTER DATABASE OperationsManager SET ENABLE_BROKER
   ALTER DATABASE OperationsManager SET MULTI_USER
   

Database gudang data Manajer Operasi

Pusat Sistem - Manajer Operasi menyisipkan data ke gudang data Pelaporan dalam waktu dekat secara real time, penting untuk memiliki kapasitas yang memadai di server ini yang mendukung penulisan semua data yang sedang dikumpulkan ke gudang data Pelaporan. Seperti database Operations Manager, sumber daya paling penting pada gudang data Pelaporan adalah subsistem I/O penyimpanan. Pada sebagian besar sistem, beban pada gudang data Pelaporan mirip dengan database Operations Manager, tetapi dapat bervariasi. Selain itu, beban kerja yang diletakkan di gudang data Pelaporan dengan pelaporan berbeda dari beban yang diletakkan pada database Operations Manager oleh penggunaan konsol Operations.

Faktor-faktor yang memengaruhi beban pada gudang data Pelaporan meliputi:

• Tingkat pengumpulan data operasional. Untuk memungkinkan pelaporan yang lebih efisien, gudang data Pelaporan menghitung dan menyimpan data agregat selain data mentah dalam jumlah terbatas. Melakukan pekerjaan ekstra ini berarti bahwa pengumpulan data operasional ke gudang data Pelaporan bisa sedikit lebih mahal daripada ke database Operations Manager. Biaya tambahan ini biasanya diseimbangkan dengan pengurangan biaya pemrosesan data penemuan oleh gudang data Pelaporan versus database Operations Manager.
• Jumlah pengguna pelaporan bersamaan atau pembuatan laporan terjadwal. Karena laporan sering meringkas data dalam volume besar, setiap pengguna pelaporan dapat menambahkan beban yang signifikan pada sistem. Jumlah laporan berjalan secara bersamaan dan jenis laporan yang dijalankan keduanya memengaruhi kebutuhan kapasitas secara keseluruhan. Umumnya, laporan yang mengkueri rentang tanggal besar atau sejumlah besar objek memerlukan sumber daya sistem tambahan.

Berdasarkan faktor-faktor ini, ada beberapa praktik yang direkomendasikan untuk dipertimbangkan saat mengukur gudang data Pelaporan:

• Pilih subsistem penyimpanan yang sesuai. Karena gudang data Pelaporan adalah bagian integral dari aliran data keseluruhan melalui grup manajemen, memilih subsistem penyimpanan yang sesuai untuk gudang data Pelaporan adalah penting. Seperti database Operations Manager, RAID 0 + 1 sering menjadi pilihan terbaik. Secara umum, subsistem penyimpanan untuk gudang data Pelaporan harus mirip dengan subsistem penyimpanan untuk database Operations Manager, dan panduan yang berlaku untuk database Operations Manager juga berlaku untuk gudang data Pelaporan.
• Pertimbangkan penempatan log data yang sesuai vs. log transaksi. Sedangkan untuk database Operations Manager, memisahkan data SQL dan log transaksi sering kali merupakan pilihan yang tepat saat Anda meningkatkan jumlah agen. Jika database Manajer Operasi dan gudang data Pelaporan terletak di server yang sama dan Anda ingin memisahkan data dan log transaksi, Anda harus menempatkan log transaksi untuk database Operations Manager pada volume fisik terpisah dan spindel disk dari gudang data Pelaporan untuk menerima manfaat apa pun. File data untuk database Operations Manager dan Gudang data pelaporan dapat berbagi volume fisik yang sama selama volume memberikan kapasitas yang memadai dan performa I/O disk tidak berdampak negatif pada fungsi pemantauan dan pelaporan.
• Pertimbangkan untuk menempatkan gudang data Pelaporan di server terpisah dari database Operations Manager. Meskipun penyebaran skala yang lebih kecil sering dapat mengonsolidasikan database Operations Manager dan Melaporkan gudang data di server yang sama, ada baiknya untuk memisahkannya saat Anda meningkatkan jumlah agen dan volume data operasional yang masuk. Ketika gudang data Pelaporan dan Server Pelaporan berada di server terpisah dari database Manajer Operasi, Anda mengalami performa pelaporan yang lebih baik.

Database gudang data Operations Manager adalah satu sumber kegagalan untuk grup manajemen, sehingga dapat dibuat sangat tersedia menggunakan konfigurasi failover yang didukung seperti SQL Server Grup Ketersediaan AlwaysOn atau Instans Kluster Failover.

SQL Server Always On

SQL Server grup ketersediaan AlwaysOn mendukung lingkungan failover untuk sekumpulan database pengguna (database ketersediaan) diskrit. Setiap set database ketersediaan dihosting oleh replika ketersediaan.

Dengan System Center 2016 dan yang lebih baru - Operations Manager, SQL Always On lebih disukai daripada pengklusteran failover untuk memberikan ketersediaan tinggi untuk database. Semua database kecuali penginstalan Reporting Services mode asli, yang menggunakan dua database untuk memisahkan penyimpanan data persisten dari persyaratan penyimpanan sementara, dapat dihosting di Grup Ketersediaan AlwaysOn.

Dengan Operations Manager 2022, Anda dapat menyiapkan dan meningkatkan database Operations Manager dengan penyiapan Always-On SQL yang ada tanpa perlu perubahan konfigurasi pasca.

Untuk menyiapkan grup ketersediaan, Anda harus menyebarkan kluster Windows Server Failover Clustering (WSFC) untuk menghosting replika ketersediaan, dan mengaktifkan Always On pada node kluster. Anda kemudian dapat menambahkan database SQL Server Manajer Operasi sebagai database ketersediaan.

• Pelajari selengkapnya tentang prasyarat AlwaysOn.
• Pelajari selengkapnya tentang menyiapkan WSFC untuk grup ketersediaan AlwaysOn.
• Pelajari selengkapnya tentang menyiapkan grup ketersediaan.

Catatan

Setelah menyebarkan Operations Manager pada simpul server SQL yang berpartisipasi dalam SQL Always On, untuk mengaktifkan keamanan ketat CLR, jalankan skrip SQL pada setiap database Operations Manager.

String multisubnet

Manajer Operasi tidak mendukung kata kunci string koneksi (MultiSubnetFailover=True). Karena grup ketersediaan memiliki nama pendengar (dikenal sebagai nama jaringan atau Titik Akses Klien di Manajer Kluster WSFC) tergantung pada beberapa alamat IP dari subnet yang berbeda, seperti ketika Anda menyebarkan dalam konfigurasi failover lintas situs, permintaan koneksi klien dari server manajemen ke pendengar grup ketersediaan akan mencapai batas waktu koneksi.

Pendekatan yang disarankan untuk mengatasi batasan ini saat Anda telah menyebarkan simpul server dalam grup ketersediaan di lingkungan multi-subnet adalah dengan melakukan hal berikut:

1. Atur nama jaringan pendengar grup ketersediaan Anda untuk hanya mendaftarkan satu alamat IP aktif di DNS.
2. Konfigurasikan kluster untuk menggunakan nilai TTL rendah untuk catatan DNS terdaftar.

Pengaturan ini memungkinkan, ketika failover ke node di subnet yang berbeda, untuk pemulihan dan resolusi nama kluster yang lebih cepat dengan alamat IP baru.

Jalankan perintah PowerShell berikut pada salah satu simpul SQL untuk mengubah pengaturannya:

Import-Module FailoverClusters
Get-ClusterResource "Cluster Name"|Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource "Cluster Name"|Set-ClusterParameter HostRecordTTL 300
Stop-ClusterResource "Cluster Name"
Start-ClusterResource "Cluster Name"
Start-ClusterGroup "Cluster Name"

Jika Anda menggunakan Always On dengan nama pendengar, Anda juga harus membuat perubahan konfigurasi ini pada pendengar. Untuk informasi selengkapnya tentang mengonfigurasi listener grup ketersediaan, lihat dokumentasi di sini: Mengonfigurasi listener grup ketersediaan - SQL Server AlwaysOn

Jalankan perintah PowerShell berikut pada simpul SQL yang saat ini menghosting pendengar untuk mengubah pengaturannya:

Import-Module FailoverClusters
Get-ClusterResource <Listener Cluster Resource name> | Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource <Listener Cluster Resource name> | Set-ClusterParameter HostRecordTTL 300
Stop-ClusterResource <Listener Cluster Resource name>
Start-ClusterResource <Listener Cluster Resource name>
Start-ClusterGroup <Listener Cluster Group name>

Ketika instans SQL kluster atau Always On digunakan untuk ketersediaan tinggi, Anda harus mengaktifkan fitur pemulihan otomatis di server manajemen Anda untuk menghindari layanan Operations Manager Data Access dimulai ulang kapan saja terjadi failover antar node. Untuk informasi tentang cara mengonfigurasi ini, lihat artikel KB berikut layanan Manajemen Pusat Sistem berhenti merespons setelah instans SQL Server offline.

Mengoptimalkan SQL Server

Secara umum, pengalaman penyebaran sebelumnya dengan pelanggan menunjukkan bahwa masalah performa biasanya tidak disebabkan oleh pemanfaatan sumber daya yang tinggi (yaitu prosesor atau memori) dengan SQL Server itu sendiri; melainkan terkait langsung dengan konfigurasi subsistem penyimpanan. Penyempitan performa biasanya dikaitkan dengan tidak mengikuti panduan konfigurasi yang direkomendasikan dengan penyimpanan yang disediakan untuk instans database SQL Server. Contoh tersebut adalah:

• Alokasi spindle yang tidak cukup untuk LUN untuk mendukung persyaratan IO Operations Manager.
• Hosting log transaksi dan file database pada volume yang sama. Kedua beban kerja ini memiliki karakteristik IO dan latensi yang berbeda.
• Konfigurasi TempDB salah sehubungan dengan penempatan, ukuran, dan sebagainya.
• Ketidakselarasan partisi disk volume yang menghosting log transaksi database, file database, dan TempDB.
• Mengabaikan konfigurasi SQL Server dasar seperti menggunakan AUTOGROW untuk database dan file log transaksi, pengaturan MAXDOP untuk paralelisme kueri, membuat beberapa file data TempDB per inti CPU, dan sebagainya.

Konfigurasi penyimpanan adalah salah satu komponen penting untuk penyebaran SQL Server untuk Operations Manager. Server database cenderung sangat terikat I/O karena aktivitas baca dan tulis database yang ketat dan pemrosesan log transaksi. Pola perilaku I/O Operations Manager biasanya 80% menulis dan 20% bacaan. Akibatnya, konfigurasi subsistem I/O yang tidak tepat dapat menyebabkan performa dan pengoperasian sistem SQL Server yang buruk dan menjadi terlihat di Operations Manager.

Penting untuk menguji desain SQL Server dengan melakukan pengujian throughput subsistem IO sebelum menyebarkan SQL Server. Pastikan bahwa pengujian ini dapat mencapai persyaratan IO Anda dengan latensi yang dapat diterima. Gunakan Utilitas Diskspd untuk mengevaluasi kapasitas I/O subsistem penyimpanan yang mendukung SQL Server. Artikel blog berikut, yang ditulis oleh anggota tim File Server di grup produk, memberikan panduan dan rekomendasi terperinci tentang cara melakukan pengujian stres menggunakan alat ini dengan beberapa kode PowerShell dan menangkap hasilnya menggunakan PerfMon. Anda juga dapat merujuk ke Operations Manager Sizing Helper untuk panduan awal.

Ukuran unit alokasi NTFS

Penyelarasan volume, yang biasa disebut sebagai penyelarasan sektor, harus dilakukan pada sistem file (NTFS) setiap kali volume dibuat pada perangkat RAID. Kegagalan untuk melakukannya dapat menyebabkan penurunan performa yang signifikan dan paling umum merupakan hasil dari ketidakselarasan partisi dengan batas unit stripe. Ini juga dapat menyebabkan ketidakselarasan cache perangkat keras, yang mengakibatkan pemanfaatan cache array yang tidak efisien. Saat memformat partisi yang akan digunakan untuk SQL Server file data, disarankan agar Anda menggunakan ukuran unit alokasi 64 KB (yaitu, 65.536 byte) untuk data, log, dan tempdb. Namun, perlu diketahui bahwa menggunakan ukuran unit alokasi yang lebih besar dari 4 KB menghasilkan ketidakmampuan untuk menggunakan kompresi NTFS pada volume. Meskipun SQL Server mendukung data baca-saja pada volume terkompresi, tidak disarankan.

Memori cadangan

Catatan

Banyak informasi di bagian ini berasal dari Jonathan Kehayias dalam posting blognya Berapa banyak memori yang sebenarnya dibutuhkan SQL Server saya? (sqlskills.com).

Tidak selalu mudah untuk mengidentifikasi jumlah memori fisik dan prosesor yang tepat untuk dialokasikan untuk SQL Server dalam mendukung System Center Operations Manager (atau untuk beban kerja lain di luar produk ini). Kalkulator ukuran yang disediakan oleh grup produk memberikan panduan berdasarkan skala beban kerja, tetapi rekomendasinya didasarkan pada pengujian yang dilakukan di lingkungan lab yang mungkin atau mungkin tidak selaras dengan beban kerja dan konfigurasi Anda yang sebenarnya.

SQL Server memungkinkan Anda untuk mengonfigurasi jumlah memori minimum dan maksimum yang akan dicadangkan dan digunakan oleh prosesnya. Secara default, SQL Server dapat mengubah persyaratan memorinya secara dinamis berdasarkan sumber daya sistem yang tersedia. Pengaturan default untuk memori server min adalah 0, dan pengaturan default untuk memori server maks adalah 2.147.483.647 MB.

Masalah terkait performa dan memori dapat muncul jika Anda tidak menetapkan nilai yang sesuai untuk memori server maks. Banyak faktor memengaruhi berapa banyak memori yang perlu Anda alokasikan untuk SQL Server untuk memastikan bahwa sistem operasi dapat mendukung proses lain yang berjalan pada sistem tersebut, seperti kartu HBA, agen manajemen, dan pemindaian real-time anti-virus. Jika memori yang cukup tidak diatur, OS dan SQL akan halaman ke disk. Hal ini dapat menyebabkan I/O disk meningkat, menurunkan performa lebih lanjut dan menciptakan efek riak di mana ia menjadi terlihat di Operations Manager.

Sebaiknya tentukan setidaknya 4 GB RAM untuk memori server min. Ini harus dilakukan untuk setiap simpul SQL yang menghosting salah satu database Manajer Operasi (operasional, gudang data, ACS).

Untuk memori server maks, kami sarankan Anda awalnya memesan total:

• RAM 1 GB untuk OS
• RAM 1 GB per setiap RAM 4 GB terpasang (RAM hingga 16 GB)
• RAM 1 GB per setiap RAM 8 GB terpasang (RAM di atas 16 GB)

Setelah Anda mengatur nilai-nilai ini, pantau penghitung Memory\Available MBytes di Windows untuk menentukan apakah Anda dapat meningkatkan memori yang tersedia untuk SQL Server. Windows menandakan bahwa memori fisik yang tersedia hampir habis pada 96 MB, jadi idealnya penghitung tidak boleh berjalan lebih rendah dari sekitar 200-300 MB, untuk memastikan Anda memiliki buffer. Untuk server dengan RAM 256 GB atau lebih tinggi, Anda mungkin ingin memastikannya tidak berjalan lebih rendah dari 1 GB.

Perlu diingat bahwa perhitungan ini mengasumsikan Anda ingin SQL Server dapat menggunakan semua memori yang tersedia, kecuali Anda memodifikasinya untuk memperhitungkan aplikasi lain. Pertimbangkan persyaratan memori khusus untuk OS Anda, aplikasi lain, tumpukan utas SQL Server, dan alokator perkalian lainnya. Rumus umumnya adalah ((total system memory) – (memory for thread stack) – (OS memory requirements) – (memory for other applications) – (memory for multipage allocators)), di mana memori untuk tumpukan utas = ((max worker threads) (stack size)). Ukuran tumpukan adalah 512 KB untuk sistem x86, 2 MB untuk sistem x64, dan 4 MB untuk sistem IA64, dan Anda dapat menemukan nilai untuk utas pekerja maks di kolom max_worker_count sys.dm_os_sys_info.

Pertimbangan ini juga berlaku untuk persyaratan memori agar SQL Server berjalan di komputer virtual. Karena SQL Server dirancang untuk menyimpan data di kumpulan buffer, dan biasanya akan menggunakan memori sebanyak mungkin, mungkin sulit untuk menentukan jumlah RAM ideal yang diperlukan. Saat mengurangi memori yang dialokasikan ke instans SQL Server, Anda akhirnya akan mencapai titik di mana jatah memori yang lebih rendah diperdagangkan untuk akses I/O disk yang lebih tinggi.

Untuk mengonfigurasi memori SQL Server di lingkungan yang telah disediakan secara berlebihan, mulailah dengan memantau lingkungan dan metrik performa saat ini, termasuk harapan hidup halaman SQL Server Buffer Manager dan pembacaan halaman/detik dan nilai disk Disk Fisik membaca/detik. Jika lingkungan memiliki memori berlebih, harapan hidup halaman akan meningkat dengan nilai satu per detik tanpa penurunan di bawah beban kerja, karena penembolokan; halaman SQL Server Buffer Manager membaca/detik nilai akan rendah setelah cache naik; dan disk Disk Fisik membaca/detik juga akan tetap rendah.

Setelah Memahami garis besar lingkungan, Anda dapat mengurangi memori server maks sebesar 1 GB, lalu melihat bagaimana hal itu memengaruhi penghitung kinerja Anda (setelah pembilasan cache awal mereda). Jika metrik tetap dapat diterima, kurangi dengan 1 GB lain, lalu pantau lagi, ulangi seperti yang diinginkan hingga Anda menentukan konfigurasi yang ideal.

Untuk informasi selengkapnya, lihat Opsi konfigurasi memori server.

Optimalkan TempDB

Ukuran dan penempatan fisik database tempdb dapat memengaruhi performa Operations Manager. Misalnya, jika ukuran yang ditentukan untuk tempdb terlalu kecil, bagian dari beban pemrosesan sistem dapat diambil dengan tempdb pertumbuhan otomatis ke ukuran yang diperlukan untuk mendukung beban kerja setiap kali Anda menghidupkan ulang instans SQL Server. Untuk mencapai performa tempdb yang optimal, kami merekomendasikan konfigurasi berikut untuk tempdb di lingkungan produksi:

• Atur model pemulihan tempdb ke SIMPLE. Model ini secara otomatis mengklaim kembali ruang log untuk menjaga persyaratan ruang tetap kecil.
• Pra-alokasikan ruang untuk semua file tempdb dengan mengatur ukuran file ke nilai yang cukup besar untuk mengakomodasi beban kerja umum di lingkungan. Ini mencegah tempdb berkembang terlalu sering, yang dapat memengaruhi performa. Database tempdb dapat diatur ke autogrow, tetapi ini harus digunakan untuk meningkatkan ruang disk untuk pengecualian yang tidak dienkripsi.
• Buat file sebanyak yang diperlukan untuk memaksimalkan bandwidth disk. Menggunakan beberapa file mengurangi ketidakcocokan penyimpanan tempdb dan menghasilkan peningkatan skalabilitas. Namun, jangan membuat terlalu banyak file karena dapat mengurangi performa dan meningkatkan overhead manajemen. Sebagai pedoman umum, buat satu file data untuk setiap prosesor logis di server (akuntansi untuk pengaturan masker afinitas apa pun) lalu sesuaikan jumlah file ke atas atau ke bawah seperlunya. Sebagai aturan umum, jika jumlah prosesor logis kurang dari atau sama dengan 8, gunakan jumlah file data yang sama dengan prosesor logis. Jika jumlah prosesor logis lebih besar dari 8, gunakan delapan file data dan kemudian jika pertikaian berlanjut, tingkatkan jumlah file data dengan kelipatan 4 (hingga jumlah prosesor logis) hingga ketidakcocokan dikurangi ke tingkat yang dapat diterima atau buat perubahan pada beban kerja/kode. Jika ketidakcocokan tidak berkurang, Anda mungkin harus meningkatkan jumlah file data lebih banyak.
• Membuat setiap file data berukuran sama, memungkinkan performa pengisian proporsional yang optimal. Ukuran file data yang sama sangat penting karena algoritma pengisian proporsional didasarkan pada ukuran file. Jika file data dibuat dengan ukuran yang tidak sama, algoritma pengisian proporsional mencoba menggunakan file terbesar lebih banyak untuk alokasi GAM alih-alih menyebarkan alokasi di antara semua file, sehingga mengalahkan tujuan membuat beberapa file data.
• Letakkan database tempdb pada subsistem I/O cepat menggunakan drive solid-state untuk performa yang paling optimal. Gunakan striping disk jika ada banyak disk yang terpasang langsung.
• Letakkan database tempdb pada disk yang berbeda dari yang digunakan oleh database pengguna.

Untuk mengonfigurasi tempdb, Anda dapat menjalankan kueri berikut atau mengubah propertinya di Management Studio.

USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 8)
GO
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', NEWNAME = N'tempdb', SIZE = 2097152KB , FILEGROWTH = 512MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdb2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb2.mdf' , SIZE = 2097152KB , FILEGROWTH = 512MB )
GO

Jalankan kueri SELECT * from sys.sysprocesses T-SQL untuk mendeteksi ketidakcocokan alokasi halaman untuk database tempdb. Dalam output tabel sistem, sumber daya tunggu dapat muncul sebagai "2:1:1" (Halaman PFS) atau "2:1:3" (Halaman Peta Alokasi Global Bersama). Tergantung pada tingkat ketidakcocokan, ini juga dapat menyebabkan SQL Server tampak tidak responsif untuk waktu yang singkat. Pendekatan lain adalah memeriksa Tampilan Manajemen Dinamis [sys.dm_exec_request atau sys.dm_os_waiting_tasks]. Hasilnya akan menunjukkan bahwa permintaan atau tugas ini menunggu sumber daya tempdb dan memiliki nilai yang sama seperti yang disorot sebelumnya saat Anda menjalankan kueri sys.sysprocesses .

Jika rekomendasi sebelumnya tidak secara signifikan mengurangi ketidakcocokan alokasi dan ketidakcocokan ada di halaman SGAM, terapkan bendera pelacakan -T1118 dalam parameter Startup untuk SQL Server sehingga bendera pelacakan tetap berlaku bahkan setelah SQL Server didaur ulang. Di bawah bendera pelacakan ini, SQL Server mengalokasikan jangkauan penuh untuk setiap objek database, sehingga menghilangkan ketidakcocokan pada halaman SGAM.

Catatan

Bendera pelacakan ini memengaruhi setiap database pada instans SQL Server.

Tingkat paralelisme maksimum

Konfigurasi default SQL Server untuk penyebaran ukuran kecil hingga menengah Manajer Operasi memadai untuk sebagian besar kebutuhan. Namun, ketika beban kerja grup manajemen menskalakan ke atas menuju skenario kelas perusahaan (biasanya 2.000+ sistem yang dikelola agen dan konfigurasi pemantauan tingkat lanjut, yang mencakup pemantauan tingkat layanan dengan transaksi sintetis tingkat lanjut, pemantauan perangkat jaringan, lintas platform, dan sebagainya) perlu untuk mengoptimalkan konfigurasi SQL Server yang dijelaskan di bagian dokumen ini. Salah satu opsi konfigurasi yang belum dibahas dalam panduan sebelumnya adalah MAXDOP.

Opsi konfigurasi Tingkat paralelisme maksimum (MAXDOP) Microsoft SQL Server mengontrol jumlah prosesor yang digunakan untuk eksekusi kueri dalam rencana paralel. Opsi ini menentukan sumber daya komputasi dan utas yang digunakan untuk operator rencana kueri yang melakukan pekerjaan secara paralel. Bergantung pada apakah SQL Server disiapkan pada komputer multiproses simetris (SMP), komputer akses memori non-seragam (NUMA), atau prosesor yang mendukung hyperthreading, Anda harus mengonfigurasi tingkat maksimum opsi paralelisme dengan tepat.

Ketika SQL Server berjalan di komputer dengan lebih dari satu mikroprosesor atau CPU, ia mendeteksi tingkat paralelisme terbaik, yaitu, jumlah prosesor yang digunakan untuk menjalankan satu pernyataan, untuk setiap eksekusi rencana paralel. Secara default, nilainya untuk opsi ini adalah 0, yang memungkinkan SQL Server menentukan tingkat paralelisme maksimum.

Prosedur dan kueri tersimpan yang telah ditentukan sebelumnya di Operations Manager karena berkaitan dengan operasional, gudang data, dan bahkan database audit tidak menyertakan opsi MAXDOP, karena tidak ada cara selama penginstalan untuk secara dinamis mengkueri berapa banyak prosesor yang disajikan ke sistem operasi, juga tidak mencoba untuk mengodekan nilai untuk pengaturan ini, yang dapat memiliki konsekuensi negatif ketika kueri dijalankan.

Catatan

Opsi konfigurasi paralelisme tingkat maksimum tidak membatasi jumlah prosesor yang digunakan SQL Server. Untuk mengonfigurasi jumlah prosesor yang SQL Server gunakan, gunakan opsi konfigurasi masker afinitas.

• Untuk server yang menggunakan lebih dari delapan prosesor, gunakan konfigurasi berikut: MAXDOP=8
• Untuk server yang menggunakan delapan atau lebih sedikit prosesor, gunakan konfigurasi berikut: MAXDOP=0 hingga N

  Catatan

  Dalam konfigurasi ini, N mewakili jumlah prosesor.