Restore database error SQL Server 2008

I got this error when trying to restore AX database in my local PC :

Cannot open backup device 'c:\path\backupdbname.bak'. Operating system error 5(failed to retrieve text for this error. Reason: 15105)

It turns out that the sql server service account is Network Service, and this account does not have access to the backupdbname.bak file.

To solve this, right click the backup file, go to security tab, click edit. Then add the Network Service account to have read access to the file.

Rekomendasi konfigurasi RAID untuk database Dynamics AX

Akhirnya! keluar juga informasi resmi soal konfigurasi RAID untuk database Dynamics AX! Dalam Installation Guide AX 2012, dijelaskan rekomendasi konfigurasi RAID untuk database SQL Server. Informasi ini tidak disebutkan secara explisit di versi 2009 dan sebelumnya.

Mengapa RAID?
Aplikasi enterprise seperti Dynamics Ax merupakan software terintegrasi yang tidak hanya memuat banyak data, tetapi juga diakses oleh banyak pengguna secara bersamaan. Seluruh data ini disimpan di satu database, sehingga kinerja database menjadi salah satu kunci penting untuk mengoptimalkan kinerja aplikasi.
Teknologi RAID merupakan salah satu jawaban untuk meningkatkan kinerja database.
Salah satu pepatah dalam dunia pemrograman adalah  avoid reinventing the wheel, jadi saya tidak akan mejelaskan tentang definisi RAID, karena banyak literatur di internet. Salah satunya yang bagus  bisa dibaca di http://www.sohoconsult.ch/raid/raid.html. Ketika membaca tentang RAID, saran saya cukup perhatikan RAID 0, RAID 1, RAID 5, RAID 0+1, dan RAID 10.

Lalu bagaimana rekomendasi RAID yang bagus  untuk Dynamics AX?
Jika anda sudah membaca link di atas, maka setidaknya anda paham macam-macam RAID. Nah untuk Dynamics AX, konfigurasi RAID untuk server database SQL Server yang direkomendasikan adalah sebagai berikut :

• Dua disk dibuat menjadi RAID 1. Oleh sistem operasi ini dibaca sebagai satu disk. Gunakan disk ini untuk tempat instalasi Sistem Operasi Windows dan software database SQL Server.
• Empat disk berkapasitas kecil dikonfigurasi sebagai RAID 0+1, atau dua disk dikonfigurasi sebagai RAID 1. Oleh sistem operasi ini juga dibaca sebagai satu disk. Gunakan disk ini untuk lokasi file log database.
• Empat disk berkapasitas lebih besar dikonfigurasi sebagai RAID 0 +1. Oleh sistem operasi ini juga dibaca sebagai satu disk. Gunakan disk ini untuk lokasi file data database. Jika ingin hemat, jumlah disk bisa dikurangi cukup menjadi 3 dan dikonfigurasi menjadi RAID 5.

Masih belum paham dengan RAID?

Gak masalah, serahkan saja pada ahlinya! Alias serahkan saja pada vendor hardware untuk mengatur bagaimana RAID dikonfigurasi. Tidak semua hal harus kita ketahui. Yang penting beritahukan bahwa kita mau konfigurasi RAID ini difungsikan untuk ini. Vendor hardware seharusnya paham cukup dengan instruksi ini. Jika tidak paham, mungkin saatnya perlu cari vendor hardware yang lain :)

Install SQL Server Management Studio and other tools

Once I came across a situation where my SQL Server installation did not complete on the client components part. It was due to the virtual pc unable to locate the CD 2 needed for the installation. The database service was installed but no client tools.

So I need to figure out how to install the client components only.

I found this site http://aspadvice.com/blogs/ssmith/archive/2008/03/21/SQL-2005-Tools-Install-Experience-is-the-suck.aspx and it turns out that I have run this tool :

Toos > Setup > SQLRun_Tools.msi.

Configuring reporting service for Dynamic Ax 2009

