March 2, 2016 at 9:19 am
GilaMonster (3/2/2016)
szejiekoh (3/2/2016)
T3 - user created some objects (will this objects be recorded in system database ?)No, they will not be in the system databases.
The system DBs store database info (what databases are around), login info (passwords, server-level permissions), jobs, backup history, stuff like that.
Hi GilaMonster,
Thanks for the prompt reply!
How about the object/tables' statistics (e.g. no of rows, row size etc) and structure information ? (to be use for performance tuning and optimizing query plans ?)
Are they not store in the system databases too ?
Also, you mentioned the system databases store database info (what database are there).. so if i restore a T2 system database and restore a T4 user database, T2 system database will not have T4 user database information - will that cause any issue ?
Regards,
Noob
March 2, 2016 at 9:20 am
szejiekoh (3/2/2016)
GilaMonster (3/2/2016)
szejiekoh (3/2/2016)
T3 - user created some objects (will this objects be recorded in system database ?)No, they will not be in the system databases.
The system DBs store database info (what databases are around), login info (passwords, server-level permissions), jobs, backup history, stuff like that.
How about the object/tables' statistics (e.g. no of rows, row size etc) and structure information ? (to be use for performance tuning and optimizing query plans ?)
Are they not store in the system databases too ?
No. The system DBs store database info (what databases are attached to the instance), login info (passwords, server-level permissions), jobs, backup history, stuff like that.
its seems that there isn't much concept information about SQL server from Microsoft.. - am i not searching thoroughly enough ?
You're not searching thoroughly enough. There's a tonne of info on SQL architecture, and not just from Microsoft.
so if i restore a T2 system database and restore a T4 user database, T2 system database will not have T4 user database information - will that cause any issue ?
Why would it?
By restoring the user database, you're putting the database-related info into the system DB. Doesn't matter in the slightest if there was any info for that DB before the restore or not. If there was, it gets overwritten when you restore the user DB. If there wasn't, it gets added when you restore the user DB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 2, 2016 at 9:23 am
GilaMonster (3/2/2016)
szejiekoh (3/2/2016)
GilaMonster (3/2/2016)
szejiekoh (3/2/2016)
T3 - user created some objects (will this objects be recorded in system database ?)No, they will not be in the system databases.
The system DBs store database info (what databases are around), login info (passwords, server-level permissions), jobs, backup history, stuff like that.
How about the object/tables' statistics (e.g. no of rows, row size etc) and structure information ? (to be use for performance tuning and optimizing query plans ?)
Are they not store in the system databases too ?
No. The system DBs store database info (what databases are attached to the instance), login info (passwords, server-level permissions), jobs, backup history, stuff like that.
Hi GilaMonster,
Sorry to miss your last reply.
You mentioned the system databases store database info (what database are there).. so if i restore a T2 system database and restore a T4 user database, T2 system database will not have T4 user database information - wouldnt' that cause any issue ?
Is there anywhere to read about what system databases stored ?
Regards,
Noob
March 2, 2016 at 9:25 am
szejiekoh (3/2/2016)
You mentioned the system databases store database info (what database are there).. so if i restore a T2 system database and restore a T4 user database, T2 system database will not have T4 user database information - wouldnt' that cause any issue ?
Um, answered above...
Is there anywhere to read about what system databases stored ?
Yup, the SQL documentation, known as Books Online. Or you can query the system DBs and see what's in them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 2, 2016 at 9:31 am
GilaMonster (3/2/2016)
szejiekoh (3/2/2016)
You mentioned the system databases store database info (what database are there).. so if i restore a T2 system database and restore a T4 user database, T2 system database will not have T4 user database information - wouldnt' that cause any issue ?Um, answered above...
Is there anywhere to read about what system databases stored ?
Yup, the SQL documentation, known as Books Online. Or you can query the system DBs and see what's in them.
Hi Gila!
Thanks for the reply and explanation !
Just to double confirm.. the "Books Online" you are referring to , are those on the Microsoft Technet library yea ?
Regards,
Noob
March 2, 2016 at 10:04 am
The URL for the SQL 2014 Books Online is https://technet.microsoft.com/en-us/library/ms130214(v=sql.120).aspx If were all printed out it would be 10cm thick or more. You should find most of your answers there, but sometimes there is so much information available it can take a while to find what you need.
SQL Serve also has a very active online community. You are connected to one of the best sites, but there are others that you can easily find. Other good sources of information are local user groups, most of which are free to join. There are many free or low cost conferences. Google for 'SQL Saturday' and the name of the country or city you are in, and you may find something near you. In the UK (and for most of Europe) the largest conference with a free day is SQLBits. Google is an excellent but slightly dumb assistant DBA that can find advice and fixes for many of your problems, but occasional it can be frustrating when you cannot get it to focus on the right answers.
To add a few points to previous replies, each database has its own set of system-created tables that holds its own metadata. This includes schema names, table definitions, etc, in fact any object that forms part of the database.
The system databases hold just the stuff that needs to be shared across the instance. When you restore a user DB, the instance-level metadata it needs is automatically created.
For logins, the system databases hold the user name and a flag to say if they are Windows accounts or internal SQL Server accounts, and if they are internal the password is also held. A given user database would just hold the login name and the permissions that apply to that database. Books Online should help clarify all of this.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 2, 2016 at 12:27 pm
szejiekoh (3/2/2016)
so if i restore a T2 system database and restore a T4 user database, T2 system database will not have T4 user database information - will that cause any issue ?
Though Gail already addressed this specific issue, let me add that it is indeed possible to do funky stuff by deliberately messing with the system database.
For example:
1. Backup master (the system database that holds information about all databases)
2. Create a new user database
3. Restore master - now SQL Server no longer knows about the database you created and its files are orphaned on the file system and have to be removed manually,
Or:
1. Backup master
2. Drop an existing user database - this will remove the entry from master and remove the files from the file system
3. Restore master - now SQL Server thinks the user database exists but cannot find the files, so it will be marked as suspect.
Neither of these will impact the stability of the SQL Server instance, or of any of the other databases - just the database you were playing around with.
March 3, 2016 at 9:22 am
hi all,
Thanks for the valuable inputs and advises. You folks here are simply amazing and contributing to the community by sharing information and knowledge here. 100 thanks for that.
I shall end my question here and proceed to dive into the documentation. Its kind of exciting, picking up a knowledge. :w00t:
Last question here, is choosing and marking 1 correct post as the correct answer the only choice in the thread ?
Regards,
Noob
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply