SQL Server Hosting-JaringanHosting :: Mengelola SQL Server Services dengan PowerShell

Pendahuluan

PowerShell menyediakan shell command-line dan scripting language (dibangun pada NET Framework) didesain khusus untuk administration task automation dan configuration management. PowerShell termasuk perintah (disebut cmdlets) untuk mengelola SQL Server bersamaan dengan memiliki cmdlets untuk mengelola produk server Microsoft lainnya. Hal ini memberikan administrator bahasa scripting umum pada platform server.

SQL Server Hosting Indonesia - JaringanHosting.com

PowerShell menyediakan lebih banyak kemampuan dari sekedar pemberian SQL Server, tidak mungkin untuk menjelaskan semuanya dalam satu artikel. Artikel ini akan berbicara tentang bagaimana caranya, dengan bantuan PowerShell, Anda dapat mengelola Windows service yang berhubungan dengan SQL Server baik pada mesin lokal atau mesin remote.

 Mengidentifikasi SQL Server Service

Get-Service PowerShell cmdlet yang mengambil daftar layanan Windows baik dari mesin lokal atau dari mesin remote.

Pastikan  PowerShell command prompt atau tools PowerShell ISE dijalankan dengan izin Administrator untuk melaksanakan cmdlet ini untuk mengakses Windows service dan mengelolanya. Juga Anda harus menjadi administrator pada mesin target untuk mengelola layanan Windows.

 

Anda dapat menerapkan filter dengan Get-Service cmdlets untuk mengambil hanya layanan yang mengandung beberapa kata-kata tertentu dalam namanya. Misalnya, script di bawah ini menghasilkan daftar layanan Windows dari mesin lokal, yang mengandung “SQL” dalam namanya (semua layanan yang terkait dengan SQL Server).

 

Memulai dan Menghentikan SQL Server Service

 

Anda dapat menggunakan Start-Service PowerShell cmdlets untuk memulai Windows service pada komputer lokal atau remote. Dengan script ini Anda dapat mulai layanan SQL Server terkait.

 

Demikian juga, Anda dapat menggunakan Stop-service PowerShell cmdlets untuk menghentikan layanan Windows yang berjalan pada komputer lokal atau remote.

 

Stop-Service cmdlets mungkin gagal jika Anda mencoba untuk menghentikan layanan yang bergantung pada layanan lain. Sebagai contoh,  layananSQL Server agent tergantung pada layanan SQL Server mesin database dan karenanya jika Anda mencoba menghentikan layanan SQL Server database engine tanpa terlebih dahulu berhenti perintah layanan SQL Server Agent akan gagal. Dalam hal ini, baik Anda harus terlebih dahulu menghentikan layanan SQL Server agen kemudian menghentikan layanan SQL Server mesin database atau yang lain menggunakan parameter force tegas untuk menghentikan semua layanan yang saling bergantung.

 

 

Harap dicatat, berdasarkan jenis instalasi SQL Server (default atau bernama) nama layanan mungkin berbeda dan karenanya Anda perlu memberikan nama layanan yang tepat ketika memulai atau menghentikan nama layanan. Misalnya, dengan instalasi default nama layanan untuk mesin database SQL Server adalah MSSQLSERVER tetapi misalkan Anda memiliki contoh bernama disebut Contoso nama layanan akan menjadi MSSQL Contoso..

 

Mengelola SQL Server Services Remote Machine

Sebagai administrator Anda mungkin bertanggung jawab untuk menjaga beberapa mesin SQL Server. Sekarang perhatikan sebuah skenario di mana Anda diminta untuk mengubah account pengguna domain untuk SQL Server layanan terkait pada semua mesin ini atau jika Anda ingin memperbarui domain akun atau password untuk semua layanan ini (pada lokal atau pada remote ). Apakah Anda benar-benar perlu log on ke setiap mesin dan melakukan perubahan? Tidak persis, dengan bantuan PowerShell scripting Anda tidak hanya dapat memulai dan menghentikan layanan tetapi juga dapat mengubah properti lain seperti pengguna akun log-on atau password, ketik startup, dll

 

Dengan script di bawah ini saya terlebih dahulu menciptakan sebuah sesi remote dengan mesin MySQLBox dan kemudian mengeksekusi perintah untuk mengubah log-on nama account pengguna dan password.

Setelah mengubah nama account pengguna log-on dan password Anda harus berhenti dan memulai atau restart layanan agar untuk dapat melihat perubahannya

 

