Tuesday, December 18, 2012

Backup mirror databases script


-- Authro: George Wen
-- this script is to be run on database mirror and will backup all principle databases.

DECLARE @dbname varchar(200)
DECLARE @tsql varchar(500)

if OBJECT_ID('tempdb..#databases') is not null drop table #databases

select distinct d.name as dbname
into #databases
from sys.database_mirroring m join sys.databases d on m.database_id = d.database_id
where mirroring_role = 1

SELECT @dbname = MIN(dbname) FROM #databases

while @dbname is not null
begin
  select @tsql = 'BACKUP DATABASE ' + @dbname + ' TO  DISK = N''E:\MSSQL10\MSSQL\Backup\' + @dbname + CONVERT(varchar(10),getdate(),120) + '.bak'
  exec(@tsql)
  --print @tsql
  select @dbname = min(dbname) from #databases where dbname > @dbname

end

Monday, November 26, 2012

index auditing script

Index counts:

select
o.name, COUNT(*)
from  sys.indexes i inner join sys.objects o on i.object_id=o.object_id
where o.type='U' and i.index_id<>1
group by o.name
order by COUNT(*) desc

index usage/unused indexes

select
--db_name(db_id()) as [database]
--, objectname=object_name(s.object_id)
ROW_NUMBER() over (partition by o.name,i.name order by i.index_id) as rowcouter
,
'CREATE ' +
CASE i.IS_UNIQUE WHEN 1 THEN 'UNIQUE ' ELSE ' ' END +
'NONCLUSTERED ' + ' INDEX '+
i.NAME + ' ' +' ON ' +
OBJECT_NAME (i.OBJECT_ID) +
'('+
DBO.fGetIndexCols (object_NAME(i.OBJECT_ID), i.index_id) + ')'
+Case when dbo.fGetIncludedIndex(object_NAME(i.OBJECT_ID), i.index_id) is null then '' else ' INCLUDE ('+dbo.fGetIncludedIndex(object_NAME(i.OBJECT_ID), i.index_id)+')' end +
' ON ['+
( SELECT GROUPNAME
FROM SYSFILEGROUPS
WHERE GROUPID = DATA_SPACE_ID
) + ']' IndexScript
,
'drop index ' + i.name + ' on ' + o.name as dropscript
,    Indexed_Columns  = LEFT(ixColumns, LEN(ixColumns) -1)
     , Included_Columns = LEFT(includedColumns, LEN(includedColumns) -1)
, o.name  as tablename
, indexname=i.name
, i.index_id 
, user_seeks
, user_scans
, user_lookups
, user_updates
--, user
--, reads=user_seeks + user_scans + user_lookups 
--, writes =  user_updates 
from
 sys.indexes i inner join sys.objects o on i.object_id=o.object_id
left join sys.dm_db_index_usage_stats s on i.index_id=s.index_id and i.object_id=s.object_id
 CROSS APPLY (
            SELECT sc.Name + CASE WHEN is_descending_key = 1 THEN ' DESC'
                                  ELSE '' END
                 + ', '
              FROM sys.index_columns ic
              JOIN sys.columns sc
                on ic.object_id = sc.object_id
               AND ic.column_id = sc.column_id
             WHERE i.object_id = ic.object_id
               AND is_included_column = 0
               and i.index_id = ic.index_id
             ORDER BY ic.Key_Ordinal
              FOR XML PATH('') ) ix (ixColumns)
CROSS APPLY (
            SELECT sc2.Name + ', '
              FROM sys.index_columns ic2 JOIN sys.columns sc2 on ic2.object_id = sc2.object_id
               AND ic2.column_id = sc2.column_id
             WHERE i.object_id = ic2.object_id
               AND is_included_column = 1
               and i.index_id = ic2.index_id
             ORDER BY ic2.Key_Ordinal
              FOR XML PATH('') ) nc (includedColumns)
             
 where o.type='U' --and s.database_id=DB_ID()
  and i.index_id <>1
  and o.name not like 'tem%'
  and (isnull(user_seeks,0) + isnull(user_scans,0) + isnull(user_lookups,0))= 0  --comment out to show all indexes usage
  and i.name is not null
   --and i.index_id=0
--order by o.name, user_seeks, user_scans,user_lookups, user_updates


Missing indexes

;WITH I AS (

-- Missing Indexes current database by Index Advantage

-- This DMV Query written by Glenn Berry
SELECT user_seeks * avg_total_user_cost *
      (avg_user_impact * 0.01) AS [index_advantage],

migs.last_user_seek,
mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns,
mid.included_columns,migs.unique_compiles, migs.user_seeks,
migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
      AND user_seeks * avg_total_user_cost *
      (avg_user_impact * 0.01) > 10000 -- Set this to Whatever
)