I follow the steps in Ax installation guide on how to install reporting service for Ax.
When I came on setting up application pool, I got this error

The identity of application pool 'AxReportServer' is invalid, so the World Wide Web Publishing Service can not create a worker process to serve the application pool. Therefore, the application pool has been disabled.
This is corrected by assigning Ax proxy account to local IIS_WPG user group.

Later I need to assign ax proxy account to ReportServer and ReportServerTempdb database.

Property owner is not available for database

Recently I got this error message when tried to see database properties :

Cannot show requested dialog.

Additional information:

Cannot show requested dialog. (SqlMgmt)

Property Owner is not available for Database ’[ ]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights.

(Microsoft.SqlServer.Smo)

This happened after we established a new domain so I suspected that this is caused by orphaned SID since most of the time I created database with Windows credential. After googling I got sure answer that this is database owner problem, exactly due to sid being out of sych.

So based on idea from this blog, I run the following simple sql statement (SQL 2005):

exec sp_msforeachdb 'alter authorization on database::? to sa;'

This will reset database owner of all databases to sa. This statement is safe as the alter authorization statement is not applicable to system databases.

If you want to update a single database only just use this statement:

alter authorization on database::dbname to sa;

For SQL 2000, use this statement :

exec dbname..sp_changedbowner 'sa'

Search which database has specific column name

select *
from information_schema.columns

where column_name = 'searchcolumn'

Find database given physical file name

I was looking for ways to clean up my server disk space. One of them by shrinking sql server log file. I found a log file that was quite big. Unfortunately, there was no database name that has similar name with the physical file. I would not check physical name of each database one by one by issuing sp_helpdb command as my server hold tens databases. I would rather run script to find exactly what database hold this file.
Here is what I get:
SELECT
DB_NAME(database_id) database_name,
physical_name
FROM master.sys.master_files
where physical_name ='physicalfilename'

SQL Server Reporting Service database version error

I was learning SQL Server 2005 reporting service, when I got this error during database setup:
Couldn't generate the upgrade script. There is no upgrade script available for this database version.
It seems that the database server that host the report server database is not the same with the reporting services installation.
I checked my database is SQL Server 2005 SP2, but I don't know my Reporting Service version.
Then I searched through Microsoft MSDN and found this page which tells me various ways to check reporting service version.
By checking the log file as suggested, I found that my Reporting Services is SQL Server 2005 SP1.
So now I am going to upgrade my reporting service.

Axapta view of view may cause wrong results

When create view in Axapta, an existing view should not be used as data source because it may produce wrong result.
This happen when the originating view has more than one data source in it.
When view is created, Axapta uses the following technique :
- Use the first data source's dataareaid field as the view dataareaid field
- Use the second data source's dataareaid field as the view dataareaid#2 field
When used as a whole, this view will only show the first dataareaid as its dataareaid. Axapta kernel will filter based on dataareaid and dataareaid#2 with the correct company account. This case no problem.

Problem occurs when the view is used in another view, with additonal datasource attached to it.
Using the same rule as above, the new view's dataareaid#2 field will be replaced by the new datasource attached to it, so the original view second data source is no longer filtered by dataareaid. This may lead to a duplicate data when the same data of the original view's second data source exists in more than one company.

Delete duplicate rows

Recently I need to build an Axapta instance where the application has been modified while I only have a standard demo database.
Problem arises as the modified application has new indexes or change the table properties 'SaveDataPerCompany' to No instead of default Yes. Both may cause duplicate record problem. I don't want to just delete the entire record yet I need to synchronize the table with Axapta application.

It will be no problem if the table contains less than 20 records, I can just delete it manually. The problem is that I find a table has hundreds of records. Then I write this script to do this job less tedious.

--drop table #mytemptable -- This is needed only if run more than once
select distinct
module,type,groupid --specify as many as key fields
into #mytemptable
from pricediscgroup --this is the table name

