Why We Need PCTFREE instead of PCTUSED at Index ?




Basically, a table is a "heap".  We stick data WHERE EVER. an index is a structure, we have to stick data where it belongs.

When we take an index block off of the free list -- it is "empty".  We will then totally fill it up  until we have to split it.  It only goes back onto the free list when it is empty again.  We cannot  put a block on the freelist unless it is empty -- the fact that the INDEX IS A STRUCTURE, prevents us from sticking just any data on the block.  Only data that can go onto that block can go there.  
It is just "not a heap". 

Oracle Performance Tuning Best Advice #1




The first important advance in Oracle optimization technology follows from a simple mathematical observation:


You can't extrapolate detail from an aggregate.

Here's a puzzle to demonstrate my point. Imagine that I told you that a collection of 1,000 rocks contains 999 grey rocks and one special rock that's been painted bright red. The collection weighs 1,000 pounds. Now, answer the following question: "How much does the red rock weigh?" If your answer is, "I know that the red rock weighs one pound," then, whether you realize it or not, you've told a lie. You don't know that the red rock weighs one pound. With the information you've been given, you can't know. If your answer is, "I assume that the red rock weighs one pound," then you're too generous in what you're willing to assume. Such an assumption puts you at risk of forming
conclusions that are incorrect—perhaps even stunningly incorrect.

The correct answer is that the red rock can weigh virtually any amount between zero and 1,000 pounds. The only thing limiting the low end of the weight is the definition of how many atoms must be present in order for a thing to be called a rock. Once we define how small a rock can be, then we've defined the high end of our answer. It is 1,000 pounds minus the weight of 999 of the smallest possible rocks. The red rock can weigh virtually anything between zero and a thousand pounds. Answering with any more precision is wrong unless you happen to be very lucky. But being very lucky at games like this is a skill that can be neither learned nor taught, nor repeated with acceptable
reliability.

Detect Gap on Oracle Data Guard 11gR2




Gap can occur if LNS is unable to keep pace and the log buffer is recycled before the redo can be transmitted to the standby.

so how can we detect gap on our Dataguard environment?
some people use this method.