Kesimpulan

PowerShell menyediakan shell baris perintah dan scripting language (dibangun pada NET Framework) didesain khusus untuk otomatisasi tugas administrasi dan manajemen konfigurasi. Pada artikel ini, saya menunjukkan bagaimana, dengan bantuan PowerShell, Anda dapat mengelola layanan Windows yang berhubungan dengan SQL Server baik pada mesin lokal atau mesin remote.

 

SQL 2012 Hosting Indonesia –Jaringan Hosting.Com :: Cara Membuat Cron Job untuk backup SQL Database

Pada postingan kali ini kami akan membahas mengenai cara membuat Cron Job untuk keperluan backup SQL Database. Cron jobs digunakan sebagai scheduling task untuk tugas tugas yang berjalan di dalam server. Biasanya cron jobs digunakan untuk otomatisasi pemeliharaan system dan administrasi. Akan tetapi cron jobs juga relevan jika digunakan untuk keperluan pengembangan aplikasi web. Ada beberapa situasi ketika aplikasi web membutuhkan berbagai task untuk berkerja secara periodik. Cron job/scheduled task merupakan tugas otomatis dalam system yang bisa di atur untuk menjalankan sebanyak apapun pada jarak tertentu. Cron/scheduled task memungkinkan anda untuk menjadwalkan perintah ataupun script untuk berjalan pada waktu tertentu di suatu hari , ataupun di hari tertentu pada suatu minggu, ataupun pada waktu tertentu dalam hari dan bulan yang lebih spesifik. Cron job juga memungkinkan untuk mengatur lebih rinci sampai ke menit ataupun lebih jauh ke acara tahunan.

SQL Server 2012 Hosting Indonesia - Jaringan Hosting

Berikut ini langkah sederhana bagaimana membangun Cron Job untuk backup SQL Database

  • Step 1 buat folder penyimpanan file backup di dalam FTP application

Buka ftp application dan connect ke account yang databasenya ingin anda back up. Buat folder diluar dari webcontent misalnya “backups”.

  • Step 2 Atur folder permissions dalam FTP Application

Klik kanan folder dan tambahkan semua write permission. Jika ftp software anda tidak mampu melakukan hal ini cobalah gunakan FTP client free seperti FileZilla.

  • Step 3 Buat sebuh prosedur penyimpanan yang melakukan backup dengan nama file sebagai input parameternya

Connect ke database menggunakan client dan jalankan query berikut. Prosedur ini dinamakan FullBackup sebagai contoh

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[FullBackup] @FileName  nvarchar(256)
AS
BEGIN

SET NOCOUNT ON;

BACKUP DATABASE [123456_YourDatabase] TO  DISK = @FileName WITH NOFORMAT, NOINIT,  NAME = N’Full Database Backup’, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

END

  • Step 4 buat halaman web yang mengeksekusi prosedur penyimpanan

Anda dapat menggunakan php ataupun asp.net. untuk yang sederhana seperti ini anda dapat menggunakan classic asp. Pada hal ini tidakl ada file dll yang ditangani dan juga tidak dibutuhkan restart aplikasi. Sekarang buatlah halaman asp baru backupdb.asp. isi dari filenya akan mengikuti, jika telah selesai, upload file ini ke folder di dalam content area.

Script in akan meng-generate filename berdasarkan data yang ada. Jika backup pada hari tersebut sudah ada, maka prosedur tersebut akan manambah hitungan sampai filename terbaru ditemukan.

Script ini akan meng-generatte 1 file tiap eksekusi. modifikasi sesuai kebutuhan jika anda ingin menambah sebuah file. edit lokasi dan connection string agar dapat bekerja.

<%@LANGUAGE=”VBSCRIPT” CODEPAGE=”65001″%>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=”http://www.w3.org/1999/xhtml”>
<head>
<meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″ />
<title>Untitled Document</title>
</head>

<body>

<%
dim thismonth, thisday, thisyear, location, filelename, ver, extention, abolutespath

thismonth= datepart(“m”, now())
thisday=datepart(“d”, now())
thisyear=datepart(“yyyy”,now())

location=”fs1-n03stor1wc1dfw8382492382489www.yoursite.combackups”
filename=”dbBackup-” & thismonth & “-” & thisday & “-” & thisyear & “_”
ver=1
extention=”.bak”

absolutepath=location & filename & ver & extention

