We now host 200 G data in SQL Server 7.0 in MSCS. The data will be 1 terabyte at end of the year. The possible solution to host large data with good performance is to migrate data to Oracle or UDB in Unix platform. But we would like to try any possibility in Microsoft world before we decide to migrate data to the other databases.
I need your experience and recommendation about hosting large database in SQL Server with good web performance. Somebody mentions Microsoft tera server. Who has experience using tera server which is still in the beta release? I would like to hear some successful story to handle the large data and understand precisely hardware, software, performance moitoring requirement.
I'm running VS 2005 Enterprise, and SQL Server 2005 Developer / workgroup edition.I'm trying to follow a few online examples to play around, and some of these examples require that you add a SQL DB to your solution by going:-right click on website-Add new Item-Add SQL DataBase.WHen I do that, I get the error that SQL Express isn't installed. Now the question is, why would I want SQL Express installed when I have SQL server 2005 already installed? My Machine can support it, but it's sort of a waste when I need to run SQL Server 2000, SQL Server 2005 and then SQL Server express all at the same time.Any advice?
I volunteer for a non-for profit group and they have alot of names in amultiple databases. the problem is that some people are in multipledatabases. and if they send out a mailing from multiple databases somepeople get 2 and 3 of the same thing. my idea was to either make or find aprogram that has everyone is 1 database and then make the people part ofmultiple groups. (ex: bill is part of group a and b and im doing a mailingfrom both group i only want 1 piece of mail to go to him).It can be a separate program or with access. that use access now but i cantfigure out how to make my idea work.any question please ask.tiaStephen
I have been asked to create an archive database which we can use for reporting purpuse . The idea is to have a seperate data base which is an exact copy of the current production database , Once we setup the archive DB . we will remove all 40 days old data from Production database and form that day on should have archive DB with full data and production DB with 40 days old. I thought go going log shipping or back up restor or snapshortor something but the problem is with all these technology the both data base will come in sync . If i delete data from production that will reflect in arvhive also .
I thought of keeping the 40 days only in production and the rest in archive . But they want to wiew the both data together in one aspx page . so i had think of some joint view . But when the archive data gets grow this would be the main perfomence bottlentck
Can any one help me to select a good solution for this, thanks in advance
Hello, Im doing research for my company for a project we are about to start, but the more I find, the more Im confusing myself. Maybe someone would be so nice to help me a little.
We need a database solution, either licensing (ISV) or i think maybe an embedded database. if its licensing, its not a problem, but after research, im thinking licensing would be a waste of time or too much and theire not enough information to go by just that.
Problem: Need to develop software for a client where there will be around 300 users using the system (not all at once neccisarrily). We are creating this software for them and they want to re-sell it after completion, but they do not want to make their customers purchase a database for the purchase of their software. (because of all types of licensing). We need the database RUNTIME to run on the customers machine to make the software work.
Is there a RUNTIME license for Independant-Software-Vendor(IVS) for redistribution? or would it make sense to embed a database ( for example, the free edition of sql server;embeeded feature :), or maybe firebird embedded database?) We are developing in Visual Studio 2005.
This might sound confusing, thats where im at. For those of you with more experience, hopefully you understand what im talking about.
Thank you. I have 2 days to find a solution. Ill continue looking and post in thread if i find anything new or clearify my problem.
Hi, I am noob, so excuse me if this is a stupid question, but I just bought a Learn HTML in 3 Hours book and a learn SQL in 10 minutes book and I was wondering how I would start to go about to build a super flexible enterprise wide web based database application, that is so flexible the users can put in any old crap in the front end and make up new fields as they go. Another one of my requirements is that it perform super fast and be distributed securely across my international multi billion dollar a year company.
So like I know Excel and a little VBA and I know how to use the query builder in Access and I landed this big old contract to revamp this old comapny's infrastructure. Should be pretty easy, right? Everyone is doing it.
So can you guys like help me out for free and stuff?
guys,I have a project need to move more than 100,000 records from onedatabase table to another database table every week. Currently, usersinput date range from web UI, my store procedure will take those dateranges to INSERT records to a table in another database, then deletethe records, but it will take really long time to finish this action(up to 1 or 2 hours).My question is if there is some other way I should do to speed up theaction, I am thinking about use bcp to copy those records to datafileand then use bcp to insert it into SQL Server table. Is this the rightway to do it or should I consider other solution (then, what is thesolution.)Thanks a lot!
I have a solution with a couple of ssis projects in it. Everytime I open the solution, Visual Studio creates an extra .database file for the project's existing xxx.database file. The solution is under VSS control and VS2005 checks out the project and shows the file as a newly added file.
What causes this and how can I prevent this from occurring?
There are 2 of us working on the solution and the other fellow does not see this behavior.
The files do not show up in the VS2005 solution explorer. If they are user specific as I suspect (impersonation info?), then they should not be added to the .dtproj project file.
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.
What happens if you have a website and the hard drive on your server is say 250GB. Then the database exceeds that and the database is 300GB in size.How would you spread your database into two different hard drives?Thanks
I would like to know whether MS SQL Server 2000 Can handle Large Database size 28 gig, currently we are using Sybase but we want migrate to SQL Server 2000. Are there any performance issues?
We have many installations of our shopping cart database. One specific database is huge, now about 25 GIG, compared to the others that range from 20 to 75 MEG. The server this one resides on has three other instances of the same database that are normal size.
In a particular table in the large database there are 9700 rows taking 380MB, the same table in a normal db has 162000 rows and takes 6.MB. The tables are identical and the indexes are the same.
I upgraded a database from SQL2000 to SQL2005 and it went pretty smooth. After the transition was made, I backed up the DB. The size of the database was as expected, about 5 GB. About 5 hours later, a maintenance plan executed a few optimization jobs in the following order: Reorganize Index, Rebuild Index, Shrink Database, Update Statistics. Soon after that, another job backed up the database and it was then 32 GB. During the time after the first backup, no one was really using the database.
I've been trying to track this down for several days. Does anyone have any ideas for me?
Hello! I have a database with one table inside. The table has six columns with the following datatypes: col1 -->smallint (2byte) col2 -->int (4byte) col3 -->smallint (2byte) col4 -->smallint (2byte) col5 -->smallint (2byte) col6 -->int (4byte) I have insert 1.359.320 rows with data and the size of the sdf file is 40.116.224 byte. According to my calculation: 1.359.320 * 2byte + 1.359.320 * 4byte + 1.359.320 * 2byte + 1.359.320 * 2byte +1.359.320 * 2byte + 1.359.320 * 4byte = 21.749.120 byte I hope somebody can explain me, why the database is so large.
Importing data from an Access database, I cannot overcome the limit of 1,000 records. In DTS, I "copy one or more tables", select tables, run, and cannot see my 1,052 entries. Where can I set a max size of ~1,500 in my sql target base?
Hi to All I have large database with tables and views on the server around 80 to 100 mb amount of data.when i retrieve the data from the server it is taking long time Ex: "select * from vwcases" its taking 10 to 12 minuts time.this is my database problem.If you know the solution please reply back to me as soon as possible. thanking You.
I need to query a database with about 3 million records using a 'like' statement on various varchar columns. I've have a non-clustered index on the columns included in my query, but the performance is still pretty slow. Can anyone suggest ways to increase the speed of my queries? Does full-text indexing improve performance or does it just facilitate more advanced searches (which I don't need)? Would char instead of varchar datatypes be faster?
Hi Guys, I am looking for a large datawarehouse database implemented in SQL Server to use it to run some examples in my thesis. if someone knows where I can find a large datawarehouse database (e.g Sales database) please tell me.
Does anyone know of a good way to copy a large database or backup of a database (~10 GB) across long distances (transatlantic) in a minimal amount of time?
I have tried regular file copies, BCP, DTC, my own handwritten extraction utility, PCAnywhere, and several file splitters, all to no avail. To complicate things, the source is SQL 7 with a different code page from the destination, which is SQL 2K. The file copies always seem to get interrupted after several hours.
We have total 7 databases in the warehouse and these db are big. All the data files are approximately 300G now and they all growing more. Some are small db some are large db. (ranging from 20G to 50G of data files). Can someone please recommend me the best backup plan for this scenario. The regular backup through SQL 2005 takes too long.
I developed a vb app that imports csv data into an sql server db. The original text file is 36.5mb. The db after import is 230mb and the log file is 555mb. Is this normal?
I am developing an office automation software for a government department. To start with, I have decided to first automate the salary section of the department. Because of some issues like, TCO, easy support and maintenance, scalability etc., I have decided to use SQL Server 2005 database with VB 2005 front-end.
I have planned the layout for this first part and found that in only employee salary table, 40,000 records per month will be stored. That comes out to be around 480,000 records annually. This is for one table alone, excluding data in other tables.
Since each section of the department will be integrated into this later, this application will become the backbone the department. The employee service, leave, salary, allowance, deduction and other records shall be maintained.
For this type of mission-critical application, I want to get some queries cleared:
(1) What backup strategy should be followed? I want to have schedule and maual backups both. Is there any way to have a mirror image on another server?
(2) What considerations to keep in mind in the inital stage of database design?
(3) How to keep the design scalable and configurable to meet future needs?
1. To combine all the company's data in one large database, and use schemas and file groups to create logical and physical distribution on drives and namespaces
or
2. Distribute the data into smaller databases with related data - eg. products and product description in one db, Customers in another and orders and orderlines in a third db.
I have many, many Access databases that are roughly 1.5GB-3GBs each and they have millions of records. Each MS Access Database file corresponds to one Database in SQL server. I'm trying to simply transform the data as it is in Access to MS SQL 2005.
I'm using the 64 bit version of Windows Server 2003 and the 64 bit version of SQL 2005. The server is running four dual core AMD Operton processors and has 8GB of RAM with a 1TB RAID 5 configuration. I think the hardware should be sufficient but the SQL Server Import and Export Wizard can't seem to handle the large number of tables/records. If I do one table at a time, it works well; however, it produces the following error message whenever I try to import the entire database:
Pre-execute (Error) Messages Error 0xc0202009: {5A5BF7AD-E86B-4316-AD43-1912358C56F4}: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error". (SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. (SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 64 - District Corporal Punishment Class" (5743) failed the pre-execute phase and returned error code 0xC020801C. (SQL Server Import and Export Wizard)
Any ideas would be much appreciated! Thank you, Cody
I have a distribution database that has grown over 50g. Distribution cleanup takes over 15 hours to run and there are always very few rows in msrepl_Commands. Usually only a few hundred. Everything is being replicated fine, but I fear a storm coming. why distribution would be so large with so few rows? Also, Ghost Cleanup has been running all week, so I'm thinking maybe the records marked for deletion aren't being deleted?
I've just inherited the job of looking after our SQL server (2012). The server works fine but I am a bit concerned about the backup strategy we have in place. The current backup strategy is a SQL script that runs every 2 nights and backs up to an ISCSI box elsewhere on the network. One of the databases on the server is currently at 303GB, which will take about a full working day to restore to another server if this one fails. I am just looking at other ways which could reduce the amount of time to restore this if this server was to fail. i heard so people talking about sql clusters and configuring replication on posts by others, which i will look into if its the way to go, but i guess I am just looking what others would do if they were in the same position with such a large database.
Hi i wanna delete all the records from an large database 200 -300 tables, because i want make some changes an start from scratch,but keep the structures of the database key , index etc, i tried to generate script but when i run to many errors , plz help 10x
Hopefully I'm posting in the right area. There is a database that has grown to about 41-42 GB in size in about a 2 month period. The previous database had grown to about 22 GB before it was purged out. I'm running this on SQL 2000, and I've tried running all the DBCC SHINKFILE and SHRINKDATABASE commands to no avail. In this case, the MDF file is the one that has grown out of control as opposed to the log file (LDF file).
Does anyone have any suggestions on what could be done to control the size?
I need to transfer a database (contining one table) containing over 35 million records from one server to another. I have two options at present : (a) Use DTS to do the transfer (b) Copy the mdf file across and sp_attach_db it
Does any1 have a better idea, or does any1 know which of the two methods will be the quickest?
Here's my delema, I have a file that's 308 bytes wide by 5.7 million records. The record length is fixed and the position and width of the known within the record. When I run DTS I recieve this error Msg MS DTS flat file provide and Err Diesdription: error creating file mapping view: not enough storage is available to process this command. Then when I try to continue with the wizard, it will not allow me to separate the data into the format that I need. Is there any other way to import this file using DTS?
I am asking this question on behalf of a friend. I have little knowledge of SQL 2005 but my friend is quite knowledgeable, although this is the first time he is dealing with large database for a client. So here's the story.
His client has a database containing 1.5 million books. Now he is setting up a website which will enable users to search books. Searching by ISBN is no problem as it only takes 1 seconds. The problem is, searching by Title takes more than 20seconds, which is unacceptable. My friend has only done smaller database and he just recently thought of implementing indexing and now looking for other ideas.
Each row contains book details such as Title, Author1, Author2, Author3, Publisher, Publication Date, ISBN, etc.
Can anyone who are more experienced in doing large database share with me some design ideas? His client is aiming for 8seconds or less.