SQL> archive log list;
SQL> select  max(sequence#) from v$archived_log;
SQL> select sequence#, archived, applied from v$archived_log where applied='YES' order by 1;

Oracle Data Guard provide us with a simple view to detect a gap. we just need to query to that views.
SQL> select * v$archive_gap;

if there's no row selected, you have no gap in your oracle data guard.
don't forget to check your alert log
tail -10000f "your alert.log location"

Manually Resolving Gaps - Oracle Data Guard 11gR2








for simplicity, Gap is a range of archived redo logs that were created at a time when the standby database was not available to receive them.
in many pratices it happen because the network problems.

We have 3 method to resolving this problem. i'll dispatch it to two main methods,


  1. Manually resolving
  2. Automatic resolving : Using log switched and FAL configuration

1. start with detecting gaps in the redo logs by querying the v$archive_gap
SQL> select * from v$archive_gap;
thread#   low_sequence#   high_sequence#
-------- ----------------  ------------------
         1                     30                      34 
   the output indicates our standby database is currently missing log files from sequence 30 to
   34.

2. issue following statement on primary database to locate the archived redo log files. assuming  

    the local archive destination on primary is LOG_ARCHIVE_DEST_1
SQL> select name from v$archived_log where thread#=1 and dest_id=1 and sequence# between 30 and 34; 
name
----------------------------------
/u02/oraarchive/DB01/arch_t1_s30.dbf  
/u02/oraarchive/DB01/arch_t1_s31.dbf  
/u02/oraarchive/DB01/arch_t1_s32.dbf  
/u02/oraarchive/DB01/arch_t1_s33.dbf  
/u02/oraarchive/DB01/arch_t1_s34.dbf 
3. stop the automatic recovery (MRP) of the data guard
SQL> alter database recover managed standby database cancel;
4. transfer manually the archived log files shown on the step 2 to standby database
5. register that archived log files on standby database
SQL> alter database register logfile '/u02/oraarchive/DB01/arch_t1_s30.dbf'; 
SQL> alter database register logfile '/u02/oraarchive/DB01/arch_t1_s31.dbf'; 
SQL> alter database register logfile '/u02/oraarchive/DB01/arch_t1_s32.dbf'; 
SQL> alter database register logfile '/u02/oraarchive/DB01/arch_t1_s33.dbf'; 
SQL> alter database register logfile '/u02/oraarchive/DB01/arch_t1_s34.dbf'; 
6. put the standby database into automatic recovery managed mode
 SQL> alter database recover managed standby database disconnect from session;
7. verify that the gap gets resolved on standby database
SQL> select sequence#, applied from v$archived_log order by sequence#; 

now you figure it out, that your archived log files (ARCLs) has been synchronize with your primary database. as a DBA you must take a notice at log using this SQL statement
SQL> select message from v$dataguard_status where severity like 'Warning';
also on your alert.log


hope it will make your day easier.


Best Regards,
Dev Yudh



Arsitektur Database Oracle (2)

Background Proses


Untuk memaksimalkan kinerja dan untuk mengakomodasi kepentingan banyak user, sistem Multiproses Oracle menggunakan proses yang disebut background process, proses-proses yang ada disini berfungsi sebagai penjembatan antara Instance (SGA & PGA) dengan database (datafiles, control files, redo log files). Sistem oracle dapat mempunyai banyak background process, tergantung dari konfigurasinya. Background process tersebut adalah sebagai berikut:

  • Database Writer (DBWR)
  • Log Writer (LGWR)
  • Checkpoint (CKPT)
  • System Monitor (SMON)
  • Process Monitor (PMON)
  • Archiver (ARCH)
  • Recoverer (RECO)
  • Lock (LCKn)
  • Snapshot Refresh (SNPn)
  • Shared Server (Snnn)
  • Dispatcher (Dnnn)
  • Parallel Query (Pnnn)
Proses PMON dan SMON berfungsi untuk me-reclaim sumber daya database yang tidak dibutuhkan lagi oleh seorang user.

PMON (Process Monitor)
  • me-Clean up hubungan yang diputuskan secara tidak normal
  • me-Rollback transaksi yang belum di commit
  • me-Release locking yang dipegang oleh suatu proses yang terminated
  • Membebaskan sumber daya SGA (Memori SGA) yang dialokasikan pada proses yang gagal
  • Me-restart proses server shared yang gagal dan men-dispatch proses-prosesnya.
SMON (System Monitor)
  • Melakukan recovery instance secara otomatis.
  • me-Reclaim space yang digunakan oleh Temporary segment yang tidak digunakan lagi
  • Melakukan merge free space area pada datafile.
Empat Mandatory Process
  • PMON, SMON, DBWR dan LGWR adalah proses mandatory yang harus dijalankan pada suatu instance Oracle. sementara proses lain nya optional
  • PMON, SMON, DBWR dan LGWR tidak bisa dikendalikan dengan mengubah parameter inisialisasi
Jika ada salah satu dari empat proses yang gagal, instance akan crash dan harus direstart.







Proses RECO, LCKn, Pnnn dan SNPn
RECO, LCKn, Pnnn, dan SNPn akan dicreate ketika menggunakan salah satu Add On Option.


Proses

  • Reco me-resolce kegagalan yang terlibat dalam suatu transaksi terdistribusi
  • LCKn melakukan interinstance locking pada suatu sistem server paralel
  • Pnnn menyediakan query paralel, pembuatan index paralel, loading data paralel, dan kemampuan CREATE TABLE AS SELECT paralel.
  • SNPn melakukan refresh secara otomatis dari snapshots (table replikasi read-only). Proses tersebut juga akan bertanggung jawab pada antrian pekerjaan server dan antrian replikasi.
Options
  • Paralel Query Option adalah suatu licensed product yang terpisah
  • Procedural Option dibutuhkan untuk snapshots. hal itu termasuk dalam Oracle7 Server
  • Distributed Option dibutuhkan untuk transaksi terdistribusi. Option ini adalah licensed product yang terpisah.
  • repilcation Option adalah suatu licensed product yang terpisah.



Proses User




Suatu proses user digunakan ketika seorang user menjalankan suatu program aplikasi. proses User:

  • Menjalankan tool / application atas pertimbangan client. contohnya Server Manager, Oracle Forms dan Pro*C.
  • mem-pass SQL ke proses server untuk mendapatkan hasil..



Proses Server


sebelum data dapat diakses, sebuah proses server harus menempatkan data terlebih dahulu ke Database Buffer Cache. Untuk memproses perintah SQL, proses server akan menggunakan shared memory dalam SGA.


Task Proses Server

  • Melakukan parsing dan mengeksekusi perintah SQL
  • membaca blok data dari disk ke buffer database (Database buffer cache) shared di SGA
  • mengembalikan hasil dari perintah SQL ke proses USer.





























asa

















Arsitektur Database Oracle (1)

Arsitektur Database Oracle terdiri dari 2 bagian:

  • Instance Database
  • Database
Instance database adalah segala hal yang berhubungan dengan Memory.

Sedangkan Database adalah bentuk Physical dari database oracle, dimana terdapat 3 bagian, yakni: Datafiles, Controlfiles dan Redo logfiles.


Instance Database Structure
Instance database terdiri dari blok besar SGA (Shared/System Global Area) dan PGA (Private Global Area).

Blok Database Instance.
from docs.oracle.com

Di dalam SGA Terdapat : 
  • Mandatory: Shared Pool, Database Buffer Cache, Redo Log Buffer, Streams Pool 
  • Optional : Large Pool dan Java Pool
Jadi, SGA adalah kumpulan (group) dari struktur shared memory yang memuat data dan informasi pengendali untuk suatu Sistem Basisdata Oracle. SGA harus selalu berada di dalam memori yang non-paged atau non-swapped. jika banyak user yang terhubung bersamaan, maka data pada SGA akan di shared diantara user-user tersebut. Ini mengapa SGA banyak diartikan sebagai (Shared Global Area).

Shared Pool

Shared Pool adalah suatu bagian dari SGA yang memuat konstruksi shared SQL Area dan Data Dictionary Cache.

** Shared SQL Area: adalah bagian dari SGA yang berisi segala macam sintaks SQL yang pernah dilakukan atau di eksekusi oleh USER. Setiap shared SQL area mengandung informasi yang digunakan untuk menjalankan suatu perintah SQL tunggal. proses-proses yang berjalan akan mengeksekusi perintah-perintah SQL identik yang informasinya di share. suatu perintah SQL ditempatkan pada suatu shared SQL Area berdasarkan pada suatu algoritma hashing yang diterapkan pada perintah tersebut. oleh karena itu, hanya perintah-perintah yang sama seluruhnya yang akan dialokasikan ke lokasi memori yang sama. bisa juga disebut Library Cache

** Private SQL Area: adalah bagian dari PGA yang berisi segala sintaks Private SQLS (Explicit cursor SQLs) yang dilakukan di tiap session.

beda dari keduanya adalah, Shared SQL Area dilakukan pada arsitektur multi-threaded dimana yang menangani proses session adalah SGA, sedangkan untuk Private SQL Area adalah bila sebuah session dijalankan pada sebuah arsitektur Dedicated (diambil alih oleh PGA = Private Global Area)

** Data Dictionary Cache
Data Dictionary merupakan suatu kumpulan tabel dan view basisdata yang mengandung referensi informasi tentang basis data, struktur, dan user. data yang disimpan dalam data dictionary adalah sebagai berikut:
  • Nama dari semua tabel dan views dalam basis data
  • Nama dan tipe data dari kolom dalam tabel basis data
  • Privileges dari semua user Oracle
Content Shared Pool
  • Teks perintah SQL atau PL/SQL
  • Hasil parsing dari perintah SQL atau PL/SQL
  • Rencana eksekusi dari perintah SQL atau PL/SQL
  • Data Dictionary cache yang mengandung baris dari informasi data dictionary


Jadi, bila seorang user login ke database atau aplikasi yang terhubung ke database, dan melakukan eksekusi. maka SQL sintaks tersebut pertama kali akan diakses oleh Shared Pool ini. Meskipun sebelumnya, pada saat dia eksekusi SQL sintaks akan di cek dulu user tersebut login dari arsitektur multi-threaded (ditangani langsung SGA) atau dedicated (ditangani oleh PGA).

Database Buffer Cache

Database buffer cache adalah bagian dari SGA yang berfungsi untuk menyimpan copy dari blog data yang dibaca dari disk. semua user yang terhubung ke sistem memperoleh sharing untuk mengakses database buffer cache.

Pengaksesan Data
  • Cache Miss : Terjadi pada saat pertama kali seorang User mengakses suatu bagian data, proses harus meng-copy data dari disk ke cache sebelum mengakses nya.
  • Cache Hit : Terjadi ketika sebuah proses mengakses bagian data yang sudah ada di cache, proses tersebut dapat membaca data dari memori secara langsung.
sebuah pengaksesan data melalui cache hit akan berlangsung lebih cepat daripada pengaksesan data melalui suatu cache miss. karena cache memiliki keterbatasan ruang, maka semua data pada disk tidak dapat dimasukkan dalam cache. Ketika cache penug, subsekuen cache misalnya akan memberikan intruksi untuk menuliskan data yang sudah ada di cache ke disk untuk membuat tempat bagi data baru. dan akan menyebabkan suatu cache miss (karena data baru di tulis ke disk tuk pertama kali).

Database Buffer Cache akan dikunjungi jika seorang user melakukan query SQL setelah melewati Shared Pool.

Redo Log Buffer

Redo Log Buffer adalah suatu circular buffer yang berisi informasi tentang perubahan-perubahan yang terjadi di database, tugas Redo Log Buffer adalah:
  • Mencatat semua perubahan yang terjadi pada basis data dalam redo log buffer
  • merekonstruksi perubahanyang terjadi pada entry-entry basis data dan rollback segment pada saat terjadi proses recovery
  • dapat di bypass menggunakan kata kunci UNRECOVERABLE pada perintah CREATE TABLE dan CREATE INDEX
  • dapat di bypass oleh ORACLE data Loader











Keutamaan Puasa Tasu’a Dan ‘Asyura [9 & 10 Muharram]


Bismillah…
Hari ‘Asyura di depan mata. Siapkan ilmu untuk mengahadapinya. Jangan seperti Syiah Rafidhah atau Sufiyah! Jadilah Anda seorang muslim pejuang sunnah an-nabawiyah. Selamat menyimak, semoga Allah mudahkan kita tuk mengamalkannya…
Oleh: Asy Syaikh Muhammad bin Shalih Al Utsaimin

[Di dalam kitab beliau Riyadhus Shalihin, Al-Imam An-Nawawi -rahimahullah- membawakan tiga buah hadits yang berkenaan dengan puasa sunnah pada bulan Muharram, yaitu puasa hari Asyura / Asyuro (10 Muharram) dan Tasu’a (9 Muharram)]
Hadits yang Pertama
عن ابن عباس رَضِيَ اللَّهُ عَنهُ أن رَسُول اللَّهِ صَلَّى اللَّهُ عَلَيهِ وَسَلَّم صام يوم عاشوراء وأمر بصيامه. مُتَّفّقٌ عَلَيهِ
Dari Ibnu Abbas -radhiyallahu ‘anhuma-, “Bahwa Rasulullah shallallahu ‘alaihi wasallam berpuasa pada hari ‘Asyura dan memerintahkan untuk berpuasa padanya”. (Muttafaqun ‘Alaihi).
Hadits yang Kedua
عن أبي قتادة رَضِيَ اللَّهُ عَنهُ أن رَسُول اللَّهِ صَلَّى اللَّهُ عَلَيهِ وَسَلَّم سئل عن صيام يوم عاشوراء فقال: ((يكفر السنة الماضية)) رَوَاهُ مُسلِمٌ.
Dari Abu Qatadah -radhiyallahu ‘anhu-, bahwa Rasulullah shallallahu ‘alaihi wasallam ditanya tentang puasa hari ‘Asyura. Beliau menjawab, “(Puasa tersebut) Menghapuskan dosa satu tahun yang lalu”. (HR. Muslim)
Hadits yang Ketiga
وعن ابن عباس رَضِيَ اللَّهُ عَنهُما قال، قال رَسُول اللَّهِ صَلَّى اللَّهُ عَلَيهِ وَسَلَّم: ((لئن بقيت إلى قابل لأصومن التاسع)) رَوَاهُ مُسلِمٌ.
Dari Ibnu Abbas -radhiyallahu ‘anhuma- beliau berkata: “Rasulullah shallallahu ‘alaihi wasallam bersabda, “Apabila (usia)ku sampai tahun depan, maka aku akan berpuasa pada (hari) kesembilan” (HR. Muslim)
“Rasulullah shallallahu ‘alaihi wasallam ditanya tentang puasa pada hari ‘Asyura, beliau menjawab, ‘Menghapuskan dosa setahun yang lalu’, ini pahalanya lebih sedikit daripada puasa Arafah (yakni menghapuskan dosa setahun sebelum serta sesudahnya –pent). Bersamaan dengan hal tersebut, selayaknya seorang berpuasa ‘Asyura (10 Muharram) disertai dengan (sebelumnya, ed.) Tasu’a (9 Muharram). Hal ini karena Nabi shallallahu ‘alaihi wasallam bersabda, ‘Apabila (usia)ku sampai tahun depan, maka aku akan berpuasa pada yang kesembilan’, maksudnya berpuasa pula pada hari Tasu’a.
Penjelasan
Rasulullah shallallahu ‘alaihi wasallam memerintahkan untuk berpuasa pada hari sebelum maupun setelah ‘Asyura [1] dalam rangka menyelisihi orang-orang Yahudi karena hari ‘Asyura –yaitu 10 Muharram- adalah hari di mana Allah selamatkan Musa dan kaumnya, dan menenggelamkan Fir’aun dan para pengikutnya. Dahulu orang-orang Yahudi berpuasa pada hari tersebut sebagai syukur mereka kepada Allah atas nikmat yang agung tersebut. Allah telah memenangkan tentara-tentaranya dan mengalahkan tentara-tentara syaithan, menyelamatkan Musa dan kaumnya serta membinasakan Fir’aun dan para pengikutnya. Ini merupakan nikmat yang besar.
Oleh karena itu, setelah Nabi shallallahu ‘alaihi wasallam tinggal di Madinah, beliau melihat bahwa orang-orang Yahudi berpuasa pada hari ‘Asyura [2]. Beliau pun bertanya kepada mereka tentang hal tersebut. Maka orang-orang Yahudi tersebut menjawab, “Hari ini adalah hari di mana Allah telah menyelamatkan Musa dan kaumnya, serta celakanya Fir’aun serta pengikutnya. Maka dari itu kami berpuasa sebagai rasa syukur kepada Allah”. Rasulullah shallallahu ‘alaihi wasallam berkata, “Kami lebih berhak terhadap Musa daripada kalian”.
Kenapa Rasulullah mengucapkan hal tersebut? Karena Nabi dan orang–orang yang bersama beliau adalah orang-orang yang lebih berhak terhadap para nabi yang terdahulu. Allah berfirman,
إِنَّ أَوْلَى النَّاسِ بِإِبْرَاهِيمَ لَلَّذِينَ اتَّبَعُوهُ وَهَذَا النَّبِيُّ وَالَّذِينَ آَمَنُوا وَاللَّهُ وَلِيُّ الْمُؤْمِنِينَ
“Sesungguhnya orang yang paling berhak dengan Ibrahim adalah orang-orang yang mengikutinya dan nabi ini (Muhammad), serta orang-orang yang beriman, dan Allah-lah pelindung semua orang-orang yang beriman”. (Ali Imran: 68)
Maka Rasulullah shallallahu ‘alaihi wasallam adalah orang yang paling berhak terhadap Nabi Musa daripada orang-orang Yahudi tersebut, dikarenakan mereka kafir terhadap Nabi Musa, Nabi Isa dan Muhammad. Maka beliau shallallahu ‘alaihi wasallam berpuasa ‘Asyura dan memerintahkan manusia untuk berpuasa pula pada hari tersebut. Beliau juga memerintahkan untuk menyelisihi Yahudi yang hanya berpuasa pada hari ‘Asyura, dengan berpuasa pada hari kesembilan atau hari kesebelas beriringan dengan puasa pada hari kesepuluh (’Asyura), atau ketiga-tiganya. [3]
Oleh karena itu sebagian ulama seperti Ibnul Qayyim dan yang selain beliau menyebutkan bahwa puasa ‘Asyura terbagi menjadi tiga keadaan:
1. Berpuasa pada hari ‘Asyura dan Tasu’ah (9 Muharram), ini yang paling afdhal.
2. Berpuasa pada hari ‘Asyura dan tanggal 11 Muharram, ini kurang pahalanya daripada yang pertama. [4]
3. Berpuasa pada hari ‘Asyura saja, sebagian ulama memakruhkannya karena Nabi shallallahu ‘alaihi wasallam memerintahkan untuk menyelisihi Yahudi, namun sebagian ulama yang lain memberi keringanan (tidak menganggapnya makhruh). [5]
Wallahu a’lam bish shawab.
(Sumber: Syarh Riyadhis Shalihin karya Asy-Syaikh Muhammad bin Shalih Al-Utsaimin terbitan Darus Salam – Mesir, diterjemahkan Abu Umar Urwah Al-Bankawy, muraja’ah dan catatan kaki: Al-Ustadz Abu Abdillah Muhammad Rifai)
CATATAN KAKI:
[1] Adapun hadits yang menyebutkan perintah untuk berpuasa setelahnya (11 Asyura’) adalah dha’if (lemah). Hadits tersebut berbunyi:
صوموا يوم عاشوراء و خالفوا فيه اليهود صوموا قبله يوما و بعده يوما . -
“Puasalah kalian hari ‘Asyura dan selisihilah orang-orang yahudi padanya (maka) puasalah sehari sebelumnya dan sehari setelahnya. (HR. Ahmad dan Al Baihaqy. Didhaifkan oleh As Syaikh Al-Albany di Dha’iful Jami’ hadits no. 3506)
Dan berkata As Syaikh Al Albany – Rahimahullah- di Silsilah Ad Dha’ifah Wal Maudhu’ah IX/288 No. Hadits 4297: Penyebutan sehari setelahnya (hari ke sebelas. pent) adalah mungkar, menyelisihi hadits Ibnu Abbas yang shahih dengan lafadz:
“لئن بقيت إلى قابل لأصومن التاسع” .
“Jika aku hidup sampai tahun depan tentu aku akan puasa hari kesembilan”
Lihat juga kitab Zaadul Ma’ad 2/66 cet. Muassasah Ar-Risalah Th. 1423 H. dengan tahqiq Syu’aib Al Arnauth dan Abdul Qadir Al Arna’uth.
لئن بقيت لآمرن بصيام يوم قبله أو يوم بعده . يوم عاشوراء) .-
“Kalau aku masih hidup niscaya aku perintahkan puasa sehari sebelumnya (hari Asyura) atau sehari sesudahnya” ((HR. Al Baihaqy, Berkata Al Albany di As-Silsilah Ad-Dha’ifah Wal Maudhu’ah IX/288 No. Hadits 4297: Ini adalah hadits mungkar dengan lafadz lengkap tersebut.))
[2] Padanya terdapat dalil yang menunjukkan bahwa penetapan waktu pada umat terdahulu pun menggunakan bulan-bulan qamariyyah (Muharram s/d Dzulhijjah, Pent.) bukan dengan bulan-bulan ala Eropa (Jan s/d Des). Karena Rasulullah shalallahu ‘alaihi wasallam mengabarkan bahwa hari ke sepuluh dari Muharram adalah hari di mana Allah membinasakan Fir’aun dan pengikutnya dan menyelamatkan Musa dan pengikutnya. (Syarhul Mumthi’ VI.)
[3] Untuk puasa di hari kesebelas haditsnya adalah dha’if (lihat no. 1) maka – Wallaahu a’lam – cukup puasa hari ke 9 bersama hari ke 10 (ini yang afdhal) atau ke 10 saja.
Asy-Syaikh Salim Bin Ied Al Hilaly mengatakan bahwa, “Sebagian ahlu ilmu berpendapat bahwa menyelisihi orang Yahudi terjadi dengan puasa sebelumnya atau sesudahnya. Mereka berdalil dengan hadits yang diriwayatkan dari Rasulullah Shalallahu’alaihi Wasallam,
صوموا يوم عاشوراء و خالفوا فيه اليهود صوموا قبله يوما أو بعده يوما .
“Puasalah kalian hari ‘Asyura dan selisihilah orang-orang Yahudi padanya (maka) puasalah sehari sebelumnya atau sehari setelahnya”.
Ini adalah pendapat yang lemah, karena bersandar dengan hadits yang lemah tersebut yang pada sanadnya terdapat Ibnu Abi Laila dan ia adalah jelek hafalannya.” (Bahjatun Nadhirin Syarah Riyadhus Shalihin II/385. cet. IV. Th. 1423 H Dar Ibnu Jauzi)
[4] (lihat no. 3)
[5] Asy-Syaikh Muhammad Bin Shalih Al-Utsaimin rahimahullah mengatakan,
والراجح أنه لا يكره إفراد عاشوراء.
Dan yang rajih adalah bahwa tidak dimakruhkan berpuasa ‘Asyura saja. (Syarhul Mumthi’ VI)
Wallaahu a’lam.

