How To Migrate Existing Indexes To A Different File Group?
Apr 21, 2003
Is there any utility to migrate the existing indexes on default filegroup to a new user defined file group? The only way that I know is to delete the index and recreate with new filegroup.
How about for table? Is there any utility for migrating the existing tables to a different filegroup? The only way that I know is to create a clustered index on the new filegroup which will move the table to the new filegroup. If a clustered index exists then drop the clustered index and recreate it with the new filegroup
I am looking for an existing utility which would do the above?
Any help is appreciated.
Thanx in advance.
View 4 Replies
ADVERTISEMENT
Jul 30, 2007
I have a huge MDF File - 120 GB File (Had setup as 1 MDF initially) -- Did not anticipate that the DB would grow to that size!!
Anyways.. I heard that the general performance woul grow if i had them as "File Groups"..
Is there anyway - to split the existing MDF file into Mutliple files as a File Group?
Where should i start? Can someone please direct me..
View 1 Replies
View Related
Mar 12, 2007
With help of others on this group, I've been learning and researchingabout indexes; an area I neglected.I see I can specify which filegroup I wish to create an index, whichthe default is Primary.I have more than one drive in my SQL server where I put data and logson their own logical raid groups.My databases are SIMPLE, so they dont use much, if any logs (none as Iunderstand).I was thinking of adding an additional file to my database and use itsolely for the indexes.Any thoughts?SQL Server 2005 Enterprise x64 SP28 disk SAS Raid 1+0 w/ 512mb ram w/ battery backup.Thanks,Rob
View 2 Replies
View Related
Jul 14, 2015
I have a database around 500 GB. right now the database have only one data file and one log, it has only one filegroup also.all the indexes and table are placed in Primary Filegroup . we are going to separate them. the planing is to move all the indexes to Secondary filegroup and all the table will be in Primary filegroup.But there will be a problem while implementing it because there are around 600 tables and each table have at least 2 non-clustered index , so is there any way to move all the index to Secondary Filegroup.
View 3 Replies
View Related
Jul 30, 2007
Typically small companies or small projects which use SQL Server / IIS / File Shares / .NET solutions deploy these apps on a single server many times using just one IP address and the NETBIOS name of the server for both IIS / SQL. Many times there is no use of DNS and even when there is everything is mapped to the server/netbios name.
As environments grow and there is a requirement to create an n-tier server setup possible due to new security requirements, extention of the intranet to the internet, or to scale out an application you may need to split IIS / SQL into different servers. The question then becomes how do you do this without affecting existing services and you want to have minimal down time without having to change all connection information within deployed apps until you have time to review the changes carefully.
First let me begin with how to move IIS away from SQL and then I'll discuss the reverse.
The best way I've found to control the move of IIS sites is by assigning each site it's own ip address / dns host record and adding the ip address to the server network card and IIS site. Now each time you need to move 1 or more sites you can simply replicate the site to another server and change the dns ip pointer to the new pointer. If you have existing sites which were not setup in this fashion you can also move them to a new server, create a site with name of the old netbios machine name, and redirect the default web site to this new site.
Now you successfully isolated SQL Server from IIS/.NET apps you can take your time and correct the apps to use the new dns entry in connection strings instead of using the netbios name of the machine. Once you have corrected the app you can turn off any redirection.
Now let's assume you have the opposite scenario where you started off with a small server and your SQL database server now requires a server with more memory / more processors and you want to keep IIS / FileShares were they are and want to move SQL Databases to a new server. As I mentioned above a good way to do this is by setting up each database with it's own dns entry a very effective redirection mechanism. However you are may be inheriting a system which does not employ this mechanism and again uses the default netbios machine name, furthermore this may get more complicated because there may be multiple instances of SQL Server running on different port numbers.
You could use SQL Aliases on the clients to redirect connections to the newly created instances but this would require changing every pc and IIS server and it would be cumbursome to manage. It would have been nice if microsoft provided a way to redirect from the server just like IIS does but they did not. A product which will help you setup a server based redirection of ports 1433/1434 and/or any other ports is called porttunnel by steelbytes.com. Once you've installed the redirecting service on the server all new requests on port the ports selected will be redirected to the new server which I recommend be redirected to their own dns entries and this will buy the applications developer time to change their applications.
I would like to hear any comments or experiences with this that any of you may have had or if anyone has found a better solution. I'm in no way associated with the product / or company mentioned above just found it to be a good solution.
Best Regards,
Valter Borges
View 4 Replies
View Related
Jun 8, 2007
If I use the DTSMigrationWizard.exe, to migrate my existing DTS packages from SQL Server 2000 to SQL Server 2005 will it delete the DTS packages on the 2000 server?
On this one server I have 65 DTS packages and I want to migrate them all at once if possible but still keep the DTS packages on the 2000 server.
View 1 Replies
View Related
Nov 7, 2000
I need to add syntax to a stored procedure to check for the existence of a specific indexes on a table before dropping it. If they do not exist I need it to NOT through an error message. Performing this on a table is relatively simple:
if exists (select * from sysobjects where id = object_id(N'[dbo].[TABLENAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TABLENAME]
Since indexes are not represented in sysobjects how can I do this?
View 5 Replies
View Related
Nov 20, 2015
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.
View 17 Replies
View Related
May 19, 2015
partition with single file group or multiple file group which one best.
we have some report running from partition table, few reports don't have any partition Key and after creating 400 partition with 400 file group it is slow.what is best practices to crate 400 file group or single file group.
View 9 Replies
View Related
Oct 25, 2015
Currently in my environment we are using SQL server 2012.We setup Alwayson with synchronous commit.Details of existing AlwaysOn: one primary and two secondary.
Primary: On-Premise server.
Secondary1: On-Premise server.
Secondary2: Azure VM.
Requirement: We need to add Secondary3 New Azure VM on same AG with asynchronous mode or synchronous mode.
Or
We need to create one more AG on same DB and add the new replica with asynchronous.Is it possible above 2 option in this scenario? My cluster environment is Manual failover only not auto failover.
View 2 Replies
View Related
Dec 24, 2005
helloi am working on a mdf file on sql server express 2005 and i would like to migrate the content to sql server 2005.How can i do it ?
View 3 Replies
View Related
Mar 29, 2006
How do I insert data from a flat file or .csv file into an existing SQL database???
Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better way to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@Event, @Year, @ConLoss, @CompLoss, @IndLossProp, @IndLossWC, @eventsInfo)", dbconn)
insertComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
insertComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
insertComm.Parameters.Add("@eventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @Event " & _
",[Year] = @Year " & _
",[Contract Loss] = @ConLoss " & _
",[Company Loss] = @CompLoss " & _
",[IndInsured Loss Prop] = @IndLossProp " & _
",[IndInsured Loss WC] = @IndLossWC " & _
",[Event Info] = @EventInfo", dbconn)
upComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
upComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
upComm.Parameters.Add("@EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKS
View 6 Replies
View Related
Mar 29, 2006
How do I insert data from a flat file or .csv file into an existing SQL database???
Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better wway to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@Event, @Year, @ConLoss, @CompLoss, @IndLossProp, @IndLossWC, @eventsInfo)", dbconn)
insertComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
insertComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
insertComm.Parameters.Add("@eventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @Event " & _
",[Year] = @Year " & _
",[Contract Loss] = @ConLoss " & _
",[Company Loss] = @CompLoss " & _
",[IndInsured Loss Prop] = @IndLossProp " & _
",[IndInsured Loss WC] = @IndLossWC " & _
",[Event Info] = @EventInfo", dbconn)
upComm.Parameters.Add("@Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")
upComm.Parameters.Add("@IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")
upComm.Parameters.Add("@EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKS
View 3 Replies
View Related
May 15, 2006
good morning,
I want to load data that i receive everydays from my customers in .xls file format (excel) or cvs file format, to the database that i have created on this purpose. but when trying to do that whith SSIS; i got an error message .... that i can't import redudant data in my database column.
Best regards.
View 1 Replies
View Related
Jan 12, 2008
I am attempting to populate an empty table from a csv file. I tried to use BULK insert but i got an error saying it wasn't supported. I am using Visual Web Developer 2008 Express Edition and I am using the query window. If someone can direct me to a link, or give me the simple sql script to do it, that would be most appreciated.
Thanks in advance!
View 2 Replies
View Related
Apr 2, 2012
I have an excel sheet with 10 tabs (work sheets) and each work sheet contains data. I want to migrate all data once into my database table.
View 2 Replies
View Related
Jul 23, 2005
Something strange.I have a database(SQL2000) with two file group(on seperate physicaldrives).One is meant for table data[PRIMARY] and one for indexes [INDEX].So i create a table on the [PRIMARY] file group, and fill indata.Next I build a clustered index on the table, on the [INDEX] filegroup.Once the index is built, the database now indicates that the filegroupfor the table [INDEX]! and not [PRIMARY] as i originally set it up for!My question it then: Has the table been moved or is this somehow anerror in SQL server?I would really appreciate any thought anyone might have on this?
View 4 Replies
View Related
Jul 6, 2015
For a database, we have 4 data files in a particular file group and the file sizes are almost 70 GB each.
Do I come across any performance issues if I create/pre-allocate an additional data file in the same file group so that the existing files don't grow too much?
View 5 Replies
View Related
Apr 27, 2015
In a server we had File Growth,And then We had to Add New Hard Drive And New File On It.And Now We have New server with a Huge Hard Drive.But all files remaind.Can I Reduce This files to One data file or not ?
View 3 Replies
View Related
Apr 2, 2008
I am using MS SQL 2005 Express. I have a database with two filegroups, the PRIMARY default filegroup, and then filegroup X. I have a backup of the PRIMARY filegroup only, and am trying to restore it to my database. When I select the .bak file to restore from, I am NOT given any 'backup sets to restore' - I therefore cannot select the filegroup to restore it. Can anyone advise? (NOTE - I have backed up the transaction log on the database I am restoring to already)
Thanks
MArco
View 5 Replies
View Related
May 28, 2015
Can we create the SDF file from existing Database in SQL server?
View 2 Replies
View Related
Sep 22, 2000
Hi all -
Quick question... I want to move all my non-clustered indexes to their own seperate drive array. How would I accomplish this?
Do I just add the filename at the end of the statement like you do in Oracle? (EXAMPLE: CREATE INDEX IDX_Cls_Code on dbo.Class(Code) on secondary.ndf)
If this isn't correct, can someone please post the correct syntax?
Thanks!
Rich
View 1 Replies
View Related
Apr 29, 2014
Why shrinkfile empty file does not redistribute data evenly in the primary file group with multiple files:
Please run the script attached to see what the end result is.
This is what I set up last night on my test machine.
1) Create database [FGTest] size 200MB
2) Create table called TEST on primary
3) Insert 40MB of data into test
4) Create another file group called temp in primary size 200MB
5) Shrinkfile('FGTest',emptyfile) so that all data is transfered from FGTest into temp file group.
6) Add another 2 files called DATA2 and DATA3. Both are 200MB.
7) We now have 3 empty files that I want data distributed evenly on. FGTest, DATA2 & DATA3
8) Shrinkfile('temp',emptyfile) to move all the data from temp over the 3 file groups evenly
I would expect at this stage to have the following:
FGTest = 13MB,
DATA2 = 13MB,
DATA3 = 13MB
(40MB of data over 3 files should be about 13 MBish in each file)
What I actually end up with is this:
FGTest = 20MB
DATA1 = 10MB
DATA2 = 10MB
It looks as though SQL Server is allocating 50% of all data to the original file and then 50% evenly over
the remaining files in PRIMARY.
View 3 Replies
View Related
Aug 12, 2005
I need to populate tables in my MS SQL 2000 DB with content from an excel file. I am not sure how this is done or how to format the excel file. If someone could help me with this it would be much appreciated!Thanks!
View 3 Replies
View Related
Jan 3, 2007
I'm having a problem. When I use the SQL query to make a backup of the database, it worked fine. But everytime I use it, the backed-up file's size kept growing in size. Say I have the file, test.bak whose filesize is 450 MB then I run a new backup to overwrite the existing test.bak file, it just end up as 900 MB. If I run it again, it become 1350 MB and so on.
Is there a way to prevent that from happening?
View 1 Replies
View Related
Jan 16, 2008
This is a SQL question while using Microsoft SMS.
I am trying to write a SQL query looking for a specific file in a specific directory. If the File does not exist, then I want it to display.
here is the Code that I have for it that shows that the file exists... how do I make it show that the file doesn't exist?... what am I doing wrong?
Thanks for all your help in advance!
**********************************
SELECT TOP 100 PERCENT SYS.Netbios_Name0, SYS.User_Name0, SF.FileName, SF.FileVersion, SF.FileSize, SF.FileModifiedDate, SF.FilePath,
SYS.Operating_System_Name_and0
FROM dbo.v_GS_SoftwareFile SF INNER JOIN
dbo.v_R_System SYS ON SYS.ResourceID = SF.ResourceID
WHERE (SF.FileName = 'UdaterUI.exe')
AND (SF.FilePath = 'C:Program FilesMcAfeeCommon Framework')
OR
(SF.FileName = 'UdaterUI.exe')
AND (SF.FilePath = 'c:Program FilesNetwork AssociatesCommon Framework')
ORDER BY SF.FileVersion, SYS.Netbios_Name0
View 1 Replies
View Related
Jan 22, 2015
I have a SQL 2012 enterprise server, and I'm using Commvault as my backups. So commvault can restore a .bak file to my server, but it cannot use sql compression on the file apparently. So what would be a 150GB .bak backup file is now 600GB. I have to manually upload these files to an auditing firm on an sftp server and the transfer times are now huge.
Is there a way to use something in sql to compress this already existing .bak file down?
View 5 Replies
View Related
Jan 29, 2015
I need to add an existing shared folder to a SQL FileTable.So this is the path and I created a SQL Filetable and now I need to add it to the filetable.
View 0 Replies
View Related
May 9, 2007
Hi,I need to output result of my query to txt file. So I'm using -oparameter, for example:osql.exe -s (local) -d database1 -U sa -P sa -i 'c:\queryFile.sql' -o'c:\output.txt'But it clears existing output.txt file first and then outputs theresult, while I need to append the result of my query without clearingexisting file content. Is it possible ?
View 1 Replies
View Related
Jan 1, 2006
I have a data file, call it Data.mdf, that I am accessing through Visual Studio. It does not show up in SQL Server 2000 Enterprise Manager.
How can I get it assigned to, or imported into, the local server so I can manage it?
Thanks
Glen
View 6 Replies
View Related
Jun 18, 2007
I want to use an Active Directory security group that is a Distribution List for a new role assignment for an existing report. Can someone tell me if this is possible? I get an error each time I try:
The user or group name <DLName> is not recognized. (rsUnknownUserName)"
View 1 Replies
View Related
Jan 29, 2015
I need to add an existing shared folder to a SQL FileTable. So this is the path and I created a SQL Filetable and now I need to add it to the filetable.
View 0 Replies
View Related
Mar 16, 2015
I am trying my first bulk update to an existing SWL table from a CSV text file,The text file naming is exacrtly the same as the SQL table, with the same attributes
The statements:
BULK INSERT [Jedox_prod].[dbo].[B_BP_Customer]
FROM 'c:Baanjedox_dailyjdcom4401.txt'
WITH
[code]....
The error message is:
[size=1Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 3 (BP_Country).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".size=1]..The have checked and re-checked the BP_Country field ( the 1st field after the key) and I am not seeing any mismatches.
View 5 Replies
View Related
