SQL Geeks Indonesia

SQL Server 2012 Community Lunch Jakarta

2012-07-05T08:26:00Z

SQL Server 2012, versi terbaru dari SQL Server, telah hadir. Tentunya rekan-rekan community ingin tahu lebih banyak mengenai fasilitas-fasilitas terbaru yang diberikan, seperti bagaimana SQL Server 2012 dapat membantu penerapan Hybrid IT, sebuah kombinasi Cloud Computing dan On Premise Computing. SQL Server 2012 juga membuka peluang aplikasi-aplikasi yang dibangun menggunakan teknologi non-Microsoft untuk menggunakan fasilitas-fasilitas canggih ini, misalnya bagi para pengembang aplikasi berbasis PHP.

SQL Server 2012 Community Launch ini ditujukan untuk rekan-rekan Developer dan Profesional TI yang tertarik untuk mengetahui lebih rinci mengenai fasilitas-fasilitas ini. Manfaatkan juga kesempatan untuk bertemu dengan para pembicara untuk berdiskusi secara langsung.

Lokasi:
Bursa Efek Indonesia, Tower II, Lt. 18,
PT. Microsoft Indonesia,
Microsoft Auditorium.

Waktu:
Sabtu, 7 July 2012, 08.30 - 16.00

Audience:
Developer and IT Pro

Profil Pembicara Utama

Chris Randall
Director of Training, SolidQ, North America.

Chris Randall adalah Director untuk training di SolidQ, Amerika Utara. Chris dikenal melalui kompetensinya sebagai public speaking di berbagai teknologi konferensi di level global, spesialis untuk training, courseware workbooks, T-SQL, SQL Server Integration Services, SSAS, dan SSRS. Chris adalah mentor SolidQ yang telah familiar dengan SQL Server sejak tahun 1996. Aktif sebagai trainer, konsultan dan juga sebagai pembicara. Chris telah banyak berkontribusi terhadap konten training yang berhubungan dengan T-SQL, SSIS dan Arsitektur Business Intelligence. Chris sudah banyak mengajar ribuan peserta training dalam kelas dan konferensi kelas dunia, termasuk diantaranya SQL PASS, SQL Saturday dan berbagai konferensi untuk Microsoft Certified Trainers (MCT). Chris memiliki MCT dan MCITP sertifikasi untuk SQL Server 2008 portofolio. Chris juga sebelumnya adalah board member Denver SQL Server User Group, chapter PASS. Selain tugasnya mengajar dan sebagai konsultan, Chris sangat bersemangat untuk hadir di Indonesia dan menyampaikan berbagai peluang yang muncul dengan kehadiran SQL Server 2012.

Ikuti Chris di twitter : @cfrandall dan blog http://cfrandall.com/

Berikut adalah topik-topik yang akan dibahas pada SQL Server 2012 Community Launch ini :

Agenda
	Main Session
09.00 - 09.15	Opening by Manish Chopra M&O Director, Mirosoft Indonesia
09.15 - 10.00	Case Study: Implementing SQL Server 2012 Business Intelligence
10.00 - 10.45	Note from The Field: PHP Development with SQL Server Bastian Harahap CTO Urbanesia
10.45 - 12.15	Embracing Big Data with SQL Server 2012 Chris Randall Director of Training of SolidQ - North America
12.15 - 13.00	Lunch

Breakout Session
	IT Pro	Developer and BI
13.00 - 14.00	Best Practices Running SQL Server 2012 on Hyper-V Hotma Roland Pasaribu, MCT Mirosoft User Group Indonesia	Build Modern Windows 8 Metro Apps and SQL Azure Rudy Setyo Purnomo CEO Erudeye
14.00 - 15.00	AlwaysOn High Availability Group in SQL Server 2012 Kiki Rizki Noviandi Microsoft MVP (SQL)	Powerful Data Visualization with PowerView Hendra Eka Putra Microsoft MVP (SQL)
15.00 - 15.15	Break
15.15 - 16.15	Monitoring SQL Server 2012 with SCOM Faisal Susanto Senior Consultant, Microsoft Services	Performance Tuning in SQL Server 2012 Choirul Amri Senior Consultant, Microsoft Services
16.15 - 16.30	Closing remaks and door prize

Pendaftaran

Pendaftaran dapat dilakukan dengan mengirimkan email dengan mencamtumkan informasi berikut: Nama Lengkap, perusahaan tempat bekerja, dan nomor telepon yang dapat dihubungi. Acara ini gratis, namun mengingat keterbatasan kapasitas maka diharapkan rekan-rekan komunitas dapat mencantumkan informasi lengkap untuk keperluan konfirmasi kehadiran.

Anggota komunitas dapat mendaftar ke kontak berikut:

SQL Server User Group Indonesia: Cahyo Listyanto| Email: sqlserverindo@live.com

Microsoft User Group Indonesia: Abdul| Email: Abdullah0813@live.com

Share this post: | | | |

SQL Server Indonesia Community Monthly Meeting

2011-10-27T04:30:00Z

Rekan Sekalian,

Kami mengundang kehadiran rekan-rekan dalam SQLServer-Indo monthly meeting yang akan diadakan pada:

Kamis, 27 Oktober 2011
Jam: 19.00 - 21.00
Tempat; Auditorium Microsoft Indonesia, Gedung BEJ Lantai 18 - Jakarta

Ada 2 topik yang akan dishare dalam pertemuan ini:

Master Data Services di SQL Server 2008 R2
Pembicara: Hendra Eka Putra, MVP (TBC)

SQL Server 2008 Guide for SharePoint Administrator
Membahas teknik maintenance dan tuning database SQL Server yang digunakan sebagai backend SharePoint.
Pembicara; Choirul Amri, Moderator SQLServer-Indo

Untuk reservasi/pendaftaran, silakan kirim email ke sqlserverindo at live dot com.

Note: Ada beberapa godies dan doorprize menarik di acara tersebut, pastikan anda hadir untuk mendapat hadiah utama Windows 7 Home Premium!

//salam
Moderator SQLServer-Indo

Share this post: | | | |

Selamat Tinggal 2010, Selamat datang 2011

2010-12-31T16:15:00Z

Tidak terasa waktu terus berjalan, penghujung tahun 2010 telah tiba dan fajar 2011 segera menjelang.

sudah banyak hal dan kejadian yang kita alami selama tahun 2010 ini, baik yang sifatnya suka maupun duka. yang memperkaya pengalaman hidup kita hingga kita semakin kuat dan tegar dalam mengarungi kehidupan ini.

dalam dunia IT sendiri banyak pengalaman baru yang bisa saya timba, dengan terlibat dalam team baru dengan sekala project baru. membuat hidup jadi lebih hidup

pengalaman adalah guru yang terbaik, setiap langkah pasti mengandung arti. dan meninggalkan jejak bagi pengalaman hidup kita

semoga tahun 2011 menjadi awal yang lebih baik lagi, semakin maju dengan semangat baru dan kesuksesan baru

Share this post: | | | |

Selamat Datang SQL Server 'Denali'

2010-12-17T11:25:00Z

Code Name 'DENALI' merupakan versi yang palin mutakhir dari SQL Server. dengan menunjukkan beberapa enhancement yang lebih mutakhir dari terdahulunya menyebabkan DENALI menjanjikan kelebihan dan sisi kompetitifnya dengan pesaing yang ada di pasaran

dari referensi yang bisa kita baca di MSDN, jelas sekali enhancement yang dimiliki Denali ini, enhancement ini dilakukan di 3 area penting dalam system database SQL Server yaitu pada

- Proses Instalasi

- Database Engine

- Intergration Service

berikut referensi link yang bisa di jadikan acuan untuk mengetahui lebih jauh ke handalan SQL Server DENALI Ini