VB.NET: Connect to SQL Server


Here is the step-by-step procedure to connect to SQL server:
1. Create your VB.NET project.
2. Include the following namespaces.


Imports System.Data
Imports System.Data.SqlClient

The System.Data namespace provides access to classes that represent the ADO.NET architecture while the System.Data.SqlClient namespace is the.NET Framework Data Provider for SQL Server.
3. Declare and instantiate your SQLConnection object as shown below

Dim con As New SqlConnection

SQLConnection class represents an open connection to a SQL Server database.
4. Pass the SQL connection string to ConnectionString property of your SqlConnection object.

con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security Info=True;User ID=sa;Password=12345678"

The connectionstring value usually contains the following :
Data Source - physical server hostname
Initial Catalog - your database name
User ID - SQL username use to connect to the server
Password - SQL username's password

On this sample, I am using an SQL Server 2005. For the connectionstring for other SQL version, you can get it from here 

5. Last step is to invoke the Open method of the connection object

con.Open() 

The complete sample sourcecode:

Imports System.Data.SqlClient 
Imports System.Data  
Private Sub ConnectToSQL()  
Dim con As New SqlConnection  
Dim cmd As New SqlCommand   
con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist Security  Info=True;User ID=sa;Password=12345678"  
con.Open()End Sub


