Nai's questions

I'm trying to run a postgis sql script and I'm running into the following error. Am I correct to say that updating my server version will fix it? If so, how can I go about updating it?

I'm on Mac OSX Lion and installed Postgres via brew. Apparently I have an older version installed which is 9.1.2 but installing postgis installed postgres 9.2.1 on to my system. How can I point my postgres server to the new one?

nai@nyc /usr/local/share/postgis (git::master) $ psql -d template_postgis -f postgis.sql
SET
BEGIN
psql:postgis.sql:49: ERROR:  incompatible library "/usr/local/Cellar/postgresql/9.2.1/lib/postgis-2.0.so": version mismatch
DETAIL:  Server is version 9.1, library is version 9.2.


nai@nyc /usr/local/share/postgis (git::master) $ psql
psql (9.2.1, server 9.1.2)
WARNING: psql version 9.2, server version 9.1.
         Some psql features might not work.

From Wikipedia "In computer networks, a reverse proxy is a type of proxy server that retrieves resources on behalf of a client from one or more servers. These resources are then returned to the client as though it originated from the reverse proxy itself.[1] While a forward proxy is usually situated between the client application (such as a web browser) and the server(s) hosting the desired resources, a reverse proxy is usually situated closer to the server(s) and will only return a configured set of resources."

How would you explain the term 'reverse proxy' to a non-technical person?

UPDATE

Background to the question:

I am teaching myself to code with the goal of creating a databound webapp by myself. Since I've started, I found myself doing more non-programming related tasks, a gotcha no one really told me about. So in a bid to understand the various components that fit together in creating a webapp, I would like to better understand the role of webservers and what they do and how they work. Of which, I came across nginx which provides a reverse proxy service hence the question.

I bought a domain from whois.com and I am trying to set it up on my Amazon EC2 instance. I have Apache installed on it.

I saw these two related questions here and here but could still use some hand holding given this is my first time trying to set it up.

Would be great to have some links that shows a step by step approach to setting it up from what to do on whois.com to the right config on Apache.

So far, I have created an Elastic IP and have it set under A Records for both my www.example.com and example.com domains to use.

It's not working, not sure what the next steps are? I haven't made any changes to my httpd.conf yet.

BTW, I am still not very familiar with all the sys admin jargon so go slow!

This is my first time trying to setup a webserver with Django. I've been banging my head against the wall in trying to set this up for a while now and I can't seem to find any resources that explains this in a newbie formatted step by step manner.

I've installed and setup apache and mod_wsgi on my EC2 instance. How do I map the URLs of my app set in my urls.py file to apache? Locally, I can get my app to work my entering 127.0.0.1:8000/flickr/photousers by running it on the Django development server. I'm trying to be able to get this to work on my public dns as http://ec2-122-248-194-176.ap-southeast-1.compute.amazonaws.com/flickr/photouser with apache and mod_wsgi.

When I restart my Apache server, I also get this error but with OK status [error] (EAI 2)Name or service not known: Could not resolve host name *.80 -- ignoring

Below lists the relevant snippets

django.wsgi file

import os,sys

apache_configuration = os.path.dirname(__file__)
project = os.path.dirname(apache_configuration)
workspace = os.path.dirname(project)
sys.path.append(workspace)
sys.path.append(/home/djangotest/flickr)
#sys.path.append(path)

os.environ['DJANGO_SETTINGS_MODULE'] = 'flickr.settings'

import django.core.handlers.wsgi
application = django.core.handlers.wsgi.WSGIHandler()

httpd.conf file

<VirtualHost *.80>
    DocumentRoot /home/djangotest/flickr
    ServerName ec2-122-248-194-176.ap-southeast-1.compute.amazonaws.com
    ErrorLog /home/djangotest/flickr/logs/apache_error.log
    CustomLog /home/djangotest/flickr/logs/apache_access.log combined
    WSGIScriptAlias / /home/djangotest/flickr/apache/django.wsgi

    <Directory /home/djangotest/flickr/media>
        Order deny,allow
        Allow from all
    </Directory>

    <Directory /home/djangotest/flickr/apache>
        Order deny,allow
        Allow from all
    </Directory>

    LogLevel warn

    WSGIDaemonProcess ec2-122-248-194-176.ap-southeast-1.compute.amazonaws.com user=djangotest processes=2 threads=25
    WSGIProcessGroup ec2-122-248-194-176.ap-southeast-1.compute.amazonaws.com

    Alias /media/ /home/djangotest/flickr/media/
</VirtualHost>