New Server Installation:
SQL Server Multi-Subnet Clustering (link: http://msdn.microsoft.com/en-us/library/ff878716(v=SQL.110).aspx)
Prerequisite Installation during SQL Server Setup (link: http://msdn.microsoft.com/en-us/library/ms143219(v=SQL.110).aspx)
No support for SQL Server Itanium editions
New (Database Engine):
Availability Enhancements – (http://msdn.microsoft.com/en-us/library/ff877884(v=SQL.110).aspx)
Manageability Enhancements
   SSMS,( http://msdn.microsoft.com/en-us/library/cc645579(v=SQL.110).aspx)
Contained Databases, (http://msdn.microsoft.com/en-us/library/ff929071(v=SQL.110).aspx)
  Startup Options, (http://msdn.microsoft.com/en-us/library/ms345416(v=SQL.110).aspx)
Programmability Enhancements
Full-Text Search,
     (http://msdn.microsoft.com/en-us/library/ee677637(v=SQL.110).aspx)
     (http://msdn.microsoft.com/en-us/library/ms142568(v=SQL.110).aspx)
Metadata Discovery, (http://msdn.microsoft.com/en-us/library/ms173839(v=SQL.110).aspx)
EXECUTE Statement, (http://msdn.microsoft.com/en-us/library/ms188332(v=SQL.110).aspx)
UTF-16 Supplementary Characters (SC) Collations
Ad-hoc Query Paging Implementation, (http://msdn.microsoft.com/en-us/library/ms188385(v=SQL.110).aspx)
  Circular Arc Segment Support for Spatial Types
  Sequence Objects, (http://msdn.microsoft.com/en-us/library/ff878058(v=SQL.110).aspx)
Scalability and Performance Enhancements (http://msdn.microsoft.com/en-us/library/cc645580(v=SQL.110).aspx )
New (Integration Services): (http://msdn.microsoft.com/en-us/library/bb522534(v=SQL.110).aspx)

Share this post: | | | |

The Role of a Project Manager (from http://www.tenstep.com)

2010-07-20T09:22:00Z

Here is some of good articles for Project Manager, it is usefull to open your mind about this role

this story can explain why people would rather choose to other role then become a project manager

"A new employee in the company mailroom noticed an older man sitting in the corner, sorting mail, weighing packages, adding postage and doing other simple jobs. He asked his supervisor who the man was.

That's Joe." the supervisor said. "He has been with the company for 35 years and is getting close to retirement."

"Really." the new employee replied. "And he's been in the mailroom the whole time?"

"No, he left a number of years ago. But he asked for a transfer back - after spending several years as a project manager."

Some of key thing to become good project manager from tenstep.com.

In general, the project manager is responsible for the overall success of the project. In some companies, this person might be called a Project Coordinator, or a Team Leader, however, the key aspect is that the person is responsible for ensuring the success of the project.

What does it take for the project to be a success? If you follow the TenStep Project Management Process® (www.TenStep.com), or a similar approach, you first must define the project and build the schedule. This is where the project manager's responsibilities start. If the project begins and you find out later that you are not clear on scope, the project manager is the one who is accountable. If your project is executing a poor schedule, the project manager is accountable.

The work around defining the project means that you understand and gain agreement on the overall objectives, scope, risk, approach, budget, etc. It also includes defining or adopting the specific project management procedures that will be used to manage the project.

This does not mean that the project manager must do all this work themselves. There may be an entire team of people helping to create the Project Charter and schedule. However, if something does not go right, the project manager is accountable.

Once the project starts, the project manager must successfully manage and control the work, including:

Identifying, tracking managing and resolving project issues
Proactively disseminating project information to all stakeholders
Identifying, managing and mitigating project risk
Ensuring that the solution is of acceptable quality
Proactively managing scope to ensure that only what was agreed to is delivered, unless changes are approved through scope management
Defining and collecting metrics to give a sense for how the project is progressing and whether the deliverables produced are acceptable
Managing the overall schedule to ensure work is assigned and completed on time and within budget

and I have learn about all usefull things in here http://www.lifecyclestep.com/open/407.1TheRoleoftheProjectManager.htm

Share this post: | | | |

SQL Server Base Practice - Kumpulan Link yang bermanfaat

2010-06-24T08:40:00Z

Kadang kita kesulitan untuk mendapatkan referensi dan best practice untuk SQL server di internet, setelah beberapa waktu mencoba mencari link yang berguna berupa artikel yang menarik dan berguna di internet berikut ini beberapa link yang bisa di jadikan acuan untuk best practise SQL Server

Share this post: | | | |

DICARI : Db Developer... ada yang minat ?

2010-05-17T05:13:00Z

Sudah beberapa minggu ini, saya mencari kiri kanan teman yang berminat untuk bergabung di team sebagai Db Developer. sempat beberapa waktu lalu interview orang untuk posisi ini. namun sepertinya masih belum sesuai dengan harapan. setelah meminta beberapa kali meminta pada resource manager di kantor dan ternyata tidak mendapatkan hasil yang memuaskan, akhirnya saya putuskan untuk membuka kesempatan ini langsung ke teman teman di komunitas SQL Indonesia

Posisi DB developer ini sendiri sangat menarik, skillnya tidak terlalu developer dan tidak terlalu DB, sehingga masih memungkinkan mixing skill antara dev dan db. berikut job desc yang saya cari

Database Developer
Required Skill
- Having Min 2 years experience in Developing Database using SQL Server
- Must be familiar with Data Modeling/Relational Database Design
- Experience creating and implementing physical databases from Logical Data Models
- Experience in analyzing and writing complex queries and stored procedures (SQL/TSQL)
- Good Experience with large databases and/or data warehousing
- Good experience in database development skill (i.e. normalization, referential integrity, stored procedure, Trigger)
- Some experience as an SQL DBA
- Must have strong skills in Microsoft SQL Server 2005/2008 including.
         SQL Server Integrated Services (SSIS)
         SQL Server Analysis Services (SSAS)
         SQL Server Reporting Services (SSRS)
- Familiar with Microsoft Database project in SQL Server 2005/2008
- Strong interpersonal skills to resolve problems in a professional manner, lead working groups, negotiate and create consensus

bagi teman teman yang berminat dengan kesempatan ini silahkan kirim email ke kiki.rizki.noviandi@live.com, mudah mudahan kita berjodoh

Share this post: | | | |

Menjadi pembicara di Mixed Community Lauch Visual Studio 2010 Faculty Summit UGM

2010-05-11T09:13:00Z

Kamis besok (13 Mai 2010) saya berkesempatan untuk mengisi acara Visual Studio 2010 faculty summit di UGM, kesempatan sengat berharga ini selain bisa memberikan sharing pada Komunitas IT dan Akademisi di UGM mengenai SQL Server 2008 R2 sekaligus menjadi pembicara ke tiga setelah Luke Loban (Senior Program Manager F#) dari Microsoft Corp.

Visual Studio 2010 Faculty Summit – Universitas Gadjah Mada

13.00 - 14.30	Introduction to F# and Parallel Programming Luke Hoban, Senior Program Manager F#, Visual Studio 2010, Microsoft Corp

14.30 - 15.15	Introducing Windows Phone 7 Development Erick Kurniawan, Microsoft Most Valueable Professional

15.15 - 16.00	Business Intelligence with SQL Server 2008 R2 and Visual Studio 2010 Kiki Noviandi, Microsoft Most Valueable Professional

Acara ini merupakan bagian dari rangkaian acara event Community Lauch Visual Studio 2010 di sejumlah kota di tanah air, berikut jadwal Mixed Community Lauch Visual Studio 2010 seperti tercantum pada web site Microsoft.com http://msdn.microsoft.com/id-id/ff629034.aspx

Rangkaian Jadwal Event

	Visual Studio 2010 Cianjur Launch Waktu: Sabtu 23 April 2010, 08.30 s/d 16.30 Tempat: Bale Rancage,Cianjur Pembicara: Narenda Wicaksono, Aris Lesmana, Ronald Rajagukguk, Agus Kurniawan, Rully Yulian, Agam Fuja Kurniawan, Tutang MM, Fistman Marpaung
	Visual Studio 2010 Sukabumi Launch Waktu: Sabtu 8 Mei 2010, 08.30 s/d 16.30 Tempat: Aula Akademi Citra Buana Indonesia Pembicara: Narenda Wicaksono, Aris Lesmana, Ronald Rajagukguk, Agus Kurniawan, Rully Yulian, Agam Fuja Kurniawan, Tutang MM, Fistman Marpaung, Andri Yadi
	Microsoft ISV Innovation Day(Jakarta Launch) Waktu: Selasa 11 Mei 2010, 08.30 s/d 17.00 Tempat: Balai Kartini, Jl. Gatot Subroto, Jakarta Innovation Day 2010 kali ini diadakan bersamaan dengan pelaksanaan final kompetisi lokal Imagine Cup 2010 (kategori Software Design). Untuk agenda lebih lengkap silakan kunjungi situs resmi ISV Innovation Day 2010 berikut.
	Visual Studio 2010 Faculty Summit – Universitas Indonesia Waktu: Rabu 12 Mei 2010, 13.30 s/d 16.30 Tempat: Universitas Indonesia, Depok Pembicara: Luke Hoban, Narenda Wicaksono, Agus Kurniawan, Ronald Rajagukguk
	Visual Studio 2010 - Yogyakarta Community Launch Waktu: Kamis 13 Mei 2010, 08.30 s/d 13.00 Tempat: Auditorium MM UGM, Jalan Kaliurang km 4 Yogyakarta Pembicara: Luke Hoban, Ridi Ferdiana, Risman Adnan, Narenda Wicaksono, Ronald Rajagukguk
	Visual Studio 2010 Faculty Summit – Universitas Gadjah Mada Waktu: Kamis 13 Mei 2010, 13.00 s/d 16.00 Tempat: Auditorium MM UGM, Jalan Kaliurang km 4 Yogyakarta Pembicara: Luke Hoban, Erick Kurniawan, Kiki Noviandi
	Visual Studio 2010 - Bali Community Launch Waktu: Sabtu 15 Mei 2010, 08.00 s/d 16.30 Tempat: STIKOM Bali - Convention Center Pembicara: Luke Hoban, Risman Adnan, Ronald Rajagukguk
	Visual Studio 2010 - Surabaya Launch Waktu: Senin 17 Mei 2010, 08.30 s/d 13.30 Tempat: Restauran Agis, Jalan Wisma Pagesangan 197, Surabaya Pembicara: Luke Hoban, John Muljana, Theodorus Mitsutama, Narenda Wicaksono, Ronald Rajagukguk, PT IAO
	Visual Studio 2010 - Bandung Community Launch Waktu: Selasa 18 Mei 2010, 08.00 s/d 13.00 Tempat: Ruangan Mas Soeharto, Wahana Bhakti Pos Lantai 8, Jalan Riau Bandung Pembicara: Luke Hoban, Narenda Wicaksono, Firstman Marpaung, Ronald Rajagukguk
	Visual Studio 2010 Faculty Summit – Bandung Waktu: Selasa 18 Mei 2010, 13.00 s/d 16.30 Tempat: Ruangan Mas Soeharto, Wahana Bhakti Pos Lantai 8, Jalan Riau Bandung Pembicara: Luke Hoban, Rully Yulian, Andri Yadi
	Visual Studio 2010 - Medan Community Launch Waktu: Sabtu 5 Juni 2010, 08.00 s/d 15.00 Tempat: Convention Hall Uni Plaza, Jl. M.T. Haryono - Medan Pembicara: Ronald Rajagukguk, Raymond Engelbert, Arfan Arlanda

Share this post: | | | |

MSF Agile - memanfaatkan metodology dan tools

2010-03-11T10:59:00Z

Beberapa hari ini, saya kembali turun gunung dalam team Software development dengan menerapkan konsep MSF Agile. metoda ini memungkinkan kita bisa melakukan rapid application developmen dengan menggunakan pola deployment secara iteratif yang tidak sama dengan pendekatan water fall.

fase - fase yang terdapat di MSF agile hampir sama dengan methoda software development pada umumnya, namun yang membedakan adalah dalam hal pemanfaatan agile methodology yang memungkinkan software di buat secara modular tanpa harus menunggu proses requirement dan design secara lengkap

fase2 pada msf diantaranya

1. Envisioning, kegiatan envisioning berhubungan dengan mengumpulkan infomasi fisi dari user dan harapan mereka atas sistem yang akan di buat dan menuangkan nya dalam bentuk requirement document yang biasa di sebut juga sebagai vision document

2. Planning, kegiatan pada fase ini adalah melakukan proses perencanaan dan desain dari semua kebutuhan user kedalam spesifikasi pekerjaan yang akan kita kerjakan yang di sebut juga sebagai Functional Spec. banyak cara untuk menuangkan notasi FS ini pada umumnya kita menjelaskan FS ini dengan cara naratif dan di bantu dengan tools pemodelan seperti UML. dari factional spec ini akan di terjemahkan menjadi technical spec yang lebih menitik beratkan bagaimana menjawab kebutuhan user ini dalam bahasa teknis seperti Object Model, Relation Model dan Software architecture Model

3. Developing, Pada tahap ini Dokument Technical spec akan mulai dikerjakan oleh team development. sesuai dengan spesifikasi teknis yang telah di tentukan

4. Stabilizing, tester dan QA akan melakukan proses testing secara intensif dengan menggunakan mekanisme daily build yang memungkinkan setiap error dan kesalahan dapat di deteksi dan di perbaiki sedari dini. proses Bug Finding dan Bug Fixing akan jalan terus menerus sehingga proses stabilizing selesai dan bug trend yang di hasilkan semakin minim

5. Deploy, setelah lolos dan berhasil mencapai Bug Free aplikasi akan di deploy ke mesin UAT atau production untuk di coba oleh user

Semua step ini bisa bekerja dengan sangat efektif jika kita menggunakan tools yang bisa membantu proses ini, dan microsoft memberikan kemudahan untuk MSF Agile ini dengan menggunakan Visual Studio team system.

Sebelumnya saya pernah menggunakan VSTS 2005, dan menurut saya sangat membantu dalam implementasi Software development. menggunakan VSTS 2008 merupakan pengalaman menarik, karena semakin banyak hal baru yang bisa saya temukan khususnya dalam membantu proses implementasi Software development dengan MSF Agile.

pengalaman saya dengan VSTS 2008 ini tentu saja akan saya share di tempat ini, khususnya yang kaitannya dengan penggunaanya untuk membantu team database.

nantikan pengalaman saya di blog ini

Share this post: | | | |

Database Trouble Shooting, how to recover suspect database in SQL

2010-02-27T06:05:00Z

One of Basic knowladge to understand and doing trouble shooting in SQL Server platform is understanding Database Status, each status is store on master.dbo.sysdatabases object. and the database status in SQL Server defined as

State	Definition
ONLINE	Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed.
OFFLINE	Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.
RESTORING	One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable.
RECOVERING	Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable.
RECOVERY PENDING	SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.
SUSPECT	At least the primary filegroup is suspect and may be damaged. The database cannot be recovered during startup of SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem.
EMERGENCY	User has changed the database and set the status to EMERGENCY. The database is in single-user mode and may be repaired or restored. The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect can be set to the EMERGENCY state. This could permit the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.

When the database status is suspected, it might be from trouble of Infra structure or hardware that impacted to SQL Server database performance.

Here are some step by step to recover database that have been suspended by the system

1. Look in the SQL Server Logs in Enterprise Manager(EM), There should be multiple logs starting with Current and then followed by 6 or more archived logs.

2. Review all recent Error Log(s). There WILL be an indication here as to why the database has been marked suspect.

3. When the problem has been fixed and you're either sure that the data is going to be ok, or you have no backup anyway, so you've nothing to lose, then change the database status to normal and restart SQL Server.

4. To change the database status we will us the following store procedure: sp_resetstatus.
The steps are as follows:

5. Change database status to allow update

        USE master
        GO
        sp_configure 'allow updates', 1
        GO
        RECONFIGURE WITH OVERRIDE
        GO

        sp_resetstatus
        GO
        -- Example: sp_resetstatus ‘Washington’

6. After the procedure is run, immediately disable updates to the system tables:

       sp_configure 'allow updates', 0
        GO
        RECONFIGURE WITH OVERRIDE
        GO

If database is rolling back to suspect than you should change db status to emergency mode, you have to extract all data out from database using BULK Copy or DTS, Note that the data may be corrupt or transactionally inconsistent

1.     Issue the following command to put the database into emergency mode:

   a.    USE master
        GO
        sp_configure 'allow updates', 1
        GO
        RECONFIGURE WITH OVERRIDE
        GO

   b.    UPDATE master..sysdatabases
        SET status = 32768
        WHERE name = 'DatabaseName'

2. Stop and Restart ALL SQL Server Services.

We are now ready to pull whatever data we can out of the tables in the corrupt database. Remember, some tables may be corrupt, thus you may have to play with various T-SQL statements to get the data out. First try DTS

Share this post: | | | |

SQL Server Monthly Meeting - Feb 10

2010-02-15T04:31:00Z

Share this post: | | | |

Database Unit testing with SQL Server 2005

2010-02-12T07:24:00Z

As you seen on my previous articles in my blog, Unit testing is very importan in software development. But people still not aware about this because developer commonly only focus on how to develop the application and deprioritize the quality of code it self. Unit testing will make sure system behaviour and response from every parameter input that given by user will compliant with the function spesification.

To help developer doing unit testing in stabilizing phase, Visual Studio team system for database already have capabilty to create and develop module for unit testing.

The framework in Microsoft Visual Studio 2005 Team Edition for Database Professionals (DB Pro) for database unit testing, however, offers some important value-add features above and beyond those just mentioned. The first of these is automatic generation of SQL script stubs of unit tests for stored procedures, functions, and triggers. This feature saves you the hassle of developing the boilerplate code that you see earlier in this paper.

Secondly, DB Pro provides a set of built-in test conditions to help you verify your test results. These test conditions perform the most common validation that you would want to verify, including the rows returned, scalar values, and execution time. You can easily configure these test conditions through the user interface.

And, most importantly, DB Pro offers a way for you to set the database state by using the data-generation feature to populate the database with test data before running your database tests. The beauty of the data-generation functionality is that you can repeatedly generate the same test data based on a seed value, making it repeatable and thus very applicable for unit testing.

We will explore each of these features in this paper and how best to leverage them in implementing your process for database unit testing.

Authoring Your First Database Unit Test

To get you started, the following simple walkthrough shows how you can create a database unit test for the CustOrderHist stored procedure in the Northwind database.

Ensure that Team Edition for Database Professionals is installed on your computer.
Open Microsoft Visual Studio.
On the Test menu, click New Test.
In the Add New Test dialog box, click Database Unit Test, and pick the type of test project that you want to create (either C# or Visual Basic).

Figure 1. Add New Test dialog box
Specify a name for the new test project.

Figure 2. New Test Project dialog box
Specify the database connection against which to run the test, and click OK.

Figure 3. Database test configuration

The Database Unit Test Designer appears.

Figure 4. Database Unit Test Designer (Click on the picture for a larger image)
Add a test method by clicking the plus sign (+) on the top of the designer.

Figure 5. Adding new database unit test
Name the new test, and click OK.

Figure 6. Naming new database unit test

Add the following T-SQL to the main editor window in the designer:

DECLARE @CustomerId nchar(5)
SELECT @CustomerId = 'EASTC'
EXEC dbo.CustOrderHist @CustomerId

Click the inconclusive test condition in the Test Conditions panel, in the bottom half of the designer. Click the red "x" button to delete the test condition.

Figure 7. Test Conditions panel
Add a row-count test condition by clicking Row Count in the Test Conditions list and clicking the + button.

Figure 8. Adding row-count test condition
In the Properties window, set the number of expected rows to 19.

Figure 9. Configuring test-condition properties
On the Test menu, point to Windows, and click Test View.

Figure 10. Running test from Test View
Right-click the test, and click Run Selection.
Review the results in the Test Results window.

Figure 11. Viewing test results

And your test passed!

You have just successfully created your first database unit test. Let's now drill into the details of the various phases of database unit testing.

source : http://msdn.microsoft.com/en-us/library/bb381703(VS.80).aspx#dtbunttsttedp_topic1

Share this post: | | | |

Database Unit Testing, is it sound familiar for you?

2010-02-12T03:59:00Z

have you heard about Database Unit Testing?

Unit testing is a well-understood concept in application development, but the database community has not yet embraced the advantages and strategies of this approach. Therefore, I'd like to start by exploring the fundamental tenets of the unit-testing methodology. Unit testing provides a structured and automated way of testing individual components of a system. Unit tests are most often authored by the developer of the component that is being tested. Each unit test tests a specific module of the code in an isolated fashion to ensure that the component behaves as expected.

How does this all relate to database development? The direct analog of application unit tests in the database world are tests of a database's programmability objects. These objects include, for example, a database's stored procedures, functions, and triggers.

What might a unit test for a stored procedure look like? Let's say that you are trying to test the CustOrderHist stored procedure in the Northwind database. The stored procedure should return the order history for a given customer ID. To test this behavior, you can imagine writing a SQL script that executed the stored procedure and then verified whether the expected number of rows was returned. Such a script might resemble the following:

DECLARE @CustomerId nchar(5)
SELECT @CustomerId = 'EASTC'
EXEC dbo.CustOrderHist @CustomerId

IF (@@ROWCOUNT <> 19)
RAISERROR('Actual Rowcount not equal to expected 19',11,1)

As a methodology, unit testing has many advantages over manual, ad-hoc testing and debugging. By developing database unit tests, you can create a collection of tests and run them during development to ensure that your features work as you expect. Because each unit test focuses specifically on an individual method, you can more easily determine the source of a failure for a failing unit test. Therefore, database unit tests help you determine the sources of bugs in your code.

Such a collection of tests is very useful for regression testing. As you implement new features, you can rerun existing tests to ensure that existing functionality has not been broken. Such a regression test suite facilitates database changes, because you can now make changes knowing the implications of those changes.

Unit tests, in addition, serve as documentation for users of the methods under test. Developers can quickly review unit tests to determine exactly how particular components should be consumed.

Database unit testing is not limited merely to testing the database's programmability objects. You might want to author the four classes of tests that this section describes.

Feature Tests

The first and likely most prevalent class of database unit test is a feature test. In my mind, feature tests test the core features—or APIs, if you will—of your database from the database consumer's perspective. Testing a database's programmability objects is the mainline scenario here. So, testing all the stored procedures, functions, and triggers inside your database constitute feature tests in my mind. To test a stored procedure, you would execute the stored procedure and verify that either the expected results were returned or the appropriate behavior occurred. However, you can test more than just these types of objects. You can imagine wanting to ensure that a view, for example, return the appropriate calculation from a computed column. As you can see, the possibilities in this realm are large.

Schema Tests

One of the most critical aspects of a database is its schema, and testing to ensure that it behaves as expected is another important class of database unit tests. Here, you will often want to ensure that a view returns the expected set of columns of the appropriate data type in the appropriate order. You might want to ensure that your database does, in fact, contain the 1,000 tables that you expect.

Security Tests

In today's day and age, the security of the data that is stored within the database is critical. Thus, another important class of database unit tests are those that test the database security. Here, you will want to ensure that particular users exist in your database and that they are assigned the appropriate permissions. You will often want to create negative tests that attempt to retrieve data from restricted tables or views and ensure that the access is appropriately denied.

Stock-Data Tests

Many databases contain stock data, or seed data. This data changes infrequently and is often used as lookup data for applications or end users. ZIP codes and their associated cities and states are great examples of this kind of data. Therefore, it is useful to create tests to ensure that your stock data does, in fact, exist in your database.

source : http://msdn.microsoft.com/en-us/library/bb381703(VS.80).aspx#dtbunttsttedp_topic1

Share this post: | | | |

Connection String (connect to Excel File)

2010-01-06T01:51:00Z

Sambil mengingat ngingat jaman dulu ketika memulai pemrogram dengan mengkases database. ternyata hal yang paling penting dan sering dilupakan adalah ketika kita akan melakukan koneksi kesebuah data source (database server). Ada banyak connection provider yang harus kita hapalkan Dengan cara penulisan yang spesifik dalam satu parameter yang orang kenal dengan CONNECTION STRING.

Sekarang ini orang lebih mudah melakukan koneksi kepada sebuah data source karena hampir sebagian besar Development tools menyediakan fasilitas untuk koneksi ke database yang kita inginkan, namun pekerjaan ini akan menjadi susah ketika kita di paksa untuk melakukannya dengan scripting seperti di VB Script, atau Java Script. sehingga kita perlu mengingat cara penulisan connection providernya, security model yang di gunakan dan mekanisme koneksinya

Berikut saya coba tuliskan kumpulan connection string yang biasanya paling sering di cari berdasarkan connection providernya mudah-mudahan bisa membantu kita dalam mengakses data source dari berbagai jenis database

Connection String Untuk MS Excel 2007

Pada bagian pertama ini akan di jelaskan beberapa metoda penulisan connection string untuk mengakses data di Microsoft Excel 2007, dengan menggunakan ACE OLE DB Connection. metoda ini lebih cepat dari pada menggunakan office component library

A. XLSX File

XLSX file adalah nama extention file untuk MS Excel 2007, untuk mengkases MS Excel 2007 file bisa menggunakan connection string berikut ini :

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

dengan nilai HDR = Yes jika menghendaki kolom pertama sebagai nama field dari data yang kita akses di file excel tersebut

B. Mengakses data Excel File dalam format Text

Kita bisa memperlakukan semua data dalam XLSX file tadi sebagai data dalam format text dan mengabaikan format dari setiap cell yang ada, hal ini dilakukan jika dalam file excel yang kita miliki memiliki type data yang berbeda beda dalam setiap cell nya, berikut contoh connection stringnya

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

Parameter IMEX = 1 menyatakan bahwa semua data yang diambil akan di anggap sebagai Text

C. Mengakses XLsb File

MS Excel 2007 memiliki file dengan format dan extention baru *.XLSB, yang menyimpan data dalam Office Open XML binary format. data disimpan dalam format binary sehingga berbeda dengan format XLSX, format XLSB ini bukan merupakan text sehingga tidak bisa diview dan dari sisi performace jauh lebih baik dari XLSX. berikut connection stringnya

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myBinaryExcel2007file.xlsb;Extended Properties="Excel 12.0;HDR=YES";

D. Mengakses XLSM File

Format extention XMSL merupakan office open XML format dengan Macro Enable, berbeda dengan connection string yang adalah pada statement Properties="Excel 12.0 Macro...

Berikut contoh penulisan connection string untuk mengakses XLSM file

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsm;Extended Properties="Excel 12.0 Macro;HDR=YES";

Demikian mudah mudahan tulisan contoh connection string ini bisa memerikan wawasan tambahan untuk teman teman yang memerlukan akses ke berbagai macam database. masih banyak lagi tentang contoh dan format penulisan connection string, yang insya alloh akan saya lanjutkan dan lengkapi lagi (KN-SQLG)

Share this post: | | | |

Change data capture, fitur SQL 2008 yang sangat berguna...

2009-10-23T01:22:00Z

Kemarin buka buka lagi catatan waktu ikut TPrep Project Untuk SQL Server 2008 di Redmond Tahun yang lalu, ini saya lakukan karena memang Hari Kamis kemarin (22/10/2009) berkesempatan untuk membawakan session microsoft di Acara Metro Data Solution Day di Hotel Shangrila Jakarta. Kebetulan materi yang akan di bawakan berkaitan dengan fitur - fitur SQL yang berhubungan dengan pengolahan data dalam ukuran besan sehingga judul besar dari session yang saya adalah SQL Data Warehouse Penta Byte Data Solutions.

Saya coba share sedikit apa yang saya presentasikan di blog saya ini, khususnya fitur-fitur yang berhungan dengan pengelolaan data yang memiliki ukuran sangat besar dengan memanfaatkan fitur fitur terbaru dari MS SQL 2008.

Change data capture adalah topik pertama yang saya sampaikan disini, dimana fitur baru di SQL Server 2008 ini memudahkan kita untuk melakukan capturing terhadap perubahan yang terjadi bada sebuah table dalam sebuah database. Dengan CDC ini memungkin semua operasi yang dilakukan pada sebuah table bisa kita tracking historynya dari operasi yang dilakukan, dari mulai data pertama kali di masukkan, perubahan terhadap data di setiap kolom hingga data terakhir yang ada pada sebuah table

untuk membuat CDC ini, kita bisa lakukan di SQL Server 2008 dengan terlebih dahulu mengaktifkan fitur CDC pada sebuah database dengan perintah berikut ini

EXEC sys.sp_cdc_enable_db;

Perintah ini akan mengaktifkan fitur CDC di database yang kita akan buatkan Change Data Capture-nya, ini bisa dilihat dari adanya file tambahan pada database kita di bagian system table seperti

cdc.captured_columns

cdc.change_tables

cdc.ddl_history

cdc.index_columns

cdc.lsn_time_mapping

Dengan CDC ini memungkinkan kita untuk dapat melakukan pencatatan terhadap perubahan data yang terjadi tanpa harus membuat table auditlog sendiri maupun membuat mekanisme trigger untuk mencatat sejarah perubahan data baik oleh aplikasi maupun oleh DBA secara back end

Berikut contoh scipt untuk fitur change data capture di SQL Server 2008, silahkan mencoba

--set current database context
USE [AdventureWorks]

GO

--append new user column to the HumanResources.Employee Table
ALTER TABLE humanresources.employee
ADD [User] NVARCHAR(50)

GO

--disable any triggers
DISABLE Trigger ALL ON HumanResources.employee

GO

--enable CDC for AW
EXEC Sp_cdc_enable_db

GO

--enable CDC for HumanResources.Employee
EXEC Sp_cdc_enable_table
  humanresources ,
  employee ,
  NULL ,
  1 ,
  dbo

GO

--create sample UDF to return change data
CREATE FUNCTION [dbo].[Udf_employee]
               (@start_time DATETIME,
                @end_time   DATETIME)
RETURNS @Employee TABLE(employeeid       INT,
                        nationalidnumber NVARCHAR(15),
                        contactid        INT,
                        managerid        INT,
                        title            NVARCHAR(50),
                        birthdate        DATETIME,
                        maritalstatus    NCHAR(1),
                        gender           NCHAR(1),
                        hiredate         DATETIME,
                        salariedflag     [FLAG],
                        vacationhours    SMALLINT,
                        sickleavehours   SMALLINT,
                        currentflag      [FLAG],
                        rowguid          UNIQUEIDENTIFIER,
                        [user]           NVARCHAR(50),
                        cdc_operation    VARCHAR(1))
AS
  BEGIN
    --declare local variables to hold LSNs
    DECLARE  @from_lsn BINARY(10),
             @to_lsn   BINARY(10)

    --Map the time interval to a change data capture query range.
    IF (@start_time IS NULL)
      BEGIN
        SELECT @from_lsn = sys.Fn_cdc_get_min_lsn('HumanResources_Employee')
      END
    ELSE
      BEGIN
        SELECT @from_lsn = sys.Fn_cdc_map_time_to_lsn('smallest greater than or equal',@start_time)
      END

    IF (@end_time IS NULL)
      BEGIN
        SELECT @to_lsn = sys.Fn_cdc_get_max_lsn()
      END
    ELSE
      BEGIN
        SELECT @to_lsn = sys.Fn_cdc_map_time_to_lsn('largest less than or equal',@end_time)
      END

    --if same then exit
    IF (@from_lsn = sys.Fn_cdc_increment_lsn(@to_lsn))
      BEGIN
        RETURN
      END

    -- Query for change data
    INSERT INTO @Employee
    SELECT employeeid,
           nationalidnumber,
           contactid,
           managerid,
           title,
           birthdate,
           maritalstatus,
           gender,
           hiredate,
           salariedflag,
           vacationhours,
           sickleavehours,
           currentflag,
           rowguid,
           [user],
           CASE __$operation
             WHEN 1
             THEN 'D'
             WHEN 2
             THEN 'I'
             WHEN 4
             THEN 'U'
             ELSE NULL
           END AS cdc_operation
    FROM   cdc.Fn_cdc_get_net_changes_humanresources_employee(@from_lsn,@to_lsn,'all')

    RETURN
  END

GO

--sample DML statement
UPDATE humanresources.employee
SET    title = 'Marketing Director',
       [User] = system_user
WHERE  employeeid = 13

--get the net change of the sample DML including the User
SELECT *
FROM   [dbo].[Udf_employee](NULL,NULL)

Share this post: | | | |