Archive

Archive for August, 2009

SQL Server Database Mirroring

August 20, 2009 Leave a comment

SQL Server database mirroring is cool, especially in the areas of cost savings. However setting up a proper database mirroring is not as straightforward as it seems, although it is much much easier than doing a clustering.

Things you need to do

  1. Connection String: The databases are in 2 different instances, make sure you use the latest SQL Client with the failover partner parameter to ease the failover
    “Provider=SQLNCLI;Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;”
  2. SQL Accounts: Mirror the login accounts too!
    Microsoft has a KB (http://support.microsoft.com/kb/918992) which teaches you how to create a stored procedure which prints out a create user sql which you can run on the backup server to create an exact duplicate of the user account in the primary server.
    i’ve attached the script here for easy reference

    USE master
    GO
    IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL
    DROP PROCEDURE sp_hexadecimal
    GO
    CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
    AS
    DECLARE @charvalue varchar (514)
    DECLARE @i int
    DECLARE @length int
    DECLARE @hexstring char(16)
    SELECT @charvalue = ‘0x’
    SELECT @i = 1
    SELECT @length = DATALENGTH (@binvalue)
    SELECT @hexstring = ‘0123456789ABCDEF’
    WHILE (@i <= @length)
    BEGIN
    DECLARE @tempint int
    DECLARE @firstint int
    DECLARE @secondint int
    SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
    SELECT @firstint = FLOOR(@tempint/16)
    SELECT @secondint = @tempint – (@firstint*16)
    SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
    SELECT @i = @i + 1
    ENDSELECT @hexvalue = @charvalue
    GO

    IF OBJECT_ID (‘sp_help_revlogin’) IS NOT NULL
    DROP PROCEDURE sp_help_revlogin
    GO
    CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
    DECLARE @name sysname
    DECLARE @type varchar (1)
    DECLARE @hasaccess int
    DECLARE @denylogin int
    DECLARE @is_disabled int
    DECLARE @PWD_varbinary varbinary (256)
    DECLARE @PWD_string varchar (514)
    DECLARE @SID_varbinary varbinary (85)
    DECLARE @SID_string varchar (514)
    DECLARE @tmpstr varchar (1024)
    DECLARE @is_policy_checked varchar (3)
    DECLARE @is_expiration_checked varchar (3)

    DECLARE @defaultdb sysname

    IF (@login_name IS NULL)
    DECLARE login_curs CURSOR FOR

    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
    sys.server_principals p LEFT JOIN sys.syslogins l
    ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name <> ‘sa’
    ELSE
    DECLARE login_curs CURSOR FOR

    SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
    sys.server_principals p LEFT JOIN sys.syslogins l
    ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name = @login_name
    OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    IF (@@fetch_status = -1)
    BEGIN
    PRINT ‘No login(s) found.’
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN -1
    END
    SET @tmpstr = ‘/* sp_help_revlogin script ‘
    PRINT @tmpstr
    SET @tmpstr = ‘** Generated ‘ + CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’
    PRINT @tmpstr
    PRINT ”
    WHILE (@@fetch_status <> -1)
    BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
    PRINT ”
    SET @tmpstr = ‘– Login: ‘ + @name
    PRINT @tmpstr
    IF (@type IN ( ‘G’, ‘U’))
    BEGIN — NT authenticated account/group

    SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ FROM WINDOWS WITH DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’
    END
    ELSE BEGIN — SQL Server authentication
    — obtain password and sid
    SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, ‘PasswordHash’ ) AS varbinary (256) )
    EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

    — obtain password policy state
    SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name
    SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name

    SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ WITH PASSWORD = ‘ + @PWD_string + ‘ HASHED, SID = ‘ + @SID_string + ‘, DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’

    IF ( @is_policy_checked IS NOT NULL )
    BEGIN
    SET @tmpstr = @tmpstr + ‘, CHECK_POLICY = ‘ + @is_policy_checked
    END
    IF ( @is_expiration_checked IS NOT NULL )
    BEGIN
    SET @tmpstr = @tmpstr + ‘, CHECK_EXPIRATION = ‘ + @is_expiration_checked
    END
    END
    IF (@denylogin = 1)
    BEGIN — login is denied access
    SET @tmpstr = @tmpstr + ‘; DENY CONNECT SQL TO ‘ + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN — login exists but does not have access
    SET @tmpstr = @tmpstr + ‘; REVOKE CONNECT SQL TO ‘ + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN — login is disabled
    SET @tmpstr = @tmpstr + ‘; ALTER LOGIN ‘ + QUOTENAME( @name ) + ‘ DISABLE’
    END
    PRINT @tmpstr
    END

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
    END
    CLOSE login_curs
    DEALLOCATE login_curs
    RETURN 0
    GO

  3. Preparing mirror server: Make sure the DB is not in use!
    DB Mirroring requires you to restore a backup of the existing db on the mirror side!
  4. Preparing mirror DB: When restoring to the mirror site, set it to RESTORE WITH NORECOVERY
  5. Setup Mirroring: Pretty straightforward here, right click the db, click properties -> Mirror then click the “Configure Security” button to start the wizard
  6. Backup Strategy: Because database mirroring sets the mirror db to be in a perpentual Restoring state, your normal backup jobs won’t work on it. Note this is fixed in SQL Server 2008 maintenance plan, you just need to tick the ignore if not online check box
    Here’s a sample backup script to get you going

    use [master]
    go
    create procedure DailyBackup as
    begin
    declare @BackupPath varchar(300)
    declare @DBName varchar(256)
    declare @RecoveryModel int
    declare @DT varchar(100)
    declare @SQL varchar(4000)

    set @BackupPath=N’D:\backup\’

    DECLARE db_cursor CURSOR FOR
    select [name], [recovery_model]
    from sys.databases
    where state=0 and name not in (‘tempdb’,’model’)
    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @DBName, @RecoveryModel

    WHILE @@FETCH_STATUS = 0
    BEGIN
    if (@RecoveryModel <> 3)
    begin
    — Backup Transaction Log
    set @DT = CONVERT(CHAR(8), GETDATE(), 112) + ‘_’ + replace(CONVERT(CHAR(8), GETDATE(), 108), ‘:’, ‘_’)
    set @SQL = N’BACKUP LOG [‘ + @DBName + N’] TO DISK = N”’ + @BackupPath + @DBName + @DT + N’.trn” WITH NOFORMAT, INIT, NAME = N”’ + @DBName + N’ Transaction Log Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 10′
    print (@SQL)
    exec (@SQL)
    end

    –Backup Database
    set @DT = CONVERT(CHAR(8), GETDATE(), 112) + ‘_’ + replace(CONVERT(CHAR(8), GETDATE(), 108), ‘:’, ‘_’)
    set @SQL = N’BACKUP DATABASE [‘ + @DBName + N’] TO DISK = N”’ + @BackupPath + @DBName + @DT + N’.bak” WITH NOFORMAT, INIT, NAME = N”’ + @DBName + ‘ Full Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 10’
    print (@SQL)
    exec (@SQL)

    FETCH NEXT FROM db_cursor INTO @DBName, @RecoveryModel
    END

    CLOSE db_cursor
    DEALLOCATE db_cursor
    end

Categories: SQL Server

Installing Transmission on the DNS323

August 10, 2009 9 comments
  1. SSH to your NAS
  2. Create a folder called packages in your box
    mkdir /mnt/HD_a2/packages
  3. Goto the packages folder
    cd /mnt/HD_a2/packages
  4. Install uclibc using the 3 commands below
    wget http://www.inreto.de/dns323/fun-plug/0.5/packages/uclibc-0.9.29-7.tgz
    funpkg -i uclibc-0.9.29-7.tgz
    reboot
  5. Installing Transmission using the commands below
    wget http://kylek.is-a-geek.org:31337/files/curl-7.18.1.tgz
    wget http://kylek.is-a-geek.org:31337/files/Transmission-2.12-1.tgz
    funpkg -i curl-7.18.1.tgz
    funpkg -i Transmission-1.73-7.tgz
    Note if you have previously installed transmission before, you will need to kill the processes. To do so , run top to list all the running processes, the issue kill [pid] e.g kill 2377 for each of the transmission-daemon running, then run the following 2 commands(You can refer to http://www.transmissionbt.com/download.php for the latest transmission files)

    funpkg -I curl-7.18.1.tgz
    funpkg -U Transmission-1.73-7.tgz

    chmod a+x /ffp/start/transmission.sh
    reboot

  6. Updating clutch whitelist
    vi /mnt/HD_a2/.transmission-daemon/settings.jsonEdit the line with “rpc-whitelist” to something like “rpc-whitelist”: “127.0.0.1,192.*.*.*”, (assuming 192.*.*.* is your local ip subnet)
    Edit the line with “download-dir” to the download path for your torrents e.g download-dir”: “\/mnt\/HD_a2\/Torrent\/incomplete”
    Edit the line with “blocklist-enabled” to “blocklist-enabled”: 1
  7. Access clutch via its url http://%5Bip of your nas]:9091/
  8. Configuring Block Lists
    Goto the transmission block list folder (/mnt/HD_a2/.transmission-daemon/blocklists)
    Create a new file containing the following lines

    rm level1.gz -f
    #wget http://www.bluetack.co.uk/config/level1.gz
    wget http://download.m0k.org/transmission/files/level1.gz
    tar -x level1.gz

    Save the file
    chmod a+x update.sh (set it to be executable)
    ./update.sh
    (run the update script, this will take quite a while)

  9. If you want an auto update of the block list, just create a cronjob to run the update script

 

To get the latest transmission files, goto http://forum.dsmg600.info/t2719-%5BREL%5D-Transmission.html

<blockquote>
Categories: DNS323

Setting up funplug for DNS 323

August 10, 2009 Leave a comment

Things you will need:

  1. Fun plug: http://www.inreto.de/dns323/fun-plug/
  2. Telnet/SSH client

Steps

  1. download fun_plug and fun_plug.tgz to Volume_1 of the DNS 323
  2. Reboot
  3. Once the box is up, startup your telnet client and connect to the DNS 323 via its IP
  4. Disabling telnet and enabling SSH (run the commands in bold, normal text are comments)
    pwconv
    passwd (change root password)
    enter the new root password (twice)
    usermod -s /ffp/bin/sh root
    login (do a normal login)

    You should see something like:

    NAS login: root
    Password:
    Last login: Mon Aug 10 06:34:46 +0800 2009 on pts/2 from 192.168.1.2.
    No mail.
    root@NAS:~#

    IMPORTANT:  once you login successfully, save the password via store-passwd.sh

    Copying files to mtd1…
    Copying files to mtd2…
    Done.

    Now to enable ssh..
    chmod a+x /ffp/start/sshd.sh
    sh /ffp/start/sshd.sh start

    sh /ffp/start/sshd.sh start
    Generating public/private rsa1 key pair.
    Your identification has been saved in /ffp/etc/ssh/ssh_host_key.
    Your public key has been saved in /ffp/etc/ssh/ssh_host_key.pub.
    Starting /ffp/sbin/sshd

    Once you see this, fire up your SSH Client and connect to the DNS 323. if it works, disable telnet by issuing the following command
    chmod -x /ffp/start/telnetd.sh

  5. Installing bash

    cd /mnt/HD_a2
    mkdir packages
    cd packages/
    wget http://www.inreto.de/dns323/fun-plug/0.5/packages/bash-3.2-3.tgz
    funpkg -i bash-3.2-3.tgz
    ls -lF /ffp/bin/bash

Categories: DNS323