urls.py

from django.conf.urls.defaults import *

urlpatterns = patterns('',
    # Example:
    (r'flickr/', include('flickrphotobrowser.urls')),
)

urls.py (located in my app)

from django.conf.urls.defaults import *

from flickrphotobrowser.views import *

urlpatterns = patterns('flickrphotobrowser.views',
        (r'^phototags/$', contenttags),
        (r'^photouser/$', contentuser),
)

settings.py

ROOT_URLCONF = 'flickr.urls'

TEMPLATE_DIRS = (
        os.path.join(os.path.basename(__file__), 'templates'),
    #'home/nai/Projects/flickr/flickrphotobrowser/templates', # THIS IS FOR LOCAL ONLY
)

INSTALLED_APPS = (
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.sites',
    'django.contrib.messages',
    'flickr.flickrphotobrowser',
)

Should I include any other snippets to help troubleshoot the problem? Thanks.

This sounds like a real noob question but I cannot figure this out.

I'm trying to get Apache, mod_wsgi and Django playing together on my EC2 instance. So I've installed mod_wsgi using the root user and I'm trying to edit my httpd.conf as per the instructions here.

So, my question is, where on earth is this httpd.conf file located on my Amazon instance after I ran yum install mod_wsgi.

Is there a command in linux that I can run that allows me to see all the directories and files installed for each user on the instance?

I have a small webapp written in Python/Django which works fine on my local machine. I've been tinkering and setting up my server on the free tier of Amazon EC2 by following online tutorials. However, the tutorials I have found so far shows you how to setup your instance and stops there.

So my question is, how do I get my local webapp onto my Amazon instance?

FYI, I'm a sys admin/web dev. noob.

Thanks.

These are my current settings:

Recovery mode: Simple Logfile initial size: 10 mb Logfile autogrowth: None Database auto shrink: Off

The logfile autogrowth is set to None because I don't want the logfile to grow. My objective is to keep the log file consistently small as I do not need it for recovery purposes.

Are the both settings okay for that?

Also,what happens if I have a big transaction involving numerous inserts and deletes (over 10 mb worth of change). Will the operation not complete because the log file ran out of space?

Is there anyway I can track the size of my database growth over time for SQL Server 2008? Can I see this from a dashboard somewhere or do I have to script a query to perform this?

Thanks

This is my error I am facing

TITLE: Microsoft.SqlServer.Smo

Set offline failed for Database 'Go3D_Retailer

------------------------------ ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

---

Unable to open the physical file "E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ftrow_Go3D_catalog.ndf". Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)". Database 'Go3D_Retailer' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5120)

Background to this error I've been trying to move my destination logshipping database to another physical server for analysis purposes. Because I do not have active directory set up, I had to hack my process by using the same username/password for both the source and destination servers to get the process to work.

Following that, I used this guy's solution to move the destination database to another server. However, this error occurs when I try to bring the database back online.

I don't have an E drive on my server and I have no idea why it's trying to open a file from E drive. I have over a 100gb left on my hard disk so it's definitely not a space issue. This sounds like a bug... Any ideas?

I'm running SQL Server 2008 Enterprise edition on Windows Server 2008 R2 64bit

I am trying to use the SQL Server 2008 Copy Database Wizard to copy a SQL Server 2008 database. I am using the SQL Management Object method. However, the copy fails with the following error:

ERROR : 
errorCode=-1073548784 description=Executing the query "/* 
'==============================================..." 
failed with the following error: "Cannot use a CONTAINS or 
FREETEXT predicate on table or indexed view 'Product' because 
it is not full-text indexed."

Any ideas on how I can proceed with this will be super helpful

EDIT

The background to this question stemmed from trying to move a database that is in restore/stand by mode (it is the log shipping destination db). This seems to be the only viable channel (not a hack) without breaking the logshipping chain?

Kind Regards Nai

I have a database which is the recipient of a logshipping process.

The database is in 'restoring'. Is it possible to move this database?

EDIT Just to clarify what I meant by move. I mean to make a copy of the database and transfer it to another server without breaking the logshipping process.

The reason for this is to run analysis queries against the database. I have tried using Copy Database Wizard but got stuck in the process. You can read about that problem here

I have 2 ideas for my backup solution and was looking to get feedback from the community about this. I'm running SQL Server 2008 Enterpise.

1) Once a week full back up daily incremental back ups locally. Then setting up a scheduler to copy the files to my backup server.

2) Use log shipping. To be honest, I haven't done this before but it looks relatively straight forward to implement. I am not sure of the overheads involved though. This could also be a problem if the network goes down.