--set variable s correspond to the key fields
declare @module int
declare @type intdeclare @groupid varchar(20)
declare @numberofrows int
declare mycursor Cursor for
SELECT * from #mytemptable
open mycursor

fetch next from mycursor into @module,@type,@groupid

while @@fetch_status=0
begin

select @numberofrows = count(*) from pricediscgroup where
module = @module and type= @type and groupid = @groupid
select @numberofrows, @module,@type,@groupid
if @numberofrows > 1
begin
set @numberofrows = @numberofrows - 1 -- leave one row

set rowcount @numberofrows --the select within the subquery will return this number of rows,
delete from pricediscgroup where recid in (
select recid from pricediscgroup p2
where
p2.module = @module and p2.type= @type and p2.groupid = @groupid)

end

fetch next from mycursor into @module,@type,@groupid
end

close mycursor

deallocate mycursor

It does take time to change the table name and adding some variables depend on the duplicate fields found. But it takes less time rather than deleting many rows manually.

The backup set holds a backup of a database other than the existing database

This error message appear when I tried to restore an SQL 2K SP4 db into SQL 2005, using SQL Server Management Studio.
Although I have specified overwrite existing database, the error message still appear.
So I run transact-sql statement instead :

restore filelistonly
from disk = 'D:\SQL Backup\sourceDB.bak'
GO
--This will show the logical file name needed in the operation below
restore database targetDB
from disk = 'D:\SQL Backup\sourceDB.bak'
WITH MOVE 'sourceDB_Data' TO 'D:\SQLDATA\targetDB_Data.MDF',
MOVE 'sourceDB_Log' TO 'D:\SQLDATA\targetDB_Log.ldf',
REPLACE,STATS
GO
-- The option with replace is mandatory as it will overwrite existing database. With stat will show the operation progress

Using filter on SQL Server 2005 profiler

When I first used SQL Server 2005 Profiler to trace the actual sql statement generated from Axapta, I was wonder where I could filter the result based on database name as it is in SQL Server 2000.

This question is answered easily. There is a tick box 'show all columns' in the event selection tab. After this selected, then there are more columns available on the button Columns Filters.

Windows 2003 server : mencegah file terblock

Baru-baru ini saya mengalami masalah dengan file yang dicopy dari suatu ftp site ke server Windows 2003 SP2 saya.
Seluruh file dengan extension .zip terblock oleh Windows.
Setelah googling, akhirnya saya temukan di sini bahwa penyebabnya adalah Internet Explorer Enhanced Security Configuration aktif. Ini menyebabkan security setting di Internet zone diset ke High, yang salah satu efeknya menyebabkan file2 yang di ambil dari Internet zone menjadi terblokir.
Untuk mengatasinya saya buka Internet Explorer > Tools > Option > Security tab.
Pilih Internet zone, lalu klik Custom level.
Setelah itu cari "Launching of application and unsafe files" ubah pilihan dari disable menjadi enable.
Ini membuat security setting yang tadinya High menjadi custom.
Setelah selesai menggunakan file yang tadinya terblock, saya kembalikan security setting Internet zone ke High.
Pemahaman lebih lengkap soal security setting di Internet Explorer ada di sini

SQL Server : Backup to different machine

There was a question that I curious how to backup sql server database to different machine (using Universal Naming Convention path).
Now I find the explanation in the books online and a clue from some internet resources :-)
It is the login account that is used to start the sql server instance that matters.
When I choose Local System account, it doesn't require password, but it does not have network access.
To have network access, I use domain user account and restart the service. It now can access \\servername\folder that is accessible by the domain user account.

SQL Server : Scheduled backup

SQL server 2000 has facility to schedule jobs. One of them is scheduled backup.
This can be done through database maintenance planner wizard.
We can specify how recurrent the backup will take place and how long the old backup file reside until it is overwritten.
However, I do not find a place where I can specify how the file name format would be.