To capture if the connection was successful or not, just tweak the above code:


Imports System.Data.SqlClient 
Imports System.Data  
Private Sub ConnectToSQL()  
Dim con As New SqlConnection  
Dim cmd As New SqlCommand Try  
con.ConnectionString = "Data Source=atisource;Initial Catalog=BillingSys;Persist   Security Info=True;User ID=sa;Password=12345678"  
con.Open() Catch ex As Exception  MessageBox.Show("Error while connecting to SQL Server." & ex.Message) 
Finally  
con.Close() 'Whether there is error or not. Close the connection. End TryEnd Sub


Connection strings for SQL Server 2005

in my previous article, I was asked to give a tutorial on sql server and vb.net connection. in this case i use SQL Server 2005 Standard edition.

so this is all connections strings providers for SQL Server 2005
Note : I assumed that you already installed SQL Server and Visual studio also use
this setting :

Server address  
Database name  
Username  
Password 


"adevla" was my localhost username XD


.NET Framework Data Provider for SQL Server

TYPE .NET Framework Class LibraryUSAGE System.Data.SqlClient.SqlConnectionMANUFACTURER Microsoft

Standard Security

Data Source=10.252.108.103;Initial Catalog=SQLServ2005;User Id=adelva;Password=p@ssw0rd;
Use serverName\instanceName as Data Source to connect to a specific SQL Server instance.
Are you using SQL Server 2005 Express? Don't miss the server name syntax Servername\SQLEXPRESS where you substitute Servername with the name of the computer where the SQL Server 2005 Express installation resides.