set fso = Server.CreateObject(“Scripting.FileSystemObject”)

while (fso.FileExists(absolutepath)=True)
ver=ver+1
absolutepath=location & filename & ver & extention
wend

Set cn = Server.CreateObject(“ADODB.Connection”)
cn.connectionString= “Provider=SQLNCLI;Server=mssql05-01.wc1;Database=123456_YourDatabase;Uid=123456_YourUsername; Pwd=Yourpassword;”
cn.open

Set cmd = Server.CreateObject(“ADODB.Command”)
Set cmd.ActiveConnection = cn
cmd.CommandText = “FullBackup”
cmd.CommandType = 4 ‘adCmdStoredProc

cmd.Parameters.Refresh
cmd.Parameters(1) = absolutepath

cmd.Execute

cn.close

%>

Execution complete:  Filename=<%= filename & ver & extention%>

</body>
</html>

  • Step 5. Jadwalkan cron job untuk memanggil halaman web

Akses control panel dan masuk ke tab fitur di dalam situs, dengan database. Pilih http sebagai Bahasa. Masukan url kedalam asp script dan email anda, dan jadwalkan tugas untuk berjalan setiap hari pada off hour.

 

SQL Server 2008 Hosting :: Parameter Sniffing Resolution

Pada artikel saya kali ini saya akan berbicara tentang cara-cara untuk menyelesaikan Parameter Sniffing pada SQL Server 2008. Metode untuk menyelesaikan masalah Parameter Sniffing tercantum di bawah ini:

  1. Menggunakan local variable dengan prosedur.
  2. Menggunakan recompile option saat create prosedur.
  3. Menggunakan Query OPTIME FOR.

Mari kita telusuri satu per satu.

Menggunakan Local Variable dengan Prosedur.

DROP TABLE fruit
GO
CREATE TABLE fruit (Sno int identity,frtname varchar(20),frttype int)
GO
– Insert dummy data
INSERT INTO fruit Values(‘Apple’,rand()*1000)
GO 10000
INSERT INTO fruit Values(‘Mango’,rand()*1000)
GO 10
CREATE unique clustered index ix_sno on fruit(sno)
GO
CREATE INDEX ix_frtname on fruit(frtname) include(frttype)
GO

Script di atas adalah script yang mengisi TABLE FRUIT dengan nilai-nilai(value).

CREATE Proc usp_fetchfruits
@frtname varchar(20)
AS
BEGIN
SELECT * FROM fruit WHERE [email protected]
END
GO
– procedure with local variables
CREATE Proc usp_fetchfruits_localvar
@frtname varchar(20)
AS
BEGIN
DECLARE @fruitname varchar(20)
SET @[email protected]
SELECT * FROM fruit WHERE [email protected]
END
GO

Query di atas mengcreate dua prosedur usp_fetchfruits dan usp_fetchfruits_localvar. Prosedur usp_fetchfruits_localvar menggunakan local variable.. Sekarang Mari kita bandingkan rencana pelaksanaan prosedur di atas.

1Snapshot diatas membandingkan rencana eksekusi dari dua prosedur. Perbedaan jumlah optimizer estimasi dan actual number pada baris kedua karena optimizer tidak tahu nilai parameter sebenarnya harus dilewatkan kemana, sehingga optimizer menciptakan rencana generik berdasarkan statistik yang tersedia. Table fruit memiliki total 10010 baris dan jumlah optimizer estimasi baris adalah 5005 yang persis setengah dari jumlah total.

Metode ini tidak menyelesaikan masalah performa tapi mungkin tidak begitu efektif jika jumlah baris sebenarnya adalah cara yang lebih besar daripada rata-rata baris yang diperkirakan.

Menggunakan Recompile Option saat membuat prosedur.
masalah Parameter Sniffing terjadi karena rencana cache dari eksekusi pertama digunakan dalam future procedure execution(eksekusi prosedur yang akan dating). Jika prosedur ini sedang disusun dan rencana disiapkan lagi untuk setiap eksekusi dari Parameter Sniffing maka akan diselesaikan. Inilah yang disebut dengan Recompile ketika digunakan dalam definisi prosedur. Recompile prosedur ini untuk membuat rencana baru setiap kali prosedur dijalankan.

CREATE Proc usp_fetchfruits_withrecompile
@frtname varchar(20)
WITH RECOMPILE
AS
BEGIN
SELECT * FROM fruit WHERE [email protected]
END