I'm actually for the log shipping solution even if I have no prior experience with it. Whadya guys think? Replication is out cos I don't want any schema changes. Just looking for a fuss free easy set up.

Thanks!

EDIT Sorry forgot details, I'm running SQL Server 2008 R2 16GB of RAM

My min/max memory allocation is set to 13 GB. Running perfmon - SQL Server:Memory Manager. Target Server Memory and Total Server Memory confirms this. The ram counter in Task Manager is at 15 GB (assuming 2GB is used for other applications)

What I don't understand is why the process sqlservr.exe is only showing 82KB usage in task manager? Further to that, all of memory consumed by the processes running on my server from Task Manager doesn't add up to 15 GB either.

So what gives

Just wondering what kind of processes you guys have for transferring production data offsite for analysis purposes?

I have read some of the literature but I am not sure of the overheads involved and the setup processes.

• Replication: With the production acting as both the distributor and publisher. I will employ a push system that updates my database on my analysis server weekly
• SSIS: No experience but seems to be the 'standard' way?
• Manual: Use windows scheduler to zip back my backup file and open up an FTP connection to my analysis server
• Mirroring: No experience but used more for high availability?

FYI, I am using SQL Server 2008 with Windows Server 2008 R2. MY DB is about 36 gigs. I am only interested in the data.

Other methods welcome!

*EDIT Additional information: I don't mind the database being read only and I am looking to run this process once a week.

I have a RAID 1 setup averaging 40 - 60 I/Os per disk and 0.5 average disk queue length. According to BOL example below, this is not a bottleneck.

Disk Reads/sec 80
Disk Writes/sec 70
Avg. Disk Queue Length 5

In that case, you are encountering (80 + (2 * 70))/2 = 110 I/Os per disk and your disk queue length = 5/2 = 2.5 which indicates a border line I/O bottleneck.

Which brings me to my question, how high is a high I/O? The average disk queue length is quite well documented in that it should not exceed 2 per CPU but BOL makes no mention about I/Os per disk?

Do you guys have any good step by step type links for performing an SQL Server 2005 Performance Audit?

Something like this link would be great but for SQL Server 2005.

Background to question: I'm looking to implement a caching system for my website. Currently we're exploring memcache as a means of doing this. However, I am looking to see if something similar exists for SQL Server. I understand that MySQL has query cache which although is not distributed works as a sort of 'stop gap' measure. Is MySQL query cache equivalent to the buffer cache in SQL Server?

So here are my questions:

1. Is there a way to know is currently stored in the buffer cache?
2. Follow up to this, is there a way to force certain tables or result sets into the cache? How much control do I have over what goes on in the buffer and procedure cache? I understand there used to be a DBCC PINTABLE command but that has since been discontinued.
3. Slightly off topic: Should the caching even exists on the database layer? Or it is more prudent to manage caches using Velocity/Memcache? Is so, why? It seems like cache invalidation is something of a pain when handling many objects with overlapping triggers.

Thanks!

Are there any downsides to giving Application Pools multiple Worker Processes in IIS? They seem really easy to enable and (almost) everything I’ve read seems to suggest they’re good... so why doesn’t IIS give each App Pool 10+ Worker Processes? There must be some detrimental effects, right?

I recently encountered this problem on SQL Server whereby the ram was maxing out for no apparent reason resulting in sluggish performance. I'm running 64 bit OS on 32 bit SQL Server Standard Edition.

Checking the error logs reveals this:

A significant part of sql server process memory has been paged out. This may result in performance degradation

Apparently the fix for this is to enable SQL Server to lock pages in memory. The workaround can read from here.

However! According to MSDN, enabling lock pages in memory option whilst not using AWE can significantly impair my system. My current set up does not have enough RAM (2 GIGS) to take advantage of this feature.

So what gives? If I don't implement the fix, SQL Server slows down. If I do, it slows down too.

Does anyone have any thoughts or experiences with this?

Just wondering what backup processes you guys have.

I am currently operating a weekly full database backup with daily differential backups.

My understanding is that with such a set up, the difference between Full recovery mode and Simple recovery mode is that with Full recovery mode, I will be able to use the transaction logs to rollback my DB to a specific point in time having applied the latest differential backup.

Assuming that in my scenario, the last differential backup serves as my last and ultimate 'save point', I don't see a need to rollback my DB even further back using the logs. This brings me to my question: Is there any additional benefits to be had using a Full recovery mode for my current backup process?