Standard Security alternative syntax

This connection string produce the same result as the previous one. The reason to include it is to point out that some connection string keywords have many equivalents.
Server=10.252.108.103;Database=SQLServ2005;User ID=adelva;Password=p@ssw0rd;Trusted_Connection=False;

Trusted Connection

Data Source=10.252.108.103;Initial Catalog=SQLServ2005;Integrated Security=SSPI;

Trusted Connection alternative syntax

This connection string produce the same result as the previous one. The reason to include it is to point out that some connection string keywords have many equivalents.
Server=10.252.108.103;Database=SQLServ2005;Trusted_Connection=True;

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.
Server=myServerName\theInstanceName;Database=SQLServ2005;Trusted_Connection=True;

Trusted Connection from a CE device

Often a Windows CE device is not authenticated and logged in to a domain. To use SSPI or trusted connection / authentication from a CE device, use this connection string.
Data Source=10.252.108.103;Initial Catalog=SQLServ2005;Integrated Security=SSPI;User ID=myDomain\myUsername;Password=myPassword;
Note that this will only work on a CE device.

Connect via an IP address

Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=SQLServ2005;User ID=adelva;Password=p@ssw0rd;
DBMSSOCN=TCP/IP. This is how to use TCP/IP instead of Named Pipes. At the end of the Data Source is the port to use. 1433 is the default port for SQL Server.

