Moving A VLDB
Feb 22, 2000I have a database approximately 30 GB in sixe which need to be moved from one SQL server to another. Does anyone know the most efficient way of doing this, other then backing up to tape?
View 1 Replies
ADVERTISEMENT
VLDB Performance.
Jul 16, 2007Hi experts,
We have SQL Server 2005 installed in MS Windows server 2003 with 8 GB RAM. This server has 4 processors.
Ours is a VLDB and a single table has 400 million records occupying nearly 40 GB of space.
We find it vert difficult to meet the response time set by the clients in many occasions.
Should the RAM be atleast as big as the biggest table in the database ? Is this mandatory ?
Even any other suggestions for improving the performance are welcome.
Thanks & Regards,
Hariarul
Documents In VLDB
Jul 31, 2007Hello All,
I have been experimenting with SQL Server 2005 partitions. I loaded a terabyte of information into 2 tables. The first holds the document information and the second holds the actual binary document (in this case pdf). Most of the documents are about 1 megabyte in size, but the largest is 212 megabytes.
SQL Server has no problem storing the blobs. The problem occurs when I attempt to get the data.
I did some quick tests to test how fast I could pull the documents out. The largest took about 24 seconds. The 1 meg documents are sub-second.
Here is how the 212 meg doc breaks down:
Time to load datatable: 18.79 seconds
Time to load byte array: 3.84 seconds
Time to Write and open document: 0.01 seconds
If I access the file from a file server, the time is 0.04 seconds to begin showing the document.
As you can see, the longest time period is related to retrieving the data from SQL, and it is much slower that launching it from disk across the network. (note: the sql server and file server used to test are next to each other).
My question is, how can I speed up the access from SQL Server? I believe the keys are "partition aligned". Any suggestions would be appreciated.
I will add the table definitions and partition information as a reply since only 5000 chars are allowed in the post.
VLDB Backup
Oct 11, 2006Hi There
I have a 50Gig OLTP production database that currently takes +- 50 minutes to backup, (normal sql flat file backup to disk).
This database will grow to +- a terrabyte by next year.
My major concern is how will i be able to backup this DB when it is that big in 2 hours or less.
I have been checking out my options, in terms of SAN snapshots/clones. Also multiple backup devices and using differential/filegroup/full backup strategy.
What i want to know is if anyone out there is backing up VLDB's what strategy/methos/tools are you using, even 3rd party tools for faster,smaller backups?
Any pointers/best practices for VLDB backups would be greatly appreciated.
Thanx
VLDB RAM Requirement
Jul 17, 2007Hi experts,
We have SQL Server 2005 installed in MS Windows server 2003 with 8 GB RAM. This server has 4 processors.
Ours is a VLDB and a single table has 400 million records occupying nearly 40 GB of space.
We find it vert difficult to meet the response time set by the clients in many occasions.
Should the RAM be atleast as big as the biggest table in the database ? Is this mandatory ?
Even any other suggestions for improving the performance are welcome.
Thanks & Regards,
Hariarul
SQL Server 7.0 Performance For VLDB !!!
Jan 13, 2000I hard that SQL Server 7.0 has problems when the database reaches
50 - 100GB, in areas such as backup, transaction logging, and database
admin and that by 100GB parallel queries are also affected.
Is this true ? Where I can get information on this ?
Thanks in Advance.
Regards,
Vidyadhar
VLDB Across Multiple Devices
Sep 14, 1998Hello
Does anyone have experience/advice with large databases (5-10 Gig)? If so, I was wondering about
performance/other benefits of spanning a large database across multiple devices (different disks). Would anyone
vote for or against doing this?
Suggestions...
Thanks
Tim
VLDB Replication Or Dev Copy
Jul 20, 2005I have a production database that is in the low gigabyte size andgrowing steadily. No issue there.I wish to completely refresh the development database daily on asecond server. What is going to be the fastest easiest way to do thiswith hindering performance on the production system ?Thanks,Craig
View 2 Replies View RelatedIndex Fragmentation Strategy For VLDB
Nov 20, 2007I need to manage the problem of negative performance implications when I fragment a 1TB+ DB. I want to perform Index Reorganization if fragmentation is no higher than 30%, and Index Rebuild if the fragmentation exceeds 30%.
Firstly can anyone recommend a script which uses sys.dm_db_index_physical_stats system to ascertain the
fragmentation level. Secondly, is there a technique I can employ to prevent the ONLINE operation completely killing performance on 27/4 production system?
ALTER INDEX REORGANIZE/REBUILD WITH (ONLINE=ON)
VLDB Data Type Changes... Same Size
Jul 20, 2007I've got a few VLDB's that we want to make smaller. Since the tables are running on legacy stuff, all of it's basically made with int's and char's and it's horriably inefficant.
The problem that I came across is when I made a new table with the best data types and copied the data from the old table, the table size was the exact size (excluding the index size). It was estimated that a total of ~20 GB would be saved with this change. As it turned out, 0 bytes of data were saved with the data types chagnes.
Why are the two tables the same, even though one has much more efficant data types?
If you want more information about the table I'm using:
391 columns.
50,147,035 rows.
65,295.625 MB in size.
SQL 2012 :: Migrating VLDB To Availability Group
Feb 4, 2015I have been using AlwaysON AG for a long time now and currently have about 10TB of data across 120 databases and 3 AG groups for any application that is on SQL 2012 with great success. Each AG group is running on patch level 11.0.5058.0 with 2 synchronous replica(on different SANS) in Primary Data center and 1 ASYNC replica in DR. Migration has been a non-issue because none of the databases weren't substantial enough that I could not fit into my maintenance window which is 12-4AM on SAT morning.
My issue is that my last application to migrate to 2012 includes a 4TB TDE encrypted databases database which is about 10x larger than any of the previous ones I have migrated. The database takes 4 hours to backup after tuning extensively(I hate TDE!!)
The restore to the primary replica is instant because of seeding incremental but the issue comes from having to backup the database before adding to the availability group. 4 hours is my exact outage window and I can't get any more. My plan to migrate application is to -
First Outage Window
1) Restore Database from 2008 to 2012 Primary Replica
2) Change application ARECORD(or cname not sure which) to Primary replica
3) Run database on single node until next outage window
Week Later
1) Add database to availability group
2) Change ARECORD/CNAME to listener
What I don't like about this is I am going an entire week with 1 node instead of 3 which is worrisome. How to accomplish this I would love to hear from you or any type of comment from people who have worked with VLDB in availability groups and what you like/hate/loved about doing it. I am trying to go all in on this software and have loved it so far but getting worried when it comes to the VLDB migration.
Adding A Column To VLDB 200GB Table
May 1, 2007The column I'm adding needs to be part of the clustered PK (it will be the last of three columns) so I need to recreate all the indexes.
My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've tried two methods so far.
Method 1:
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_copyoftablewithnewfield
(
) ON PRIMARY
IF EXISTS(SELECT * FROM dbo.originaltable)
EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields>)
SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.originaltable
GO
EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
'OBJECT'
GO
<recreate PK constraint>
<rebuild indexes>
COMMIT
Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
Con's: Tons of wasted space and time. It took about 15 hours.
Method 2:
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
<drop PK constraint>
<drop indexes>
ALTER TABLE [dbo].[originaltable] ADD
[newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_newfield] DEFAULT
((1))
<recreate PK constraint>
<rebuild indexes>
COMMIT TRANSACTION
Pro's: No making a copy of the entire table taking up 200GB more space in the db data file
Con's: My tempdb grew to accomodate the row versioning info for every row in the 200GB table. It took over 30 hours.
A lot of time and disk space is wasted with both.
Since the db is going to be unavailable to users I have some flexibility here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then trying method 2 again which should stop the versioning in tempdb and then turning it back on.
I was also curious if setting the database recovery mode to SIMPLE would cut down on db log usage and then I could set it back to FULL when done.
Do these really need to be in a transaction? If there's some hardware failure or something unexpected I can just restore from backup and do the conversion again. If the presence of the transaction itself is causing more disk usage for logging or any other slowdown, I think I'd rather do without.
Given the amount of time this conversion takes, I wanted to get some
feedback other than "just try it" before doing any new tests.
Thanks.
VLDB Design Ideas And Suggestions-Appreciated
Mar 24, 2008Hi,
Im a Jr DBA and have been given an assignment by my lead to find information on the following.
We are to migrate existing db of size 4TB to a
DELL PowerEdge 2950[Mem:Up to 32GB]
OS : Windows Server 2003 Std Edition X64 SP2
DB : SQL Server Enterprise Edition x64
I am to find on how to design the db to provide optimum performance,fail over and consider the growing factor of the db.
1)What would be the recommended RAID settings?
2)Placement of the tempdb ?
3)Should we do clustering and why ?
4)What Data partioning would do to help?
5)Any Other aspects to be considered for sizing db ?
6)Placement of data files and log file on separate physical disk ?
7)Indexing?
I have read many sites.I would appreaciate if someone could write suggestions and opinions based on their current db design spec or previous experience,by selecting best db design points.Thank You.
Can Any One Tell Me How To Delete Data In Batches Of 10000 From A VLDB Table
Nov 14, 2001Hi,
I would like to delete a data from a 750million row table in chunks of 10000,without blocking the users.As ours is a 24/7 shop I donot want to block the users for a long time.
Answer for this is highly appreciated.
Thanks
Samna
Transact SQL :: How To Get Moving Total Like Moving Average
Nov 10, 2015I trying to get the moving total (juts as moving average). It always sum up the current record plus previous two records as well and grouped by EmpId.For example, attaching a image of excel calculation.
View 3 Replies View RelatedMoving ALL DB's
Jun 28, 2000does anyone have a script that will read the sysdatabase table and transfer all datbase's to another server. example pseudo code:
start loop
get local sserver db name
create db on other server
transfer ALL objects to new database
end loop
Moving SQL From C: To D:
May 29, 2002I have inherited an SQL server that is running out of disk space. SQL 2000 was installed on a regretfully small C: partition, and I need to move as much as I can to the D: partition. I really don't want to uninstall and start over, and I am hoping there is a whitepaper or something that I can use to accomplish the same thing.
Any help is appreciated!
Moving DB From X64 To X86
Apr 26, 2007My company recently purchased a new ERP system which does not support 64 bit SQL Server. For this reason, I need to uninstall the 64 bit SQL server 2005 and install the 32 bit version. This leads to my question. I have an application currently using a database on the 64 bit version and would like to confirm that the DB will work correctly in the 32 bit version. Do I just have to detach the database, copy the files, then install SQL server 2005 32 bit and attach the DB?
View 4 Replies View RelatedHELP MS Access Moving To SQL And Don't Know How
Oct 6, 2006I have been using MS Access for a long time. The last couple of months my sites have really picked up in business (good rankings) and we keep crashing the server (No sites on the server that use MS Access will run) We get the errorSystem.Data.OleDb.OleDbException: Unspecified error at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider()at System.Data.OleDb.OleDbConnection.Open()at DreamweaverCtrls.DataSet.DoInit()Also I am finding out from some of my customers that their sites they can't use at times because of the error but the server hasn't crashed yet because others are up. Well I believe we are exceeding MS Access very badly because it crashes the server daily now and sometimes more then once a day. So I am going to start moving my MS Access database to SQL but have NO CLUE how where to start etc or use SQL correctly. If people can point me in the right direction that would be very helpful. Everything I find online is people needing help but no solutions that work for me. My new server is a windows server running ASP.Net 2.0 and to edit my SQL I have "ASP.Net Enterprise Manager" and Plesk for the server.1. A good book to follow and easy to follow for SQL (connections / how tos) or website2. Good examples or book on "ASP.Net Enterprise Manager"3. How to convert access databases into SQL easy (some have over 50,000 listings)4. how to convert Tab files into SQL easy.Thank you so much for any help. Also I am using Dreamweaver for most of the edditing and connections. I know its not your favorite but I don't have the time to write everything by hand and it works good for what I do on the websites.Thanks again,Rusty
View 2 Replies View RelatedMoving A Database
Apr 23, 2007Hi
I want to transfer a database from one PC to another. (Both running SQL Server 2005 express)
I have copied the files (SQL Server Database Primary Data File and SQL Server Database Transaction Log File) from the C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData folder of the source PC to the destination PC (Same folder).
I was under the impression that using Microsoft SQL Server 2005 - SQL Server Management Studio Express these files would be picked up and could be used, do I infact need to do something to attach the files/database so that they will appear in SQL Server management?
Moving Data
Oct 23, 2007I have 2 databases on one server that I want to consolidate into one database. I'm just learning SQL Server 2005. What is the easiest way to move my 3 tables from one database to a new one on the same server? Do I have use SSIS to do it, or can a simple query be written? I'm new so please be a little detailed in your answer. Thanks in advance for any comments.
View 2 Replies View RelatedMoving A DTS To Another Server
Dec 28, 2003Hi I'm very new to this but what I'm looking to do should'nt be too difficult although I've tried to no avail.
I'm looking to deploy a web site that uses SQL2000 to another server. Im trying to find out how to move the database to that server. Ideally I would like to be able to save it to CD for safety but its not like old access where you save the mdb file. I need to save the whole 'DTS' package, is that what its called? stored procedures, triggers the lot.
I tried the wizards but just got in a mess.
Thanks
Moving Ms SQL Databases
May 5, 2005I've created a web application with Visual Studio 2003. Uploading my application to the server is easy enough. I use the Copy Project utility from the Project selection on the menu bar.
I'm using a remote server-- and hosting account that supports ASP.NET and ms SQL. But now I want to copy my ms SQL database and can't find any way to copy my DB to the server.
What do the best way to to this?
PaulTucson, AZ
Moving Tempdb
Mar 20, 2002Can someone tell me how I can move the temp database. I know it get's recreated on startup so there must be somewhere that it's referencing.. Is it the model database it uses?
Thanks!
Moving SQL 7 Databases
Apr 11, 2002What is the best way to move the databases to a new hard drive connected to the system? We added a new larger hard drive and I would like to move just the databases to the new hard drive.
Thanks
Moving An SQL 7.0 Database
Apr 9, 2001I have an SQL database running on an old server and i am upgrading the server, but i am having some problems moving my SQL database over. Has any one got a work through they could mail me to give me a hand!!
Cheers
Mike
Moving DTS Packages
Apr 30, 2001Is there a way that I can move DTS packages from test machine to production machine? Or do I have to recreate new packages in production.
Thanks,
Jim
Moving Db To 2000
May 4, 2001Is there any easiest way to move a 100 Gig sql 7.0 database to sql2000?
View 1 Replies View RelatedMoving Database
May 29, 2001Hi, Everyone,
I need to copy a Database onto another Server, what is the best way to do it?
Thanks
Moving SQL 7.0 To New Server
Jul 28, 2000I have serveral jobs set up on my current SQL Server, and I am not sure how to move the jobs to my new SQL server. What is the best way to do this?
Thanks,
John
Moving Data Between 6.5 And 7
Aug 3, 2000I have a scenario where I need to refresh a database that is in 7.0 (converted from 6.5 database) from the original database. Is there an easy way to do this. I have tried creating a DTS package but the data never seems to make it accross.
View 1 Replies View RelatedMoving Databases
Aug 4, 2000Hi,
Iam trying to move the database from one server to the other server.I know it can be done in the following 3 ways i.e
Copy .mdf and .ldf files to the other server and attach them.
or
restore the databses from the backups
or
use data transformation services.
Now let me know which is the fastest way of doing this.
and also what are advantages and disadvantages of above methods if possible.
Any suggestion would be appreciated.
Thanks.
Moving .LDF Onto The Different Drive
Aug 7, 2000Hi
I have a database(CEB) and my CEB.mdf is on D Drive and CEB.LDF is on
G DRIVE ...NOw I want to move the CEB.LDF on to the different drive ..
can any one suggest me the way and will I have any effect on the database.
It is kind of urgent.
Thanks
RAGHU