2Snapshot diatas, baik dalam eksekusi rencana optimizer yang estimasi maupun catatan actual number yang sama karena rencana yang telah di recompile untuk setiap nilai parameter bukan rencana cache yang digunakan pada eksekusi kedua.

Masalah dengan metode ini adalah bahwa resource digunakan untuk recompile prosedur setiap kalinya. Rencana ini bahkan merecompile untuk nilai-nilai parameter yang mungkin mendapat manfaat dari rencana cache.

Query OPTIMIZE FOR
Dengan menggunakan query OPTIMIZE FOR kita dapat memiliki sebuah query SQL Server optimize berdasarkan nilai parameter tertentu.

CREATE Proc usp_fetchfruits_optimizefor
@frtname varchar(20)
AS
BEGIN
SELECT * FROM fruit WHERE [email protected]
OPTION (OPTIMIZE FOR (@frtname = ‘Mango’))
END

Pada prosedur di atas, tidak memperdulikan nilai parameter yang mensupply select query yang akan dioptimalkan pada nilai “Mango”.

3Snapshot diatas membandingkan rencana eksekusi untuk nilai parameter “Apple” dan “Mango”. Dalam kedua rencana tersebut nilai-nilai estimasi tetap 10000 yang merupakan jumlah total untuk parameter “Apple”.

SQL Server 2008 memperluas query OPTIMIZE FOR sehingga dapat mengoptimalkan query untuk nilai yang tidak diketahui. Ini agak mirip dengan metode menggunakan variabel lokal.

CREATE Proc usp_fetchfruits_optimizeforunknown
@frtname varchar(20)
AS
BEGIN
SELECT * FROM fruit WHERE [email protected]
OPTION (OPTIMIZE FOR UNKNOWN)
END

Prosedur di atas menggunakan query OPTIMIZE FOR UNKNOWN untuk menyingkirkan masalah parameter sniffing.

4Snapshot diatas membandingkan eksekusi untuk nilai parameter Apple dan Mango. Optimizer memperkirakan jumlah rata-rata baris yang dikembalikan seperti ketika menggunakan variabel lokal.

Cara lain adalah dengan membuat prosedur yang tersimpan ganda dan memanggil mereka didasarkan pada nilai parameter. Misalnya, Dalam hal ini kita dapat membuat 2 prosedur yang berbeda satu untuk parameter nilai mangga dan satu untuk nilai parameter Apple dan memanggil mereka berdasarkan nilai parameter yang disediakan.

Kesimpulan
Pada artikel ini saya berbicara tentang cara-cara untuk meningkatkan kinerja pada saat menghadapi masalah PARAMETER SNIFFING.

 

Truncate transaksi log (Shrink transaksi log) pada SQL 2008

Saya baru mengamati bahwa TFS transaction log kini hadir hingga 200GB pada SQL Server 2008 sementara sebenarnya data ukuran DB hanya sekitar 20GB. Hal ini dikarenakan tidak adanya pemotongan pada transaction log file.

TRUNCATE hanya dihapus di SQL 2008. Jadi sekarang ada sedikit langkah lebih untuk mengikuti pemotongan transaction log di SQL Server 2008 / R2.

Berikut adalah langkah-langkah untuk shrink transaction log untuk full recovery mode database di SQL 2008 / R2:

1. Backup Database Pada SSMS Obyek Explorer, klik kanan pada database => Tasks => [Back Up ...] menu.

image 1

Anda dapat memperkirakan ukuran DB backup dengan menggunakan sp_spaceused sebelum melanjutkan backup DB.

2. Backup Transaction Log selama dua kali
Memunculkan BackUp Database dialog yang sama  seperti # 1 di atas. Pilih Transaction Login Backup Type.

image 2

Anda mungkin memerlukan backup dari transaction log selama dua kali untuk shrink file log dengan benar.

3. Shrink Transaction Log
Dalam SSMS Obyek Explorer, klik kanan pada database => Tasks => Shrink => [File] menu.

image 3

 4. Sesuaikan ukuran Transaksi Log (Opsional)
Dalam SSMS Obyek Explorer, klik kanan pada database => [Properties] menu.
Mengubah Ukuran awal pada Log

image 4Meningkatkan transaction log mungkin berguna jika ukuran transaction log terlalu kecil. Jika Anda tahu Tingkat kenaikan transaction log, Anda dapat menyesuaikan dengan baik ukuran awal dan Autogrowth rate.