Can Anyone Offer Me Any Suggestions On How To Create A Back Up Site W/ Db In The Even
Mar 23, 2004
I am currently running a site on a shared environment with sql db. The account is with verio.com: Windows Server 2003m SQL 2000.
What we would like to do is create a back up site with a backup db. I would like this functionality so that in the event of a crash, while the current site is being repaired, or if the server is down for a prolonged period, I can simply switch to the backup site w/ db.
What would the preferable method of doing this in such an environment? Any help would be greatly appreciated. Thanks.
I need to create a new web site in Internet Information Service.
This can be done by opening IIS manager.
In IIS Manager, expand the local computer, right-click the Web Sites folder, point to New, and then click Web Site. The Web Site Creation Wizard appears.
But if i right click the Web Site Folder. only Properties and Refresh option is showing.New option is not shown.
I will appreciate if any one give solution to solve this problem.
I have an sql script that I ran on my local system using osql. Itcreated all the tables, views, and even inserted sample data into thetables.Now I need to create that same database on a shared hoster site. Onlythe hoster does not allow the running of osql.How can I create my sql server database on my shared hoster site?Can I save and restore my database somehow?Is there a stored procedure I can run which will run the .sql scriptfile?thanks,-Steve
Hi, I keep getting a connection failed error message... CANNOT the DATABASE " " requested by the Login. The Login failed for User 'NT Authority/ Network Service' error and I figure I should create an account for the site to access the Database with but I do not know how to create an account in SQL server using the Management studio?? Anyone willing to give me the exact information I need to do this so I do not do something else and ruin things please??? I am not much of an SQL DBA. thanks in Advance.
On my VWD 2005 Express, I have installed 2 SQL Server 2005 Express databases in the App_Data folder.I kind of remember these 2 databases were installed from Wrox web site or a zip file. Now we still useSQL Server 2000 databases for our ASP.net applications. I have downloaded the SQL Server 2005 Express.These 2 databases in the App_Data folder seem to work fine although I can not see them from theManagement Studio Express. My question is: How do I 'manually' create a new SQL Server 2005 database into the App_Data folder? TIA,Jeffrey
SQL SERVER 2000Hi allThis is my first attempt at writing a stored procedure. I have managed toget it working but its unlikely to be the best way of handling the problem.While writing it I found some things that I don't understand so if any onecould shed any light it would be much appreciated. I have posted these atthe end.Sorry about the length but I thought it might be worthwhile posting the codeThe purpose of the procedures is as follows : we have a view of lots of bitsof information that need automatically mailing to different people. eachelement of information has a name allocated against it. If we had 100 piecesof data, 50 could go to manager 1 25 could go to manager 2 and 25 to manager3 etc...Both SP's look at the same viewThe first SP generates a distinct list of managers and for each managercalls the second SPThe second SP filters the view for the data belonging to the selectedmanager, and builds an HTML mail. It then sends all the bits of informationbelonging to that manager off in an EMAIL to him/her. ( It uses a brilliantbit of code from sqldev.net to handle the mail)the first mail then repeats for all the managers in the listCODE ---- SP 1ALTER PROCEDURE dbo.PR_ADMIN_CLIENT_WEEKLY_NOTIFICATION_2ASbeginSET NOCOUNT ONdeclare @no_of_managers as intdeclare @current_record as intdeclare @manager_name as varchar(100)-- count how many distinct managers we need to send the mail toselect @no_of_managers = COUNT(DISTINCT manager_name) FROMdbo.vw_client_notification_email_1-- open a cursor to the same distinct listdeclare email_list cursor for select distinct manager_name fromdbo.vw_client_notification_email_1 dscopen email_list-- for each distinct manager get the managers name and pass it to the storedprocedure that generates the mail.set @current_record = 0while (@current_record) < @no_of_managersbeginfetch next from email_list into @manager_nameEXECUTE dbo.pr_admin_client_weekly_notification @manager_nameset @current_record = @current_record+1end-- close the cursorclose email_listdeallocate email_listendCODE ---- SP2ALTER PROCEDURE dbo.PR_ADMIN_CLIENT_WEEKLY_NOTIFICATION(@current_manager_name as varchar(100))-- a unique managers name is passed from the calling procedureas beginSET NOCOUNT ON-- declarations for use in the stored procedureDECLARE @to as varchar(100)DECLARE @entry varchar(500)DECLARE @region as varchar(100)DECLARE @type as varchar(100)DECLARE @site_ref as varchar(100)DECLARE @aborted as varchar(100)DECLARE @weblink as varchar(1000)DECLARE @manager_name as varchar(100)DECLARE @manager_email as varchar(100)DECLARE @body VARCHAR(8000)DECLARE @link varchar(150)DECLARE @web_base VARCHAR(150)-- set up a connection to the view that contains the details for the mailDECLARE email_contents cursor for select region,type,site_ref,aborted_visit,link,manager_name,manager_e mail fromvw_client_notification_email_1 where manager_name = @current_manager_nameopen email_contents--some initial textset @body = '<font color="#FF8040"><b>Reports W/E ' +convert(char(50),getdate()) + '</b></font><br><br> <a href = http://xxxx > Click here to logon to xxxxx </a><br><br> '--fetch the first matching record from the table and build the body of themessagefetch next from email_contents into@region,@type,@site_ref,@aborted,@link,@manager_na me,@manager_emailset @web_base = 'http://'set @weblink = @web_base + @linkif @aborted = 0 set @aborted = '' else set @aborted = 'ABORTED'set @body = @body + '<font size="2"><b> Region </b>' + @region+ ' <b>Type</b> ' + @type+ ' <b>Site Reference </b> <a href = "' + @weblink + '">' + @site_ref+'</a>'+ ' <b>Unique Report Reference </b>' + @link + '<br>'-- continue reading the records for this particular message and adding on tothe body of the textwhile(@@fetch_status = 0)beginfetch next from email_contents into@region,@type,@site_ref,@aborted,@link,@manager_na me,@manager_emailif @aborted = 0 set @aborted = '' else set @aborted = 'ABORTED'if (@@fetch_status = 0) set @body = @body + '<b> Region </b>' + @region+ ' <b>Type</b> ' + @type+ ' <b>Site Reference </b> <a href = "' + @weblink + '">' + @site_ref+'</a>'+ '<b>Unique Report Reference </b>' + @link + '<br>'end-- close the cursorset @body = @body + '</font>'close email_contentsdeallocate email_contents-- generate the mailDECLARE @rc int EXEC @rc = master.dbo.xp_smtp_sendmail@FROM = N'FROM ME',@TO = @manager_email,@server = N'server',@subject = N'Weekly Import',@message = @body,@type = N'text/html'endQuestionsis the way I've done it OK. I thought I would be able to do it in a singleSP but I really struggled nesting the cursor things.@@fetchstatus seems to be global, so if your using nested cursors, how doyou know which one you are refering to. If you have multiple calls to thesame SP how does it know which instance of the SP it refers to.When I first wrote it, I used a cursor in SP1 to call SP2, but I couldn'tget the while loop working - I have a feeling it was down to the @@fetchstatus in the 'calling' procedure being overwritten by the@@fetchstatus in the 'called' procedure.The whole @@fetchatus thing seems a bit odd. In the second procedure, I haveto fetch, then check, manipulate then fetch again, meaning that the samemanipulation code is written twice. thats why in the first procedure I usedthe select distint count to know how long the record set is so I only haveto run the manipulation code once. Is what I have done wrong?its possible that the body of the mail could be > 8K, is there anotherdatatype I can use to hold more than 8Kmany thanks for any help or adviceAndy
I noticed that by inserting another group higher than the existing one gave me the effect of a nested group...so what would a recursive hierarchy have done for me that is different? I read the BOL writeup and didnt come away with any sense of what these hierarchies offer, seemingly thru the use of a "parent" entry.
A contract can many conditions so 1:M...Col 2 is the contract reference and the linking join to contract
Now - a condition related to an contract can have many re-trys and the causes the pk to increment. As you can see there are three conditions related to first attempt and then another three conditions.I want to create an ouput which just reflects the latest conditions when joining back to contract - Is this possible?I have requested to application providers to provide flag, but this will take some time...
would you use sql server "full text search" feature as your site index? from some reason i can't make index server my site search catalog, and i wonder if the full text is the solution. i think that i wll have to you create new table called some thing like "site text" and i will need to write every text twice- one the the table (let's say "articles table") and one to the text. other wise- there is problems finding the right urlof the text, searching different tables with different columns name and so on... so i thought create site search table, with the columns: id, text, url and to write every thing to this table. but some how ot look the wrong way, that every forum post, every article, album picture or joke will insert twice to the sqr server... what do you think?
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on theĀ roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
Hello,I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db).1.back-up the entire database2.truncate the log3.shrink the log4.back-up once again.As you may have determined, I am relatively new to managing a sql server database and while I have found multiple articles online about the topics I need to accomplish, I cannot find any actual examples that explain where I input the coded used to accomplish the above-mentioned steps. I do understand the theory behind the steps I just do not know how to accomplish them!If you know of a well-documented tutorial, please point me in the right direction.Regards.
Does anybody know of a way to rollback SQL Server 2005 databases back to SQL Server 2000? Is there a way of doing it without resorting to Copy Database Wizard? I love to find a way of attaching a SS 2005 database to a SS 2000 instance without any issues.
I recently upgraded to SS 2005 and I am very unhappy with the SS 2005 and I want to rollback to SS 2000, which was a lot more stable. I am having several major issues that are affecting my whole company's day-to-day operations and the managers are not happy. Some of the issues include night time batch running very sluggish for no apparent reason. This is a biggest problem because it only occurs once or so a week and causes a disturbance with the daily activities when the night time processing isnt completed on time. The rest of the time, the batch processing runs great, even a little better then on SS 2000. I don't believe it is a matter of my application needing to be retuned because if that was the case, then why isn't it running sluggish every night? Also, it's never the same day that the sluggish behavior occurs. If it was occurring on the same night, then I would have something to investigate within our application, but it doesn't. Another issue that I am having involves a night time job that restores a copy of the production database to the Data Warehouse server to be used for updating the data warehouse. Again, most of the time it runs great (~2 1/2 hours), but once or twice a week, it goes stupid and takes 6 1/2 hours for no apparent reason. Again, it is not happening the same day either, which could give me something to invesigate. On SS 2000, this same job ran flawlessly. Never I did I run into situation that the database restoration took that long to run. Even another issue involves a SQL Server Agent Job that was put into suspended state. What's a suspended state and how can I get it out of suspended state? I can find no information about suspended state in BOL. I did a Google and nothing came up. If this suspended state was put in for security reasons, great, but then tell me how I can remove the suspended state. I am also not happy with the fact that I can't get accurate information about the queries that are actively running at that particular moment. In SS 2000, when I noticed high CPU usage on the server, I would run the sp_who2 active stored proc and it would show me all the active thread and how much CPU it was consuming. I would then find the running threads with the highest CPU numbers and investigate the query and see if we could improve it. Now in SS 2005, I get in the same situation and run the sp_who2 stored proc, and there is no smoking gun. All of the active threads are showing very little CPU usage, which I am very suspect of. What the heck happen to sp_who2? I looked at some of the other ways of looking at running processes (i.e... sys.sysprocesses) and they don't appear to be giving the information that I need.
I am very unhappy and I just want to roll back to SS 2000 and wait a couple of years before I upgrade to SS 2005.
ApplicantID FirstName LastName CompanyName Line1 Line2 City
State Zip PhoneNum
Owner
OwnerID FirstName LastName CompanyName Line1 Line2 City
State Zip PhoneNum
Now i know what im doing with the applicantID and ownerID...but the BZAcase# is a number/unique identifier that looks like this....2007-VU-000, 2007-VU-001, 2007-VU-003....so my question is 1. how do i get the last three numbers to increment each time a new application is created? 2. how do i retrieve the last record in the table??? 3. Do you have any other suggestions?? i have to have the number and what type of form they applied for in the "case#"???
Background: We are changing the way we pay commissions to our rep groups. We used to pay when the order was placed, now we want to pay when the invoice is paid.
Problem: The commision information is currently stored in the customer order, not in the invoice. These orders get deleted a couple weeks after the order was completed (shipped).
I want to create another, rather dynamic, table/structure that will store the order number and the commission percentage.
This info in this table should:
Be deleted: if the order has been deleted and the invoice either does not exist or was payed some period of time ago (maybe 6 months)
Be updated: if the customer order has been updated (i.e. the commission was changed)
Be inserted: if the order exists but the order number is not in the new table.
hi i have written a procedure for stock report. its working fine. please go through the sp and give me some Suggestions. please tell me where i need to improve my code. thanks
Note: User is required to execute this procedure daily. i am taking the sum of issues,purchases,returns,physical adjustments for each and every product from last updated date to today's date and storing it in a table i,e stock_Dump. from this table i generate the date wise stock report
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
CREATE PROCEDURE dbo.spUpdateStock @strReturn varchar(70) output AS BEGIN declare @maxDt smalld atetime if exists(Select * from Stock_Dump where Txn_Date= Convert(varchar,Getdate(),101)) BEGIN set @strReturn='Stock Table already generated for the day. cannot generate it again' END
ELSE BEGIN TRUNCATE TABLE Stock_Dump_Temp select @maxDt=max(Txn_Date) from Stock_Dump /* insert (Opening stock) Closing stock for all all the products from last max Date*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date, Closing_Stock as Opening_Stock , 0,0,0,0,0,0,0 from Stock_Dump Where Txn_Date=Convert(varchar,@maxDt,101) /* Issues*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0, Sum(Qty) as Issue_Qty,0,0,0,0,0,0 from Issue_Details Where Issue_No IN(Select Issue_No from Issue_Hdr Where Issue_Date > Convert(varchar,@maxDt,101) and Issue_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Goods receipt*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, Sum(Qty) as Purchase,0,0,0,0,0 from Dlv_note_Details Where Dlv_Note_No IN(Select Dlv_Note_No from Dlv_Hdr Where Dlv_Note_Date > Convert(varchar,@maxDt,101) and Dlv_Note_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Rejection after receipt*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, 0,Sum(Qty) as Rejected,0,0,0,0 from Rejection_Details Where Rejection_No IN (Select Rejection_No from Rejection_Hdr Where Rejection_Date > Convert(varchar,@maxDt,101) and Rejection_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Issues returns*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, 0,0,Sum(Qty) As Issue_Returns,0,0,0 from Issue_Return_Details Where Issue_R_No IN(Select Issue_R_No from Issue_Return_Hdr Where Return_Date > Convert(varchar,@maxDt,101) and Return_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Physical Stock + */ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, 0,0,0,Sum(Var_Qty) as Phy_Qty_P,0,0 from Physical_Details Where Var_Qty>0 and Txn_No IN(Select txn_No from Physical_Hdr Where Txn_Date > Convert(varchar,@maxDt,101) and Txn_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* Physical -*/ INSERT INTO Stock_Dump_Temp Select Product_code, convert(varchar,GetDate(),101) as Txn_Date,0,0, 0,0,0,0,Sum(Var_Qty) as Phy_Qty_M,0 from Physical_Details Where Var_Qty<0 and Txn_No IN(Select txn_No from Physical_Hdr Where Txn_Date > Convert(varchar,@maxDt,101) and Txn_Date <= Convert(varchar,getdate(),101)) Group by Product_Code /* insert all the records into actual table i,e Stock_dump from Stock_dump_temp (temporory table)*/ INSERT INTO Stock_Dump Select Product_code,Txn_Date, Sum(Opening_Stock) as Opening_Stock,Sum(Issue_Qty) as Issue_Qty,Sum(purchase) as Purchase,Sum(Rejected) as Rejected,Sum(Issue_Returns) as Issue_returns, Sum(Phy_Qty_P) as Phy_Qty_P,Sum(Phy_Qty_M) as Phy_Qty_M,0 as Closing_Stock from Stock_Dump_Temp Group By ProducT_Code,Txn_Date /* update closing stock*/ UPDATE Stock_Dump Set Closing_Stock=abs((Opening_Stock+Purchase+Issue_Returns+Phy_Qty_P)-(Issue_Qty+Rejected+Phy_Qty_M)) Where Txn_Date=Convert(varchar,Getdate(),101) /* delete unwanted records */ DELETE From Stock_Dump Where Opening_Stock=0 and Issue_Qty=0 and Purchase=0 and Rejected=0 and Issue_Returns=0 and Phy_Qty_M=0 and Phy_Qty_P=0
set @strReturn='Stock Table Update Successfully' return END
END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
I have a database which contains more than 20000 stored procedureswhich were created withansi nulls off. This i found out using the querySELECT name,AnsiNullsOn FROM(SELECT name, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') AS AnsiNullsOnFROM sysobjects WHERE type = 'P' ) A WHERE AnsiNullsOn=0Is there any way that i can set this property to 1 for all the storedprocedures i have??I know the alternate method is to drop the procedure and execute thescripts again with AnsiNullsOn = 1.Is there any other simple ways?? It will be very helpful for me..
Now I have lets say VISID 1 to 50. I'm using this SP to change the text on a button. Now I have 50 buttons. So I run this SP, then I run this in my vb.net code
Code Snippet Dim constr As New SqlConnection(PVDBConn) Try 'Variable to hold the results Dim results As String = String.Empty cmdUpd = New SqlCommand("SelVis1Name", constr) cmdUpd.CommandType = CommandType.StoredProcedure constr.Open() 'Set results to the value returned from ExecuteScalar() results = CType(cmdUpd.ExecuteScalar(), String) constr.Close() 'Set our buttons text to that value Button1.Text = results Catch ex As Exception MsgBox(ex.Message.ToString) End Try
At any time, when I start my program, I may need to label 10 buttons, or up to 50. Now I will have this number in a text file. Can I grab that number from a text file, and pass it into a SP?
And can I write this SP only once, to work for more than one label per time. Or do I have to write this sp 50 times?
I have a database that will be used by two or more organizations. I would like to use pass phrase encryption to encrypt a couple of columns.
I'm looking for suggestions on how I might set up the db to let the organization change the pass phrase that is used for their encryption?
I don't really want to hard code it into stored procedures or select statements with parameters. I will be using SSL if that should make a difference with what you suggest.
I have two stored procedures (l'll call them P1 & P2). P1, after a lot of processing, creates a temporary table that is used by P2 after an "exec P1" is done. I've separated the logic into two stored procedures because, ultimately, other sprocs will need the output of P1.
I get an error if I use #tempTable as the output table in P1 because it no longer exists after P1 finishes. ##tempTable works, but I'm concerned about concurrency issues. Any suggestions on what construct(s) I should be using?
Hi All, I am new to SQL Server but have been doing database programming since last 3 years. I recently attended MOC (Microsfot Official Curriculum) training on SQL Server and have started to use at my company. I am comfortable with SQL but want to dig deeper into T-SQL side. I searched on the Internet but not many good books available in that either they are ranked very low or are very old i.e. written around 1999/2000 or covers SQL Server 2000 as a whole. Can anybody suggest me any T-SQL book which was written recently and focuses purely or majorly on T-SQL?
Thanks to all for your time and advice in advance.
I'm looking for some help on how i should index this table.
current table has about 500k records in it. the fields in the table are: member_num (varchar(12), not null) first_name (varchar(20), null) last_name (varchar(20), null) ssn (varchar(50), null) address1 (nvarchar(200), null) address2 (nvarchar(200), null) city (nvarchar(200), null) state (nvarchar(200), null) zip (nvarchar(100), null) phone1 (nvarchar(50), null)
all of the fields are searchable through an asp.net webform.
my first stab at this consisted of creating a clustered index on member_num and then creating a separate index for each of the remaining fields.
What I have. I have a spreadsheet that is used in 4 or more locations on a daily basis by 1-3 ppl per locations. The spreadsheet is used to gather Quality Control information. So everyday there are a couple of spreadsheets from each system that is used to generate weekly and monthly reports. This is becoming to much work and I would like to automate the process.
What I have access to. I currently run a Sharepoint 2007 Server for all our collaboration and document needs. I also have the ability to setup any sql server.
What I want. I want the QC techs in each system to be able to upload the data at the end of each day and be done with it. This way they do not have email or do a weekly report. I would prefer to use Sharepoint and create reports weekly and monthly that can be pulled just by going to a site.
I'm knowledgeable in Sharepoint and Excel. I have some skills in VBA. I haven't dealt with SQL any, but willing to learn. Also I'm knowledgeable in Microsoft Access as well.
Any suggestions on how I could accomplish this would be appreciated.
Well, as a VB/VBA applications developer I'm not well prepared for this, but it looks like I will be riding herd on a production SQL Server.
TSQL I know well enough to get along, but where can I get a fast fix on all the logins, security, and process management info? Today we had a DTS package crash overnight and it took me forever to figure out that it had left half a dozen tables locked. (Note that the scripts for the DTS package are being re-written as we speak with use of transactions and NOLOCK.) Meanwhile tech support was handling a whole mess of grumpy users.
Are there any books you would recommend as resources/references? Is there a particular author who is good at writing the stuff you really need to know in English that can be read by a mere mortal like I? I am fond of the Microsoft resources/help files but I'd like to have somthing that holds highlighter and post-it flags a bit better. Not to mention something that focuses more on the beast as a whole rather than the minutia at length.
I have a set of tables with about the same structure
dataID, recordID, 15 other columns
dataID is unique but is never referenced in queries
recordID is one of the most referenced columns but only has a cardinality of about 30%
The current structure has a clustered PK on (dataID,recordID)
Someone suggested reversing the clustered PK to (recordID,dataID) because of the number of references to recordID but that didn't seem to boost performance any
After staring at this for a while I came up with something but I'd like some advice whether it makes sense or not.
create a non-clustered PK on dataID create a non-unique clustered index on recordID
Let me know if any other information is needed. Thanks
I would be teaching an applied database course to buisness majorundergrads. I'm looking for a book that introduces database conceptsusing SQLServer as the database. I would really appreciate if you couldrecommend me a few such books.ThanksNemo
I'm trying to count the number of records in 'game_dates' where thecolumns home_team_id or away_team_id have the same value. E.g., iwant to know the number of records for each team_id where team_id ishome_team_id or away_team_id.I'm doing this in two separate select statements now. Example:SELECT count(home_team_id),home_team_id FROM gamesWHERE league_id = 218 and ((home_score IS NOT NULL OR away_score ISNOT NULL) OR (home_score <> 0 OR away_score <> 0))GROUP BY home_team_idandSELECT count(away_team_id),away_team_id FROM gamesWHERE league_id = 218 and ((home_score IS NOT NULL OR away_score ISNOT NULL) OR (home_score <> 0 OR away_score <> 0))GROUP BY away_team_idand then combining the results. Is there anyway to combine these toqueries into one query? ...and have a single result set returned withtwo columns (count,team_id)?Thanks,Glenn
Need some suggestions for senior management for DR Purposes:
Background:
WSS/MOSS2007 is being used as a Document Management solution.
17 Servers geographically dispersed around the UK. Each server runs WSS 3, SQL Server 2005 and IIS. Each server is linked into a PiP cloud via 2MB MPLS.
At each location; We are looking at 20 core databases; each pre-sized to 10GB. If I take one site as an example, the previous nights backup totalled 135GB.
The company has taken a centralised view on backup's, so SQL Server Data and Log files are replicated using Double-Take to a central location where by the files are taken onto tape daily (Full backup of all files).
As a precaution, I take a Full SQL Server backup daily and also Tran Logs every 4 hours locally and keep it there for 2 days; however if the site goes boom I loose those, so for this purpose; please forget they exist.
As I expect; when I restore the mdf and ldf files from tape, I will get errors when I attach those files into SQL Server for transactional inconsistencies which I'm well aware of.
Other options I've considered are:
1) DB Mirroring. Not a bad option, but still have to get the DB to the Mirror Server in the first place. Also DB Mirroring is not recommended for more than 10 mirrored databases.
2) Log Shipping. Same issue as above; Have to get the data here in the first place. Then once Log Shipping is setup; if I have a failure; I need to start the whole lot off again.
3) Transactional Replication. Issue is with the initial replication getting the data from A to B, then if I need to use it in a DR situation; I will get issues saying this table is being used for replication. This can be worked around, but it's a not a quick process...
4) 3rd Party Backup Compression. E.G. Litespeed; Redgate SQL Backup, etc. Good; Tests have shown a 42% compression for us, however if I refer to the earlier example of 135GB, this compresses to 81GB. Throw in the theoretical max for a 2MB link of 19GB / 24 Hours, this would take 4 Days to copy.
Other thoughts I've come up with are:
A) Split the tables into different file groups; not sure how easy this would be as the DB's and Tables already exist.
B) Full/Diff/Tran. Still have the issue of scheduling the full backup over the weekend and taking 4 days to get here.
C) Local Tape Backups. Issue is relying on someone to change the tape on a daily basis. It's not centrally managed and how do we restore in a DR situation ?
Hi folks, I have a very typical database for an ASP.net application. There is a table which will contain a hierarchical data..much like files-folders structure of a file system. But we know that the table will be a giant one in production. There will be a huge collection of data need to persist in it. we are already facing some performance problem with some queries during the QA/test machine. Currently there is a table which is keeping all file and folder information and another table maintaing their hierarchy relation using two column namely, parentID and childID. My first question is, would it be better to keep this hierarchy relation into the same table rather using a different one? (much like managerID and empID in AdventureWorks sample?) My Second question, what is the best way to design this kind of structure to get the highest performance benifit?
All kind of thoughts will be appreciated much! thanks