Enabling MARS (multiple active result sets)

Server=10.252.108.103;Database=SQLServ2005;Trusted_Connection=True; MultipleActiveResultSets=true;
Use ADO.NET 2.0 for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1.

Attach a database file on connect to a local SQL Server Express instance

Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf;Database=dbname; Trusted_Connection=Yes;
Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;
Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Using an User Instance on a local SQL Server Express instance

The User Instance functionality creates a new SQL Server instance on the fly during connect. This works only on a local SQL Server 2005 instance and only when connecting using windows authentication over local named pipes. The purpose is to be able to create a full rights SQL Server instance to a user with limited administrative rights on the computer.
Data Source=.\SQLExpress;Integrated Security=true; AttachDbFilename=|DataDirectory|\mydb.mdf;User Instance=true;
To use the User Instance functionality you need to enable it on the SQL Server. This is done by executing the following command: sp_configure 'user instances enabled', '1'. To disable the functionality execute sp_configure 'user instances enabled', '0'.

Database mirroring

If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.
Data Source=10.252.108.103;Failover Partner=myMirrorServerAddress;Initial Catalog=SQLServ2005;Integrated Security=True;
There is ofcourse many other ways to write the connection string using database mirroring, this is just one example pointing out the failover functionality. You can combine this with the other connection strings options available.

Asynchronous processing

A connection to SQL Server 2005 that allows for the issuing of async requests through ADO.NET objects.
Server=10.252.108.103;Database=SQLServ2005;Integrated Security=True;Asynchronous Processing=True;

SQL Native Client 9.0 OLE DB provider