Note: multiple sources were used for the above script.

Saturday, November 17, 2012

free web stress test tools

Since LoadRunner is somewhat expensive and I am interested to find out if there is any free tools available that can be similar job, so far I have read a few good reviews and articles, it seems the combination of Fiddler + WCat and Badboy+Jmeter might be a good way to go, will try to do some test soon.

Monday, September 24, 2012

automated backup of mysql and web site content to dropbox under text mode

I used to run a personal web site on a linux VPS, below is how I backup my sites and database content to dropbox.
(please note, not all these ideas came from me, I came across a few sources from internet and just put it together and make it work for me)

1. install dropbox on linux, see http://www.dropboxwiki.com/Text_Based_Linux_Install#Post-installation, I use folder /var/backup/Dropbox as dropbox folder.
2. use crontab to backup mysql and web content to dropbox folder, use compression to reduce bandwidth usage.
3. you can also you crontab to remove old backup files automatically.

purgeDropbox.sh:
#!/bin/bash
# limit on the size (in KB) of the directory
limit=1572864
adminmail=webmaster@yourdomain.com
# if directory exists, find out it's size
if [ -d /var/backup/Dropbox ]
then
  size=$(/usr/bin/du -sk /var/backup/Dropbox | cut -f1)
else
  echo "/var/backup/Dropbox is invalid !!!" | /bin/mail -s "Invalid directory" $adminmail
  exit
fi
# if directory size is greater than limit, alert the user
if [ $size -gt $limit ]
then
  echo "Data volume in your user home dir /var/backup/Dropbox with a total size of  ${size} kB exceeds the maximum limit of $limit kB (1.5 GB) ! Please take action!" |
 /bin/mail -s "user home dir exceeds max limit" $adminmail
   find /var/backup/Dropbox/redmine -name "*.gz" -mtime +30 -exec rm {} \; >> /root/purge.log 2>&1
fi
crontab entries:
30 20 * * *  mysqldump -u root -pPASSWORD --databases db1 db2 |gzip >/var/backup/Dropbox/database_$(date +\%Y\%m\%d__\%H:\%M:\%S\%z).sql.gz
00 19 * * * /root/purgeDropbox.sh
00 22 * * *  /bin/tar czfv /var/backup/Dropbox/web_$(date  +\%Y\%m\%d__\%H:\%M:\%S\%z).tar.gz /var/www

Saturday, August 25, 2012

Update Resource Cost rate Using PSI « Project Server And Sharepoint Solutions

Update Resource Cost rate Using PSI « Project Server And Sharepoint Solutions:

'via Blog this'

AJAX call web service behind ISA / TMG firewall with authentication

To be able to call web service behind ISA / TMG firewall, a few conditions need to be met:
1. use beforeSend when making the service call:

 $.ajax({
    url: weburl,
    beforeSend: function (xhr) { xhr.setRequestHeader ("Authorization", "Basic xxx"); }, 
    type: "POST",
    dataType: "xml",
    data: soapEnv,
    complete: processResult,
    contentType: "text/xml; charset=\"utf-8\""
   });

2. Basic authentication needs to be enabled on both ISA / TMG and IIS.
3. Enable keberos authentication for the published web app.
4. disable loopback check on the web server.

Sunday, June 10, 2012

Batch Import dtsx packages from folder to SSIS Store

FOR %i In (*.dtsx) DO dtutil /FILE "%i" /DESTSERVER LOCALHOST /COPY SQL;"\MYBI\Project Analysis\Initial Load\%~ni"

Tuesday, April 10, 2012

Authentication Problems by using of NTLM - Damir Dobric Posts - developers.de

While working at solving the infamous error message:
The HTTP request is unauthorized with client authentication scheme 'Ntlm'. The authentication header received from the server was 'NTLM'.
I came across the following article:


Authentication Problems by using of NTLM - Damir Dobric Posts - developers.de:

unfortunately, this isn't exactly the same issue, at the end, my problem was fixed by enabling cookies in app.config. it seems that when accessing a web service behind a ISA web farm and the infinity settings is set to by cookies, we will need to enable cookies in the app.config to be able to access the web service correctly, which in turn, is not hard to understand.

Friday, March 9, 2012

How to re-produce the "black screen with cursor before login" issue

I came into this two days ago and it has given me a really hard time to figure out a fix.
it turned out though, the cause of my issue was probably bit different: I have two video output from the computer connect to the same monitor, one of them via a KVM. this setup had been working for a while but somehow when I rebooted my PC this time, I only get this infamous black screen death symptom, it is only after I powered off one of the monitor that I figured out that's the cause...