tag:blogger.com,1999:blog-82353550143502150302024-03-13T08:02:40.215-07:00Binary YogaCtr C, Ctr V and everything in betweenyogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.comBlogger76125tag:blogger.com,1999:blog-8235355014350215030.post-88380715331590213372014-05-09T07:45:00.000-07:002014-05-09T08:00:46.398-07:00Restore all SQL Server backups from a directory using Python<div dir="ltr" style="text-align: left;" trbidi="on">
Problem:<br />
<br />
I had to setup database mirroring for 50 databases on amazon EC2 servers which are in different availability zones. Mirroring needs to be initiated with a full and at least one transaction log restores. Restoring 50 databases manually is a cumbersome task. Some of these databases have multiple data files and the logical names are different. I thought it would be better if I could generate restore scripts for all backups in a backup folder and that is a perfect excuse to try Python at work. I used pyodbc to make connection to database. If you dont have pyodbc module, you might want to install it before trying the script. The script can handle databases with multiple data files. Default target data file/logfile directories can be declared. <br />
<br />
<pre class="brush:python">import os, sys
import pyodbc
backupdir = 'E:\\SQLData\\backups\\'
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=DBAdmin;UID=sa;PWD=sysadmin')
cursor = conn.cursor()
DataDir = 'D:\SQLData\Data'
LogDir = 'E:\SQLData\Logs'
def iter_islast(row):
it = iter(row)
prev = it.next()
for item in it:
yield prev, False
prev = item
yield prev, True
for root, dirs, files in os.walk(backupdir, topdown = False):
for name in files:
TempSQL = ""
f = os.path.join(root, name)
print "Restore Script for backupfile " + f + " below: "
print "==============================================="
cursor.execute("restore headeronly from disk = '%s';" % f)
rows = cursor.fetchall()
for row in rows:
TempSQL = "RESTORE DATABASE '" + row.DatabaseName + "' FROM DISK = '" + f + "' , WITH \n"
cursor.execute("restore filelistonly from disk = '%s';" % f)
rows2 = cursor.fetchall()
for row, islast in iter_islast(rows2):
if islast:
if row.Type == 'L':
TempSQL += " MOVE " + "'"+row.LogicalName+"' TO '" + LogDir + "', NORECOVERY \n"
else:
TempSQL += " MOVE " + "'"+row.LogicalName+"' TO '" + DataDir + "', NORECOVERY \n"
else:
if row.Type == 'L':
TempSQL += " MOVE " + "'"+row.LogicalName+"' TO '" + LogDir + "', \n"
else:
TempSQL += " MOVE " + "'"+row.LogicalName+"' TO '" + DataDir + "', \n"
print TempSQL
</pre>
<br />
The result will look like this. Just copy the commands and execute!<br />
<br />
<a href="http://3.bp.blogspot.com/-rzT9c9EwMik/U2zq-rdrcKI/AAAAAAAAVWI/wM2RJ8r4woU/s1600/restore-py.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-rzT9c9EwMik/U2zq-rdrcKI/AAAAAAAAVWI/wM2RJ8r4woU/s320/restore-py.jpg" height="203" width="400" /></a><br />
<br />
<br /></div>
yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-37537298329920334752014-04-24T03:28:00.001-07:002014-04-24T03:28:46.962-07:00Upload backups to Amazon S3 bucket using boto, PythonHere is a script to upload backups older than 1 day to S3 bucket. We would like to exclude transaction log backups so I used "if 'LOG' not in name" - you may change it accordingly. <br />
<br />
<pre class="brush:python">import os, sys, time
from boto.s3.connection import S3Connection
from boto.s3.key import Key
conn = S3Connection('YOURKEY','SECRETKEY')
bucket = conn.get_bucket('MyBeautifulBucket')
age = 86400 #1 day = 24*60*60
backupdir = 'O:\Backups\'
for root, dirs, files in os.walk(backupdir, topdown=False):
for name in files:
if 'LOG' not in name: #exclude copying transaction log backups
f = os.path.join(root, name)
now = time.time()
modified = os.stat(f).st_mtime
if modified > now - age:
k=Key(bucket)
k.key= 'mySubBucket/' + str(name)
k.set_contents_from_filename(f)
</pre>yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-51837192399356818852014-04-12T09:53:00.001-07:002014-04-12T09:54:12.050-07:00Run a query on multiple schemas postgresIf you have one huge database with multiple schemata in it, one for each customer (single database, multi tenant model), then from time to time you might have to run a query on all schemata, for example - getting a build number or user count etc. You could either write a shell script to bring all tenants from a tenant list and build sql queries to execute dynamically or you could use a function in postgres pl sql. Below is the function to get a count of users from all schemata, querying the table tUser:<br />
<br />
<pre class="brush:sql">-- create a record for the returned data
DROP FUNCTION IF EXISTS count_em_all ();
DROP TYPE IF EXISTS table_count;
CREATE TYPE table_count AS (table_name TEXT, num_rows INTEGER);
CREATE OR REPLACE FUNCTION count_em_all () RETURNS SETOF table_count AS
$$
DECLARE
the_count RECORD;
t_name RECORD;
r table_count%ROWTYPE;
BEGIN
-- list the schemas
FOR t_name IN
select distinct schemaname
from pg_catalog.pg_tables
where tablename = 'tUser' --and schemaname not ilike 'something|_you|_liketofilter%' escape '|'
LOOP
-- Run your query on each schema
FOR the_count IN EXECUTE 'SELECT COUNT(*) AS "count" FROM "'||t_name.schemaname||'".tUser'
LOOP
END LOOP;
r.table_name := t_name.schemaname;
r.num_rows := the_count.count;
RETURN NEXT r;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
select count_em_all();
</pre>yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-30317239235500761382014-04-12T06:12:00.001-07:002014-04-12T08:28:19.500-07:00SQL Server: Setup Mirroring Step by Step using TSQLIt is 2014 and people should be using AlwaysOn instead of mirroring. But some clients are still on SQL Server 2008R2 and I am about to setup mirroring for one such customer in a couple of days and posting these steps here won't hurt. There is a handy GUI wizard that lets you create mirroring pretty easily but if you have to mirror around 30 databases, using scripts is lot easier. <br />
<br />
Our setup is simple. SQL01 and SQL02 are our two servers - SQL01 is the principal and SQL02 is the mirror. We are not using a witness for this setup. <br />
<br />
<b>Steps on Mirror Server (SQL02)</b><br />
<br />
1. On SQL01, take a full backup and also a transaction log backup of the database you would like to mirror. (So your database would have to be in FULL recovery mode, otherwise no mirroring for you!)<br />
<br />
2. Restore database on SQL02<br />
<pre class= "brush:sql">Use master;
RESTORE DATABASE [MirrorMe] FROM DISK = N'K:\Backups\MirrorMe_041214.bak' WITH FILE = 1,
NORECOVERY, NOUNLOAD, STATS = 10;
RESTORE LOG [MirrorMe] FROM DISK = N'K:\Backups\MirrorMe_log.trn' WITH FILE = 1,
NORECOVERY, NOUNLOAD, STATS = 10;
GO
</pre><br />
3. Create an endpoint for mirror instance, SQL02. An <a href="http://technet.microsoft.com/en-us/library/ms179511.aspx">endpoint</a> is necessary for database mirroring/alwayson to recieve connections from other servers. <br />
<pre class= "brush:sql">CREATE ENDPOINT [endpoint_SQL02]
AUTHORIZATION [BINYOGA\yogirk]
STATE=STARTED
AS TCP (LISTENER_PORT = 7024, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4);
GO
</pre><br />
4. Create a login for using in mirroring setup and grant connect on the endpoint.<br />
<br />
<pre class = "brush:sql">USE [master]
CREATE LOGIN [BINYOGA\iMirror] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GRANT CONNECT on ENDPOINT::endpoint_SQL02 TO [BINYOGA\iMirror];
GO
</pre><br />
5. You might want to create and maintain same set of logins/users on both Principal and Mirror server. When you do a failover, you can simply fix orphaned users and your application can keep talking to the database. <br />
<br />
<pre class = "brush:sql">SELECT
'create login [' + p.name + '] ' +
case when p.type in('U','G') then 'from windows ' else '' end +
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
'sid = ' + master.sys.fn_varbintohexstr(l.sid) +
', check_expiration = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
'check_policy = ' + case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
FROM sys.server_principals p
LEFT JOIN sys.sql_logins l ON p.principal_id = l.principal_id
LEFT JOIN sys.credentials c ON l.credential_id = c.credential_id
WHERE p.type in('S','U','G')
AND p.name <> 'sa'
--AND p.name in ('myLogin1','myLogin2' );
</pre><br />
6. Add the databases to Mirroring. You'll notice that the endpoint is set to 7023 and that is yet to be created on our Primary server, SQL01. <br />
<br />
<pre class="brush:sql">USE master;
GO
--assuming that you have already restored these databases and at least one transaction log backup with NORECOVERY
ALTER DATABASE MirrorMe01 SET PARTNER = 'TCP://10.10.2.10:7023';
ALTER DATABASE MirrorMe02 SET PARTNER = 'TCP://10.10.2.10:7023';
ALTER DATABASE MirrorMe03 SET PARTNER = 'TCP://10.10.2.10:7023';
ALTER DATABASE MirrorMe04 SET PARTNER = 'TCP://10.10.2.10:7023';
</pre><br />
<b>Steps on Principal Server (SQL01)</b><br />
<br />
1. Now switch to Primary server, in this case SQL01 and create an endpoint<br />
<br />
<pre class="brush:sql">--you can check existing endpoints, if any using below query
SELECT name, role_desc, state_desc FROM sys.database_mirroring_endpoints;
SELECT name, port FROM sys.tcp_endpoints;
--create an endpoint
CREATE ENDPOINT [endpoint_SQL01]
AUTHORIZATION [BINYOGA\yogirk]
STATE=STARTED
AS TCP (LISTENER_PORT = 7023, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4);
GO
</pre><br />
2.Mirror login<br />
<pre class = "brush:sql">USE [master]
CREATE LOGIN [BINYOGA\iMirror] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GRANT CONNECT on ENDPOINT::endpoint_SQL02 TO [BINYOGA\iMirror];
GO
</pre><br />
<br />
3. Add databases to Mirroring<br />
<br />
<pre class="brush:sql">USE master;
ALTER DATABASE MirrorMe01 SET PARTNER = 'TCP://10.10.2.21:7024';
ALTER DATABASE MirrorMe02 SET PARTNER = 'TCP://10.10.2.21:7024';
ALTER DATABASE MirrorMe03 SET PARTNER = 'TCP://10.10.2.21:7024';
ALTER DATABASE MirrorMe04 SET PARTNER = 'TCP://10.10.2.21:7024';
GO
</pre><br />
Done, you should now see the databases in mirrored state. <br />
yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-67325613228862011582014-04-05T02:24:00.001-07:002014-04-05T07:56:19.282-07:00Shell script: Analyze schema (update statistics) - PostgreSQL<a href="http://www.postgresql.org/docs/9.1/static/sql-analyze.html">Analyze</a> in PostgreSQL is like sp_updatestats of SQL Server. Analyze gathers statistics about number of rows in a table and stores them in pg_statistics system catalog, which are used by query planner to create optimal query plans. Below is a shell script that is useful to update statistics on a schema. If you are on a single database and multi-tenant model, this is pretty useful to update the target tenant schema, without updating the entire database. <br />
<br />
Before executing this, you might want to add postgres user, password and host to your <a href="http://www.postgresql.org/docs/9.1/static/libpq-pgpass.html">pgpass</a> file, so that you can execute this shell script without being prompted for password. <br />
<br />
<pre class = "brush:bash">vi /root/.pgpass
#Add details to file hostname:port:database:username:password
*:*:*:dbadmin:SysAdminPwd
#set file permissions
chmod 600 /root/.pgpass
</pre><br />
Now the shell script: You can download script from <a href="https://www.dropbox.com/s/d00rfkkxzmyrvbl/analyze_schema.sh">here</a><br />
<br />
<pre class = "brush:bash">#!/bin/sh
#
#
# Title : analyze_schema.sh
# Description:
# Shell script
#
# Options:
# Schema name that requires a statistic update
#
# Requirements:
# Installed psql and added path to binary in PATH system variable.
#
# Examples:
# $HOME/analyze_schema.sh "MySchemaName"
#
SCHEMANAME="$1"
DBNAME="${2:-MyDatabase}"
DBUSER="${3:-dbadmin}"
DBPORT="${4:-7432}"
ANALYZESQL="anaylze_schema.sql"
ANALYZELOG="analyze_schema.log"
test=$(psql -A -1 -t -U $DBUSER -d $DBNAME -p $DBPORT -c "select * from pg_namespace where nspname='$SCHEMANAME'")
if [[ "$test" == "" ]]; then
echo "Schema \"$SCHEMANAME\" in db \"$DBNAME\" for dbuser \"$DBUSER\" and port \"$DBPORT\" does not exists"
exit 1
fi
GENANALYZESQL="
select 'ANALYZE VERBOSE \"'||current_database()||'\".\"'||t.schemaname||'\".\"'||t.tablename||'\"; ' as analyze_statement
from pg_tables t
where schemaname = '$SCHEMANAME'
order by t.tablename;
"
echo "Started generating analyze script for Schema \"$SCHEMANAME\" started at $(date +'%Y-%m-%d %T')"
psql -A -q -1 -t -p 7432 -U dbadmin -d MyDatabase -c "$GENANALYZESQL" > "$ANALYZESQL" 2>"$ANALYZELOG"
if ! [[ "$(cat $ANALYZELOG)" == "" ]]; then
echo "Generation of analyze script for schema \"$SCHEMANAME\" in db \"$DBNAME\" for dbuser \"$DBUSER\" and port \"$DBPORT\" had error(s):"
cat "$ANALYZELOG"
exit 1
fi
echo "Analyzing schema \"$SCHEMANAME\" started at $(date +'%Y-%m-%d %T')"
psql -A -q -1 -t -p 7432 -U dbadmin -d MyDatabase -f "$ANALYZESQL" 2>&1 | /usr/sbin/prepend_datetime.sh >> "$ANALYZELOG"
echo "Analyzing schema \"$SCHEMANAME\" ended at $(date +'%Y-%m-%d %T')"
echo "Analyzing schema sql script is in file $ANALYZESQL. Analyze log is below:"
cat "$ANALYZELOG"
</pre>yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-79486262729786470632014-04-01T16:26:00.000-07:002014-04-01T16:27:26.437-07:00Delete files older than x days - Python, bash etcOur postgres logs directory had logs from almost 6 months and we only needed 5 days worth of data. Its pretty straightforward to remove stuff using bash:<br />
<br />
<pre class="brush:ps">find /db/logs/archive_data/* -type f -mtime +5 | xargs rm
</pre><br />
But <b>find</b> sometimes fails with the error "too many arguments" when it has to deal with too many files. <br />
<br />
Here is another way in Python:<br />
<br />
Example usage: Save this as cleanup.py and run - <i>python cleanup.py /db/data 5</i><br />
<pre class="brush:python">from sys import argv
import os, time
script, dir, age = argv
print "Searching directory %s for file older than %s day(s)" % (str(argv[1]), str(argv[2]))
#convert age to sec. 1 day = 24*60*60
age = int(age)*86400
for file in os.listdir(dir):
now = time.time()
filepath = os.path.join(dir, file)
modified = os.stat(filepath).st_mtime
if modified < now - age:
if os.path.isfile(filepath):
os.remove(filepath)
print 'Deleted: %s (%s)' % (file, modified)
</pre>
<br />
yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-42779922909024198342014-03-30T07:34:00.000-07:002014-03-30T07:46:53.010-07:00SQL Server Database Mirroring: automating mirror failovers<div dir="ltr" style="text-align: left;" trbidi="on"><div dir="ltr" style="text-align: left;" trbidi="on"><b>Problem</b>:<br />
<br />
You have a bunch of databases on a production server and you decided to go with SQL Server mirroring as a DR solution. Your IT dept, in its infinite wisdom, thought that it is a waste of resources to have a witness server and you were asked to somehow adjust with a Principal and a Mirror server. Your databases are critical and though the option of automated failovers is ruled out due to lack of witness, you would like to put process in place such that you are always battle ready to deal with a possible disaster and recover quickly. The solution should be designed in such a way that even the most junior of your DBAs should be able to handle it. <br />
<br />
<b>Solution</b>: <br />
<br />
For the sake of this explanation, lets us assume my production server is <b>YIN </b>and Mirror server is <b>YANG. </b>So what happens when a disaster occurs? Assuming that you already have mirroring setup between <b>YIN </b>and <b>YANG</b> and it is functional, you might have to do the following after a disaster. <br />
<br />
<ul style="text-align: left;"><li>When <b>YIN</b> goes down, databases on <b>YANG</b> server will be in restoring/disconnected state. You'll bring all those databases online by performing a forced failover with allow data loss.</li>
<li>When the failover is done, you might want to fix orphaned users</li>
<li>You now make necessary changes to whichever mechanism your application uses to connect to the databases to point to the new server. In our case, we store database names and connection strings in a SQL that sits on a well maintained cluster (lets call this <b>MOTHERSHIP)</b>, so we update those connection strings with IP address of <b>YANG. </b></li>
<li>We have lot of business specific SQL Agent jobs running on our production servers apart from regular maint jobs. These jobs exist both on <b>YIN </b>and <b>YANG. </b>The jobs are kept in disabled state on mirror server. Now that we had a disaster and we failover to the evil <b>YANG, </b>we might have to enable these jobs. </li>
<li>Send a notification that failover of some sort occurred. </li>
</ul><div><b>What Next?</b></div><div><b><br />
</b></div><div>Ok, you did all of that and we were back in business with minimal downtime. Our former Principal server was resurrected and is back online. <b>YANG </b>is currently the Principal and you might want to add <b>YIN </b>back to mirroring setup as the new mirror. After a couple of days, you are sure that <b>YIN </b>is now stable and it should be primary because it probably has better resources compared to <b>YANG. </b>You might have to do a soft (planned) failover. So following are various scenarios that are possible in a setup like this:</div><div><ul style="text-align: left;"><li>Planned Failover from Yin to Yang. Stop jobs on Yin, enable jobs on Yang, fix users, update connection strings</li>
<li>Planned Failover from Yang to Yin etc.,</li>
<li>Forced Failover on Yin</li>
<li>Forced Failover on Yang</li>
<li>Resume Mirroring on Yin</li>
<li>Resume Mirroring on Yang</li>
</ul><div>Enough of theory. Lets get our hands dirty. Creating batch scripts for each of these scenarios appeared like the easiest simplest (if not elegant) solution to me. The actual logic that performs a failover can be a stored procedures and the batch scripts can be stored on a server which is highly available. </div></div><div><br />
</div><div>Create these stored procedures on <b>both Principal and Mirror Servers</b>.</div><div><ul style="text-align: left;"><li><a href="https://dl.dropboxusercontent.com/u/2928961/blog/Mirroring/usp_PlannedFailover.sql">usp_PlannedFailover</a></li>
<li><a href="https://dl.dropboxusercontent.com/u/2928961/blog/Mirroring/usp_Forcedfailover.sql">usp_ForcedFailover</a></li>
<li><a href="https://dl.dropboxusercontent.com/u/2928961/blog/Mirroring/usp_ResumeMirroring.sql">usp_ResumeMirroring</a></li>
</ul><div> Example procedure below. Click links above for other sps. </div></div><div><br />
</div><div><pre class="brush:sql">USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_ForcedFailover]
AS
/* stored procedure for doing a forced failover of all database to YANG
example usage: EXEC master.dbo.usp_MirrorFailover
*/
DECLARE @sql NVARCHAR(300)
,@maxid INT
,@i INT
,@dbname SYSNAME
DECLARE @MirrorDBs TABLE (
[dbid] INT identity(1, 1) NOT NULL PRIMARY KEY
,dbname SYSNAME NOT NULL
)
SET NOCOUNT ON
INSERT INTO @MirrorDBs (dbname)
SELECT DB_NAME(database_id)
FROM sys.database_mirroring
WHERE mirroring_role = 2 -- Mirror
AND mirroring_state IN (0,1) -- Disconnected or Suspended
AND mirroring_safety_level = 2
--select * from @MirrorDBs
SELECT @maxid = max([dbid])
,@i = 1
FROM @MirrorDBs;
WHILE @i <= @maxid
BEGIN
SELECT @dbname = dbname
FROM @MirrorDBs
WHERE [dbid] = @i
SET @sql = 'ALTER DATABASE ' + QUOTENAME(@dbname) + ' SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;'
PRINT @sql
EXEC (@sql);
SET @i = @i + 1
END
SET NOCOUNT OFF
</pre><pre class="brush:sql"></pre><pre class="brush:sql"></pre></div></div><br />
<br />
Next, create scripts for enabling/disabling jobs:<br />
<pre class="brush:sql">declare @sql nvarchar(max) = '';
select
@sql += N'exec msdb.dbo.sp_update_job @job_name = ''' + name + N''', @enabled = 1;
' from msdb.dbo.sysjobs
where enabled = 0
order by name;
print @sql;
exec (@sql);
</pre><br />
</div><br />
Create scripts for fixing users<br />
<br />
<pre class="brush:sql">DECLARE @sql NVARCHAR(300)
,@maxid INT
,@i INT
,@dbname SYSNAME
DECLARE @Mirrors TABLE (
[dbid] INT identity(1, 1) NOT NULL PRIMARY KEY
,dbname SYSNAME NOT NULL
)
SET NOCOUNT ON
INSERT INTO @mirrors (dbname)
SELECT DB_NAME(database_id)
FROM sys.database_mirroring
WHERE mirroring_role = 1 -- Principal
AND mirroring_state = 4 -- Synchronized
AND mirroring_safety_level = 2
--select * from @mirrors
SELECT @maxid = max([dbid])
,@i = 1
FROM @mirrors;
WHILE @i <= @maxid
BEGIN
SELECT @dbname = dbname
FROM @mirrors
WHERE [dbid] = @i
SET @sql = 'USE ' + QUOTENAME(@dbname) +
' EXEC sp_change_users_login ''Auto_Fix'', ''myUser1'';
EXEC sp_change_users_login ''Auto_Fix'', ''myUser2'';
'
PRINT @sql
EXEC (@sql);
SET @i = @i + 1
END
</pre>
Update connection strings on <b>MOTHERSHIP</b> server
<pre class="brush:sql">USE ConnectionDB
GO
UPDATE COMPANY SET ConnectionString='Data Source=10.10.2.10;User ID=fancyuser;Password=fancypass;Max Pool Size=200'
WHERE ConnectionString='Data Source=10.10.2.11;User ID=fancyuser;Password=fancypass;Max Pool Size=200';
</pre>Finally, the batch file that brings all these together:
<pre class="brush:ps">@echo off
goto :startbatch
Description
=============================================================
This batch file is used for
1. Performing a soft failover of YIN to YANG. YANG will become the primary
2. Fix Database users
3. Update connection strings in connectiondb database post failover
4. Disable jobs on YIN
5. Enable jobs on YANG
6. Send a notification email
===============================================================
:startbatch
set source_server=10.10.2.10
set mothership_server=10.10.2.23
set mirror_server=10.10.2.11
set serverlogin=fancyuser
set serverpass=fancypass
set connectiondb=connectiondb
set home_dir=d:\mirrorme\Failover_YIN_TO_YANG
set failover_script=%home_dir%\failover.sql
set failover_out=%home_dir%\failover_out.txt
set fixusers_script=%home_dir%\fixusers.sql
set fixusers_out=%home_dir%\fixusers_out.txt
set update_connectiondb=%home_dir%\update_connectiondb.sql
set update_connectiondb_out=%home_dir%\update_connectiondb_out.txt
set disablejobs_script=%home_dir%\disablejobs.sql
set disablejobs_out=%home_dir%\disablejobs_out.txt
set enablejobs_script=%home_dir%\enablejobs.sql
set enablejobs_out=%home_dir%\enablejobs_out.txt
REM Failover from YIN to YANG
sqlcmd -S tcp:%source_server%,1433 -d master -U %serverlogin% -P %serverpass% -s "," -W -h -1 -i %failover_script% -o %failover_out%
REM Run fix users script on YANG to deal with orphan users
sqlcmd -S tcp:%mirror_server%,1433 -d master -U %serverlogin% -P %serverpass% -s "," -W -h -1 -i %fixusers_script% -o %fixusers_out%
REM update connectiondb database with new connection string
sqlcmd -S tcp:%connectiondb_server%,1433 -d %connectiondb% -U %serverlogin% -P %serverpass% -s "," -W -h -1 -i %update_connectiondb% -o %update_connectiondb_out%
REM disable jobs on YIN because we failedover to YANG
sqlcmd -S tcp:%source_server%,1433 -d master -U %serverlogin% -P %serverpass% -s "," -W -h -1 -i %disablejobs_script% -o %disablejobs_out%
REM Enable jobs on YANG since it is the new primary
sqlcmd -S tcp:%mirror_server%,1433 -d master -U %serverlogin% -P %serverpass% -s "," -W -h -1 -i %enablejobs_script% -o %enablejobs_out%
REM send emails on completion
C:\work\tool\sendemail -t allcoolsdudes@Nirvanainc.com -cc -u "Mirroring failover of YIN to YANG" -m "Database Mirroring Failover of YIN to YANG has been triggered and completed. SQL Agent jobs on YIN were disabled and jobs on YANG were enabled. Check logs attached. " -s 192.24.2.110:25 -f "LordOfServers@NirvanaInc.com" -a %failover_out% -a %fixusers_out% -a %update_connectiondb_out% -a %disablejobs_out% -a %enablejobs_out%
</pre><br />
To do:<br />
<br />
Update this post with scripts for all other possible failover scenarios: yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-6557097508812317642014-03-20T14:46:00.000-07:002014-03-20T15:20:14.559-07:00Batch Script: SQL Server Database Backup, Refresh, sanitize, run script and upload csv to sftpOne of the customers requested the following:<br />
<br />
Backup a production database and restored it on a dev server. Sanitize the refreshed database and run a script and upload results as a csv file to sftp site. I know this can be done with <code>SSIS</code>, but I thought a batch job would be simpler to manage and troubleshoot. <br />
<br />
First, create a directory for this job and add the following files to it. I will call mine D:\NirvanaInc. We are going to use the following tools to get this task done. <br />
<br />
<ul><li><a href="http://technet.microsoft.com/en-us/library/ms170207%28v=sql.105%29.aspx">SQLCMD</a> - To run backup/restore commands, sanitize scripts etc</li>
<li><a href="http://the.earth.li/~sgtatham/putty/0.52/htmldoc/Chapter6.html">PSFTP</a> - To upload files to an SFTP site</li>
<li><a href="http://www.howtogeek.com/125045/how-to-easily-send-emails-from-the-windows-task-scheduler/">sendemail</a> - To send notification emails</li>
</ul><br />
Now that we are ready, lets start building the scripts we will use. The idea is to create one batch file which will call and run various scripts that do backup, restore, cleanup etc. this approach will make it incredibly easier to maintain and make changes to the job in future, if needed. <br />
<br />
<b>backup.sql</b><br />
<br />
This is simple backup script:<br />
<br />
<pre class="brush:sql">USE master;
DECLARE @startdate VARCHAR(100);
SET @startdate = 'backup start: ' + convert(VARCHAR(32), getdate(), 121);
PRINT @startdate;
BACKUP DATABASE [NirvanaProd] TO DISK = N'R:\Backups\NirvanaProd.bak'
WITH COPY_ONLY
,NOFORMAT
,INIT
,NAME = N'NirvanaProd-Full Database Backup'
,SKIP
,NOREWIND
,NOUNLOAD
,COMPRESSION
,STATS = 10
GO
DECLARE @backupSetId AS INT
SELECT @backupSetId = position
FROM msdb.dbo.backupset
WHERE database_name = N'NirvanaProd.bak'
AND backup_set_id = (
SELECT max(backup_set_id)
FROM msdb..backupset
WHERE database_name = N'NirvanaProd.bak'
)
IF @backupSetId IS NULL
BEGIN
RAISERROR (
N'Verify failed. Backup information for database ''NirvanaProd'' not found.'
,16
,1
)
END
RESTORE VERIFYONLY
FROM DISK = N'R:\Backups\NirvanaProd.bak'
WITH FILE = @backupSetId
,NOUNLOAD
,NOREWIND
GO
DECLARE @enddate VARCHAR(100);
SET @enddate = 'backup end: ' + convert(VARCHAR(32), getdate(), 121);
PRINT @enddate;
</pre><br />
<b>Restore.sql</b><br />
<br />
<pre class="brush:sql">-- restore backup on target server, SQL:
USE master;
-- wait for start time
DECLARE @now DATETIME
,@targettime DATETIME;
-- targettime is set to 02:00:00 AM, If the backup is done sooner, it will wait till 2 AM to kick off restore
SET @targettime = substring(CONVERT(VARCHAR(39), GETDATE(), 121), 1, 10) + ' 02:00:00';
SET @now = GETDATE();
IF (@targettime > @now)
BEGIN
WAITFOR TIME @targettime;
END;
DECLARE @startdate VARCHAR(100);
SET @startdate = 'db restore start: ' + convert(VARCHAR(32), getdate(), 121);
PRINT @startdate;
IF EXISTS (
SELECT *
FROM sys.databases
WHERE NAME = 'NirvanaDev'
)
BEGIN
ALTER DATABASE [NirvanaDev] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END
RESTORE DATABASE [NirvanaDev]
FROM DISK = N'R:\Remotebackups\Nirvanaprod.bak'
WITH FILE = 1
,MOVE N'FinancialSoftwareSystems_Data' TO N'D:\SQLData\NirvanaDev.mdf'
,MOVE N'FinancialSoftwareSystems_log' TO N'D:\SQLData\nirvanaDev.ldf'
,NOUNLOAD
,REPLACE
,STATS = 10
GO
ALTER DATABASE [NirvanaDev] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO
DECLARE @enddate VARCHAR(100);
SET @enddate = 'db users restore end: ' + convert(VARCHAR(32), getdate(), 121);
PRINT @enddate;
</pre><br />
I will leave out sanitize and grant permissions script because they are specific to your environment and you should build your scripts accordingly. Next up is running the a report script on target database and uploading the output as CSV to SFTP site. I chose PSFTP because of its ease of use. <br />
<br />
<b>run_psftp.bat</b><br />
<br />
<pre class = "brush:ps">echo lcd D:\NirvanaInc > psqftp.bat
echu lpwd >> psftp.bat
echo put D:\NirvanaInc\sql_results.csv >> psftp.bat
echo quit >> psftp.bat
c:\sysutil\psftp mysftpuser@sftp.nirvanainc.com -pw myFancyPwd -b psftp.bat -v
</pre><br />
By now, you should have the following files on your D:\NirvanaInc folder:<br />
backup.sql<br />
restore.sql<br />
sanitize.sql<br />
grant_script.sql<br />
run.sql<br />
run_psftp.bat<br />
<br />
<br />
Finally, the main batch file:<br />
<br />
<pre class="brush:ps">@echo off
goto :startbatch
Description
=============================================================
This batch file is used for
1. Taking a backup of NirvanaProd database
2. Copying backup file to a Dev db server
3. Restore database on Dev Environment
4. Sanitizing Database
5. Creating user and granting permission
6. Run a script
7. Upload results to sftp
===============================================================
:startbatch
REM set variablesa
set source_server=10.10.20.21
set target_server=10.10.20.22
set source_db=NirvanaProd
set target_db=NirvanaDev
set home_dir=D:\NirvanaInc\NirvanaProd
set backup_script=%home_dir%\backup.sql
set backup_out=%home_dir%\backup_out.txt
set copy_out=%home_dir%\copy_out.txt
set restore_script=%home_dir%\restore.sql
set restore_out=%home_dir%\restore_out.txt
set sanitize_script=%home_dir%\sanitize.sql
set sanitize_out=%home_dir%\sanitize_out.txt
set grant_script=%home_dir%\grant_script.sql
set grant_out=%home_dir%\grant_out.txt
set run_script=%home_dir%\run.sql
set run_script_out=%home_dir%\sql_results.csv
set serverlogin=mylogin
set serverpass=fancypassword123
REM backup your production database
sqlcmd -S tcp:%source_server%,1433 -d master -U %serverlogin% -P %serverpass% -s "," -W -h -1 -i %backup_script% -o %backup_out%
REM copy backup from source server to target server, log is in %copy_out%
echo copy of backup file started at: %date% %time% > %copy_out%
copy /y \\%source_server%\R$\Backups\%source_db%.bak \\%target_server%\R$\RemoteBackups 1>>%copy_out% 2>&1
echo copying of the backup file completed at: %date% %time% >> %copy_out%
REM Restore backup on the target server
sqlcmd -S tcp:%target_server%,1433 -d master -U %serverlogin% -P %serverpass% -s "," -W -h -1 -i %restore_script% -o %restore_out%
REM Sanitize database and create user, grant permissions
sqlcmd -S tcp:%target_server%,1433 -d %target_db% -U %serverlogin% -P %serverpass% -s "," -W -h -1 -i %sanitize_script% -o %sanitize_out%
sqlcmd -S tcp:%target_server%,1433 -d %target_db% -U %serverlogin% -P %serverpass% -s "," -W -h -1 -i %grant_script% -o %grant_out%
REM Run sql script
sqlcmd -S tcp:%target_server%,1433 -d %target_db% -U %serverlogin% -P %serverpass% -s "," -W -h -1 -i %run_script% -o %run_script_out%
REM Upload run_sql.csv results to SFTP
call %home_dir%/run_psftp.bat
REM send emails on completion
C:\sysutil\sendemail -t alldba@nirvanainc.com -cc ImeMyself@nirvanainc.com -u "%target_db% refresh" -m "%source_db% backup from server %source_server% is restored on %target_server% as db %target_db% . The script ran successfully and the output was uploaded to SFTP" -s 192.178.6.221:25 -f "Gandalph@NirvanaInc.com" -a %backup_out% -a %restore_out% -a %sanitize_out% -a %grant_out%
</pre><br />
have fun! :)<br />
yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-10035683131065736882014-03-08T00:25:00.000-08:002014-03-20T14:46:42.098-07:00Postgres en_US - UTF 8 Collation on WindowsI was restoring a schema today on a stage server and ran into a collation error. The source of schema backup was Postgres 9.1, CentOS and the destination was Postgresq 9.1, windows server 2008 R2. <br />
<br />
<blockquote>pg_restore: [archiver (db)] Error while PROCESSING TOC:<br />
pg_restore: [archiver (db)] Error from TOC entry 2125939; 1259 50334466 INDEX ix_rd_vendorlist_facts_defaultsort_en_us <br />
pg_restore: [archiver (db)] could not execute query: ERROR: collation "public.en_US" for encoding "UTF8" does not exist</blockquote><br />
After reading a related <a href="http://stackoverflow.com/questions/6579621/lc-collate-and-lc-ctype-suport-for-utf-8-in-postgresql">stackoverflow</a> thread I realized en_US locale is Linux specific and the windows equivalent is 'English_United States.1252'. I created these collations on template database and the user database I was trying to restore this schema on and the restore finally worked. <br />
<br />
<pre class="brush:sql">CREATE COLLATION pg_catalog."en_US" (
LC_COLLATE = 'English_United States.1252'
,LC_CTYPE = 'English_United States.1252'
);
CREATE COLLATION pg_catalog."fr_CA" (
LC_COLLATE = 'French_Canada.1252'
,LC_CTYPE = 'French_Canada.1252'
);
CREATE COLLATION pg_catalog."pt_BR" (
LC_COLLATE = 'Portuguese_Brazil.1252'
,LC_CTYPE = 'Portuguese_Brazil.1252'
);
-- add public collations
CREATE COLLATION PUBLIC."en_US" (
LC_COLLATE = 'English_United States.1252'
,LC_CTYPE = 'English_United States.1252'
);
CREATE COLLATION PUBLIC."fr_CA" (
LC_COLLATE = 'French_Canada.1252'
,LC_CTYPE = 'French_Canada.1252'
);
CREATE COLLATION PUBLIC."pt_BR" (
LC_COLLATE = 'Portuguese_Brazil.1252'
,LC_CTYPE = 'Portuguese_Brazil.1252'
);
</pre>yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com1tag:blogger.com,1999:blog-8235355014350215030.post-62502710661338634372013-10-01T00:51:00.002-07:002013-10-18T07:02:32.937-07:00Setup: Python/Flask dev environment on windows using virtualenv and pip<a href="http://chocolatey.org/">Chacolatey</a>is a sudo apt-get for the windows crowd and its awesome! Chacolatey makes it incredibly easy to setup multiple dev environments for Python/flask/django which is otherwise a multi-step process (installing setuptools, pip, virtualenv etc). Here are the steps.<br />
<br />
<b>Install Chacolatey</b><br />
<br />
Launch a powershell prompt using "Run As Admin" and set execution policy to unrestricted. <br />
<br />
<pre class = "brush:ps">Set-ExecutionPolicy Unrestricted
</pre><br />
And run the following command in the same window to install chacolatey<br />
<br />
<pre class = "brush:ps">iex ((new-object net.webclient).DownloadString('https://chocolatey.org/install.ps1'))
</pre><br />
Once its installed, you'll have <b>csint</b> added to your path and it is the equivalent of apt-get. <br />
<br />
<b>Install Python</b><br />
<br />
If you don't already have Python installed, run the following in a powershell/cmd<br />
<br />
<pre class = "brush:ps">cinst Python
</pre><br />
This will install Python 2.7 at C:\Python27. We need a few more things to setup a proper dev environment. Instead of installing flask package directly to the main Python installation, it can be separated using <b>virtualenv</b>. If you are developing an app on django and another using flask and would like to keep these environments separate, <b>virtualenv</b> is the way to go. <br />
<br />
<b>Install Pip</b><br />
<br />
<pre class = "brush:ps">cinst pip
</pre><br />
<pre class = "brush:ps">pip install virtualenvwrapper
pip install virtualenvwrapper-powershell
</pre><br />
This will add virtualenv.exe to your system path and you can create multiple Python dev environments using this. Lets call our flask app <b>FlaskApp</b>. Lets create a directory for this app on D drive. Create a virtual env in that folder. <br />
<br />
<pre class = "brush:ps">virtualenv FlaskApp
</pre><br />
This will create a Python instance in FlaskApp folder. Now activate the virtual environment we just created. cd to FlaskApp/Scripts and run activate<br />
<br />
<pre class = "brush:ps">cd FlaskApp\Scripts\
activate
#your command line should change to (FlaskApp) D:\FlaskApp\Scripts.
pip install flask
# To come out of (FlaskApp), simply type 'deactivate'
</pre><br />
Now you are all set. We created a virtual environment called FlaskApp and installed flask package. Lets see if it works. Create a sample app. Create a hello.py with below <br />
<br />
<pre class = "brush:py">#!C:\flaskapp\Scripts
-- the above line is to let the computer run python.exe from our VirtualEnv not the system wide Python
from flask import Flask
app = Flask(__name__)
@app.route('/')
def hello_world():
return 'Hello World!'
if __name__ == '__main__':
app.run()
</pre><br />
Run<br />
<br />
Python hello.py<br />
<br />
It should run the web server on 127.0.0.1:5000. <br />
<br />
<br />
<br />
<br />
<br />
<br />
yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-31770790952377047262013-07-25T05:53:00.001-07:002013-07-26T10:44:16.131-07:00Virtual Lab: Multi node Hadoop cluster on windows (HortonWorks, HDInsight)<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
Microsoft recently <a href="http://blogs.technet.com/b/serverandtools/archive/2012/10/24/microsoft-hdinsight-server-for-windows-and-windows-azure-hdinsight-service-announced.aspx" target="_blank">announced HDInsight</a>, built on top of <a href="http://hortonworks.com/products/hdp/" target="_blank">HortonWorks DataPlatform</a> (a distribution of Hadoop similar to Cloudera). There is a <a href="http://social.technet.microsoft.com/wiki/contents/articles/14141.installing-the-developer-preview-of-hdinsight-services-on-windows.aspx" target="_blank">developer preview of HDInsight</a> available as a free download from Microsoft, which is very easy to setup. However, the <a href="http://social.msdn.microsoft.com/Forums/windowsazure/en-US/a30e726a-c907-4642-a8da-4188b4c60c6d/installaling-hdinsight-on-multi-nodes" target="_blank">developer preview is a single box solution</a> and it is not possible to simulate a real world deployment with multi node clusters. You can create a 4 node hadoop cluster on Azure with a few clicks but it is <a href="http://www.windowsazure.com/en-us/pricing/details/hdinsight/" target="_blank">prohibitively costly</a> (and the cluster will be shutdown after free tier usage, if your account is a trial one). This is a step by step guide to setup a multi node cluster for free on your laptop using HortonWorks Data Platform. HDInsight is actually built on top of Hortonworks solution, so there won’t be many differences apart from some usability features and nice looking clients. So let’s get started with the setup:</div>
<div style="text-align: justify;">
<br /></div>
<h3 style="text-align: justify;">
What will we build?</h3>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div style="text-align: justify;">
We will build a Virtualbox Base VM with all the necessary software installed, sysprep it and spin linked clones from the base for better use of available resources, We will build a domain controller and add 4 VMs to the domain. You can achieve similar setup using HyperV too, using differential disks. We should end up with something like this:</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<strong>Domain: TESLA.COM</strong></div>
<div style="text-align: justify;">
<strong><br /></strong>
</div>
<table border="0" cellpadding="2" cellspacing="0" style="text-align: justify; width: 657px;"><tbody>
<tr> <td valign="top" width="118"><div style="text-align: left;">
<strong>Name of the VM</strong></div>
</td> <td valign="top" width="334"><div style="text-align: left;">
<strong>Description</strong><br />
<strong><br /></strong></div>
</td> <td valign="top" width="91"><div style="text-align: left;">
<strong>IP Address</strong></div>
</td> <td valign="top" width="112"><div style="text-align: left;">
RAM</div>
</td></tr>
<tr> <td valign="top" width="118">HDDC</td> <td valign="top" width="334">Domain Controller and Database host for Metastore</td> <td valign="top" width="91">192.168.80.1</td> <td valign="top" width="112">1024</td></tr>
<tr> <td valign="top" width="118">HDMaster</td> <td valign="top" width="334">Name node, Secondary Name node, Hive, Oozie, Templeton</td> <td valign="top" width="91">192.168.80.2</td> <td valign="top" width="112">2048</td></tr>
<tr> <td valign="top" width="118">HDSlave01</td> <td valign="top" width="334">Slave/Data Node</td> <td valign="top" width="91">192.168.80.3</td> <td valign="top" width="112">2048</td></tr>
<tr> <td valign="top" width="118">HDSlave02</td> <td valign="top" width="334">Slave/Data Node</td> <td valign="top" width="91">192.168.80.4</td> <td valign="top" width="112">2048</td></tr>
<tr> <td valign="top" width="118">HDSlave03</td> <td valign="top" width="334">Slave/Data Node</td> <td valign="top" width="91">192.168.80.5</td> <td valign="top" width="112">1024<br />
<br /></td></tr>
</tbody></table>
<div style="text-align: justify;">
In prod environments, HIVE, OOZIE and TEMPLETON usually have separate servers running the services, but for the sake of simplicity, I chose to run all of them on a single VM. If you don’t have enough resources to run 3 data /slave nodes, then you can go with 2. Usually 2 slave nodes and 1 master is necessary to get a feel of real Hadoop Cluster. </div>
<div style="text-align: justify;">
<br /></div>
<h3 style="text-align: justify;">
System Requirements:</h3>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div style="text-align: justify;">
You need a decent laptop/desktop with more than 12 GB of RAM and a decent processor like core i5. If you have less RAM, you might want to run a bare minimum of 3 node cluster (1 master and 2 slaves) and adjust the available RAM accordingly. For this demo, check the way I assigned RAM in the table above. You can get away with assigning lesser memory to data nodes. </div>
<div style="text-align: justify;">
<br /></div>
<h3 style="text-align: justify;">
Software Requirements:</h3>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<table border="0" cellpadding="2" cellspacing="0" style="text-align: justify; width: 626px;"><tbody>
<tr> <td valign="top" width="624"><a href="http://www.microsoft.com/en-in/download/details.aspx?id=11093" target="_blank">Windows Server 2008 R2 or Windows Server 2012 Evaluation edition, 64 bit</a></td></tr>
<tr> <td valign="top" width="624"><a href="http://www.microsoft.com/en-in/download/details.aspx?id=17718" target="_blank">Microsoft .NET Framework 4.0</a></td></tr>
<tr> <td valign="top" width="624"><a href="http://www.microsoft.com/en-in/download/details.aspx?id=14632" target="_blank">Microsoft Visual C++ 2010 redistributable, 64 bit</a></td></tr>
<tr> <td valign="top" width="624"><a href="http://www.oracle.com/technetwork/java/javase/downloads/jdk-6u31-download-1501634.html" target="_blank">Java JDK 6u31 or higher</a></td></tr>
<tr> <td valign="top" width="624"><a href="http://www.python.org/getit/" target="_blank">Python 2.7</a></td></tr>
<tr> <td valign="top" width="624"><a href="http://www.microsoft.com/en-in/download/details.aspx?id=8158" target="_blank">Microsoft SQL Server 2008 R2</a> (if you would like to use it for Metastore)</td></tr>
<tr> <td valign="top" width="624"><a href="http://www.microsoft.com/en-in/download/details.aspx?id=21599" target="_blank">Microsoft SQL Server JDBC Driver</a></td></tr>
<tr> <td valign="top" width="624"><a href="https://www.virtualbox.org/wiki/Downloads" target="_blank">VirtualBox for virtualization</a> (Or HyperV, if you prefer)</td></tr>
<tr> <td valign="top" width="624"><a href="http://hortonworks.com/partners/microsoft/" target="_blank">HortonWorks DataPlatform for Windows</a><br />
<br /></td></tr>
</tbody></table>
<h3 style="text-align: justify;">
<strong>Building a Base VM</strong>:</h3>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div style="text-align: justify;">
This guide assumes that you are using VirtualBox for virtualization. The steps are similar even if you are using HyperV with minor differences like creating differencing disks before creating VM clones from it. Hadoop cluster setup involves installing a bunch of software and setting environment variables on each server. Creating a base VM and spinning linked clones from it makes it remarkably easier and saves lot of time and it helps creating more VMs within the available limits of your disk space compared to stand alone VMs. </div>
<div style="text-align: justify;">
1. Download and Install VirtualBox</div>
<div style="text-align: justify;">
2. Create a new VM for a base VM. I called mine <strong>MOTHERSHIP</strong>. Select 25 GB of initial space, allowing dynamic expansion. We will do a lot of installations on this machine and to make them work quick, assign maximum amount of RAM temporarily. At the end of the setup, we can adjust the RAM to a lesser value. </div>
<div style="text-align: justify;">
3. Enable two network adapters on your VM. <strong>Adapter1</strong> connected to NAT (if you care about internet access on VMs). <strong>Adapter2</strong> for Internal only Domain Network.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<a href="http://lh6.ggpht.com/-Xp0HrP8-FV4/UfEfrJCY5sI/AAAAAAAASXo/t9o-0EsMNa4/s1600-h/image%25255B2%25255D.png"><img alt="image" border="0" height="104" src="http://lh6.ggpht.com/-aP0oBj5IqNY/UfEfr0jVkFI/AAAAAAAASXw/JOZcU21R2Cs/image_thumb.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="244" /></a> <a href="http://lh5.ggpht.com/-0P2J5pEQqEU/UfEfsi0pjLI/AAAAAAAASX4/U38za_CytQU/s1600-h/image%25255B5%25255D.png"><img alt="image" border="0" height="100" src="http://lh3.ggpht.com/-c3z0iTLoxfE/UfEftWq4MKI/AAAAAAAASYA/cjOiFKwXnBs/image_thumb%25255B1%25255D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="244" /></a> </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
4. From Setting > Storage > Controller IDE , mount Windows Server 2008 R2 installation media iso. </div>
<div style="text-align: justify;">
5. Start the VM and boot from the iso. Perform Windows Installation. This step is straight forward. </div>
<div style="text-align: justify;">
6. Once the installation is done, set password to <a href="mailto:P@$$w0rd">P@$$w0rd</a>! and login. </div>
<div style="text-align: justify;">
7. Install guest additions for better graphics, shared folders etc. </div>
<div style="text-align: justify;">
8. You will move a lot of file from your Host machine to the VM, so configuring a shared folder will help. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<a href="http://lh3.ggpht.com/-mf8SkZKQa58/UfEfuMr5VpI/AAAAAAAASYI/dRfIW7xYQ6Q/s1600-h/image%25255B8%25255D.png"><img alt="image" border="0" height="136" src="http://lh6.ggpht.com/-3rnzCIG2yFw/UfEfu1BdToI/AAAAAAAASYQ/LlJNILftKXI/image_thumb%25255B2%25255D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="244" /></a> </div>
<div style="text-align: justify;">
<br /></div>
<h3 style="text-align: justify;">
<strong>Install necessary software on BaseVM</strong>:</h3>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div style="text-align: justify;">
1. Login to the base VM you built above and install <strong>Microsoft .NET Framework 4.0</strong></div>
<div style="text-align: justify;">
2. Install <strong>Microsoft 2010 VC ++ redistributable package.</strong></div>
<div style="text-align: justify;">
3. Install<strong> Java JDK 6u31</strong> or higher. Make sure that you select a destination directory that doesn’t have any spaces in the address during installation. For example, I installed mine on<strong> C:\Java\jdk1.6.0_31</strong></div>
<div style="text-align: justify;">
4. Add an environment variable for Java. If you installed Java on<strong> C:\Java\jdk1.6.0_31</strong>, you should add that path as a new system variable. Open a Powershell windows as an admin and execute the below command:</div>
<div class="wlWriterEditableSmartContent" id="scid:812469c5-0cb0-4c63-8c15-c81123a09de7:1a50f67a-338e-4e50-901f-998357e1fcbb" style="display: inline; float: none; margin: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">
<pre class="java" name="code" style="text-align: justify;">[Environment]::SetEnvironmentVariable("JAVA_HOME","c:\java\jdk1.6.0_31","Machine")</pre>
</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
5. Install Python. Add Python to Path. Use the command below in a powershell shell launched as Administrator </div>
<div style="text-align: justify;">
<br /></div>
<div class="wlWriterEditableSmartContent" id="scid:812469c5-0cb0-4c63-8c15-c81123a09de7:7945f531-1147-4a66-ae80-78efc2fd5ccf" style="display: inline; float: none; margin: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">
<pre class="py" name="code" style="text-align: justify;">$env:Path += ";C:\Python27"
</pre>
</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
6. Confirm that the environment variables have been set properly by running <strong>%JAVA_HOME%</strong> from Run, it should open the directory where JDK was installed. Also, run <strong>Python </strong>from Run and it should open Python shell. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
7. We are almost done with the Base VM. We can now generalize this image using sysprep. Go to <strong>Run</strong>, type <strong>sysprep</strong> and enter. It should open /system32/sysprep directory. Run <strong>sysprep.exe</strong> as an Admin. Select '<strong>System Out of Box Experience’ </strong>, check <strong>Generalize </strong>and select <strong>Shutdown. </strong></div>
<div style="text-align: justify;">
<strong><br /></strong></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<a href="http://lh4.ggpht.com/-tgaJbcGU5po/UfIRff7I8uI/AAAAAAAASYg/6QvCVl8EpOQ/s1600-h/image11.png"><img alt="image" border="0" height="200" src="http://lh6.ggpht.com/-wvonh7kHuZw/UfIRgPSH7yI/AAAAAAAASYo/0xJzHA7RksU/image_thumb3.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="244" /></a> </div>
<div style="text-align: justify;">
<br /></div>
<h3 style="text-align: justify;">
Create Linked Clones:</h3>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div style="text-align: justify;">
1. Go to VirtualBox, Right click on the Base VM you created above and select clone.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<a href="http://lh6.ggpht.com/-Axp_FrhLnFc/UfIRg-DEhII/AAAAAAAASYw/NSOcyZ4XdA4/s1600-h/image14.png"><img alt="image" border="0" height="164" src="http://lh5.ggpht.com/-KoHYQe0fewQ/UfIRh3Vv65I/AAAAAAAASY4/G8gD499anbw/image_thumb4.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="244" /></a> </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
2. Give the VM an appropriate name. In my case, its HDDC for domain controller. Make sure you select <strong>Re-Initialize MAC Addresses of all network cards</strong></div>
<div style="text-align: justify;">
3. In the <strong>Clone Type </strong>Dialog, select <strong>Linked Clone, </strong>click <strong>Clone. </strong></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<a href="http://lh4.ggpht.com/-iVb-NpWnWSU/UfIRiRRIchI/AAAAAAAASZA/wmESIVsrvYQ/s1600-h/image17.png"><img alt="image" border="0" height="244" src="http://lh5.ggpht.com/-S8DoH3rBRbY/UfIRjP-ikQI/AAAAAAAASZI/ECQ2gopGqQs/image_thumb5.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="214" /></a> </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
4. <strong>Repeat</strong> steps # 1 – 3 for creating other VMs that will be part of your cluster. You should end up with<strong> HDDC, HDMaster and slave nodes - HDSlave01, HDSlave02, HDSlave03</strong> (depending on your available resources)</div>
<div style="text-align: justify;">
5. Since we created these clones from the same base, it will inherit all settings, including memory. You might want to go to the properties of each VM and assign memory as you see fit.</div>
<div style="text-align: justify;">
<br /></div>
<h3 style="text-align: justify;">
Configuring Domain on HDDC</h3>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div style="text-align: justify;">
We will not go into details of setting up active directory. Google is your friend. But below are some steps specific to our setup.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
1. Start HDDC VM and login.</div>
<div style="text-align: justify;">
2. VirtualBox creates funny computers names, even though your VM name is HDDC. Change the computer name to HDDC and reboot.</div>
<div style="text-align: justify;">
3. Go to the network settings and you should see two network adapters enabled. Usually ‘Network Adapter2’ is your domain only internal network. To confirm, you can go to VM settings while the VM is running, go to <strong>Network </strong>tab and temporarily simulate cable disconnection by unchecking <strong>cable connected. </strong>The Adapter attached to NAT is for internet and the one created as Internal is for Domain Network. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<a href="http://lh3.ggpht.com/-GPtAVIcBu5A/UfIn8T2HXWI/AAAAAAAASZc/9W5CfZy67kw/s1600-h/image%25255B6%25255D.png"><img alt="image" border="0" height="132" src="http://lh3.ggpht.com/-IBk2lZ5w5LQ/UfIn9K0gITI/AAAAAAAASZk/61nTWJx8W5s/image_thumb%25255B2%25255D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="244" /></a> </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
4. Go to properties of Domain Network Adapter inside the VM and set below ip configuration </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<a href="http://lh5.ggpht.com/-hxZJAsuq9AY/UfIn9o1kIXI/AAAAAAAASZs/-IQ267SZpzQ/s1600-h/image%25255B9%25255D.png"><img alt="image" border="0" height="244" src="http://lh4.ggpht.com/-kTDule0Xa9w/UfIn-rCroKI/AAAAAAAASZ0/rJAX81tXx5c/image_thumb%25255B3%25255D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline;" title="image" width="220" /></a></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
5. Enable Active Directory services role and reboot. Use dcpromo.exe to create a new domain. I chose my domain name as TESLA.COM, named after the greatest scientist ever lived. If you need help configuring AD Domain, check <a href="http://www.elmajdal.net/win2k8/setting_up_your_first_domain_controller_with_windows_server_2008.aspx" target="_blank"><strong>this page</strong></a>. Post configuration and reboot, logon to the Domain Controller as TESLA\Administrator and create an active directory account and make it a Domain Admin, this will make things easier for you for the rest of setup.</div>
<div style="text-align: justify;">
6. Optionally, if you are planning on using this VM as Metastore for Hadoop cluster, you can install SQL Server 2008 R2. We will not go into the details of that setup. It is just a straight forward installation. Make sure you create a SQL Login which has sysadmin privileges. My login is <strong>hadoop. </strong>I have also created a couple of blank databases on it, namely <strong>hivedb, ooziedb.</strong></div>
<div style="text-align: justify;">
<strong><br /></strong>
</div>
<h3 style="text-align: justify;">
Network Configuration and Joining other VMs to Domain</h3>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div style="text-align: justify;">
1. Log on to HDMaster as an Administrator</div>
<div style="text-align: justify;">
2. Find the Domain network Adapter (refer to step #3 in Configuring Domain section). Make sure your HDDC (Domain controller) VM is running.</div>
<div style="text-align: justify;">
3. Change the adapter settings: <strong>IP - 192.168.80.2, subnet mast – 255.255.255.0, Default Gateway – Leave it blank, Preferred DNS – 192.168.80.1</strong></div>
<div style="text-align: justify;">
4. Right click on My Computer > Setting > Computer name, domain, workgroup settings > Settings and Change the computer name to <strong>HDMaster</strong> and enter the Domain name <strong>TESLA.COM. </strong>Enter Domain Admin login and password when prompted and you’ll be greeted with a welcome message. Reboot.</div>
<div style="text-align: justify;">
5. Logon to each of the remaining VMs and repeat steps #1 to 4 – the only difference is the IP address. Refer to the list of IPs under ‘What will we build’ section of this article. Preferred DNS remains same for all the VMs i.e, <strong>192.168.80.1</strong></div>
<div style="text-align: justify;">
<strong><br /></strong>
</div>
<h3 style="text-align: justify;">
Preparing The environment for Hadoop Cluster</h3>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div style="text-align: justify;">
We now have a domain and all the VMs added to it. We also installed all the necessary software to the base VM so all the VMs created from it will inherit those. It is now time to prepare the environment for hadoop installation. Before we begin, make sure ALL VMs have same date, time settings.</div>
<div style="text-align: justify;">
<br /></div>
<br />
<div style="text-align: justify;">
<span style="font-weight: bold;"><br /></span></div>
<div style="text-align: justify;">
<strong>Disable IPV6 on ALL VMs:</strong></div>
<br />
<div style="text-align: justify;">
<strong><br /></strong>
</div>
<div style="text-align: justify;">
Log on to each VM, open each network adapter and uncheck IPV6 protocol from adapter settings. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<strong>Adding firewall exceptions:</strong></div>
<br />
<div style="text-align: justify;">
<span style="font-weight: bold;"><br /></span></div>
<div style="text-align: justify;">
<span style="font-weight: bold;"><br /></span></div>
<div style="text-align: justify;">
Hadoop uses multiple ports for communicating between clients and other services so we need to create firewall exceptions on all the servers involved in the cluster. Execute the code below on <strong>each VM</strong> of the cluster on a powershell prompt launched as Administrator. If you want to make it simpler, you can just turn off Firewall at the domain level altogether on all VMs. But we don’t normally do that on production.</div>
<br />
<div style="text-align: justify;">
netsh advfirewall firewall add rule name=AllowRPCCommunication dir=in action=allow protocol=TCP localport=50470<br />
netsh advfirewall firewall add rule name=AllowRPCCommunication dir=in action=allow protocol=TCP localport=50070<br />
netsh advfirewall firewall add rule name=AllowRPCCommunication dir=in action=allow protocol=TCP localport=8020-9000<br />
netsh advfirewall firewall add rule name=AllowRPCCommunication dir=in action=allow protocol=TCP localport=50075<br />
netsh advfirewall firewall add rule name=AllowRPCCommunication dir=in action=allow protocol=TCP localport=50475<br />
netsh advfirewall firewall add rule name=AllowRPCCommunication dir=in action=allow protocol=TCP localport=50010<br />
netsh advfirewall firewall add rule name=AllowRPCCommunication dir=in action=allow protocol=TCP localport=50020<br />
netsh advfirewall firewall add rule name=AllowRPCCommunication dir=in action=allow protocol=TCP localport=50030<br />
netsh advfirewall firewall add rule name=AllowRPCCommunication dir=in action=allow protocol=TCP localport=8021<br />
netsh advfirewall firewall add rule name=AllowRPCCommunication dir=in action=allow protocol=TCP localport=50060<br />
netsh advfirewall firewall add rule name=AllowRPCCommunication dir=in action=allow protocol=TCP localport=51111<br />
netsh advfirewall firewall add rule name=AllowRPCCommunication dir=in action=allow protocol=TCP localport=10000<br />
netsh advfirewall firewall add rule name=AllowRPCCommunication dir=in action=allow protocol=TCP localport=9083<br />
netsh advfirewall firewall add rule name=AllowRPCCommunication dir=in action=allow protocol=TCP localport=50111<br />
<div>
<br /></div>
</div>
<div style="text-align: justify;">
<br /></div>
<h4 style="text-align: justify;">
Networking Configurations and Edit Group Policies:</h4>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div style="text-align: justify;">
1. Logon to your Domain Controller VM, <strong>HDDC </strong>with a Domain Administrator account (ex: TESLA\admin)</div>
<div style="text-align: justify;">
2.Go to start and type <strong>Group Policy Management </strong>in the search bar. Open Group Policy Management.</div>
<div style="text-align: justify;">
3. If you don’t see your Forest TESLA.COM already, add a Forest and provide your domain name</div>
<div style="text-align: justify;">
4. Expand Forest –> Domain –> Name of your Domain –> right click on default domain policy –> edit</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<a href="http://lh3.ggpht.com/-rieANp5wFHo/UfJJBs5m-vI/AAAAAAAASaE/RuPQ_9iiMQM/s1600-h/image%25255B12%25255D.png"><img alt="image" border="0" height="232" src="http://lh4.ggpht.com/-LKc1_vXRjlg/UfJJCZHsrHI/AAAAAAAASaM/_y9ZNHHdfRY/image_thumb%25255B4%25255D.png?imgmax=800" style="border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline;" title="image" width="244" /></a> </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
5. Group Policy Management Editor (lets call it <strong>GPM Editor</strong>) will open up and <strong>note that ALL the steps following below are done through this editor</strong>.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
6. In GPM Editor, navigate to <strong>Computer Configuration -> Policies -> Windows Settings -> Security Settings ->System Services -> Windows Remote Management (WS-Management). </strong>Change the startup mode to <strong>Automatic</strong></div>
<br />
<div style="text-align: justify;">
<span style="font-weight: bold;"><br /></span></div>
<div style="text-align: justify;">
7. Go to <strong>Computer Configuration -> Policies -> Windows Settings -> Security Settings -> Windows Firewall with Advanced Security</strong>. Right click on it and create a new <strong>inbound </strong>firewall rule. For Rule Type, choose <strong>pre-defined, </strong>and select Windows Remote Management from the dropdown. It will automatically create 2 rules. Let them be. click <strong>Next, </strong>Check <strong>Allow The Connection.</strong></div>
<br />
<br />
<div style="text-align: justify;">
<span style="font-weight: bold;"><br /></span></div>
<div style="text-align: justify;">
8. This step is for enabling Remote Execution Policy for Powershell. Go to <strong>Computer Configuration -> Policies -> Administrative Templates -> Windows Components -> Windows PowerShell. </strong>Enable <strong>Turn on Script Execution. </strong>You’ll see <strong>Execution Policy </strong>dropdown under options, select <strong>Allow All Scripts</strong></div>
<br />
<br />
<div style="text-align: justify;">
<span style="font-weight: bold;"><br /></span></div>
<div style="text-align: justify;">
9. Go to <strong>Computer Configuration -> Policies -> Administrative Templates -> Windows Components -> Windows Windows Remote Management (WinRM) -> WinRM Service</strong>. Enable “<strong>Allow Automatic Configuration of Listeners</strong>” . Under Options, enter <strong>* (asterisk symbol) </strong> for <strong>IPV4 filter.</strong> Also change “<strong>Allow CredSSP Authentication” </strong>to <strong>Enabled.</strong></div>
<br />
<br />
<div style="text-align: justify;">
<span style="font-weight: bold;"><br /></span></div>
<div style="text-align: justify;">
10. We configured WinRM Service so far, now it’s WinRM Client’s turn. Go to <strong>Computer Configuration -> Policies -> Administrative Templates -> Windows Components -> Windows Windows Remote Management (WinRM) -> WinRM Client. </strong>Set <strong>Trusted Hosts </strong>to <strong>Enabled </strong>and under Options, set <strong>TrustedHostsList </strong>to <strong>* (asterisk symbol) .</strong> In the same dialog, look for <strong>Allow CredSSP Authentication </strong> and set it to <strong>Enabled.</strong></div>
<br />
<br />
<div style="text-align: justify;">
<span style="font-weight: bold;"><br /></span></div>
<div style="text-align: justify;">
11. Go to <strong>Computer Configuration -> Policies -> Administrative Templates -> System -> Credentials Delegation. </strong>Set <strong>Allow Delegating Fresh Credentials</strong> to <strong>Enabled. </strong>Under options, click on <strong>Show </strong>next to <strong>Add Servers To The List</strong> and set WSMAN to <strong>*</strong> as shown below: (make a new entry like so: <strong>WSMAN/*.TESLA.COM</strong>, replace it with your domain here)</div>
<br />
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<a href="http://lh5.ggpht.com/-S-hjekrak5Q/UfJMNxaj-wI/AAAAAAAASac/Uf-FnGPCDSE/s1600-h/image%25255B15%25255D.png"><img alt="image" border="0" height="142" src="http://lh5.ggpht.com/-J04cpV__VNY/UfJMOlF82VI/AAAAAAAASak/ZGI_D3tfnXE/image_thumb%25255B5%25255D.png?imgmax=800" style="border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline;" title="image" width="244" /></a> </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
12. Repeat instructions in step#11 above, this time for property <strong>NTLM-only server authentication</strong></div>
<br />
<div style="text-align: justify;">
<span style="font-weight: bold;"><br /></span></div>
<div style="text-align: justify;">
13. Already tired? We are almost done. Just a couple more steps before we launch the installation. Go to <strong>Run</strong> and type <strong>ADSIEdit.msc </strong>and enter. Expand <strong>OU=Domain Controllers</strong> menu item and select <strong>CN=HDDC (controller hostname).</strong> Go to <strong>Properties -> Security -> Advanced –> Add.</strong> Enter <strong>NETWORK SERVICE</strong>, click <strong>Check Names</strong>, then Ok. In the Permission Entry select <strong>Validated write to service principal name</strong>. Click <strong>Allow</strong> and <strong>OK</strong> to save your changes.</div>
<br />
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
14.On the Domain controller VM, launch a powershell windows an an administrator and run the following: <strong>Restart-Service WinRM</strong></div>
<br />
<div style="text-align: justify;">
<span style="font-weight: bold;"><br /></span></div>
<div style="text-align: justify;">
15. Force update gpupdate on the other VMs in the environment. Run this command: <strong>gpupdate /force</strong></div>
<br />
<div style="text-align: justify;">
<strong><br /></strong>
</div>
<h3 style="text-align: justify;">
Define Cluster Configuration File</h3>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div style="text-align: justify;">
When you download HortonWorks Dataplatform for windows, the zip contains a sample <strong>clusterproperties.txt</strong> file which can be used as a template during installation. The installation msi depends on clusterproperties file for cluster layout definition. Below is my Cluster Configuration file. Make changes to yours accordingly. </div>
<div style="text-align: justify;">
<br /></div>
<div class="wlWriterEditableSmartContent" id="scid:812469c5-0cb0-4c63-8c15-c81123a09de7:9b96609e-12e1-4ea7-8228-2bf6850323e0" style="display: inline; float: none; margin: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">
<pre class="c" name="code"><div style="text-align: justify;">
#Log directory
HDP_LOG_DIR=c:\hadoop\logs
#Data directory
HDP_DATA_DIR=c:\hdp\data
#Hosts
NAMENODE_HOST=HDMaster.tesla.com
SECONDARY_NAMENODE_HOST=HDMaster.tesla.com
JOBTRACKER_HOST=HDMaster.tesla.com
HIVE_SERVER_HOST=HDMaster.tesla.com
OOZIE_SERVER_HOST=HDMaster.tesla.com
TEMPLETON_HOST=HDMaster.tesla.com
SLAVE_HOSTS=HDSlave01.tesla.com, HDSlave02.tesla.com
#Database host
DB_FLAVOR=mssql
DB_HOSTNAME=HDDC.tesla.com
#Hive properties
HIVE_DB_NAME=hivedb
HIVE_DB_USERNAME=sa
HIVE_DB_PASSWORD=P@$$word!
#Oozie properties
OOZIE_DB_NAME=ooziedb
OOZIE_DB_USERNAME=sa
OOZIE_DB_PASSWORD=P@$$word!</div>
</pre>
</div>
<div style="text-align: justify;">
<br /></div>
<h3 style="text-align: justify;">
Installation</h3>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div>
<div style="text-align: justify;">
<br /></div>
</div>
<div style="text-align: justify;">
1. Keep all of your VMs running and logon to <strong>HDMaster, </strong>with domain admin account.</div>
<div style="text-align: justify;">
2. Create a folder <strong>C:\setup</strong> and copy the contents of HortonWorks Data Platform installation media you downloaded at the beginning of this guide.</div>
<div style="text-align: justify;">
3. Replace the clusterproperties.txt with the one you created under <strong>Define Cluster Config File </strong>section. This file is same for ALL the participant servers in the cluster.</div>
<div style="text-align: justify;">
4. open a command prompt as administrator and cd to <b>C:\setup</b>.</div>
<div style="text-align: justify;">
5. Type the below command to kick off installation. Beware of unnecessary spaces in the command. It took me a lot of time to figure out the space was the issue. Edit the folder path according to your setup. There is no space between HDP_LAYOUT and ‘=’.</div>
<div style="text-align: justify;">
<br /></div>
<div class="wlWriterEditableSmartContent" id="scid:812469c5-0cb0-4c63-8c15-c81123a09de7:567c29aa-6b03-4744-b681-a48807dc3a03" style="display: inline; float: none; margin: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">
<pre class="c" name="code" style="text-align: justify;">msiexec /i "c:\setup\hdp-1.1.0-GA.winpkg.msi" /lv "hdp.log" HDP_LAYOUT="C:\setup\clusterProperties.txt" HDP_DIR="C:\hdp\Hadoop" DESTROY_DATA="no"</pre>
</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
6. If everything goes well, you should see a success message.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<a href="http://lh4.ggpht.com/-2oYxpeCaFkc/UfJT9MU53VI/AAAAAAAASa0/H6PX_CVMiY4/s1600-h/image%25255B18%25255D.png"><img alt="image" border="0" height="116" src="http://lh5.ggpht.com/-hcO_7BaEBHA/UfJT-Ow6VUI/AAAAAAAASa8/_BuKQbbANUc/image_thumb%25255B6%25255D.png?imgmax=800" style="border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline;" title="image" width="244" /></a> </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
7. Log on to each of the other VMs, and <strong>repeat steps from #2 to 6</strong>.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<b>Congratulations</b>, you have now successfully created your first Hadoop Cluster. You’ll see shortcuts like below on your desktop on each VM. Before starting Hadoop exploration, we need to start services on both Local and remote machines. Log on to the HDMaster server, go to hadoop installation directory and look for \hadoop folder. You’ll see a couple of batch files to start/stop services.</div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
<a href="http://lh5.ggpht.com/-YDUp1kZ4S5s/UfJW02yfkvI/AAAAAAAASbM/fq2fwK6yqLw/s1600-h/image%25255B21%25255D.png"><img alt="image" border="0" height="244" src="http://lh5.ggpht.com/-wZN2HD6Vj2E/UfJW1mTsWdI/AAAAAAAASbU/k1ZiWJtoZds/image_thumb%25255B7%25255D.png?imgmax=800" style="border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline;" title="image" width="92" /></a> </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
In the next blog post, we will write a sample MapReduce job using Pig and one using Python. Have fun!</div>
</div>
yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com1tag:blogger.com,1999:blog-8235355014350215030.post-78544628443818361442013-07-06T11:44:00.000-07:002013-07-06T11:49:00.880-07:00VirtualBox: SSH to ubuntu guest from windows hostMake sure you have ssh server installed on ubuntu.<br />
<br />
<pre class = "brush:ps">sudo apt-get install open-sshserver
</pre><br />
Go to VM properties => Network; you should see an adapter attached to NAT. Go to advanced properties and click port forwarding. Create a new port forwarding rule with the below details.<br />
<br />
Name: SSH<br />
Protocol: TCP<br />
Host Port: 3022<br />
Guest Port: 22<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/-oqspPDfBNBk/UdhmhDiGeNI/AAAAAAAASG8/Ht1w3OdgMpA/s1600/ssh.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/-oqspPDfBNBk/UdhmhDiGeNI/AAAAAAAASG8/Ht1w3OdgMpA/s1600/ssh.jpg" /></a></div><br />
Or, you can run this command to configure:<br />
<br />
<pre class = "brush:ps">VBoxManage modifyvm myubuntuVM --natpf1 "ssh,tcp,,3022,,22"
</pre><br />
Use your favorite SSH client (I use cygwin on my win 8 machine) and use the following command to connect:<br />
<br />
<pre class = "brush:ps">ssh -p 3022 cherry@127.0.0.1
</pre><br />
<br />
<br />
yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-84759777552272386482013-06-16T23:46:00.000-07:002013-06-16T23:46:07.631-07:00Python Game: Asteroids (Rice Rocks)The due time for this project submission is over so I can post this here. With this final mini project, <a href="https://class.coursera.org/interactivepython-002">An Introduction to Interactive Programming in Python</a> class is complete. It has been a wonderful experience! You can <a href="http://www.codeskulptor.org/#user16_r0lBc2SuaC_0.py">play the game here</a>:<br />
<br />
<pre class = "brush:py">__author__ = 'RK Kuppala'
# program template for Spaceship
import simplegui
import math
import random
# globals for user interface
WIDTH = 800
HEIGHT = 600
score = 0
lives = 3
time = 0
wrap = 20
started = False
alive = True
rock_group = set([])
class ImageInfo:
def __init__(self, center, size, radius = 0, lifespan = None, animated = False):
self.center = center
self.size = size
self.radius = radius
if lifespan:
self.lifespan = lifespan
else:
self.lifespan = float('inf')
self.animated = animated
def get_center(self):
return self.center
def get_size(self):
return self.size
def get_radius(self):
return self.radius
def get_lifespan(self):
return self.lifespan
def get_animated(self):
return self.animated
# art assets created by Kim Lathrop, may be freely re-used in non-commercial projects, please credit Kim
# debris images - debris1_brown.png, debris2_brown.png, debris3_brown.png, debris4_brown.png
# debris1_blue.png, debris2_blue.png, debris3_blue.png, debris4_blue.png, debris_blend.png
debris_info = ImageInfo([320, 240], [640, 480])
debris_image = simplegui.load_image("http://commondatastorage.googleapis.com/codeskulptor-assets/lathrop/debris2_blue.png")
# nebula images - nebula_brown.png, nebula_blue.png
nebula_info = ImageInfo([400, 300], [800, 600])
nebula_image = simplegui.load_image("http://commondatastorage.googleapis.com/codeskulptor-assets/lathrop/nebula_blue.png")
# splash image
splash_info = ImageInfo([200, 150], [400, 300])
splash_image = simplegui.load_image("http://commondatastorage.googleapis.com/codeskulptor-assets/lathrop/splash.png")
# ship image
ship_info = ImageInfo([45, 45], [90, 90], 35)
ship_image = simplegui.load_image("http://commondatastorage.googleapis.com/codeskulptor-assets/lathrop/double_ship.png")
# missile image - shot1.png, shot2.png, shot3.png
missile_info = ImageInfo([5,5], [10, 10], 3, 50)
missile_image = simplegui.load_image("http://commondatastorage.googleapis.com/codeskulptor-assets/lathrop/shot2.png")
# asteroid images - asteroid_blue.png, asteroid_brown.png, asteroid_blend.png
asteroid_info = ImageInfo([45, 45], [90, 90], 40)
asteroid_image = simplegui.load_image("http://commondatastorage.googleapis.com/codeskulptor-assets/lathrop/asteroid_blue.png")
# animated explosion - explosion_orange.png, explosion_blue.png, explosion_blue2.png, explosion_alpha.png
explosion_info = ImageInfo([64, 64], [128, 128], 17, 24, True)
explosion_image = simplegui.load_image("http://commondatastorage.googleapis.com/codeskulptor-assets/lathrop/explosion_alpha.png")
# sound assets purchased from sounddogs.com, please do not redistribute
soundtrack = simplegui.load_sound("http://commondatastorage.googleapis.com/codeskulptor-assets/sounddogs/soundtrack.mp3")
missile_sound = simplegui.load_sound("http://commondatastorage.googleapis.com/codeskulptor-assets/sounddogs/missile.mp3")
missile_sound.set_volume(.4)
ship_thrust_sound = simplegui.load_sound("http://commondatastorage.googleapis.com/codeskulptor-assets/sounddogs/thrust.mp3")
ship_thrust_sound.set_volume(.4)
explosion_sound = simplegui.load_sound("http://commondatastorage.googleapis.com/codeskulptor-assets/sounddogs/explosion.mp3")
explosion_sound.set_volume(.5)
# helper functions to handle transformations
def angle_to_vector(ang):
return [math.cos(ang), math.sin(ang)]
def dist(p,q):
return math.sqrt((p[0] - q[0]) ** 2+(p[1] - q[1]) ** 2)
# Ship class
class Ship:
def __init__(self, pos, vel, angle, image, info):
self.pos = [pos[0],pos[1]]
self.vel = [vel[0],vel[1]]
self.thrust = False
self.angle = angle
self.angle_vel = 0
self.image = image
self.image_center = info.get_center()
self.image_size = info.get_size()
self.radius = info.get_radius()
def draw(self,canvas):
if alive:
if self.thrust:
self.image_center = (135, 45)
else:
self.image_center = (45, 45)
canvas.draw_image(ship_image, self.image_center, self.image_size, self.pos, self.image_size, self.angle)
if self.pos[0] <self.radius:
canvas.draw_image(ship_image, self.image_center, self.image_size,
[self.pos[0]+WIDTH,self.pos[1]], self.image_size, self.angle)
if self.pos[0] > WIDTH - self.radius:
canvas.draw_image(ship_image, self.image_center, self.image_size,
[self.pos[0]-WIDTH,self.pos[1]], self.image_size, self.angle)
if self.pos[1] < self.radius:
canvas.draw_image(ship_image, self.image_center, self.image_size,
[self.pos[0],self.pos[1]+HEIGHT], self.image_size, self.angle)
if self.pos[1] > HEIGHT - self.radius:
canvas.draw_image(ship_image, self.image_center, self.image_size,
[self.pos[0],self.pos[1]-HEIGHT], self.image_size, self.angle)
def update(self):
if alive:
self.pos[0] += self.vel[0]
self.pos[0] = (self.pos[0] % WIDTH)
self.pos[1] += self.vel[1]
self.pos[1] = (self.pos[1] % HEIGHT)
self.vel[0] *= 0.97
self.vel[1] *= 0.97
self.angle += self.angle_vel
if self.thrust:
self.vel[0] += 0.3 * (angle_to_vector(self.angle)[0])
self.vel[1] += 0.3 * (angle_to_vector(self.angle) [1])
def angleincrement(self):
self.angle_vel += 0.08
def angledecrement(self):
self.angle_vel -= 0.08
def thrusters(self):
self.thrust = not self.thrust
if self.thrust and started and alive:
ship_thrust_sound.play()
else:
ship_thrust_sound.pause()
def shoot(self):
global missile_group
missile_timer.start()
if alive and len(missile_group) < 2:
missile_group.add(Sprite([self.pos[0]+self.radius*angle_to_vector(self.angle)[0], self.pos[1]+self.radius*angle_to_vector(self.angle)[1]],
[11*angle_to_vector(self.angle)[0] + 0.4*self.vel[0], 11*angle_to_vector(self.angle)[1] + 0.4*self.vel[1]],
0, 0, missile_image, missile_info, missile_sound))
def respawn(self):
global alive, started
if lives > 0:
self.thrust = False
alive = True
self.vel[0] = 0
self.vel[1] = 0
respawn_timer.stop()
else:
started = False
soundtrack.rewind()
ship_thrust_sound.rewind()
# Sprite class
class Sprite:
def __init__(self, pos, vel, ang, ang_vel, image, info, sound = None):
self.pos = [pos[0],pos[1]]
self.vel = [vel[0],vel[1]]
self.angle = ang
self.angle_vel = ang_vel
self.image = image
self.image_center = info.get_center()
self.image_size = info.get_size()
self.radius = info.get_radius()
self.lifespan = info.get_lifespan()
self.animated = info.get_animated()
self.age = 0
if sound:
sound.rewind()
sound.play()
def draw(self, canvas):
if self.animated:
canvas.draw_image(self.image, [self.image_center[0] + self.image_size[0] * self.age, self.image_center[1]],
self.image_size, self.pos, self.image_size, self.angle)
else:
canvas.draw_image(self.image, self.image_center, self.image_size, self.pos, self.image_size, self.angle)
if self.pos[0] < self.radius:
canvas.draw_image(self.image, self.image_center, self.image_size,
[self.pos[0]+ WIDTH,self.pos[1]], self.image_size, self.angle)
if self.pos[0] > WIDTH - self.radius:
canvas.draw_image(self.image, self.image_center, self.image_size,
[self.pos[0]- WIDTH,self.pos[1]], self.image_size, self.angle)
if self.pos[1] < self.radius:
canvas.draw_image(self.image, self.image_center, self.image_size,
[self.pos[0],self.pos[1]+HEIGHT], self.image_size, self.angle)
if self.pos[1] > HEIGHT - self.radius:
canvas.draw_image(self.image, self.image_center, self.image_size,
[self.pos[0],self.pos[1]- HEIGHT], self.image_size, self.angle)
def update(self):
global wrap
self.pos[0] += self.vel[0]
self.pos[0] = (self.pos[0] % WIDTH)
self.pos[1] += self.vel[1]
self.pos[1] = (self.pos[1] % HEIGHT)
self.angle += self.angle_vel
self.age += 1
def check_lifespan(self):
return self.age < self.lifespan
def collide(self, other_object):
if dist(self.pos,other_object.pos) < (self.radius + other_object.radius):
return True
def draw(canvas):
global time, lives, score, timer, started, alive, rock_group
# animiate background
time += 1
center = debris_info.get_center()
size = debris_info.get_size()
wtime = (time / 8) % center[0]
canvas.draw_image(nebula_image, nebula_info.get_center(), nebula_info.get_size(), [WIDTH/2, HEIGHT/2], [WIDTH, HEIGHT])
canvas.draw_image(debris_image, [center[0]-wtime, center[1]], [size[0]-2*wtime, size[1]],
[WIDTH/2+1.25*wtime, HEIGHT/2], [WIDTH-2.5*wtime, HEIGHT])
canvas.draw_image(debris_image, [size[0]-wtime, center[1]], [2*wtime, size[1]],
[1.25*wtime, HEIGHT/2], [2.5*wtime, HEIGHT])
if not started:
canvas.draw_image(splash_image, splash_info.get_center(),
splash_info.get_size(), [WIDTH/2, HEIGHT/2],
splash_info.get_size())
# draw ship and sprites
elif started:
my_ship.draw(canvas)
process_sprite_group(rock_group, canvas)
process_sprite_group(missile_group, canvas)
process_sprite_group(explosion_group, canvas)
canvas.draw_text("Lives: " + str(lives), [50,50], 20, "White")
canvas.draw_text("Score: " + str(score), [WIDTH-150,50], 20, "White")
# update ship and sprites
my_ship.update()
if group_collide(rock_group, my_ship) > 0:
alive = False
lives -= 1
explosion_sound.play()
respawn_timer.start()
if group_group_collide(missile_group, rock_group) > 0:
score += 1
explosion_sound.rewind()
explosion_sound.play()
def keydown(key):
if key == simplegui.KEY_MAP['right']:
my_ship.angleincrement()
elif key == simplegui.KEY_MAP['left']:
my_ship.angledecrement()
elif key == simplegui.KEY_MAP['up']:
my_ship.thrusters()
elif key == simplegui.KEY_MAP['space']:
my_ship.shoot()
def keyup(key):
if key == simplegui.KEY_MAP['right']:
my_ship.angledecrement()
elif key == simplegui.KEY_MAP['left']:
my_ship.angleincrement()
elif key == simplegui.KEY_MAP['up']:
my_ship.thrusters()
elif key == simplegui.KEY_MAP['space']:
missile_timer.stop()
# timer handler that spawns a rock
def rock_spawner():
global rock_group
random_place = [random.randrange(0, WIDTH), random.randrange(0, HEIGHT)]
while dist(random_place,my_ship.pos) < 150:
random_place = [random.randrange(0, WIDTH), random.randrange(0, HEIGHT)]
random_velocity = [random.choice([-1, -2, 1, 2]), random.choice([-1, -2, 1, 2])]
random_angle = random.randrange(0, 7)
random_anglevel = (random.randrange(-30,30) / 1000)
if len(rock_group) < 5:
rock_group.add((Sprite(random_place, random_velocity, random_angle, random_anglevel, asteroid_image, asteroid_info)))
def process_sprite_group(a_set, canvas):
for sprite in a_set:
sprite.draw(canvas)
for sprite in a_set:
sprite.update()
for sprite in a_set:
if not sprite.check_lifespan():
a_set.remove(sprite)
def group_collide(group, other_object):
collide = 0
for an_object in group:
if Sprite.collide(an_object, other_object):
explosion_group.add(Sprite(an_object.pos, (0,0), 0, 0, explosion_image, explosion_info, explosion_sound))
if other_object.radius > 10:
explosion_group.add(Sprite(other_object.pos, (0,0), 0, 0, explosion_image, explosion_info, explosion_sound))
group.remove(an_object)
collide += 1
return collide
def group_group_collide(group1, group2):
collide = 0
for an_object in group1:
if group_collide(group2, an_object) > 0:
group1.remove(an_object)
collide += 1
return collide
def click(pos):
global started, lives
center = [WIDTH / 2, HEIGHT / 2]
size = splash_info.get_size()
inwidth = (center[0] - size[0] / 2) < pos[0] < (center[0] + size[0] / 2)
inheight = (center[1] - size[1] / 2) < pos[1] < (center[1] + size[1] / 2)
if (not started) and inwidth and inheight:
reset()
soundtrack.play()
started = True
def reset():
global my_ship, rock_group, missile_group, lives, score
my_ship = Ship([WIDTH / 2, HEIGHT / 3], [0, 0], 0, ship_image, ship_info)
rock_group = set()
missile_group = set()
explosion_group = set()
score = 0
lives = 3
timer.start()
def missile():
my_ship.shoot()
# initialize frame
frame = simplegui.create_frame("Asteroids", WIDTH, HEIGHT)
# initialize ship and two sprites
my_ship = Ship([WIDTH / 2, HEIGHT / 2], [0, 0], 0, ship_image, ship_info)
a_rock = Sprite([WIDTH / 3, HEIGHT / 3], [1, 1], 0, 0, asteroid_image, asteroid_info)
a_missile = Sprite([2 * WIDTH / 3, 2 * HEIGHT / 3], [-1,1], 0, 0, missile_image, missile_info, missile_sound)
# register handlers
frame.set_draw_handler(draw)
frame.set_mouseclick_handler(click)
timer = simplegui.create_timer(500.0, rock_spawner)
frame.set_keydown_handler(keydown)
frame.set_keyup_handler(keyup)
my_ship = Ship([WIDTH / 2, HEIGHT / 3], [0, 0], 0, ship_image, ship_info)
missile_group = set()
explosion_group = set()
missile_timer = simplegui.create_timer(700.0, missile)
respawn_timer = simplegui.create_timer(700.0, my_ship.respawn)
# get things rolling
timer.start()
frame.start()
</pre>
yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-77885707971126338932013-06-06T10:08:00.001-07:002013-06-06T10:13:53.891-07:00Playing with text file data in Python: Building SQL delete statements from unstructured dataYesterday I played around with <a href="http://binyoga.blogspot.in/2013/06/manipulating-excel-data-with-python.html">excel files to build SQL update statements</a> and today I got an opportunity do similar stuff, but this time I got the text file that had column names in the first line and values in following lines separated by spaces. The goal is delete a bunch of records from a table based on the eventids etc. It looks like this:<br />
<br />
<a href="http://3.bp.blogspot.com/-CXzh0LsIiRI/UbB3nkQ8YvI/AAAAAAAARQI/fuCRErVMe-0/s1600/line01.jpg" imageanchor="1" ><img border="0" src="http://3.bp.blogspot.com/-CXzh0LsIiRI/UbB3nkQ8YvI/AAAAAAAARQI/fuCRErVMe-0/s320/line01.jpg" /></a><br />
<br />
First, you need to <a href="http://docs.python.org/2/tutorial/inputoutput.html">open the file</a> to read it. The <b>rb</b> argument is to open the file for reading in binary mode in windows. <br />
<br />
<pre class = "brush:py">myFile = open("D:\Scripts\deldata.txt", "rb")
</pre><br />
I initially tried to find the number of lines in the text file thinking this might be useful while iterating through the lines and when trying to make a list out of these . I know, you could just open the text file in notepad++ and look at the end of the document, but where is the fun in that? So this is what I came up with. <br />
<br />
<pre class = "brush:py">myFile = open("D:\Scripts\deldata.txt", 'rb')
i = 0
for line in enumerate(myFile):
i += 1
print i
</pre><br />
I know that totally sucks. May be there a more elegant way? <br />
<br />
<pre class = brush:py">myFile = open("D:\Scripts\deldata.txt", "rb")
numLines = sum(1 for line in open myFile)
print numLines
</pre><br />
It turns out, you don't really need it. You can just loop through each line and manipulate the lines as needed. When I printed the lines, there were lots of white spaces and tabs in the resulting strings. After browsing through a lot of stackoverflow questions and reading documentation and unsuccessfully trying to use regular expressions, I found an easier way to deal with the white spaces. Just use <b>string.split()</b> method and turn the string into a list. <br />
<br />
<pre class = "brush:py">__author__ = 'RK'
myFile = open("D:\Scripts\deldata.txt", "rb")
for line in myFile:
line = line.split() #remove white spaces and tabs
for value in line:
print ("DELETE FROM REGISTR WHERE eventid = '%s' AND subeventid = '%s' AND personid = '%s'
AND RegNo = '%s'" %(line[0], line[1], line[2], line[3]))
</pre><br />
<br />
<br />
<br />
<br />
yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-47221669093177699982013-06-05T02:16:00.001-07:002013-06-05T03:45:46.573-07:00Manipulating Excel data with Python: Building SQL statements from data in excelI got an excel doc with a couple of sheets from our dev team. Each sheet has two columns from one of our database tables. Lets call these columns <b>CurrentID, LegacyID</b>. I need to update the LegacyID based on CurrentID. Each sheet has more than 2000 values and it is pointless trying to build the SQL statements manually. Its pretty easy to get this done using TSQL. Just import this data into a temporary table and build update statements using TSQL. Since I have been trying to learn Python, I thought I'd give it a try. I used <b>xlrd</b> module. Check the documentation <a href="https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html?p=4966">here</a>. Below code writes the update statements to a text file from sheet1. <br />
<br />
<pre class = "brush:py">import xlrd
book = xlrd.open_workbook('D:\Scripts\UpdateID01.xls')
sheet = book.sheet_by_index(0)
myList = []
for i in range(1,sheet.nrows):
myList.append(sheet.row_values(i))
#print myList
outFile = open('D:\Scripts\update.txt', 'wb')
for i in myList:
outFile.write("\nUPDATE MyTable SET LegacyID = '%s' WHERE CurrentID = '%s'" %( int(i[1]), str(i[0])))
</pre><br />
The second sheet had white spaces at the end of CurrentID values and the update statements will fail if they are not removed. To deal with it, use <a href="http://docs.python.org/2/library/stdtypes.html#str.replace">str.replace</a> method. <br />
<br />
<pre class = "brush:py">import xlrd
book = xlrd.open_workbook('D:\Scripts\UpdateID01.xls')
sheet = book.sheet_by_index(1)
myList = []
for i in range(1,sheet.nrows):
myList.append(sheet.row_values(i))
#print myList
outFile = open('D:\Scripts\updatemyesi.txt', 'wb')
for i in myList:
i[0] = str(i[0]).replace(' ', '')
outFile.write("\nUPDATE MyTable SET LegacyID = 0 WHERE CurrentID = '%s'" %(str(i[0])))
</pre>yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-53095576079004308192013-05-31T13:25:00.000-07:002013-06-01T10:14:53.105-07:00Python Game: BlackjackThis week's game is Blackjack. <a href="http://www.codeskulptor.org/#user15_hTIMs0uOiI_1.py">Go play it here</a><br />
<br />
<pre class = "brush:py"># Blackjack
__author__ = 'RK'
import simplegui
import random
CARD_SIZE = (73, 98)
CARD_CENTER = (36.5, 49)
card_images = simplegui.load_image("http://commondatastorage.googleapis.com/codeskulptor-assets/cards.jfitz.png")
CARD_BACK_SIZE = (71, 96)
CARD_BACK_CENTER = (35.5, 48)
card_back = simplegui.load_image("https://www.dropbox.com/s/75fcadgeewharzg/joker.jpg?dl=1")
# initialize some useful global variables
in_play = False
outcome = ""
score = 0
# define globals for cards
SUITS = ('C', 'S', 'H', 'D')
RANKS = ('A', '2', '3', '4', '5', '6', '7', '8', '9', 'T', 'J', 'Q', 'K')
VALUES = {'A':1, '2':2, '3':3, '4':4, '5':5, '6':6, '7':7, '8':8, '9':9, 'T':10, 'J':10, 'Q':10, 'K':10}
# define card class
class Card:
def __init__(self, suit, rank):
if (suit in SUITS) and (rank in RANKS):
self.suit = suit
self.rank = rank
else:
self.suit = None
self.rank = None
print "Invalid card: ", suit, rank
def __str__(self):
return self.suit + self.rank
def get_suit(self):
return self.suit
def get_rank(self):
return self.rank
def draw(self, canvas, pos):
card_loc = (CARD_CENTER[0] + CARD_SIZE[0] * RANKS.index(self.rank),
CARD_CENTER[1] + CARD_SIZE[1] * SUITS.index(self.suit))
canvas.draw_image(card_images, card_loc, CARD_SIZE, [pos[0] + CARD_CENTER[0], pos[1] + CARD_CENTER[1]], CARD_SIZE)
# define hand class
class Hand:
def __init__(self):
self.hand = []
def __str__(self):
s = ''
for c in self.hand:
s += str(c)
s += " "
return s
def add_card(self, card):
self.hand.append(card)
def hit(self,deck):
self.add_card(deck.deal_card())
player.get_value()
dealer.get_value()
def get_value(self):
""" count aces as 1, if the hand has an ace, then add 10 to hand value if it doesn't bust"""
global in_play, message, score
self.value = 0
A = 0
for card in self.hand:
if card[1] == 'A':
A += 1
self.value += VALUES[card[1]]
if A > 0 and self.value < 12:
self.value += 10
if self.value > 21:
if in_play and (player.value > 21):
message = "You lose! The computer wins!"
score -= 1
in_play = False
return self.value
def draw(self, canvas, pos):
"""draw a hand on the canvas, use the draw method for cards"""
x = 0
for card in self.hand:
card = Card(self.hand[x][0], self.hand[x][1])
card.draw(canvas, [x * 90 + 50, pos * 200])
x += 1
# define deck class
class Deck:
def __init__(self):
self.deck = [(suit, rank) for suit in SUITS for rank in RANKS]
self.shuffle()
def shuffle(self):
random.shuffle(self.deck)
def deal_card(self):
return self.deck.pop()
#define event handlers for buttons
def deal():
global outcome, in_play,deck, hand, dealer, hand_total, player, message, score
message = "Do you choose to Hit or Stand?"
if in_play:
score -= 1
message = "That's cheating!"
hand_total = 0
deck = Deck()
player = Hand()
dealer = Hand()
player.hit(deck)
player.hit(deck)
dealer.hit(deck)
dealer.hit(deck)
in_play = True
def hit():
global player, in_play, message
"""if the hand is in play, hit the player"""
player.get_value()
if (player.value <= 21) and in_play:
player.hit(deck)
if player.value < 21:
message = "OK. Do you want to Hit again or Stand?"
"""if busted, assign a message to outcome, update in_play and score"""
else:
message = "STOP. CLICKING. THAT."
def stand():
global value, message, in_play, score
if in_play == False:
message = "STOP. CLICKING. THAT."
else:
player.value
message = "Please wait! Computer is making its move..."
timer.start()
in_play = False
def dealercard():
global score, message
if dealer.value < 17:
dealer.hit(deck)
elif (player.value > dealer.value) or (dealer.value > 21):
message = "You win. Congrats! Deal again?"
score += 1
timer.stop()
else:
message = "Computer Wins. Deal again?"
score -= 1
timer.stop()
# draw handler
def draw(canvas):
global dealer, player, message, in_play, score
scorestring = "Your points are: "
scorestring += str(score)
dealer.draw(canvas, 1)
player.draw(canvas, 2)
canvas.draw_text(message, [50, 185], 18, "Black")
canvas.draw_text(scorestring, [480, 555], 14, "Yellow")
canvas.draw_text("BlackJack Game", [480, 585], 18, "Black")
if in_play:
canvas.draw_image(card_back, CARD_BACK_CENTER, CARD_BACK_SIZE, (88, 249), (70, 94))
# initialization frame
frame = simplegui.create_frame("Blackjack", 600, 600)
frame.set_canvas_background("Gray")
timer = simplegui.create_timer(1000, dealercard)
#create buttons and canvas callback
frame.add_button("Deal", deal, 200)
frame.add_button("Hit", hit, 200)
frame.add_button("Stand", stand, 200)
frame.set_draw_handler(draw)
# get things rolling
frame.start()
deck = Deck()
player = Hand()
dealer = Hand()
message = "BlackJack Game. Please hit Deal"
</pre><br />
yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-82878773263085041322013-05-30T12:55:00.001-07:002013-05-31T00:08:19.254-07:00A simple list manipulation - Computing primes using Sieve of Eratosthenes processI am incredibly dumb that I had to wrestle with this problem for a long time before being able to solve it. But the experience has been invaluable. <br />
<br />
The Problem:<br />
<br />
Initialize n to be 1000. Initialize numbers to be a list of numbers from 2 to n, but not including n. With results starting as the empty list, repeat the following as long as numbers contains any numbers.<br />
1. Add the first number in numbers to the end of results.<br />
2. Remove every number in numbers that is evenly divisible by (has no remainder when divided by) the number that you <br />
had just added to results.<br />
<br />
How long is results? <br />
<br />
The Solution (It is ugly!)<br />
<br />
<pre class = "brush:py">n = 1000
numbers = range(2, n)
results = []
while numbers:
i = numbers[0]
results.append(i)
for number in numbers:
if number % i == 0:
numbers.remove(number)
print numbers
print len(results)
</pre><br />
<br />
It looks like this computes the primes less than n, by a process known as <a href="http://en.wikipedia.org/wiki/Sieve_of_Eratosthenes">Sieve of Eratosthenes</a>. Below is another, more elegant and <b>pythonic</b> way to do it. <br />
<br />
<pre class = "brush:py">n = 1000
numbers = range(2, n)
results = []
while numbers != []:
results.append(numbers[0])
numbers = [n for n in numbers if n % numbers[0] != 0]
print len(results)
</pre><br />
I was stuck with infinite loops with my original program before I stumbled upon <a href="http://www.pythontutor.com/">pythontutor.org</a> and saw the execution step by step. Below is the visualized execution of my program. <br />
<br />
<iframe width="800" height="500" frameborder="0" src="http://pythontutor.com/iframe-embed.html#code=n++%3D+20+%0Anumbers+%3D+range(2,+n)%0Aresults+%3D+%5B%5D%0Awhile+numbers%3A%0A++++i+%3D+numbers%5B0%5D%0A++++results.append(i)%0A++++for+number+in+numbers%3A%0A++++++++if+number+%25+i+%3D%3D+0%3A%0A++++++++++++numbers.remove(number)%0A+%0Aprint+numbers%0Aprint+len(results)&cumulative=false&heapPrimitives=false&drawParentPointers=false&textReferences=false&showOnlyOutputs=false&py=2&curInstr=0"> </iframe><br />
<br />
yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-14303474644865603172013-05-30T10:49:00.000-07:002013-05-30T12:45:50.095-07:00SQL Server: Storage internals, recovery model and log behaviorThis is not a comprehensive post on SQL Server storage and internals, but we recently ran into an interesting issue. We were doing PCI scans on some of our servers for PCI compliance. There is a tool that scans each bit and byte inside the database and server and return a list of possible violations. Our developers identified data that needs to be removed in some legacy systems and they cleaned up the data by updating offending fields with blank values, deleting some tables altogether etc. But the scan tool would often find violations even after truncating/deleting data. Some of those databases were on simple and some were in FULL recovery mode and I had to explain how log backups, recovery and checkpoint affect the databases and I came up with this small tutorial.<br />
<br />
Lets create a database to play with. Lets set that database in FULL recovery mode and create some data and see whats in the log. <br />
<pre class = "brush:sql">CREATE DATABASE storagefun;
GO
ALTER DATABASE storagefun SET RECOVERY FULL
GO
</pre><br />
A database with no data in it is no good. Lets create a table and see. I am going to define DEFAULTS just to save some time and effort while inserting records into that table. I'm just lazy:<br />
<br />
<pre class = "brush:sql">USE storagefun
GO
CREATE TABLE CreditInfo
(
id int identity,
name nvarchar(30) DEFAULT 'SQL Server is fun!',
ccnumber nvarchar(40) DEFAULT '1234-4567-7891-1023'
)
</pre><br />
A full backup initiates an LSN chain, so lets do a FULL backup. <br />
<pre class = "brush:sql">BACKUP DATABASE storagefun TO DISK = 'D:\SQLData\Backups\Storagefun.bak'
</pre><br />
Lets check and try to find more details about our transaction log. There is a very useful DBCC command, LOGINFO which allows us to do that. <a href="http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-more-on-the-circular-nature-of-the-log/">Here is Paul Randal demonstrating</a> the circular nature of the log using DBCC LOGINFO.<br />
<br />
<pre class = "brush:sql">DBCC LOGINFO
GO
</pre><br />
You should see some thing similar to the below image. <br />
<br />
<a href="http://2.bp.blogspot.com/-gmn7r0x_Xis/UaeEdEnjbII/AAAAAAAAQ_M/h7kN5PKVoXk/s1600/1.jpg" imageanchor="1" ><img border="0" src="http://2.bp.blogspot.com/-gmn7r0x_Xis/UaeEdEnjbII/AAAAAAAAQ_M/h7kN5PKVoXk/s320/1.jpg" /></a><br />
<br />
So there are a couple of VLFs (Virtual Log Files) and on one of them, the status is <b>2</b>, which means '<b>Active</b>'. Can we find more information? What is in those log files? Yes we can, using the undocumented function <b>fn_dblog</b><br />
<br />
<pre class = "brush:sql">SELECT
[Previous LSN] ,
[Current LSN] ,
[Transaction ID] ,
[Operation],
[CONTEXT],
[AllocUnitName] ,
[Page ID],
[Slot ID],
[Offset in Row] ,
[Transaction Name] ,
[Begin Time],
[End Time],
[Number of Locks] ,
[Lock Information] ,
[RowLog Contents 0] ,
[RowLog Contents 1] ,
[RowLog Contents 2] ,
[RowLog Contents 3] ,
[RowLog Contents 4] ,
[Log Record],
[Log Record Fixed Length] ,
[Log Record Length]
FROM fn_dblog (null,null)
GO
</pre><br />
This will return a lot of data which may not make much sense to you at first. That's ok. We just created a table and there is no data in it. Let us insert some data and see how the log behaves. <br />
<br />
<pre class = "brush:sql">INSERT INTO CreditInfo VALUES ( DEFAULT, DEFAULT);
GO 200
</pre><br />
How does out VLF files look now?<br />
<br />
<pre class = "brush:sql">DBCC LOGINFO
GO
</pre><a href="http://3.bp.blogspot.com/-2Y5nxwDGed4/UaeItZVNuwI/AAAAAAAAQ_c/ofchaFimzw8/s1600/2.jpg" imageanchor="1" ><img border="0" src="http://3.bp.blogspot.com/-2Y5nxwDGed4/UaeItZVNuwI/AAAAAAAAQ_c/ofchaFimzw8/s320/2.jpg" /></a><br />
<br />
Our VLF count has increased and now two VLFs are marked as <b>Active</b>. Let us insert some more records and see what happens to the log<br />
<br />
<pre class = "brush:sql">INSERT INTO CreditInfo VALUES ( DEFAULT, DEFAULT);
GO 5000
DBCC LOGINFO
GO
</pre><br />
Since we seem to have a lot of data, may be try and read the log and see if we can make sense of the output from fn_dlog function output. Lets use the query we used above, but only this time, we know we only ever dealt with the CreditInfo table. So lets narrow the scope of the results based on this.<br />
<br />
<pre class = "brush:sql">SELECT
[Previous LSN] ,
[Current LSN] ,
[Transaction ID] ,
[Operation],
[CONTEXT],
[AllocUnitName] ,
[Page ID],
[Slot ID],
[Offset in Row] ,
[Transaction Name] ,
[Begin Time],
[End Time],
[Number of Locks] ,
[Lock Information] ,
[RowLog Contents 0] ,
[RowLog Contents 1] ,
[RowLog Contents 2] ,
[RowLog Contents 3] ,
[RowLog Contents 4] ,
[Log Record],
[Log Record Fixed Length] ,
[Log Record Length]
FROM fn_dblog (null,null)WHERE AllocUnitName LIKE '%CreditInfo%'
</pre><br />
You'll notice the details of the operation we performed, <b>LOP_INSERT_ROWS</b>. So is that the actual data? Yes and no. Its not actual data in the sense of data in a data file. It is knowledge about what happened to that data for SQL Server's use. I had to demonstrate this to our devs because they were claiming that there is credit card info in the log files as per the scanning tool. <br />
<br />
<a href="http://3.bp.blogspot.com/-OBsd7GDjnmE/UaeM_UByFPI/AAAAAAAAQ_s/C3juuOtLja4/s1600/3.jpg" imageanchor="1" ><img border="0" src="http://3.bp.blogspot.com/-OBsd7GDjnmE/UaeM_UByFPI/AAAAAAAAQ_s/C3juuOtLja4/s320/3.jpg" /></a><br />
<br />
Coming back to log, observe how the log file grows and how most of the VLFs are in Active mode. This means they are currently being used and are not marked for reuse yet. We obviously dont want our log file to grow indefinitely. It needs to be cleared, right? So what clears a transaction log file when the database is in Full Recovery Mode? Two things- First, When a checkpoint has occurs <i>since the last full backup</i> AND a transaction log backup. Lets issue a manual CHECKPOINT and do a log backup and see what happens to our log. <br />
<br />
<pre class = "brush:sql">CHECKPOINT
GO
BACKUP LOG storagefun TO DISK = 'D:\SQLData\storagefun_log.trn'
GO
</pre><br />
What happened? All the VLFs are still there, but the log backup has cleared the transaction log and the existing virtual log files are marked as inactive. So they can be used again for any transactions in future. Now, try to read the log again and limit your scope to CreditInfo table. <br />
<br />
<pre class = "brush:sql">SELECT
[Previous LSN] ,
[Current LSN] ,
[Transaction ID] ,
[Operation],
[CONTEXT],
[AllocUnitName] ,
[Page ID],
[Slot ID],
[Offset in Row] ,
[Transaction Name] ,
[Begin Time],
[End Time],
[Number of Locks] ,
[Lock Information] ,
[RowLog Contents 0] ,
[RowLog Contents 1] ,
[RowLog Contents 2] ,
[RowLog Contents 3] ,
[RowLog Contents 4] ,
[Log Record],
[Log Record Fixed Length] ,
[Log Record Length]
FROM fn_dblog (null,null) WHERE AllocUnitName LIKE '%CreditInfo%'
GO
</pre><br />
Boom! there is nothing related to CreditInfo object. Didn't I tell you that the log backup cleared the transaction log?<br />
<br />
But you can see that data in the data file. Use a simple select. <br />
<br />
Ok, this is credit card data and one internet kitten will die for each row you keep in your database. Its vile. So lets get rid of it. After you truncate the table, will you find any information about it in the log file? Yes, you will. You'll see LOP_MODIFY_ROW operation. <br />
<br />
<pre class = "brush:sql">TRUNCATE TABLE CreditInfo;
SELECT
[Previous LSN] ,
[Current LSN] ,
[Transaction ID] ,
[Operation],
[CONTEXT],
[AllocUnitName] ,
[Page ID],
[Slot ID],
[Offset in Row] ,
[Transaction Name] ,
[Begin Time],
[End Time],
[Number of Locks] ,
[Lock Information] ,
[RowLog Contents 0] ,
[RowLog Contents 1] ,
[RowLog Contents 2] ,
[RowLog Contents 3] ,
[RowLog Contents 4] ,
[Log Record],
[Log Record Fixed Length] ,
[Log Record Length]
FROM fn_dblog (null,null) WHERE AllocUnitName LIKE '%CreditInfo%'
</pre><br />
The credit card scan utility still sees the data, we just truncated the table and still. Ok, lets do a log backup.<br />
<br />
<pre class = "brush:sql">BACKUP LOG storagefun TO DISK = 'D:\SQLData\storagefun_lon1.trn'
</pre><br />
Read the log again<br />
<br />
<pre class = "brush:sql">SELECT
[Previous LSN] ,
[Current LSN] ,
[Transaction ID] ,
[Operation],
[CONTEXT],
[AllocUnitName] ,
[Page ID],
[Slot ID],
[Offset in Row] ,
[Transaction Name] ,
[Begin Time],
[End Time],
[Number of Locks] ,
[Lock Information] ,
[RowLog Contents 0] ,
[RowLog Contents 1] ,
[RowLog Contents 2] ,
[RowLog Contents 3] ,
[RowLog Contents 4] ,
[Log Record],
[Log Record Fixed Length] ,
[Log Record Length]
FROM fn_dblog (null,null) WHERE AllocUnitName LIKE '%CreditInfo%'
</pre><br />
Still see the records? How about a checkpoint<br />
<br />
<pre class = "brush:sql">CHECKPOINT
GO
</pre><br />
At this point, if you try to read again, you wont see any records related to CreditInfo and its completely gone. I was able to explain about FULL Recovery mode, nature of log using this example to our devs and I hope you find it useful too. <br />
<br />
yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-14844972857376097312013-05-29T11:48:00.003-07:002013-05-29T11:55:05.432-07:00Python Classes and ObjectsIt took me 4 hours to figure out whats going on with classes and objects in Python and I am not even sure if I understand them completely yet. Yeah, I am dumb. Anyways - I was able to complete this 'Hello World' kind of program for calculating account balance and fees. <br />
<br />
<pre class = "brush:py">__author__ = 'RK Kuppala'
class BankAccount:
def __init__(self, balance):
"""Creates an account with the given balance."""
self.balance = balance
self.counter = 0
def withdraw(self, amount):
"""
Withdraws the amount from the account. Each withdrawal resulting in a
negative balance also deducts a penalty fee of 5 dollars from the balance.
"""
if self.balance - amount < 0:
self.balance -= amount+5
self.counter += 5
else:
self.balance -= amount
def deposit(self, amount):
"""Deposits the amount into the account."""
self.balance += amount
def get_balance(self):
"""Returns the current balance in the account."""
return self.balance
def get_fees(self):
"""Returns the total fees ever deducted from the account."""
return self.counter
#lets test if it works
my_account = BankAccount(5)
my_account.withdraw(15)
my_account.deposit(20)
my_account.withdraw(60)
my_account.deposit(20)
my_account.withdraw(60)
print my_account.get_balance(), my_account.get_fees()
</pre>yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-58150894449592637822013-05-24T10:41:00.001-07:002013-05-29T00:47:21.384-07:00Another simple Python game: MemoryThis week's assignment is a simple and straight forward Memory game. You play with a set of cards facedown and you are allowed to flip and view two cards in one turn and if they both match, they remain opened. If they don't they go back to facedown mode and you use your memory to match the cards. You can <a href="http://www.codeskulptor.org/#user14_KGExQ3fM96_0.py">play it here</a>: <br />
<br />
UPDATE: I was doing peer evaluation for this and I found one implementation which is better than mine. It uses classes. <a href="http://www.codeskulptor.org/#user14_cUvfKV2wKm_3.py">Check it here</a>: <br />
<br />
<pre class = "brush:py">__author__ = 'RK Kuppala'
# implementation of card game - Memory
import simplegui
import random
num_list = []
exposed = []
state = 0
first_pick = 0
second_pick = 0
moves = 0
# helper function to initialize globals
def init():
global num_list, exposed, moves
moves = 0
num_list = [i%8 for i in range(16)]
random.shuffle(num_list)
exposed = [False for i in range(16)]
pass
# define event handlers
def mouseclick(pos):
global state, first_pick, second_pick, moves
this_pick = int(pos[0] / 50)
if state == 0:
first_pick = this_pick
exposed[first_pick] = True
state = 1
moves += 1
elif state == 1:
if not exposed[this_pick]:
second_pick = int(pos[0] / 50)
exposed[second_pick] = True
state = 2
moves += 1
elif state == 2:
if not exposed[this_pick]:
if num_list[first_pick] == num_list[second_pick]:
pass
else:
exposed[first_pick] = False
exposed[second_pick] = False
first_pick = this_pick
exposed[first_pick] = True
state = 1
moves += 1
l.set_text("Moves = " + str(moves))
pass
# cards are logically 50x100 pixels in size
def draw(canvas):
offset = 50
hor_pos = -25
for i in range(len(num_list)):
hor_pos += offset
canvas.draw_text(str(num_list[i]), [hor_pos, 50], 30, "White")
exposed_pos = -50
for i in exposed:
exposed_pos += offset
if not i:
canvas.draw_polygon([(exposed_pos, 0), (exposed_pos + 50, 0), (exposed_pos + 50, 100), (exposed_pos + 0, 100)], 10, "White", "Orange")
# create frame and add a button and labels
frame = simplegui.create_frame("Memory", 800, 100)
frame.add_button("Restart", init)
l=frame.add_label("Moves = 0")
# initialize global variables
init()
# register event handlers
frame.set_mouseclick_handler(mouseclick)
frame.set_draw_handler(draw)
# get things rolling
frame.start()
</pre>yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-4568963020605168862013-05-18T05:54:00.000-07:002013-05-18T10:15:15.260-07:00Simple Python Game: PongHere goes this week's game. The classic Pong game. You can play this game on your <a href="http://www.codeskulptor.org/#user13_Ov15iuXkR9_1.py">browser here</a> (don't try this on IE). Its a two player game. Player 1 can use 'w' and 's' keys and Player 2 can use ↑ and ↓. I would like to make this game working locally on Windows using tkinter. Let see <br />
<br />
<pre class = "brush:py"># Author: RK
import simplegui
import random
# initialize globals - pos and vel encode vertical info for paddles
WIDTH = 600
HEIGHT = 400
BALL_RADIUS = 20
PAD_WIDTH = 8
PAD_HEIGHT = 80
HALF_PAD_WIDTH = PAD_WIDTH / 2
HALF_PAD_HEIGHT = PAD_HEIGHT / 2
ball_pos = [WIDTH/2, HEIGHT/2]
ball_vel = [-random.randrange(60, 180) / 60, random.randrange(120, 240) / 60]
paddle1_pos = HEIGHT/2
paddle2_pos = HEIGHT/2
# helper function that spawns a ball by updating the
# ball's position vector and velocity vector
# if right is True, the ball's velocity is upper right, else upper left
def ball_init(right):
global ball_pos, ball_vel # these are vectors stored as lists
ball_pos = [WIDTH/2,HEIGHT/2]
ball_vel[1] = -random.randrange(60, 180)/60
if right == True:
ball_vel[0] = random.randrange(120, 240)/60
else:
ball_vel[0] = -random.randrange(120, 240)/60
pass
def new_game():
global paddle1_pos, paddle2_pos, paddle1_vel, paddle2_vel
global score1, score2
paddle1_pos = HEIGHT/2
paddle2_pos = HEIGHT/2
paddle1_vel = 0
paddle2_vel = 0
score1 = 0
score2 = 0
ball_init(0 == random.randrange(0, 11) % 2)
pass
# define event handlers
def draw(c):
global score1, score2, paddle1_pos, paddle2_pos, paddle1_vel, paddle2_vel, ball_pos, ball_vel
# update paddle's vertical position, keep paddle on the screen
if paddle1_pos < (HALF_PAD_HEIGHT) and paddle1_vel < 0:
paddle_vel = 0
if paddle2_pos < (HALF_PAD_HEIGHT) and paddle2_vel < 0:
paddle2_vel = 0
if paddle1_pos > (HEIGHT - (HALF_PAD_HEIGHT)) and paddle1_vel >0:
paddle2_vel = 0
if paddle2_pos > (HEIGHT - (HALF_PAD_HEIGHT)) and paddle2_vel > 0:
paddle2_vel = 0
paddle1_pos += paddle1_vel
paddle2_pos += paddle2_vel
# draw mid line and gutters
c.draw_line([WIDTH / 2, 0],[WIDTH / 2, HEIGHT], 1, "White")
c.draw_line([PAD_WIDTH, 0],[PAD_WIDTH, HEIGHT], 1, "White")
c.draw_line([WIDTH - PAD_WIDTH, 0],[WIDTH - PAD_WIDTH, HEIGHT], 1, "White")
# draw paddles
c.draw_polygon([(0, paddle1_pos-HALF_PAD_HEIGHT), (0, paddle1_pos+HALF_PAD_HEIGHT), (PAD_WIDTH-2, paddle1_pos+HALF_PAD_HEIGHT),(PAD_WIDTH-2,paddle1_pos-HALF_PAD_HEIGHT)], PAD_WIDTH-1, "White","White")
c.draw_polygon([(WIDTH, paddle2_pos-HALF_PAD_HEIGHT), (WIDTH, paddle2_pos+HALF_PAD_HEIGHT), (WIDTH-PAD_WIDTH+2, paddle2_pos+HALF_PAD_HEIGHT),(WIDTH-PAD_WIDTH+2,paddle2_pos-HALF_PAD_HEIGHT)], PAD_WIDTH-1, "White","White")
# update ball
ball_pos[0] += ball_vel[0]
ball_pos[1] += ball_vel[1]
if ball_pos[1] >= (HEIGHT - BALL_RADIUS) or ball_pos[1] <= (BALL_RADIUS):
ball_vel[1] = -ball_vel[1]
if ball_pos[0] <= (PAD_WIDTH + BALL_RADIUS):
if ball_pos[1] < (paddle1_pos - HALF_PAD_HEIGHT) or ball_pos[1] > (paddle1_pos + HALF_PAD_HEIGHT):
ball_init(True)
score2 += 1
else:
ball_vel[0] = -ball_vel[0] * 1.1
if ball_pos[0] >= (WIDTH - PAD_WIDTH - BALL_RADIUS):
if ball_pos[1] < (paddle2_pos - HALF_PAD_HEIGHT) or ball_pos[1] > (paddle2_pos + HALF_PAD_HEIGHT):
ball_init(False)
score1 += 1
else:
ball_vel[0] = -ball_vel[0] * 1.1
# draw ball and scores
c.draw_circle(ball_pos, BALL_RADIUS, 2, "Yellow", "White")
c.draw_text(str(score1), (170, 50), 36, "Yellow")
c.draw_text(str(score2), (400, 50), 36, "Yellow")
def keydown(key):
global paddle1_vel, paddle2_vel
if key == simplegui.KEY_MAP['w']:
paddle1_vel = -4
elif key == simplegui.KEY_MAP['s']:
paddle1_vel = 4
elif key == simplegui.KEY_MAP['up']:
paddle2_vel = -4
elif key == simplegui.KEY_MAP['down']:
paddle2_vel = 4
def keyup(key):
global paddle1_vel, paddle2_vel
if key == simplegui.KEY_MAP['w']:
paddle1_vel = 0
elif key == simplegui.KEY_MAP['s']:
paddle1_vel = 0
elif key == simplegui.KEY_MAP['up']:
paddle2_vel = 0
elif key == simplegui.KEY_MAP['down']:
paddle2_vel = 0
# create frame
frame = simplegui.create_frame("Pong", WIDTH, HEIGHT)
frame.set_draw_handler(draw)
frame.set_keydown_handler(keydown)
frame.set_keyup_handler(keyup)
# start frame
frame.start()
new_game()
</pre>yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-22830811118416836392013-05-16T02:12:00.004-07:002013-05-16T20:01:56.973-07:00Step By Step: SQL Server 2012 AlwaysOn Availability Groups Lab Setup Using VirtualBox<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
I was inspired to write this post by the super awesome DBA, Jonathan Kehayias of sqlskills. He has a great series of articles on setting up a home lab using virtualbox and evaluation software from Microsoft available as free downloads. Check out his articles on <a href="http://www.sqlskills.com/blogs/jonathan/category/building-a-test-environment/">sqlskills</a>. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
If you are on this page and reading this, chances are that you already know what AlwaysOn is so I am not going to explain it again.For a quick overview of this new High availability/disaster recovery solution, <a href="http://msdn.microsoft.com/en-us/library/ff877884.aspx">go read this article</a>. Now lets get started with building our lab.</div>
<br />
<b>What do you need?</b><br />
<br />
In order to follow this tutorial, you need the following<br />
<br />
<a href="https://www.virtualbox.org/wiki/Downloads">The latest version of VirtualBox</a> <br />
<a href="http://technet.microsoft.com/en-in/evalcenter/dd459137.aspx">Windows server 2008 R2 with sp1, evaluation edition</a><br />
<a href="http://www.microsoft.com/en-in/download/details.aspx?id=29066">SQL Server 2012 Evaluation edition</a><br />
<br />
<div style="text-align: justify;">
Coming to the hardware, you need a decent laptop/desktop with 8 GB RAM and a good processor (may be core i5). You can try with lesser RAM too, but things will be a little slow. You need at least 4 VMs to test a basic setup of AlwaysOn. The server that acts as a domain controller can not be a part of availability groups. Don't worry about storage for all these VMs, because we are going to use linked clones that make use of resources from a base VM. </div>
<br />
<a name='more'></a><br /><br />
<b>Creating a base VM</b><br />
<br />
I assume you've already installed virtualbox by now. Launch VirtualBox and create a new VM. Give it a name you please. May be SQLBASEVM? <br />
<a href="http://2.bp.blogspot.com/-_WmTeb_EYEM/UZHrYDUBbsI/AAAAAAAAQrc/zSzWB5KnwXg/s1600/1.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-_WmTeb_EYEM/UZHrYDUBbsI/AAAAAAAAQrc/zSzWB5KnwXg/s320/1.jpg" /></a><br />
<br />
<div style="text-align: justify;">
I am going to assign 2 GB of RAM to the base VM because we are going to perform windows server installation and I want it to be fast. You may later adjust the amount of RAM based on your available resources from settings. </div>
<br />
<a href="http://4.bp.blogspot.com/--cC3Bv-cJw4/UZHr_uzctGI/AAAAAAAAQrk/S2DLXFGOboc/s1600/2basevm_memory.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/--cC3Bv-cJw4/UZHr_uzctGI/AAAAAAAAQrk/S2DLXFGOboc/s320/2basevm_memory.jpg" /></a><a href="http://1.bp.blogspot.com/-8MCmGW9WGPA/UZHwcIRWMFI/AAAAAAAAQr0/JPiPS572Ang/s1600/3basevm_hd.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-8MCmGW9WGPA/UZHwcIRWMFI/AAAAAAAAQr0/JPiPS572Ang/s320/3basevm_hd.jpg" /></a><br />
<br />
<a href="http://3.bp.blogspot.com/-pCIV8Kjs91c/UZHwiu-5ecI/AAAAAAAAQr8/OMZel29vjHI/s1600/5basevm_dynamicallyallocated.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-pCIV8Kjs91c/UZHwiu-5ecI/AAAAAAAAQr8/OMZel29vjHI/s320/5basevm_dynamicallyallocated.jpg" /></a> <a href="http://2.bp.blogspot.com/-CHwQGKFw1fk/UZHwn4pNVaI/AAAAAAAAQsE/gQxvYjBFPos/s1600/6basevm_storage.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-CHwQGKFw1fk/UZHwn4pNVaI/AAAAAAAAQsE/gQxvYjBFPos/s320/6basevm_storage.jpg" /></a><br />
<br />
<br />
Now you need to add Windows server installation .ISO file you downloaded to the base VM. We will later boot from this ISO and install Windows server. <br />
<br />
<a href="http://3.bp.blogspot.com/-cSO752vfnm8/UZHxmdWqeQI/AAAAAAAAQsQ/9UAmrACKYU4/s1600/7basevm_addOSDrive.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-cSO752vfnm8/UZHxmdWqeQI/AAAAAAAAQsQ/9UAmrACKYU4/s320/7basevm_addOSDrive.jpg" /></a><br />
<br />
Now we are all set to install Windows. Go ahead and start the VM and install Windows 2008 R2 with sp1. Once done, logon to the machine and the first thing that you should do it to install guest additions on your new VM. This allows you to have a smooth user experience and enhance performance. <a href="http://www.dedoimedo.com/computers/virtualbox-guest-addons.html">Here is a link</a> that explains how to do it. <br />
<br />
<a href="http://1.bp.blogspot.com/-ckxsLcvkbuE/UZH2SCH7jJI/AAAAAAAAQsc/5Qe9FlgWt7o/s1600/9basevm_guestadditions.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-ckxsLcvkbuE/UZH2SCH7jJI/AAAAAAAAQsc/5Qe9FlgWt7o/s320/9basevm_guestadditions.jpg" /></a><br />
<br />
<div style="text-align: justify;">
You may have to reboot the VM after installing guest additions. It is now time to add required features and roles to our base VM. We will be creating multiple machines that will act as Domain controller, SQL Servers and adding features like .net framework, Active directory services and failover clustering will save us a lot of time down the line. SQL Server's AlwaysON Aavailability Groups is built on top of Windows Failover Clustering platform and it is a pre requisite. Add the following features and roles:</div>
<br />
<b>.NET Framework 3.5.1</b><br />
<b>Failover Clustering</b><br />
Multipath IO (This is not required for AlwaysOn, but if you want to test clustering using the same base VM, enable this)<br />
<b>Active Directory domain services role</b><br />
<br />
<a href="http://1.bp.blogspot.com/-Vgw3lEW2JJI/UZH4cOavanI/AAAAAAAAQso/nMUmA1-MKVI/s1600/10basevm_reolesandfeatures2.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-Vgw3lEW2JJI/UZH4cOavanI/AAAAAAAAQso/nMUmA1-MKVI/s320/10basevm_reolesandfeatures2.jpg" /></a><a href="http://4.bp.blogspot.com/-SRpahrFcHdo/UZH4cGxeuUI/AAAAAAAAQss/r8jmK8DBFRM/s1600/9basevm_rolesandfeatures.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-SRpahrFcHdo/UZH4cGxeuUI/AAAAAAAAQss/r8jmK8DBFRM/s320/9basevm_rolesandfeatures.jpg" /></a> <br />
<br />
Install these and <i><b>shutdown</b></i> the VM. Don't boot it yet. <br />
<br />
<b>Network Adapters</b><br />
<br />
<div style="text-align: justify;">
We need to enable network adapters for the base VM. If you care about accessing internet from inside VMs, you can leave the default network adapter. It will usually be attached to NAT. We need one more network adapter to simulate the domain network for other servers that will form the availability group. Enable a network adapter by right clicking on the VM => settings => Network => Adapter2. Attach it to Internal network and call it "Domain Network"</div>
<br />
<a href="http://3.bp.blogspot.com/-0Jv3WfVfQpU/UZH-UG205TI/AAAAAAAAQtA/660wm8kfoWA/s1600/11basevm_net1.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-0Jv3WfVfQpU/UZH-UG205TI/AAAAAAAAQtA/660wm8kfoWA/s320/11basevm_net1.jpg" /></a><br />
<br />
<div style="text-align: justify;">
One last step before we can create clones from the base VM is to generalize the VM to provide new like experience for all the clones. We can use <a href="http://en.wikipedia.org/wiki/Sysprep#Purpose">sysprep</a> for that. Now start the Base VM, login to the server, go to RUN => type <i>sysprep</i>. It will take you to C:\Windows\System32\Sysprep. Run sysprep exe as an Admin. Select the options in below pic. Make sure Shutdown option is selected, not reboot:</div>
<br />
<a href="http://3.bp.blogspot.com/-483vkoNN-lk/UZIAKBUQb0I/AAAAAAAAQtM/6a1PkZY_7Cs/s1600/14basevm_sysprep.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-483vkoNN-lk/UZIAKBUQb0I/AAAAAAAAQtM/6a1PkZY_7Cs/s320/14basevm_sysprep.jpg" /></a> <br />
<br />
<b>Creating Clones</b> <br />
<br />
We have the base VM ready and we can now get down to real business of setting up our test environment. On VirtualBox, right click on the base VM and click "Clone"<br />
<br />
<a href="http://4.bp.blogspot.com/-sPJMwSo7Ris/UZILDGVSOJI/AAAAAAAAQtY/IxNgJ-xhDjU/s1600/15CreateClone.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-sPJMwSo7Ris/UZILDGVSOJI/AAAAAAAAQtY/IxNgJ-xhDjU/s320/15CreateClone.jpg" /></a><br />
<br />
In the next dialog box, name your VM and make sure that you select re-initialize mac address for all network cards. Failing to do this will result in issues when we add these VMs to a cluster and perform validation tests. Also, make sure that it is a Linked clone, not Full Clone. <br />
<br />
<a href="http://1.bp.blogspot.com/-_DIA1gfww-4/UZIMVUZE0-I/AAAAAAAAQtk/0OPMDIX32Ek/s1600/16CreateClone_SQLDC.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-_DIA1gfww-4/UZIMVUZE0-I/AAAAAAAAQtk/0OPMDIX32Ek/s320/16CreateClone_SQLDC.jpg" /></a> <a href="http://1.bp.blogspot.com/-1pCWpcZ_zAs/UZIMxFjJpOI/AAAAAAAAQts/a99ZgpCwc1w/s1600/17linkedclone.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-1pCWpcZ_zAs/UZIMxFjJpOI/AAAAAAAAQts/a99ZgpCwc1w/s320/17linkedclone.jpg" /></a><br />
<br />
Repeat these same steps for three more linked clones from base VM and give them appropriate names. In the end, you should have 4 linked clones. I have the following:<br />
<br />
<b>SQLDC</b> - Domain controller<br />
<b>SQL01</b> - Primary SQL Server<br />
<b>SQL02</b> - SQL Secondary 1<br />
<b>SQL03</b> - SQL secondary 2<br />
<br />
<b>Configuring Domain Controller</b><br />
<br />
If you have followed this demo so far, you'll recollect that we gave 2 GB RAM to the base VM. All the clones created from the base will inherit this and you should adjust your value to fit your available resources. I prefer to grant more RAM to the server, configure it and then change it back to a lesser value after configuring. This way, installations and setup will be faster. Since a domain controller doesn't do much, you can grant somewhere between 700 MB to 1024 MB after initial setup. <br />
<br />
Start SQLDC (or your domain controller VM) and log on to the server. It will prompt you to set an Admin password - go ahead and do that. Go to start and type 'dcpromo'. Find the rest of the instructions in these pictures below. Click on the pic to enlarge it.<br />
<br />
<a href="http://3.bp.blogspot.com/-idbyObXUGHw/UZIRurUJsyI/AAAAAAAAQt8/6u2EXQsM50A/s1600/18_SQLDC_DCPROMO.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-idbyObXUGHw/UZIRurUJsyI/AAAAAAAAQt8/6u2EXQsM50A/s320/18_SQLDC_DCPROMO.jpg" /></a><a href="http://3.bp.blogspot.com/-HzWqa5tZK_4/UZIRyIVJ80I/AAAAAAAAQuE/EcDVH3c6alo/s1600/19_SQLDC_02.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-HzWqa5tZK_4/UZIRyIVJ80I/AAAAAAAAQuE/EcDVH3c6alo/s320/19_SQLDC_02.jpg" /></a><br />
<br />
<a href="http://1.bp.blogspot.com/-2DoFN9wKJkI/UZISFWir_5I/AAAAAAAAQuU/6cdI-JVhqXk/s1600/20_SQLDC_03.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-2DoFN9wKJkI/UZISFWir_5I/AAAAAAAAQuU/6cdI-JVhqXk/s320/20_SQLDC_03.jpg" /></a><a href="http://3.bp.blogspot.com/-obbKb9IKbms/UZISFM33KSI/AAAAAAAAQuM/sY6EqxDXMe0/s1600/21_SQLDC_04.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-obbKb9IKbms/UZISFM33KSI/AAAAAAAAQuM/sY6EqxDXMe0/s320/21_SQLDC_04.jpg" /></a><a href="http://3.bp.blogspot.com/-24iacrBmZ6g/UZISFe8Vy8I/AAAAAAAAQuQ/Bu-GvdVirL0/s1600/22_SQLDC_05.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-24iacrBmZ6g/UZISFe8Vy8I/AAAAAAAAQuQ/Bu-GvdVirL0/s320/22_SQLDC_05.jpg" /></a><a href="http://2.bp.blogspot.com/-klxZpLh_R9c/UZISnMh1flI/AAAAAAAAQuk/-OVa2CAmCOI/s1600/23_SQLDC_06.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-klxZpLh_R9c/UZISnMh1flI/AAAAAAAAQuk/-OVa2CAmCOI/s320/23_SQLDC_06.jpg" /></a><br />
<br />
Select that the <b>computer will assign IP addresses automatically</b>. Say Yes to the next dialog box and choose a Domain Administrator password. Reboot VM after domain setup. When the system starts again, use your domain admin account and password to login. In my case, the domain is TESLA (named after the greatest scientist ever, not the car company). I login using <b>TESLA\Administrator</b>, my password. After reboot, go to Server Manager and Configure network. If you made no changes to the setup and followed this tutorial as is, you will see two network adapters on your VM. One attached to NAT and one for Domain networking. You need to figure out which of them is the domain network adapter because we are going to make some changes to it. The easy way to do it is by simulating a cable disconnection. Keep the VM running in a windows. Go to VirtualBox and right click on SQLDC VM. Go to network. Under adapter 2, you'll see Domain Network connection. Click on Advanced there and note that "cable connected" is enabled. uncheck that option, click OK and observe which network adapter gets disconnected inside the VM. As soona s you find it, just rename it to DomainNet. Check cable connected again and click OK. Remember this tip as you'll do it again with other VMs. <br />
<br />
<a href="http://2.bp.blogspot.com/-HFgxWrzXdAM/UZIXJbZgKEI/AAAAAAAAQu8/cQXzRc0lnxY/s1600/25_SQLDC_DCNetwork01.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-HFgxWrzXdAM/UZIXJbZgKEI/AAAAAAAAQu8/cQXzRc0lnxY/s320/25_SQLDC_DCNetwork01.jpg" /></a> <br />
<br />
Now double click the DomainNet adapter from above step and go to properties. Select TCP/IPV4. On the general tab, select "use following IP address". Check the image below. You may use the same values. You may also have to change the computer name because the linked clone process creates funny names. I renamed Domain controller to SQLDC (The VM name was SQLDC was all along, but VM name and the actual computer name are not same. I suggest that it be changed to match VM name). Reboot if required and bring it back online. <br />
<br />
<a href="http://3.bp.blogspot.com/-5O-I80CZcKU/UZSGdDyLWXI/AAAAAAAAQ38/6e6QjsAsDOM/s1600/26_SQLDC_NetworkSetup.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-5O-I80CZcKU/UZSGdDyLWXI/AAAAAAAAQ38/6e6QjsAsDOM/s320/26_SQLDC_NetworkSetup.jpg" /></a><br />
<br />
<b>Configuring Service accounts permissions for AlwaysOn</b><br />
<br />
Log on to SQLDC, go to start and type "Active Directory Users and Computers". Create a new user called 'admin', choose a password. Select Password never expires. <br />
<br />
<a href="http://2.bp.blogspot.com/-L-Cg6UNKnH0/UZIdH0PrmaI/AAAAAAAAQvY/-6zwl6OTAZU/s1600/27_SQLDC_Users.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-L-Cg6UNKnH0/UZIdH0PrmaI/AAAAAAAAQvY/-6zwl6OTAZU/s320/27_SQLDC_Users.jpg" /></a><br />
<br />
Go ahead and make that account a domain admin and you can then use <b>TESLA\admin</b> for configuring cluster, running SQL Services etc. This is only for the sake of simplicity of this demo and to avoid a few extra steps but it is not a good practice on production servers. Each SQL Server service accounts should have only the least necessary access and privileges, nothing more or nothing less.We are now almost done with Domain controller setup. <br />
<br />
<b>Joining SQL01, SQL02, SQL03 to the domain</b><br />
<br />
Follow the below steps for each VM.<br />
<br />
1. Log on to the VM, find the network adapter that is connected to internal domain network. Recall the cable connected / unplug trick. <br />
2. Rename the adapter to DomainNet for easier management<br />
3. Go to properties of DomainNet, and configure it to use static IP.<br />
<br />
The IP of SQL01 should be <b>192.168.80.2</b>, SQL02 should be <b>192.168.80.3</b>, SQL03 should be <b>192.168.80.4</b>. For each of these VMs, the preferred DNS will be the static IP address of domain controller, i.e <b>192.168.80.1</b>. I will demonstrate that for SQL01 VM and you can take care of the rest. <br />
<br />
Log on to SQL01, if you are booting it first time, it will ask you to create/change password. Login and go to network properties. Using the disable and enable "cable connected" property trick, identify Domain Network adapter and rename it to DomainNet. <br />
<br />
<a href="http://1.bp.blogspot.com/-nVJDXb67fiY/UZSHfDOSg_I/AAAAAAAAQ4I/XiJzIvGyO9U/s1600/28_SQL01_Network.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-nVJDXb67fiY/UZSHfDOSg_I/AAAAAAAAQ4I/XiJzIvGyO9U/s320/28_SQL01_Network.jpg" /></a><br />
<br />
From computer properties, change computer name to match the VM name and change domain.<br />
<br />
<a href="http://4.bp.blogspot.com/-YVl-MBKopPI/UZIml0UYCyI/AAAAAAAAQvw/3k8EsbAZrs8/s1600/29_SQL01_ComputerName.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-YVl-MBKopPI/UZIml0UYCyI/AAAAAAAAQvw/3k8EsbAZrs8/s320/29_SQL01_ComputerName.jpg" /></a><br />
<br />
You will be prompted for domain user name and password. Use TESLA\admin, your password. <br />
<br />
<a href="http://2.bp.blogspot.com/-UMeBmNHurgE/UZInDAZ8QUI/AAAAAAAAQv8/rIg92IM85YA/s1600/30_SQL01_Domainadmin.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-UMeBmNHurgE/UZInDAZ8QUI/AAAAAAAAQv8/rIg92IM85YA/s320/30_SQL01_Domainadmin.jpg" /></a><br />
<br />
You will be greeted with a 'welcome to domain' message. And the system will restart. Congrats! you have successfully added SQL01 to the domain. <b>Repeat the same steps for SQL02 and SQL03</b>, the only difference is the static IP and the rest is same. <br />
<br />
By now, you should have a fully functioning domain controller, SQLDC and SQL01, SQL02, SQL03 joined to the domain. We can now get down to the interesting stuff. <br />
<br />
<b>Installing SQL Server</b><br />
<br />
Start SQL01 VM and login using the domain user TESLA\admin. Go to Devices=> CD/DVD devices => Select SQL Server 2012 installation media ISO from your local machine. This will mount the install media to the VM. Start installing. <b>Repeat this process for SQL02 and SQL03 as well</b>. <br />
<br />
<a href="http://3.bp.blogspot.com/-Gw4HsTTPmwA/UZI2R46q-SI/AAAAAAAAQwQ/hruRcgLs1HE/s1600/31_SQL01_SQLInstall.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-Gw4HsTTPmwA/UZI2R46q-SI/AAAAAAAAQwQ/hruRcgLs1HE/s320/31_SQL01_SQLInstall.jpg" /></a><br />
<br />
The installation of SQL Server is as usual and doesn't require any special steps. Note that we need a regular installation on all three servers, not cluster installation. You can use <b>TESLA\admin</b> as the service account for all three installations. Again, this is not a recommended practice on production, its just that we are trying to make our setup simple by having a single account with enough permissions. <br />
<br />
<b>Firewall exceptions</b><br />
<br />
Once you have SQL 2012 installed on SQL01, SQL02 and SQL03 servers, we need to make sure that these three servers can talk to each other. Log on to SQL01 using domain account and go to start => type "fire wall". You should see <b>'Windows Firewall With Advanced Security'</b>. Click on it.Click on inbound rule and create a new rule for SQL Server. Select <b>Program</b> for rule type, and add the path <b>%ProgramFiles%\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe</b> to program path field. Select "Allow Connection" on the next screen. Click next and give the rule a meaningful name like "SQL Server Path". Follow similar steps to also add firewall exception on <b>port 1433</b>. In my tests, I found that TCP/IP network protocol is not enabled by default on SQL Server 2012 installations. So make sure that you enable TCP/IP from SQL Server configuration manager. <b>Repeat the same steps for SQL02 and SQL03</b>. <br />
<br />
<a href="http://4.bp.blogspot.com/-APzLB1RlBcc/UZJRklksJII/AAAAAAAAQwg/3DvBFRRDmiI/s1600/32_SQL01_Firewall_inbound.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-APzLB1RlBcc/UZJRklksJII/AAAAAAAAQwg/3DvBFRRDmiI/s320/32_SQL01_Firewall_inbound.jpg" /></a><a href="http://3.bp.blogspot.com/-8jJKUMf3-rA/UZJSoyY2oJI/AAAAAAAAQws/P0Ugb6hp-CM/s1600/33_SQL01_FirewallPath.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-8jJKUMf3-rA/UZJSoyY2oJI/AAAAAAAAQws/P0Ugb6hp-CM/s320/33_SQL01_FirewallPath.jpg" /></a> <br />
<br />
You should now be able to open SSMS on SQL01 and connect to SQL02, SQL03. Since we used TESLA\admin as the account while performing the initial installations and since I added TESLA\admin as SQL admin on each instance, we don't have to worry about configuring logins. But on production systems, you might have to. <br />
<br />
<b>Create Cluster</b><br />
<br />
SQL Server 2012 AlwaysON groups is built on <b>Windows Failover Clustering Services platform(WFCS)</b> and that is why we enabled that feature on our base VM in the beginning. It is now time to create a cluster. Make sure all your servers VMs are running (If running multiple virtualbox windows annoys you, you might want to run VMs in headless mode. Check <a href="http://binyoga.blogspot.in/2013/04/run-virtualbox-vms-in-background.html">this post</a> for details). Log on to your primary SQL Server <b>SQL01</b>. Go to start, type "cluster". You'll see <b>Failover Cluster Manager</b>. Launch it. Right click on Cluster Manager and <b>Create a Cluster</b><br />
<a href="http://3.bp.blogspot.com/-fJ-ki2aC2iU/UZJ1EYHEkdI/AAAAAAAAQw8/4SmZVUQtS5k/s1600/34_SQL01_CreateCluster.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-fJ-ki2aC2iU/UZJ1EYHEkdI/AAAAAAAAQw8/4SmZVUQtS5k/s320/34_SQL01_CreateCluster.jpg" /></a><br />
<br />
Type the name of the primary SQL Server and click add. In this case, it is <b>SQL01.TESLA.YOGIRK.COM</b><br />
<br />
<a href="http://3.bp.blogspot.com/-f86Y94xa6lQ/UZJ1Y41XIJI/AAAAAAAAQxE/5OGPfS3o9Ug/s1600/35_SQL01_Cluster02.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-f86Y94xa6lQ/UZJ1Y41XIJI/AAAAAAAAQxE/5OGPfS3o9Ug/s320/35_SQL01_Cluster02.jpg" /></a><br />
<br />
Click next and select <b>Run configuration validation tests</b> in the next window. After a couple of screens, select <b>Run All Tests</b>. <br />
<a href="http://3.bp.blogspot.com/-5d7LzoMX8gc/UZJ13folBFI/AAAAAAAAQxM/mXwQNwWJwEc/s1600/36_SQL01_Cluster03.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-5d7LzoMX8gc/UZJ13folBFI/AAAAAAAAQxM/mXwQNwWJwEc/s320/36_SQL01_Cluster03.jpg" /></a><br />
<br />
You should see the confirmation like below if everything went according to the plan. <br />
<br />
<a href="http://1.bp.blogspot.com/-8hlGcT0oPqk/UZJ2vGM_d8I/AAAAAAAAQxU/6RcvNyJmlFI/s1600/37_SQL01_Cluster04.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-8hlGcT0oPqk/UZJ2vGM_d8I/AAAAAAAAQxU/6RcvNyJmlFI/s320/37_SQL01_Cluster04.jpg" /></a><br />
<br />
On the next screen, give a name to your cluster. I named mine <b>KOALEMOS</b> (Koalemos is the Greek god of stupidity and foolishness. Greeks had a God for stupidity too - how cool is that!). After the name, choose a Static IP for your cluster. I chose <b>192.168.80.10</b> click next and next. <br />
<br />
<a href="http://3.bp.blogspot.com/-uo9RvZMPTgI/UZSIndL6DSI/AAAAAAAAQ4U/eju6Ydug7qc/s1600/39_Custer_Correct.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-uo9RvZMPTgI/UZSIndL6DSI/AAAAAAAAQ4U/eju6Ydug7qc/s320/39_Custer_Correct.jpg" /></a><br />
<br />
Go to <b>Failover Cluster Manager</b> and select our newly created cluster and locate <b>Cluster Core Resources</b>. <br />
<br />
<a href="http://4.bp.blogspot.com/-0q5ViMNLxmI/UZJ50W59cVI/AAAAAAAAQx4/IZIPQ1bQ_5M/s1600/39_SQL01_Cluster06.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-0q5ViMNLxmI/UZJ50W59cVI/AAAAAAAAQx4/IZIPQ1bQ_5M/s320/39_SQL01_Cluster06.jpg" /></a><br />
It should be up and running.<br />
<br />
<a href="http://3.bp.blogspot.com/-CS3z3hzYW2w/UZSKOvf3oxI/AAAAAAAAQ4k/PTPGzZzKUuI/s1600/40_SQL01_Cluster07.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-CS3z3hzYW2w/UZSKOvf3oxI/AAAAAAAAQ4k/PTPGzZzKUuI/s320/40_SQL01_Cluster07.jpg" /></a><br />
<br />
Now that our Cluster is online, lets add the other nodes - <b>SQL02</b> and <b>SQL03</b>. Follow screenshots below:<br />
<br />
<a href="http://2.bp.blogspot.com/-QIZh5Pf3bd4/UZJ-R2VqWYI/AAAAAAAAQy8/9ijO3id88DA/s1600/43_Addnode01.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-QIZh5Pf3bd4/UZJ-R2VqWYI/AAAAAAAAQy8/9ijO3id88DA/s320/43_Addnode01.jpg" /></a><a href="http://3.bp.blogspot.com/-t4r-bx5slts/UZJ-E-0wUQI/AAAAAAAAQyk/WLMDBzaKsMo/s1600/44_Addnodes02.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-t4r-bx5slts/UZJ-E-0wUQI/AAAAAAAAQyk/WLMDBzaKsMo/s320/44_Addnodes02.jpg" /></a><a href="http://2.bp.blogspot.com/-qnZ7WJUY4nc/UZJ-E5CJHdI/AAAAAAAAQyo/ne2dcYqddpg/s1600/54_Addnodes03.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-qnZ7WJUY4nc/UZJ-E5CJHdI/AAAAAAAAQyo/ne2dcYqddpg/s320/54_Addnodes03.jpg" /></a><a href="http://1.bp.blogspot.com/-nFDL975-zR0/UZJ-KfZm3gI/AAAAAAAAQy0/nBjBVDN7LYc/s1600/55_Addnodes04.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-nFDL975-zR0/UZJ-KfZm3gI/AAAAAAAAQy0/nBjBVDN7LYc/s320/55_Addnodes04.jpg" /></a><br />
<br />
It will run validation tests and it should pass the validation tests successfully. <b>If it fails</b> while doing IP validation tests, dont worry. I faced that situation during my initial configuration and I spent about a complete day trying to figure out what went wrong. Since we created these machines from the same base VM, it may report some of the IPs as duplicate. The best way to solve it is, remove Windows Failover Clustering Service feature on SQL02 and SQL03, reboot the VMs, add WSFC feature again and add the nodes as I showed above. That will work. Pinky promise. :) When everything goes well, you should finally see all nodes added to the cluster and cluster up and running. <br />
<br />
<a href="http://4.bp.blogspot.com/-PgNEBDCYOtU/UZKErMJWgKI/AAAAAAAAQzM/7LeIj_zDYEI/s1600/56_Cluster.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-PgNEBDCYOtU/UZKErMJWgKI/AAAAAAAAQzM/7LeIj_zDYEI/s320/56_Cluster.jpg" /></a><br />
<br />
<b>Enable Availability Groups on SQL Server</b><br />
<br />
We have our cluster ready. We now have to enable AlwaysOn Availability Groups at SQL Server level. Follow the steps below for SQL01 and <b>Repeat the same steps on SQL02 and SQL03</b>. <br />
<br />
Go to SQL Server configuration manager on SQL01. Go to SQL Services and right click on SQLSERVER(MSSQLSERVER) and go to properties. Enable AlwaysON Availability Groups. Stop Services. Restart Services. <b>Repeat the same for SQL02 and SQL03</b><br />
<br />
<a href="http://3.bp.blogspot.com/-Xr5jg35iv5Y/UZKGa9QoY1I/AAAAAAAAQzY/eF9xRKpaJ8A/s1600/57_AGEnable01.jpg" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-Xr5jg35iv5Y/UZKGa9QoY1I/AAAAAAAAQzY/eF9xRKpaJ8A/s320/57_AGEnable01.jpg" /></a><a href="http://1.bp.blogspot.com/-KRejwZUlq5Q/UZKGeT9douI/AAAAAAAAQzg/50WfSv_Vl7U/s1600/58_AGEnable02.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-KRejwZUlq5Q/UZKGeT9douI/AAAAAAAAQzg/50WfSv_Vl7U/s320/58_AGEnable02.jpg" /></a><br />
<br />
<b>Configure AlwaysOn</b><br />
<br />
A couple of things before you dive in. We need a bunch of databases on SQL01 to add to an availability group. Create your own databases or use sample databases from codeplex. We also need a file share between the 3 servers with appropriate permissions for service accounts. I created a share on C drive, SQL01 and gave read/write permissions to TESLA\admin login. The network share is accessible as <b>\\SQL01\Backups</b><br />
<br />
Log on to SQL01 and launch SQL Server Management studio. Right click on AlwaysOn HighAvailability and click <b>New Availability Group Wizard</b>. Select a name for your Availability Group. I called mine <b>AG01</b>. You'll see the list of databases to select from. If select option is grayed out, it means you need to take a full backup and initiate an LSN chain. The rest of the setup is pretty much self explanatory:<br />
<br />
<a href="http://4.bp.blogspot.com/-tS0-qZXuZRo/UZKVNYoE8wI/AAAAAAAAQzw/B_vCC9aTq4A/s1600/59_AGWizard01.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-tS0-qZXuZRo/UZKVNYoE8wI/AAAAAAAAQzw/B_vCC9aTq4A/s320/59_AGWizard01.jpg" /></a> <a href="http://4.bp.blogspot.com/-WeRSQJNR_xU/UZSLJkbcY8I/AAAAAAAAQ4w/UCXd1OsIp4s/s1600/60_AGWizard02.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-WeRSQJNR_xU/UZSLJkbcY8I/AAAAAAAAQ4w/UCXd1OsIp4s/s320/60_AGWizard02.jpg" /></a><br />
<br />
<div style="text-align: justify;">
Specify SQL02 and SQL03 as replicas. The wizard allows you control on things like whether the replica can be failed over to automatically, whether we will have a synchronous commit and asynchronous commit. In the screenshot below, I chose automatic failover between SQL01 and SQL02 with synchronous commit and readable secondary. What that means is - When something goes wrong and SQL01 goes down, SQL02 becomes primary and when SQL01 comes back, it can be the readable secondary with automatic failover enabled from SQL02. I also chose SQL03 as another secondary with no synchronous commit(possible data loss in the event of a disaster) and I set 'Read Intent-Only' property. Read only workloads like reporting can specify connection intent in their connection strings and their connections will be automagically routed to SQL03. Isn't that cool? </div>
<br />
<a href="http://4.bp.blogspot.com/-qoAhA3PkAT0/UZSNffVvnBI/AAAAAAAAQ5A/VKO8LpaVaWI/s1600/61_AGconfig.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-qoAhA3PkAT0/UZSNffVvnBI/AAAAAAAAQ5A/VKO8LpaVaWI/s320/61_AGconfig.jpg" /></a><br />
<br />
On the next tab, you'll see endpoints configured. AlwaysOn uses mirroring under the hood and it uses end points to propogate transactions between servers. Nothing to change here:<br />
<br />
<a href="http://4.bp.blogspot.com/-_4lNhf7YAeE/UZSOBaf8pvI/AAAAAAAAQ5I/j66D_UpAdnc/s1600/62_AGConfig_EndPoints.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-_4lNhf7YAeE/UZSOBaf8pvI/AAAAAAAAQ5I/j66D_UpAdnc/s320/62_AGConfig_EndPoints.jpg" /></a><br />
<br />
The next tab gives you control over where your <b>scheduled and automated</b> backups run. AlwaysOn lets you offload your backup jobs to one of your secondary servers, so that the production database can continue doing what it is supposed to do - serve the end users. <br />
<br />
<a href="http://2.bp.blogspot.com/-lajsqs9sxt8/UZSPWkX2poI/AAAAAAAAQ5Y/vwzqCir4Ew4/s1600/63_AGConfig_Backups.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-lajsqs9sxt8/UZSPWkX2poI/AAAAAAAAQ5Y/vwzqCir4Ew4/s320/63_AGConfig_Backups.jpg" /></a><br />
<br />
The next tab, lets you create a listener for this availability group. A listener allows you to deal with this Availability Group as a whole and the clients can connect to the group using this VNN (Virtual Network Name). The end users don't have to worry about what is happening under the hood and which server is the primary and which is a secondary replica. Thats DBA's task. Lets create a Listener. I am going to call it <b>Helicon</b>, the planet of <b>Hari Seldon</b>. <br />
<br />
<a href="http://4.bp.blogspot.com/-Pj_T5wBjDgs/UZSZSwSAS3I/AAAAAAAAQ58/Ip1b9cb7Eec/s1600/64_AGConfig_Listener.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-Pj_T5wBjDgs/UZSZSwSAS3I/AAAAAAAAQ58/Ip1b9cb7Eec/s320/64_AGConfig_Listener.jpg" /></a><br />
<br />
In the next screen, you choose how you would like to configure AlwaysOn. You can initialize it automatically specifying the network share (remember the network share \\SQL01\Backups we created a little while ago?) or you can just skip the initial data synchronization if you prefer doing backup and restore manually. <br />
<br />
<a href="http://4.bp.blogspot.com/-lKVyONPoCII/UZSYt3q1gKI/AAAAAAAAQ5w/dpi8ZXN6bso/s1600/65_AGConfig_laststep.jpg" imageanchor="1"><img border="0" src="http://4.bp.blogspot.com/-lKVyONPoCII/UZSYt3q1gKI/AAAAAAAAQ5w/dpi8ZXN6bso/s320/65_AGConfig_laststep.jpg" /></a><br />
<br />
If everything went according to our plan, you should see this, you are one step away from creating AG.<br />
<br />
<a href="http://2.bp.blogspot.com/-H5y7gMwJNBQ/UZSZ0q0LEXI/AAAAAAAAQ6E/28KYUnagrIQ/s1600/66_AGConfig_Success.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-H5y7gMwJNBQ/UZSZ0q0LEXI/AAAAAAAAQ6E/28KYUnagrIQ/s320/66_AGConfig_Success.jpg" /></a><br />
<br />
In the next screen, you can script out all the setup and save it somewhere for deploying again with some changes in future. When you click next, the wizard will run and it may take some time depending on your database sizes to complete. If it completes successfully, Congratulations! you have built your home lab and deployed AlwaysOn successfully.<br />
<br />
<a href="http://1.bp.blogspot.com/-68xoOQpjOAA/UZShb6R83GI/AAAAAAAAQ6U/9gcNT7c5Je4/s1600/67_AGConfig_Dashboard.jpg" imageanchor="1"><img border="0" src="http://1.bp.blogspot.com/-68xoOQpjOAA/UZShb6R83GI/AAAAAAAAQ6U/9gcNT7c5Je4/s320/67_AGConfig_Dashboard.jpg" /></a><br />
<br />
You can directly connect to your Availability Group using the listener name, Helicon in SSMS.<br />
<br />
<a href="http://2.bp.blogspot.com/-M0pujHV9Xbo/UZSh-gIBQNI/AAAAAAAAQ6c/gbKedpKvD9Q/s1600/68_Helicon.jpg" imageanchor="1"><img border="0" src="http://2.bp.blogspot.com/-M0pujHV9Xbo/UZSh-gIBQNI/AAAAAAAAQ6c/gbKedpKvD9Q/s320/68_Helicon.jpg" /></a><br />
<br />
I am not going to test failover and dig deeper into AlwaysOn as this post is already very long. Lets save that future posts. <br />
<br />
If you get stuck with the setup somewhere, please leave a comment here and I will be glad to help. <br />
<br />
Have fun!</div>
yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com3tag:blogger.com,1999:blog-8235355014350215030.post-47969017655113885192013-05-11T14:00:00.003-07:002013-05-16T20:03:02.805-07:00A not so exciting game in Python: Stop Watch<div dir="ltr" style="text-align: left;" trbidi="on">
Another week and another assignment. The game itself is not very exciting. You can start and stop the timer, every start is an attempt and every stop exactly at 0 is a win.<br />
<br />
<a name='more'></a><br /><br />
<pre class="brush:py"># Author: RK
# "Stopwatch: The Game"
import simplegui
#import random
# global variables
width = 400
height = 200
time = 0
message = "StopWatch Game"
attempts = 0
wins = 0
started = False
# Helper Function
def format_time(t):
"""converts tenths of seconds to A:BC.D"""
#global message
i = t % 10
j = int ( t / 10)
k = j % 60
l = int (j / 60)
if k < 10:
game_string = "0" + str(k)
else:
game_string = str(k)
return str(l) + ":" + game_string + "." + str(i)
# define event handlers for buttons; "Start", "Stop", "Reset"
def start_timer():
global started
timer.start()
started = True
def stop_timer():
timer.stop();
global time, wins, attempts, started
if started:
if time % 10 == 0:
wins = wins + 1
not started
attempts = attempts + 1
def reset_timer():
global time, started, attempts, wins
timer.stop()
time = 0
wins = 0
attempts = 0
# define event handler for timer with 0.1 sec interval
def timer_handler():
global time
time += 1
# define draw handler
def draw_handler(canvas):
canvas.draw_text(format_time(time), (120,120), 50, "White")
canvas.draw_text("wins/attemps: " + str(wins) + "/" + str(attempts), (200,30), 20, "Yellow")
# create frame
f = simplegui.create_frame(message, width, height)
# register event handlers
f.add_button("Start", start_timer, 100);
f.add_button("Stop", stop_timer, 100);
f.add_button("Reset", reset_timer, 100);
timer = simplegui.create_timer(100, timer_handler)
f.set_draw_handler(draw_handler)
# start frame
f.start()
# Please remember to review the grading rubric
</pre>
</div>
yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0tag:blogger.com,1999:blog-8235355014350215030.post-2035718898695556412013-05-09T04:06:00.000-07:002013-05-16T20:04:10.068-07:00Visual Fox Pro Hell: fpt file missing or invalid (memo file corruption)<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: justify;">
I know, its 2013 and nobody should be dealing with Visual Fox Pro 6.0 databases, but one of the clients I support has a legacy system that uses foxpro databases. We have an SSIS job that pulls data everyday from `foxpro` and it recently started failing with the error, `person.fpt is missing or invalid`, I initially thought it was a network issue but the frequency of the failure increased and one fine day it stopped working at all. Lesson - don't wait for it to break completely. Because some day it will. So I naturally started googling and that was a time travel back to the ancient kingdoms of the internet. Microsoft's resources didn't help much, and I had to deal with shareware, freeware software that promise `.dbf` and `.fpt` repair as soon as you pay, help forums with no answers and annoying browser addons etc. After trying a lot of demo software I thought may be its just the `.fpt` file that needs rebuilding. So going though the <a href="http://msdn.microsoft.com/en-US/library/0kwx2w0d(v=vs.80).aspx" target="_blank">documentation</a> I thought I'd make a copy of this database and see if it rebuilds `.fpt` file and fortunately, it worked!<br />
<a name='more'></a></div>
<div style="text-align: justify;">
<br /></div>
<pre class="brush:sql">USE "C:\repair\person.dbf"
? RECCOUNT()
COPY STRUCTURE TO "C:\repair\person_fixed.dbf"
USE "C:\repair\person_fixed.dbf"
APPEND FROM "C:\repair\person.dbf"
? RECCOUNT()
RENAME "C:\repair\person.dbf" TO "C:\repair\oldperson.dbf"
RENAME "C:\repair\person_fixed.dbf" to "C:\repair\person.dbf"
CLOSE DATABASES
</pre>
<br />
<div style="text-align: justify;">
If your issue is not solved by this, then you may have to buy one of those repair tools and fix your dbf/fpt files. </div>
<div style="text-align: justify;">
<br /></div>
<div style="text-align: justify;">
Some links that will help</div>
<div style="text-align: justify;">
http://support.microsoft.com/kb/94348 </div>
<div style="text-align: justify;">
http://fox.wikis.com/wc.dll?Wiki~MemoFileMissingOrInvalid</div>
</div>
yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com2tag:blogger.com,1999:blog-8235355014350215030.post-13610863981405548092013-05-01T05:16:00.004-07:002013-05-16T20:04:43.710-07:00Game: Guess the number!<div dir="ltr" style="text-align: left;" trbidi="on">
I have recently started learning Python from an online course and we had this course assignment to program this game. I am avoiding the name of the course and the name of the online resource on purpose because I don't want this show up on Google results. I'm just childishly happy that I wrote something and I wanted to save it here. This code may not work as is, because it uses some modules that are not part of standard python installations. After writing it in Python, I tried to do it on Powershell too and it kind of worked. <br />
<br />
<a name='more'></a><br /><br />
<br />
<pre class="brush:py">import simplegui
import random
# initialize global variables
secret_number = random.randrange(0,100)
maximum_guesses = 7
def range100():
"""Button that changes range to range(0,100)"""
global secret_number
global maximum_guesses
secret_number = random.randrange(0,100)
maximum_guesses = 7
print "New Game! Enter a guess between 0 and 100. \n"
def range1000():
"""button that changes range to range(0,1000)"""
global secret_number
global maximum_guesses
secret_number = random.randrange(0,1000)
maximum_guesses = 10
print "\nNew Game! Enter a guess between 0 and 1000. \n"
def get_input(guess):
global maximum_guesses
global secrent_number
maximum_guesses -= 1
guessed_number = float(guess)
if guessed_number == secret_number:
print "Your guess", guess, "is correct! YOU WIN!"
print "You had", maximum_guesses, "guesses left.\n"
range100()
elif maximum_guesses > 0:
if guessed_number > secret_number:
print guess+"?","incorrect! try LOWER.", maximum_guesses, "guess(es) left. \n"
if guessed_number < secret_number:
print guess+"?","incorrect! try HIGHER.",maximum_guesses, "guess(es) left. \n"
else:
print "Your guess", guess, "is incorrect. You lose. Why not try again!\n"
range100()
frame = simplegui.create_frame("Guess The Number", 200, 200, 200)
frame.add_input("Enter your guess and hit enter key", get_input, 200)
frame.add_button("Change range to 100", range100)
frame.add_button("Change range to 1000", range1000)
frame.start()
</pre>
Now lets try this on Powershell
<br />
<pre class="brush:ps">$random = New-Object system.random
$RandomNumber = $random.next(1,100)
do { $guess = read-host -prompt "What is your guess?"
if ($guess -gt $RandomNumber) {Write-Host "Incorrect! Try Lower!"}
elseif ($guess -lt $RandomNumber) {Write-Host "Incorrect! Try Higher!"}
elseif ($guess -eq $RandomNumber) {Write-Host "That is CORRECT! You Rock! It took you $count tries"}
else {"You have to guess a number!!!"}
$count += 1
} while ($guess -ne $RandomNumber)
</pre>
<br />
<br /></div>
yogirkhttp://www.blogger.com/profile/12205308612289741291noreply@blogger.com0