I need to bulk insert very large amount of data into several MSSQL
tables.
The first Data model definition used identities to mantain relationship
between those tables but we found that natural keys (compound) are
better for
bulk insert (there is no need to obtain the identity first)
My question is, changing the identities to natural keys (in some tables
in
order of 4, 5 attributes) will enlarge my database storage?
I think MSSQL implements relationships with pointers (or hashcodes), so
the
storage size will be similar, right?
Can anyone explain to me how a column defined with a "bit null" datatype is physically stored in MSSQL? Is it stored like a "tinyint null" physically? In other words, how many bytes on the row on the page does a "bit null" datatype consume (assuming a non-null value 0, or 1 is the current value).
Is there any good documentation about the physical storage layout for a data page?
I am a Windows developer for the IBM Tivoli Storage Manager Server (TSMS) product. Our product installation is built with InstallShield and uses the Windows Installer.
On a new installation of Windows 2003 x64 Storage Server R2, at a customer's site, the TSMS product fails to install. The install of the OS has version 3.01.400.3959 of the Windows Installer and I see no newer version that installs.
Part of our product is 32 bit (console) and another part is x64 (server). When installing I can see that the install's default is being redirected/reset to C:Program Files (x86)TivoliTSM after it is explicitly set by a custom action to ..Program Files.. . I further observe that our custom actions to write 64 bit registry entries are being refused.
REGSAM samMask = KEY_ALL_ACCESS; if ( regIsWow64Process () ) samMask = samMask | KEY_WOW64_64KEY; lStatus = RegCreateKeyEx( hLocalConnectKeyRoot, szSubkey, 0L, NULL, REG_OPTION_NON_VOLATILE, samMask, NULL, hKey, &dw ) ; The above fails to create the key.
We have tried four versions of our TSMS spanning many changes but the install acts the same. This does not happen on any other Windows OS we test on but we do not test on Windows 2003 Storage Server R2 being that it is an OEM product. We did test on Windows server 2003 R2 x64 and do not see this problem.
Do you have any suggestions on how to tackle this problem? I have full installation traces but can only see that the registry work is being refused. I can't see why.
How to implement distinct storage tiers on SQL Remote BLOB Storage (RBS)?
I want to use this SQL Feature to move files(images, videos, pdf files) from a database to a distinct database dedicated to RBS. Then I want to have several storage tiers, where objects will be saved and moved according access frequency. Old data will be arquived in cheap storage, but it must be always accessible if needed.
Description: - 1st and main tier: new and frequently accessed objects stored in high performance storage; - 2nd tier: automatically move older or less accessed objects to an inexpensive and different storage tier; - in all cases, all objects must be accessible to all users, but accessing to archived objects(2nd tier) will be much slower;
Hi! I have an SQL server installation with a 6.5GB database online at a particular location. I need to move the Database to another location which is a 1000 Miles away.
I have an additional physical Server at the other location which I can use to Sync the database from the original location. Can anyone please guide me to the best strategy to sync the database from the original location to the new location with minimum downtime??
Hello all,We are in the process of upgrading our SQL physical server (with SS2k). Inthe process we will change the OS form NT4 to W2K. What is the best way tocopy all my databases and SQL logins, roles, jobs, alerts, etc. from myactual (old) SQL Server to my new one?Thanks for your time.Yannick
I've recently started working with a public sector organisation who have 4 clustered sql instances that has 80% of it's db mirrored.
Looking at the transaction log - it seems that a transaction log backup is a good idea as the log is 4x larger than the data file.But I'm not allowed access to the physical server to check onto which drive I can create the trn. No RDP, no vmware - let's be honest I'm not even allowed to launch cmd line Also the Server Manager informs me "We will need to carefully look at database backups if you guys want to start doing these backups on box, as that will break our off box backup routine (it will screw the transaction chain)."
I don't understand how backing up the transaction log could break the "transaction chain"?
Re: Best Practices (security): Should SQL Server (2005) *not* be installed on the same physical HD as the Windows OS (Server 2003 R2) ?
Hi,
We're setting up some new servers, and today I'm looking into best practices for the SQL Server Setup portion of it.
The servers have include 2 x 250G HD, and from what I've read, where IIS is concerned, it should not be installed on the drive that has the OS on it, for security reasons. I was wondering if the installation of SQL Server should be on the non-OS drive as well ?
Hi All, Is it possbile to define connection string for web and database server which is running on different machine. Note( Not same LAN). E.g (My Web Server is in London and Database server in Sydney). Please can any one help me.?? Thanks in advance...
Hello! What is the best way to make a copy of an existing sql server database to another (physical) server? Plan to make a full backup of another sql server database to another server. I've read about detach and attach and copying the datafiles and log files but some say it is prone to data loss? Is this true?
And another thing, what if the existing sql server can't affor downtime for me to copy db files etc.?
Hi,We have a situation where we want to move our current databaseserver to a different hardware and rename the server.If we change the Physical server name, do we have to go thru the wholeprocess of changing it in sql server by running sp_dropserver,sp_addserver?Can we have an alias for the server name in DNS and can sql serverresolve the server name internally by going thru DNS?For eg: Our current database server name is FFSQL-PRD01. We have itregistered in EM as FFSQL-PRD01. Now we renamed the server toSTLSQL-PRD01, and added an entry in DNS for STLSQL-PRD01 with alias ofFFSQL-PRD01( the same old name as alias). After doing this when I goto EM and click on FFSQL-PRD01 would it give me an error? or can itresolve the server name by going to DNS.When you install SQl server the default instance has to be the physicalserver name, Why? Is the physical server name stored in Masterdatabase( SYSSERVERS table)?Thanks for your help.Geetha
I want to compare the filepath column in table with physical drive files and get the details of files which in table and not in physical and viceversa...
We have a server running SQL Server 2000 Standard Edition on a Windows 2003 Standard Edition Server. Can we install SQL Server 2005 Standard Edition on the same box? I'm assuming that so long as the instance names are different that it should not be a problem.
I am trying to figure out the best method for transferring SQL 2005 databases from 2 different physical servers to a new server running windows server 2008 64bit.I own a fully registered copy of Microsoft SQL 2005 32bit Standard Edition. However all of my SQL databases together are only 4GB in size thereby making 2008 SQL Express an option.
Here is the breakdown of the servers:
1) (Old Server1) Windows 2003 Server 32bit - SQL Express 2005 32bit 2) (Old Server2) Windows 2003 Server 32bit - SQL Server 2005 32bit Standard 3) (New Server) Windows 2008 Server 64bit - (Not sure which version to install)
**The objective is to migrate 2 SQL databases from Old Server1 and 1 SQL database from Old Server 2 to the New Server. What version should I install on the new server??? Do I go with 2008 express or install the 2005 Standard Edition?
Pending your response to which version to install, what are the correct steps to migrate (which I have never done before) the databases from both of these servers to the new server? I think this particular situation is unique in that I am transferring from both SQL Express and Standard from 2 different physical servers to a 2008 server that is 64bit.
I'm in the situation where we are suffering of poor performance on our SAN storage (VPlex) but it is mainly due to the quantity of data of different types which are on it (other applications, other I/O profile, bad storage usage...). As we plan to dedicate an ESX for SQL Server, we decide to have a new storage type. So we will go with NETAPP Clustered Data ONTAP on NAS technology.
Storage team want to enable only NFS protocol, so I'm wondering how SQL Server will handle that ? I read that NFS wasn't optimize for SQL Server and that block level (SAN) should be preferred.
i need to know where the database table stored in, where to find the data table without distrubing the sql server editor.whtr it is possible to get it from "C:Program FilesMicrosoft SQL ServerMSSQLData" or some where else.
I Run All checks for Validation cluster.I get Error On Disk Lists And Validation failed.With This error : Failed to prepare storage for testing on node "server name" The security account manager (SAM) or local security authority (LSA) server was in the wrong state to perform the security operation.
Whats the limitations of data storage in sqlserver DB. How will be the perforamcne if i have database which will get a data of 400 GB per year and all the data should be there in the table the whole year and then it can be archived.
I've been trying to get a definitive answer to this question but alas I have conflicting and patchy answers so far from other sources. I have an index that, lets say, requires 10GB of data space to rebuild..This index resides on a filegroup that spans 2 files on two seperate drives (i.e. a mdf and ndf)
When I rebuild this index how will each of these datafiles grow as the rebuild proceeds to completion? Lets for the time being remove the caveats of any other activity hitting the example index/database in question.My tests seem to show that only the mdf will grows (or the file with the lowest id in the that filegroup) provided there is enough space available in that particular file to complete the operation. The secondary ndf dat file doesnt grow at all if the mdf has enough space.
Is expected behavior? i.e. the index will be rebuilt in a contiguous manner relative to the files contained with the filegroup i.e. fileid 1 will grow till limit reached then next fileid grows etc?
I am using SQL server 7 with ASP. I have two working environment means one is korean and second it english. - one Korean OS server have SQL server 7.0 and it is my database server - second Korean OS server is only webserver - English OS is win2k and it is only Web server.
1) When i used both Korean server as my webserver + database server then there is no problem to add Korean Data to SQL server On korean OS.
2) But when I try to user English OS server as my webserver and Korean Os server as my database server then I am not able to store Korean Data in Database server insted of it stored some mis/junk/acssi characters in database.
-- I allready try with Korean version of MDAC of English os -- I also try with OEM feature in SQL server client network utility -- When I am use CODEPAGE in my .ASP page then data storage work fine .. but at the time of getting it back there is problem.
If u need any more information about problem then let me know.
So please help me in this regards.
Thanx in advance Anis Vora Partner Global SoftWeb Solutions www.globalsoftweb.com
Will both statements above access only a single page (as it fits into one page) or does the VARCHAR(MAX) always put its data on a separate page. If so, is the null insert treated differently from the '' insert?
I'm aware that it's best practice to separate mdf and ldf files onto separate drives.
However, I see a lot of servers where the underlying disk array is the same for drives on the server.
Is there still any performance benefit to separating mdf and ldf files in this situation?
For example, a single virtual server running SQL Server, with multiple drives attached.All of the drives are connected to a shared storage via iSCSI.There drives C:, D:, E: etc are all actually sharing the same underlying disks.
Obviously, there are some benefits from an administration perspective whereby individual drives can be reconfigured without affecting the others.
Hi all,I'm getting this error when trying to import data from a text file intoSQL Server 2000 (Windows Server 2003) using the DTS import wizard.Any ideas what could be causing this? There aren't any restrictions(that i can find) on the file sizes etc.Thanks in advance.Dave