TYPE OLE DB ProviderUSAGE Provider=SQLNCLIMANUFACTURER Microsoft

Standard security

Provider=SQLNCLI;Server=10.252.108.103;Database=SQLServ2005;Uid=adelva;
Pwd=p@ssw0rd;
Are you using SQL Server 2005 Express? Don't miss the server name syntax Servername\SQLEXPRESS where you substitute Servername with the name of the computer where the SQL Server 2005 Express installation resides.

Trusted connection

Provider=SQLNCLI;Server=10.252.108.103;Database=SQLServ2005;
Trusted_Connection=yes;
Equivalent key-value pair: "Integrated Security=SSPI" equals "Trusted_Connection=yes"

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.
Provider=SQLNCLI;Server=myServerName\theInstanceName;Database=SQLServ2005; Trusted_Connection=yes;
  

Prompt for username and password

This one is a bit tricky. First you need to set the connection object's Prompt property to adPromptAlways. Then use the connection string to connect to the database.
oConn.Properties("Prompt") = adPromptAlways


oConn.Open "Provider=SQLNCLI;
Server=10.252.108.103;DataBase=SQLServ2005;

Enabling MARS (multiple active result sets)

Provider=SQLNCLI;Server=10.252.108.103;Database=SQLServ2005; Trusted_Connection=yes;MARS Connection=True;
Use ADO.NET 2.0 for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1.

Encrypt data sent over network

Provider=SQLNCLI;Server=10.252.108.103;Database=SQLServ2005; Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance

Provider=SQLNCLI;Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;
Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Provider=SQLNCLI;Server=.\SQLExpress;
AttachDbFilename=|DataDirectory|mydbfile.mdf;
Database=dbname;Trusted_Connection=Yes;
Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Database mirroring

If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.
Provider=SQLNCLI;Data Source=10.252.108.103;Failover Partner=myMirrorServerAddress;Initial Catalog=SQLServ2005;
Integrated Security=True;
There is ofcourse many other ways to write the connection string using database mirroring, this is just one example pointing out the failover functionality. You can combine this with the other connection strings options available.

.NET Framework Data Provider for OLE DB

TYPE .NET Framework Wrapper Class LibraryUSAGE System.Data.OleDb.OleDbConnectionMANUFACTURER Microsoft

Bridging to SQL Native Client OLE DB

This is just one connection string sample for the wrapping OleDbConnection class that calls the underlying OLEDB provider. See respective OLE DB provider for more connection strings to use with this class.
Provider=SQLNCLI;Server=10.252.108.103;Database=SQLServ2005;
Uid=adelva; Pwd=p@ssw0rd;

SQL Server Native Client 10.0 OLE DB Provider

TYPE OLE DB ProviderUSAGE Provider=SQLNCLI10MANUFACTURER Microsoft

Standard security

Provider=SQLNCLI10;Server=10.252.108.103;Database=SQLServ2005;
Uid=adelva; Pwd=p@ssw0rd;
Are you using SQL Server 2005 Express? Don't miss the server name syntax Servername\SQLEXPRESS where you substitute Servername with the name of the computer where the SQL Server 2005 Express installation resides.

Trusted connection

Provider=SQLNCLI10;Server=10.252.108.103;Database=SQLServ2005;
Trusted_Connection=yes;
Equivalent key-value pair: "Integrated Security=SSPI" equals "Trusted_Connection=yes"

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.
Provider=SQLNCLI10;Server=myServerName\theInstanceName;Database=SQLServ2005; Trusted_Connection=yes;

Prompt for username and password

This one is a bit tricky. First you need to set the connection object's Prompt property to adPromptAlways. Then use the connection string to connect to the database.
oConn.Properties("Prompt") = adPromptAlways


oConn.Open "Provider=SQLNCLI10;Server=10.252.108.103;
DataBase=SQLServ2005;

Enabling MARS (multiple active result sets)

Provider=SQLNCLI10;Server=10.252.108.103;Database=SQLServ2005; Trusted_Connection=yes;MARS Connection=True;
Use ADO.NET 2.0 for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1.

Encrypt data sent over network

Provider=SQLNCLI10;Server=10.252.108.103;Database=SQLServ2005; Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance

Provider=SQLNCLI10;Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;
Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Provider=SQLNCLI10;Server=.\SQLExpress;
AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;
Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Database mirroring

If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.
Provider=SQLNCLI10;Data Source=10.252.108.103;Failover Partner=myMirrorServerAddress;Initial Catalog=SQLServ2005;
Integrated Security=True;
There is ofcourse many other ways to write the connection string using database mirroring, this is just one example pointing out the failover functionality. You can combine this with the other connection strings options available.

SQL Native Client 9.0 ODBC Driver

TYPE ODBC DriverUSAGE Driver={SQL Native Client}MANUFACTURER Microsoft


Standard security

Driver={SQL Native Client};Server=10.252.108.103;Database=SQLServ2005; Uid=adelva;Pwd=p@ssw0rd;
Are you using SQL Server 2005 Express? Don't miss the server name syntax Servername\SQLEXPRESS where you substitute Servername with the name of the computer where the SQL Server 2005 Express installation resides.

Trusted Connection

