What Is The Best Availability And Backup Solution?
Feb 21, 2007
Hi Everyone,
I'm working on a site that is running MS SQL 2000. They perform
regular backups of databases and log files and in the event of a
disaster they have a second backup server.
At present they regularly copy the backup up files from the production
server to the standby server. The plan, in the event of a disaster, is
to restore these backups onto the standby server and change the dns
records so that client machines will point to the standby server.
Goal:Off-site backup of database without onsite tech intervention
Obstacles: * Webhost does not want SQL Server Agent running for fear of viruses * Database is 3.4GB, LDF is 3.7GB, and a Full Backup is 3.1GB.
Connection / Access:Enterprise Manager, RDP and FTP all work to access data and backups.
I have looked into doing a full backup then differential backups, doing transaction replication, and log shipping. The client doesn't really need replication, he just wants to know that if his server with the production database goes up in flames he has a way of getting back in business without losing more than one day of data.
I am hoping there is a method of "restoring" on his off-site server the data that is "backed up" on the production server. Is that possible if we're doing differential backups?
Is that the best solution or is there a better way?
Thanks for any recommendations or advice, Alec Sherman
hi groupMy database name : developerday 1. Taken full backupday 2. taken differential backupday 3. taken differential backupday 4. taken differential backupday 5. taken differential backupday 6. taken differential backupfinished backupsmonth 1. after one month volunterily deleted records of tables.month 2. want to view only database with day 2 .( I want to restore only day2 backup (including full backup) and usethe database)Is it possible in Microsoft SQL Server?Need Good solution or tool for this.Expecting good suggestions or coding or toolsWith AdmirationRLN
We have a huge terabyte database that we are planning to upgrade to 2005. We are working on realistic backup solutions in case we have to rollback the upgrade. We have over 400 tables out of which about 100 are kind of static. The other 300 tables have very high transaction rate - thousands of batch transactions per sec. Earlier, prior to 2005 SP2 release we tried setting up replication to a 2000 box and replication agents failed to catch up with the transactions. Not sure of SP2 has any improvements. We are thinking of setting up replication for only the 300 or so tables and we will take a regular nightly back up and can use a day's old data for the 100 small tables.
What are your experiences/suggestions on a proper back up solution to have a 2000 server in almost stand by mode that we can switch back to in case of any issues?
I've just inherited (i.e., our sys admin / DBA left the company) a fairly small SQL Server that's running 7 production databases. Most are quite small, but there are two which are about 40gb each. Traffic is quite low - ~30-40 users at one time doing your basic SELECT / UPDATE / INSERT stuff.
Anyway, I was going through some of the backups jobs and noticed that the transaction logs for each database were absolutely huge (in some cases bigger than the DB itself) which led me to think the log wasn't getting truncated.
The T-SQL being run in each case was
Currently, the transaction log for 6 DBs is backed up 3 times a day (and the 7th, "mission critical" DB is backed up every 15 minutes) with the following T-SQL:
BACKUP LOG <database> to <device> WITH NOINIT, NOFORMAT, NOSKIP, NOUNLOAD
5 of the 7 databases get a full backup twice a day, with the 2 larger ones getting a differential, with e.g.,
BACKUP DATABASE <database> TO <device> WITH NOINIT , NOUNLOAD , NAME = N'db', NOSKIP , STATS = 10, DESCRIPTION = N'db', NOFORMAT , MEDIANAME = N'db'DECLARE @i INT select @i = position from msdb..backupset where database_name='db'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='db') RESTORE VERIFYONLY FROM <device> WITH FILE = @i
This is then backed up to tape each night.
Looking through the documentation, those WITH commands are largely the default settings so I'm not sure why they're specified explicitly.
If I issue a
BACKUP LOG <database> to <device> WITH INIT, SKIP
then the log does get truncated. However, could someone explain the implications of that for me? As I understand it, INIT will overwrite any existing sets in the device, but considering that it will always backup anything that hasn't been committed then should it be a problem?
Alternatively could someone perhaps explain why the log wasn't getting truncated? It is my understanding that this should happen every time a full backup is completed... which is twice a day. Or does the Transaction log need to be manually shrunk every now and then?
Also, I understand the DECLARE... part in the last part of that DB backup SQL, but is it at all necessary?
Finally, does this backup strategy seem viable? Any thoughts and comments are appreciated! Matt
I have 2 servers in a SQL Server Fail-Over Cluster. IOW I use always-on availability groups. I run backups - full, diff and log - regularly via SQL Agent on one server only depending on which is primary. If there is a fail-over, then backups will continue on the other server. If I have to restore a database in an availability group I probably would need some combination of full, diff, and log backups from each server. Would that actually work? I test the backups weekly however I just realized that I never tested that scenario.
Backing up all databases on a sql server that hosts secondary high availability databases as well as other databases. Â The other databases back up fine, but the high availability secondaries all get the same error:
BackupDiskFile::OpenMedia: Backup device 'F:MSSQLBackupdbnamedbname_backup_2015_MM_DD_tttttt_ttttttt.bak' failed to open. Operating system error 2(The system cannot find the file specified.)
we currently use Backup Scripts from Ola Hallengren, It Says Full (non copy-only) and differential backups are performed on the primary replica. Full(Copy-only) backups and transaction log backups are performed on the preferred replica.
we currently do FULL(COPY_ONLY) Backup everyday and LOG Backups for every 15 min, is there any performance benefit on running the FULL (non copy-only) on the preferred replica .
need to migrate a cluster with an AG dtabases to new data center cluster with AG.
I was wondering if is possible to do mirroring on top of the AG configuration? or what other options could be to migrate a cluster that has 3 nodes and setup the ag databases to a new datacenter.
VB.NET 2005 Express and SQL Server 2005 Express - NOT saving updates to DB - SOLUTION!
-----------------------------------
The following article is bogus and confusing:
How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy' http://msdn2.microsoft.com/en-us/library/ms246989.aspx
You must manually copy the database file to the output directory AFTER setting 'Copy to Output Directory' to 'Do not copy'.
Do not copy
The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.
You must manually copy the database file to the output directory AFTER setting 'Copy to Output Directory' to 'Do not copy'.
-----------------------------------
The above article is bogus and confusing.
This is rediculous!
This is the most vague and convoluted bunch of nonsince I've ever come accross!
Getting caught out on this issue for the 10th time! And not being able to find an exact step-by-step solution.
--------------------------
I've tried it and it doesn't work for me.
Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.
If you have a step-by-step procedure that can be reproduced this properly please PM me.
I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.
Many thanks.
This is the exact procedure I have come up with:
NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.
NOTE 2: DO NOT hand copy the database to a folder/directory in your project.
Yes, I know its hard not to do it because you want your project nice and tidy. I just simply could NOT get it to work. You should NOT have myData.mdf listed in the Solution Explorer. Ever.
Create a folder for your data following NOTE 2.
Copy your data to that folder. * mine was C:mydatamyData.mdf
Create a NEW project.
Remove any Data Connections. ( no matter what)
Save it.
Data | View Data Sources
Add New Data Source
select NEW CONNECTION ( No Matter what, do it!
Select the database. * again mine was C:mydatamyData.mdf
Answer NO to the question: Would you like to copy the file to your project and modify the connection? - NO ( no matter what - ANSWER NO ! - Absolutely NO ) Then select the tables you want in the DataSet. and Finish.
To Test ----------
From the Solution Explorer | click the table name drop down arrow | select details Now Drag the table name onto the form.
The form is then populated with a Navigation control and matching Labels with corresponding Textboxes for each field in the table.
Save it.
1) Run the app.
Add one database record to the database by pressing the Add(+) icon
Just add some quick junk data that you don't mind getting lost if it doesn't save.
YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Now exit the application.
2) Run the app again.
And verify there is one record already there.
Now add a second database record to the database by pressing the Add (+) icon.
NOW add some quick junk data that you WILL intentionally loose.
*** DO NOT *** press the save icon.
Just Exit the app.
3) Again, Run the app.
Verify that the first record is still there.
Verify that the Second record is NOT there. Its NOT there because you didn't save the data before exiting the app.
Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Also proving you must add your own code to catch the changes and ask the user to save the data before exitiing or moving to another record.
As a side note, since vb.net uses detached datasets, (a copy/snapshot of the dataset in memory and NOT directly linked to the database) the dataset will reflect all changes made when moving around the detached datasets. YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM. Otherwise, they will simply be discarded without notice.
Whewh!
I hope this saves me some time the next time I want to start a new database project.
Oh, and uh, for anyone else reading this post.
Thanks, Barry G. Sumpter
Currently working with: Visual Basic 2005 Express SQL Server 2005 Express
Developing Windows Forms with 101 Samples for Visual Basic 2005 using the DataGridView thru code and every development wizard I can find within vb.net unless otherwise individually stated within a thread.
I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.
The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).
Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?
I want to run remote procs from a local - central proc, but don't want to crash my proc if the remote NT Server or SQL Server is not available. So, I want to test for NT and SQL availability first.
/* I find the server-dbasename name in a lookup table, and then build dynamic script */ SELECT @DBName = "<SRVRNAME~DBASENAME>" SELECT @QueryString = N'master..xp_cmdshell "ECHO >' + SUBSTRING(@DBName, 1,6) + 'pipesqlquery", no_output' EXEC @Results = sp_executesql @QueryString
SELECT @Results /* <-- testing here.... */
=====================
If the NT / SQL is running, "@Results" should = 0 (success on the ECHO to the PIPE) If the NT / SQL is NOT running, "@Results" should = 1 (error on the ECHO to the PIPE)
If I test in QA, I get the expected "@Results" values.
If I run the proc, I get "@Results" = 0 for both events, and them my proc fails on the remote proc call if the NT / SQL is NOT running.
I don't know how to write DMO in TSQL to try that. I have also tried using 'odbcping' too.
HINTS? Better way to test remote NT / SQL is NOT running?
Hello everybody, We are starting new project Customer validation with minimum wait time. How to insure High-Availability? We have 2 standard servers and in past used custom log shipping.
Log shipping still requre manual intervention ,while goal to switch between servers automaticly.
Clustering is a last possible solution.
Could someone recomend any other soluion or products?
i'm not fluent with sql and i have a question to ask..
if i want to check whether a product is available between two dates, how can i do so? for example, i want to check whether it's available between 5/23/2007 to 5/25/2007..
My problem is trying to calcuate whether a room is booked during a dateperiod.I have a table with two columns (Start and End date).I need some SQL code to calculate whether a room is booked during a daterange.e.g.The booking entry is:Start21/11/2005End25/11/2005Any help on this would be appreciated.The End Date indicates the last night of the stay.Regards,Steven*** Sent via Developersdex http://www.developersdex.com ***
I am in the process of setting up server replication using the Central Publisher Model. We are doing this for application availability. The application is a .NET app and this application uses the SQL Server name in the connection string. As far as I know we can€™t have two severs on the same domain with the same name. So if our primary server (server A) goes down and that server name is in the connection string how does the application get redirected to our backup server (server B). Thanks for the help.
I am in the midst of designing a new Data Warehouse system. As we get further into the design of the system, the more we are realising how complex our ETL is going to be and that the amount of time it will take to run could be significant i.e. a few days! My question is obviously I don't want to have a down time in my relational system for this long and prevent my users from accessing the data for days at a time each month. So what functionality should I be looking at to allow me to maintain a working copy of the data that users can query whilst I perform database updates and then perform a quick promotion of the updated data to users for querying?
If you can point me in the direction of the right functionality in SQL Server 2005 and possible some relevant white papers that cover this sort of scenario I would be grateful.
I have a large db that goes through regular maintenance every week. The problem seems to be that while the db is re-indexing some of my key tables I get time out errors. Are there any suggestions on how I can combat this problem. Management has tasked me with having 24 hour access to the db because if people aren’t completing there transactions we don’t get paid. I have also noticed the timeout issue when we are running in files that create new records. Is this a basic problem with the way the db is structured? Are they any tools that I can use to change our process so that we can maintain the 24 hour access to the db. How are other companies maintaining 24 hour access to there db?
Hi, I am new in SQL SERVER.I wanted to setup high availability database.It would be great if any one could answers my questions please. -What are the options availability for HA except clustering? -How to set up log shipping HA ? -How to monitor log shipping? -Is standby DB and log shipping same ? -Can I setup log shipping without backup/restore method.Like I have to create db1 on server A and create db1 on server B and then configure log shipping? -Is it necessary to ship log for master database or only user Database? Thx -Blace
Hi, I'm a businessperson, not a software engineer, so please bear with me if my questions seem simple.
We are about to migrate from MS SQL Server 2000 to 2005. In our 2000 deployment, we have a primary server, with real-time replication to a backup. If we have a hardware failure, we do a manual failover to the backup server. This has served us well for years, with decent performance and no data loss. The SQL server is accessed by a pair of IIS servers, which use network load balancing to split the load between them.
As part of the forthcoming migration to SQL 2005, we are going to a hardware platform with 2 SQL servers sharing a single storage array. We would like to get the performance benefit of utilizing both of the SQL servers to handle requests simultaneously, and not just have the second server as a backup. Ideally, the two IIS servers would dish requests to both SQL servers, either randomly or in some sort of load-balanced manner.
So here is my question: how can we have both SQL servers active and handling requests simultaneously, achieving both high perfromance and high uptime?
If it is not supported natively in SQL Server 2005, is there a third-party software solution that can achieve this?
Finally, where else should I go to do further research on this subject?
I have to describe Microsoft SQL High Availability options in the following layers: infrastructure, middleware and application. I know there are following options available: failover clustering, replication, mirroring and AlwaysOn but I am not sure in which layer each of them are.
I have implemented log shipping between 2 databases, the transaction log size normally between 10mb - 50mb every 15 minutes during normal working hours, but it grows to 9GB when we run database optimization job and that makes it hard and long to transfer and apply the transaction log on the other database. Does anybody encountered a situation like this and is there a way to minimize the size of the Trans log after the optimization job?
how do i left join some table just to check the availabilty of the data.. say... if id in tblA exists in tblB, how do i just select say flag='Y' without joining the data in tblB that could have more than 1 record ?
tblA a1 a2
tblB a1 a1 a1
i dont want to select what i join in tblB, just to check the availability... so in this case :- a1 ----> Y a2 ----> N
I have an ASP application mainly connected to one SQL database that works great but now I am trying to add some functionality that requires to connect to another remote SQL server. Till now all is fine except that the remote SQL server is not always online and of course when this happens my ASP application stops with the following error:
Error Type: Microsoft OLE DB Provider for SQL Server (0x80004005) [DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found.
in my Global.asa I setup my session variables for DSN connections and in my pages I call my SQL connection as follow:
Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open Session("MySQL")
SQL = "my SQL statement here;" Set RS = Conn.Execute(SQL)
Every time the page hits the Conn.Open line I get the error if the remote SQL is not online!!!
Is there a way I can check a sort of returned error code holding the connection status before getting to the Conn.Open line???
We have a multi-lingual website (English, Spanish and German). We have a table called Posts that is potentially getting really big.
We are in the initial design phase of the database and would like to know what the experts are suggesting to keep our database mean and lean in the long run.
We have been talking about splitting the database up into 3 separate databases, one for English, one for Spanish and one for German. The language specific databases would also be hosted in countries where the language is spoken eg. the German database would be hosted in Germany.
Or maybe database partitioning by language???
Making changes to 3 databases once launched seems like a nightmare. It would be nice to have one main database and maybe 2 (Spanish and German) €œsatellite€? databases or something like that €“ any ideas???.
Any suggestions of how to deal with this problem the best way would be greatly appreciated. We are using SQL Server 2005.
We are beginning to design a new application with SQL Server 2005. Our current production environment is slated to be two SQL Server 2005 machines with the databases residing on an EMC SAN. We have requirements to both have automatic failover between servers for availability and also be able to balance the load over two hot servers for scalability.
Can anyone point me in the right direction for things I need to consider in order to be able to implement both of these requirements? Can I implement database mirroring (for failover) and transactional replication (for balancing) given the hardware configuration I'v mentioned? Is more information needed? Where should I turn next?
I am coming off a mainly Oracle background for the last ten or so years with a smattering of SQL Server mixed in. I've tried to hit the ground running on this project, but sometimes find myself hitting the wall running instead.
Let say I have a table that is composed of 11 columns - one the Primary Key and the other are keys to rows in another table. Of these 10 column 2-10 are nullable. Can I get all the info in one SELECT? I can't use JOINS because columns 1-10 are keys to the same table. I am not very good at explaining these things but hopefully it makes sense.