Driver={SQL Native Client};Server=10.252.108.103;Database=SQLServ2005; Trusted_Connection=yes;
Equivalent key-value pair: "Integrated Security=SSPI" equals "Trusted_Connection=yes"

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.
Driver={SQL Native Client};Server=myServerName\theInstanceName;Database=SQLServ2005; Trusted_Connection=yes;

Prompt for username and password

This one is a bit tricky. First you need to set the connection object's Prompt property to adPromptAlways. Then use the connection string to connect to the database.
oConn.Properties("Prompt") = adPromptAlways

Driver={SQL Native Client};Server=10.252.108.103;Database=SQLServ2005;

Enabling MARS (multiple active result sets)

Driver={SQL Native Client};Server=10.252.108.103;Database=SQLServ2005; Trusted_Connection=yes;MARS_Connection=yes;
Use ADO.NET 2.0 for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1.

Encrypt data sent over network

Driver={SQL Native Client};Server=10.252.108.103;Database=SQLServ2005; Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance

Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=c:\mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;
Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Driver={SQL Native Client};Server=.\SQLExpress; AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;
Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Database mirroring

If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.
Driver={SQL Server Native Client 10.0};Server=10.252.108.103;Failover_Partner=myMirrorServerAddress;Database=SQLServ2005; Trusted_Connection=yes;
There is ofcourse many other ways to write the connection string using database mirroring, this is just one example pointing out the failover functionality. You can combine this with the other connection strings options available.
Please note if you are using TCP/IP (using the network library parameter) and database mirroring, including port number in the address (formed as servername,portnumber) for booth the main server and the failover partner can solve some reported issues.

SQL Server Native Client 10.0 ODBC Driver

TYPE ODBC DriverUSAGE Driver={SQL Server Native Client 10.0}MANUFACTURER Microsoft


Standard security

Driver={SQL Server Native Client 10.0};Server=10.252.108.103;Database=SQLServ2005;Uid=adelva;Pwd=p@ssw0rd;

Trusted Connection

Driver={SQL Server Native Client 10.0};Server=10.252.108.103;Database=SQLServ2005;Trusted_Connection=yes;
Equivalent key-value pair: "Integrated Security=SSPI" equals "Trusted_Connection=yes"

Connecting to an SQL Server instance

The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.
Driver={SQL Server Native Client 10.0};Server=myServerName\theInstanceName; Database=SQLServ2005;Trusted_Connection=yes;

Prompt for username and password

This one is a bit tricky. First you need to set the connection object's Prompt property to adPromptAlways. Then use the connection string to connect to the database.
oConn.Properties("Prompt") = adPromptAlways

Driver={SQL Server Native Client 10.0};Server=10.252.108.103;Database=SQLServ2005;

Enabling MARS (multiple active result sets)

Driver={SQL Server Native Client 10.0};Server=10.252.108.103;Database=SQLServ2005;Trusted_Connection=yes; MARS_Connection=yes;
Use ADO.NET for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1.

Encrypt data sent over network

Driver={SQL Server Native Client 10.0};Server=10.252.108.103;Database=SQLServ2005; Trusted_Connection=yes;Encrypt=yes;

Attach a database file on connect to a local SQL Server Express instance

Driver={SQL Server Native Client 10.0};Server=.\SQLExpress; AttachDbFilename=c:\asd\qwe\mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;
Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Driver={SQL Server Native Client 10.0};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;
Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Database mirroring

If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.
Driver={SQL Server Native Client 10.0};Server=10.252.108.103;Failover_Partner=myMirrorServerAddress;Database=SQLServ2005; Trusted_Connection=yes;
There is ofcourse many other ways to write the connection string using database mirroring, this is just one example pointing out the failover functionality. You can combine this with the other connection strings options available.
Please note if you are using TCP/IP (using the network library parameter) and database mirroring, including port number in the address (formed as servername,portnumber) for booth the main server and the failover partner can solve some reported issues.

.NET Framework Data Provider for ODBC

TYPE .NET Framework Wrapper Class LibraryUSAGE System.Data.Odbc.OdbcConnectionMANUFACTURER Microsoft


Bridging to SQL Native Client 10.0 ODBC Driver

This is just one connection string sample for the wrapping OdbcConnection class that calls the underlying ODBC Driver. See respective ODBC driver for more connection strings to use with this class.
Driver={SQL Server Native Client 10.0};Server=10.252.108.103;Database=SQLServ2005;Uid=adelva;Pwd=p@ssw0rd;

SQLXML 4.0 OLEDB Provider

TYPE OLE DB ProviderUSAGE Provider=SQLXMLOLEDB.4.0;Data Provider=providernameMANUFACTURER Microsoft


Using SQL Server Native Client provider

Provider=SQLXMLOLEDB.4.0;Data Provider=SQLNCLI;
Data Source=10.252.108.103;Initial Catalog=SQLServ2005;User Id=adelva;Password=p@ssw0rd;
  

Context Connection

TYPE .NET Framework Class LibraryUSAGE 
MANUFACTURER Microsoft

Context Connection

Connecting to "self" from within your CLR stored prodedure/function. The context connection lets you execute Transact-SQL statements in the same context (connection) that your code was invoked in the first place.
C#
 using(SqlConnection connection = new SqlConnection("context connection=true"))
 {
     connection.Open();
     // Use the connection
 }


VB.Net
 Using connection as new SqlConnection("context connection=true")
     connection.Open()
     ' Use the connection
 End Using